Chris May

This is some useful stuff from 4guysfromrolla.com. 

BEGIN TRY
   BEGIN TRANSACTION    -- Start the transaction

   -- Your code here

   -- If we reach here, success!
   COMMIT
END TRY
BEGIN CATCH
  -- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Every now and then I go to my blog to lookup something I wrote before only to find that I never posted it.

I would have bet someone $20 that I had posted something on how to strip off the time from a DateTime in SQL.  Why would you do this?

Well, lets say that have some autogenerated DateTime field (CreatedOn?) that you later want to join with another field, but the other field only knows the DATE when something happened, not the exact Date and Time.

So, you can use the following code to just get the DATE and zero out the time component:

SELECT CAST( CONVERT( CHAR(8), GetDate(), 112) AS DATETIME)

When you are trying to get the last Identity value in SQL Server, the old way was to use the @@IDENTITY variable.

This, however, can easily give you the wrong answer back.  For example, if you have a trigger inserting a history record that also contains an identity column.

Here is an article that discusses why you should probably stick to SCOPE_IDENTITY().

The problem is that if you restore a sql server database on a different computer than the backup was created, you can have the problem that the usernames and permissions are somewhat lost.

Each username has an associated SID.  So even though you might have a user named 'Bill' and the restore database might have a user named 'Bill', they will have different SIDs and so the permissions will not be applied to the correct user.

To fix this problem you can follow the following steps:

1) run this TSQL:

sp_change_users_login 'report'

2) This will produce a table showing you Users that having issues.

3) run the following TSQL:

sp_change_users_login 'update_one', 'UserNameFromStep2Table','NameOfUserToUse'

In many cases the 2nd and 3rd parameters will be the same string.

More about this SP can be found here: http://doc.ddart.net/mssql/sql70/sp_ca-cz_4.htm

 

Many times you can have a database that has access to it restricted to SPs.  If the database is application specific, then there is a good chance that 1 user account will need to access all these SPs to run the application.

My friend Phil found and updated this script to run in SQL Server 2005 to generate the necessary code to grant execute rights to all SPs in a database for a given user.

SELECT 'GRANT EXECUTE ON ' + sysobjects.name + ' TO AccountName' + 

CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) 

FROM sysobjects 

WHERE type = 'P' and category = 0 AND name not like 'sp_%'

 

The following content has been cut and pasted from Kathy Kam's Blog. The article was Format String 101.


 

I see stuff like {0,-8:G2} passed in as a format string. What exactly does that do?" -- Very Confused String Formatter

The above format can be translated into this:

"{<argument index>[,<alignment>][:<formatString><zeros>]}"

argument index: This represent which argument goes into the string.

String.Format("first = {0};second = {1}", "apple", "orange");

String.Format("first = {1};second = {0}", "apple", "orange");

 

gives the following strings:

 

"first = apple;second = orange"

"first = orange;second = apple"

 

alignment (optional): This represent the minimal length of the string.

Postive values, the string argument will be right justified and if the string is not long enough, the string will be padded with spaces on the left.

Negative values, the string argument will be left justied and if the string is not long enough, the string will be padded with spaces on the right.

If this value was not specified, we will default to the length of the string argument.

 

String.Format("{0,-10}", "apple");      //"apple     "

String.Format("{0,10}", "apple");       //"     apple"

format string (optional): This represent the format code.

Numeric format specifier is available here. (e.g. C, G...etc.)
Datetime format specifier is available here.

Enumeration format specifier is available here.

