Grant Execute Rights To All SPs

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_%’

 

SQL Server Query Results To Excel

There are many times when I just want to run a query and send the results to someone in an excel spreadsheet.

You can easliy copy the and past the results, but you don’t get column headers, and you lose formatting in many cases.

Dinakar Nethi has a good article about how to directly import the results of a query into excel.

I just used his method and it worked great.

Format String

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>

Some options for converting DATETIME in SQL to different formats using CONVERT()

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)

SQL Server's Stupid "Decimal" DataType

Ok so, on a recent project, for some reason, I set up a few fields, and the corrosponding SP parameters as “decimal” datatypes. What a mistake.

After lots of rounding errors, which were partly ignored because the revised “specs” called for these figures to be integers (later changed to decimals), I found out that a Decimal datatype has no decimal places, unless you specifically declare it.

The reason for this is that a “decimal” is really just another name for a “numeric”. So I guess I can understand that part… but how dumb is it that a decimal, by default, has no decimal places.

I went back and changed everything to float.

Use decimal(x,x) instead of float.  See here for the reason why.