Get rid of padded spaces in queries attached with sp_send_dbmail

Have you tried to use sp_send_dbMail to send the results of a query as a csv file attachment?
If so, and it included some character fields, you have probably seen that the resulting file looks similar to this:

RecordId,Description                          ,category  
--------,-------------------------------------,----------
       1,banana                               ,        24
       2,strawberry                           ,       189

This is fine to view on a wide screen, but a pain to view on a narrow display. Also if you are sending a lot of data, the file will be much larger than necessary due to all the extra characters.

But there is an undocumented parameter for sp_send_dbmail that removes these spaces, and that is:

@query_result_no_padding = 1

Add this to your sp_send_dbmail statement, and suddenly your result set is transformed to just

RecordId,Description,category
--------,-----------,--------
1,banana,24
2,strawberry,189

This produces a much smaller file attachment size, and means you are less likely to be accused of bringing down the company's email server by sending it!

SQL 2008 - Where did Surface Area Configuration go?!

No sooner had we got used to opening the oddly titled "Surface Area Configuration" tool to change certain options on SQL 2005 servers that used to be on the properties tab in SQL 2000 (e.g. enabling xp_cmdshell), than it has moved again.

SQL 2008 brings us a new description of these items, and we must now call them a "Facet" ... and no, that's not another name for a tap.

However, on the plus side, you can now access these through the 2008 Management Studio.  Simply right click on your server name in the object explorer pane, and select "Facets" from the context menu.

This brings up the "View Facets" display.  From here you can select "Surface Area Configuration" from the Facet drop down menu, and enable or disable your required options directly from here.

The other good thing ... this also works for SQL 2005 servers, whereas alternative methods for doing this (e.g. Policies) don't.

Domain User cannot edit a SQL Job they created or own

I have come across a situation in SQL management studio where a windows domain user is unable to edit a job that they created themselves, and are still the owner of.

It seems that this is one of the rare occasions when SQL logins are case sensitive on the username.

When creating a job, SQL sets the job owner to be the name field from syslogins associated with the current user.

However, when editing a job, SQL checks the current user with SUSER_SNAME (which gives the actual domain login) and does a case sensitive comparison with the job owner.

Example for the slow ones at the back - This means if you have created a SQL login for user “GrumpyDomain\Grumpy Dba”, and he creates a job, that is the value that will be assigned to the job owner.

However, if that users account is actually “GrumpyDomain\Grumpy DBA”, then they will be unable to edit that job.

To fix it you can use ALTER LOGIN to change the account, e.g.

ALTER LOGIN [GrumpyDomain\Grumpy Dba], [GrumpyDomain\Grumpy DBA]

What makes me really grumpy is the fact that we are running this to fix an issue with logins being case sensitive, but this ALTER LOGIN statement itself isn’t actually case sensitive, so even this would work …