Custom Numeric format specifier is available here. (e.g. 0. #...etc.)

 

Custom formatting is kinda hard to understand. The best way I know how to explain something is via code:

 

int pos = 10;

int neg = -10;

int bigpos = 123456;

int bigneg = -123456;

int zero = 0;

string strInt = "120ab";

 

String.Format("{0:00000}", pos);      //"00010"

String.Format("{0:00000}", neg);      //"-00010"

String.Format("{0:00000}", bigpos);   //"123456"

String.Format("{0:00000}", bigneg);   //"-123456"

String.Format("{0:00000}", zero);     //"00000"

String.Format("{0:00000}", strInt);   //"120ab"

String.Format("{0:#####}", pos);      //"10"

String.Format("{0:#####}", neg);      //"-10"

String.Format("{0:#####}", bigpos);   //"123456"

String.Format("{0:#####}", bigneg);   //"-123456"

String.Format("{0:#####}", zero);     //""

String.Format("{0:#####}", strInt);   //"120ab"

 

While playing around with this, I made an interesting observation:

 

String.Format("{0:X00000}", pos);      //"A"

String.Format("{0:X00000}", neg);      //"FFFFFFF6"

String.Format("{0:X#####}", pos);      //"X10"

String.Format("{0:X#####}", neg);      //"-X10"

 

The "0" specifier works well with other numeric specifier, but the "#" doesn't. Umm... I think the "Custom Numeric Format String" probably deserve a whole post of it's own. Since this is only the "101" post, I'll move on to the next argument in the format string.

 

 

zeros (optional): It actually has a different meaning depending on which numeric specifier you use.

 

int neg = -10;

int pos = 10;

 

// C or c (Currency): It represent how many decimal place of zeros to show.

String.Format("{0:C4}", pos);      //"$10.0000"

String.Format("{0:C4}", neg);      //"($10.0000)"

 

// D or d (Decimal): It represent leading zeros

String.Format("{0:D4}", pos);      //"0010"

String.Format("{0:D4}", neg);      //"-0010"

 

// E or e (Exponential): It represent how many decimal places of zeros to show.

String.Format("{0:E4}", pos);      //"1.0000E+001"

String.Format("{0:E4}", neg);      //"-1.0000E+001"

 

// F or f (Fixed-point): It represent how many decimal places of zeros to show.

String.Format("{0:F4}", pos);      //"10.0000"

String.Format("{0:F4}", neg);      //"-10.0000"

 

// G or g (General): This does nothing

String.Format("{0:G4}", pos);      //"10"

String.Format("{0:G4}", neg);      //"-10"

 

// N or n (Number): It represent how many decimal places of zeros to show.

String.Format("{0:N4}", pos);      //"10"

String.Format("{0:N4}", neg);      //"-10"

 

// P or p (Percent): It represent how many decimal places of zeros to show.

String.Format("{0:P4}", pos);      //"1,000.0000%"

String.Format("{0:P4}", neg);      //"-1,000.0000%"

 

// R or r (Round-Trip): This is invalid, FormatException is thrown.

String.Format("{0:R4}", pos);      //FormatException thrown

String.Format("{0:R4}", neg);      //FormatException thrown

 

// X or x (Hex): It represent leading zeros

String.Format("{0:X4}", pos);      //"000A"

String.Format("{0:X4}", neg);      //"FFFFFFF6"

 

// nothing: This is invalid, no exception is thrown.

String.Format("{0:4}", pos));      //"4"

String.Format("{0:4}", neg));      //"-4"

 

In summary, there are four types of behaviour when using this <zeros> specifier:

Leading Zeros: D, X

Trailing Zeros: C, E, F, N, P

Nothing: G

Invalid: R, <empty>

 

Now, that we've gone through the valid specifiers, you can actually use this in more than just String.Format(). For example, when using this with Byte.ToString():

 

Byte b = 10;

b.ToString("D4");      //"0010"

b.ToString("X4");      //"000A"

 

Wow... this was way longer than I expected. The BCL team is having blog day today, I need to get back to posting something for the BCLWeblog.

<Editorial Comment>

One of the lesson I learnt from an earlier post is that, readers are not interested in a post that doesn't give you more information than what MSDN provides. Instead, readers are more interested in seeing stuff that are not available on MSDN. So when I was doing research to post about this topic, I found that MSDN actually talks about exactly what the {0,-8:G2} format does. It is just not easy to find nor centrally located.

For example, in the ToString MSDN Doc, the "Remarks" section covered some basic rules on what a "format string" is. In the String.Format MSDN Doc, the "Remarks" section actually have a pretty detail explaination of what the above format does. Furthermore, MSDN provides a format string overview as well as a the table that specifies all the values that are allowed.

This puts me in an interesting position when writing about this topic. MSDN actually have lots of info that cover it. But since I have also heard more than one person being confused about this topic, I decided to post a summary of the documents and more examples. Do you think this is useful? Should I just stick to posting exclusively on non-MSDN topics?

</Editorial Comment>

To see the effects, just run this script against your database:

PRINT '1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +
CONVERT(CHAR(19),GETDATE()) 
PRINT '2) HERE IS MM-DD-YY FORMAT ==>' +
CONVERT(CHAR(8),GETDATE(),10) 
PRINT '3) HERE IS MM-DD-YYYY FORMAT ==>' +
CONVERT(CHAR(10),GETDATE(),110)
PRINT '4) HERE IS DD MON YYYY FORMAT ==>' +
CONVERT(CHAR(11),GETDATE(),106)
PRINT '5) HERE IS DD MON YY FORMAT ==>' +
CONVERT(CHAR(9),GETDATE(),6)
PRINT '6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>' +
CONVERT(CHAR(24),GETDATE(),113)

In case I haven't put this in here before.

Here and here are 2 articles from SQL Junkies.

The first deals with the .NET CLR and it's role in the upcoming version of SQL Server, called Yukon.

The second talks about a tool that you can use to run a stress test on a SQL Servers disk setup.

This article discusses a way to generating a list of create scripts that are in order of dependency, so you don't get all those errors in the QA when you run the script.

Originally posted on 2/4/04, I should revisit this.

Here is a pretty good white paper by a MSSQL MVP about using dynamic SQL in SPs.

This article shows how you can Add SQL Server 2000 Stored Procedures to Visual SourceSafe by Using Visual Studio .NET

Finally found this.

This link points to the getting started page. If you look down the tree on the left, you can see the TSQL Reference node.

SPInfoke is a tool that allows you to call SPs as static methods in a .NET class. I guess it won some awards and stuff, but I don't think (just from looking at the article) that is is nearly as good as the tools from Ellkey.com who's StoredProcToDotNet.htm wraps the SQLHelper classes in the MS Application Blocks for Data Access. They also offer a SP generator called the StoredProcBuilder.