ALTER LOGIN [GrumpyDomain\GrUmPy dBA, [GrumpyDomain\Grumpy DBA]

Nice to have consistency.

Why Grumpy DBA?

People often ask me why I'm grumpy - well they don't actually - but that's probably because no-one likes talking to me because they think I'm grumpy.

I have been called other things, and of those that I can publish, one that I remember was "the Tourettes DBA". This title was coined by an old manager who did on occassion manage to wind me up a little - and there may have been one or two times when I pointed out that I disagreed with his opinion, or that his suggested course of action may not be the most beneficial method of solving the problem in hand.

The fact I "pointed out" this by shouting across an entire open plan office as he was about to march into the server room to reboot a server, and may have used a couple of "colourful" words, should in no way be used as evidence of grumpiness.

Good ways to make a DBA grumpy though are

• say you need access to a database, but not which server.
• tell them you can't access "the database". DBA's are of course mind readers and know exactly what every person in the entire company is working on, however these powers fade from time to time (usually due to lack of caffeine and/or alcohol).
• similarly don't tell him "my proc doesn't work" or "I can't access the table" 

Be careful or you may find your "friendly" DBA creates a database called "TheDatabase", populated with "MyProc" and "TheTable" and pedantically shows you that you can indeed access "TheDatabase", and "MyProc" does indeed run, and that you can access "TheTable" ... or maybe that was just me!!!

Generally though I'm not really that grumpy ... ask my kids.

Gits.

Add your own Management Studio Shortcut Keys

One of the things I like about SQL Server Management Studio (SSMS) is being able to customise some of the short cut keys - but it's one of those things that when someone sees it, and asks how you set it up, it's a pain to remember!

So for those of you who have a new installation and have forgotten how to set up a short cut for your favourite proc, or those new to this keyboard shortcut feature, here is how you do it.

Select "Options" under the "Tools" menu on the SSMS menu bar - and just because I can, I'll even show this to you:

With that selected, a new window titled "Options" will open.
In here expand "Environment" and then "Keyboard" and you should see the right side of the window display "Query shortcuts".

Now you can enter procs against the available Ctrl+x shortcuts - note that a few are fixed (like Alt+F1 as sp_help), but there are 7 or 8 that you can assign a proc against.

Note that these run in the context of the database you are connected to - so unless the proc you create or assign is an "sp_" prefixed proc that exists in the master database, it must be in the database you are connected to.

Once this is done, click OK, and open a new query window (won't work for windows already open as far as I remember).  Now depending on whether the proc you assigned needs parameters or not, you can simply press your shortcut key (e.g. Ctrl+3) to run it instantly, or highlight the relevant parameters (e.g. a table name) and hit the shortcut key.

Get SQL table column names in a string

SELECT * is all well and good for quick debugging and manually run statements, but when you are inserting the results into a table within a stored proc, the dangers of a SELECT * and the problems caused by changes to the underlying table should be all too obvious.

However, picture the scene - a Friday afternoon and all you want to do is head to the pub, but you know you should finish your code properly.  You have a SELECT * statement in a proc or a view, but it pulls all the fields from a table with over 100 columns ... you're not really going to start typing every field name are you?  Well I'm not - so I would hit ALT-F1 on the table name, cut and paste into excel and add commas - or do a select name + ',' from syscolumns and paste the result into my code.  However, even that is too much effort for a lazy DBA like me, so I bring you <fanfare> sp_tablecolumns.

CREATE PROC sp_tablecolumns @object varchar(100)    AS    SET NOCOUNT ON    DECLARE @STRING VARCHAR(1000)    SET @STRING = ''    SELECT @STRING = @STRING + '[' + name + '], '    FROM syscolumns WHERE id = OBJECT_ID(@object)    ORDER BY ColOrder    SELECT LEFT(@STRING, LEN(@STRING) - 1)

Stick this in the master database for it to run for all db's (with relevant permissions if necessary) or just drop into a single database (if you're not a DBA and your own won't let you near the master db!).  Then execute it or set this up on a hot key, and with one key press you get a nice comma separated string of fields you can replace that * with ... and your beer is still cold!

Upgrade to SQL 2005 fails

Recently I had a problem upgrading a couple of SQL 2000 instances to SQL 2005 running exactly the same process as I had done many times before.  Early in the update, I kept getting the error message:

UpgradeAdvisor: returned -1.

Error message:
 

Alternatively sometimes it shows:

SQL BPA command line has stopped working

Turns out this is caused by the inbuilt upgrade advisor that runs within the upgrade setup, and fails if .Net 3.5 SP1 has been applied to the server.

The fix is simply to create a new directory and copy a single file (BPAClient.dll) within the setup bootstrap folder structure. 

Either just search for the file name BPAClient.dll, or navigate to the BPA folder (probably C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA) - the BPAClient.dll file should be in the "bin" directory in here.

Within the BPA directory, create a new directory called "BPAClient" - and into this directory copy the BPAClient.dll - now run the SQL 2005 setup to upgrade your instance and it should work.

UPDATE: Alternatively, just copy the BPAClient.dll file from the ...\BPA\bin folder into the ...\BPA directory - this also fixes the problem

Virtual Server freezes

Not strictly a DBA issue, but one I came across when trying to install SQL 2008 64 bit on a VMWare virtual server (under ESX 3.5).

Initially the install would crash, at random places, giving an alert saying:

“Invoke or BeginInvoke cannot be called on a control until the window handle has been created.”

Why am I here?

Over my 10+ years as a SQL DBA, I've frequently scanned the internet to find code that has helped me out with a specific problem, or provided the basis for something that has subsequently become a standard piece of code that I use.

This blog was encouraged by colleagues (OK, well one anyway) who recognised the value of some of these code snippets that I've put together, and suggested I share them with the SQL community.

Well - here I am.

Blogging is new to me, so this is likely to be rather hit and miss to start with - and probably more miss at that - but I'll stick with it, and hopefully this will build up into a useful resource that may one day prove useful to a few people.