SQL Service Pack Versions

I know this information is available in multiple other places, but I can never find it when I need it, so I'm putting it here for my benefit ... and for anyone who stumbles across this!

First up, it is possible to determine the service pack level directly from SQL using the SERVERPROPERTY function with the 'productlevel' key, for example:

SELECT @@SERVERNAME AS "SQLInstance" ,

SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS "Server",

SERVERPROPERTY ('ProductVersion') AS "Build",

SERVERPROPERTY ('edition') AS "Edition",

SERVERPROPERTY ('productlevel') AS "Patch"

would return results similar to

SQLInstance	Server	Build	Edition	Patch
GRUMPY\DBA	GRUMPYCLUSTER1	10.50.4000.0	Enterprise Edition (64-bit)	SP2

Loop through all your servers and you can quickly keep track of which servers are at what build, and what needs patching.

However, if you just want to check what patch level a certain build number equates to (since you can see that as soon as you connect in SSMS), here are the major releases and their version numbers:

SQL 2000	Shiloh
8.00.194	RTM
8.00.384	SP1
8.00.534	SP2
8.00.760	SP3
8.00.2039	SP4
SQL 2005	Yukon
9.00.1399	RTM
9.00.2047	SP1
9.00.3042	SP2
9.00.4035	SP3
9.00.5000	SP4
SQL 2008	Katmai
10.00.1600	RTM
10.00.2531	SP1
10.00.4000	SP2
10.00.5500	SP3
10.00.6000	SP4
SQL 2008 R2	Kilimanjaro
10.50.1600	RTM
10.50.2500	SP1
10.50.4000	SP2
10.50.6000	SP3
SQL 2012	Denali
11.00.2100	RTM
11.00.3000	SP1
11.00.5058	SP2
11.00.6020	SP3
SQL 2014	Hekaton
12.00.2000	RTM
12.00.4050	SP1 (original)
12.00.4100	SP1 (re-released)
SQL 2016
13.00.200	CTP

Decipher blocked process waitresource

When attempting to identify blocking issues, you may find yourself looking in sysprocesses at the waitresource column, and trying to decipher the meaning of something along the lines of:

KEY: 32:72057594097827840 (f5593d0e605b)

Thanks - you couldn't just tell me the actual object could you?!

But help is at hand - apparently this key consists of the database id, followed by a a hobt_id - no, not a small person with large feet from New Zealand, but a "Heap or B-Tree" id.

This id value can be found in the sys.partitions table, so a quick join from that to sys.indexes, and back to sys.obejcts finally reveals the index we are blocked on.
E.g.

SELECT o.name, i.name
FROM sys.partitions p
JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.indexes i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE p.hobt_id = 72057594097827840

Truncate table DOES rollback

The post on table variables not being affected by transactions reminds me of another DBA interview question that I very rarely hear people answer correctly.

What is the difference between DELETE and TRUNCATE?

The standard answer trotted out is that DELETE is logged and can be rolled back, whereas TRUNCATE can't.

Really?

OK, try this:

CREATE TABLE #Trunc (MyId INT)
INSERT #Trunc SELECT 1 UNION SELECT 2


SELECT * FROM #Trunc 
-- returns 2 rows


BEGIN TRAN
  TRUNCATE TABLE #Trunc
  SELECT * FROM #Trunc 
-- returns 0 rows - we have truncated the table
ROLLBACK TRAN


SELECT * FROM #Trunc

Now if TRUNCATE was indeed non-logged and cannot be rolled back, you would expect the final SELECT statement to also return 0 records, and then laugh at you as you realised you had lost all your data ...

But it doesn't!  It returns the original 2 rows that were there before you issued the TRUNCATE statement ... if you don't believe me, give it a try ...

Table variables don't roll back

A standard question asked at DBA interviews is what is the difference between a temp table and a table variable.

Aside from the usual incorrect answer of one being a physical table in tempdb and the other being just held in memory, and the less often heard but correct fact of one taking the collation of tempdb and the other taking the collation of the current database, a little known aspect is that one of them is totally unaffected by transactions.

So what I hear you say ...

Combine this with TRY and CATCH error logic, and you can capture the data causing transactions to roll back and write it somewhere else after the transaction has failed.

Don't believe me??  OK, have a look at this:

DECLARE @Data TABLE (MyID INT)


BEGIN TRANSACTION 
INSERT @Data VALUES (1)
ROLLBACK TRANSACATION

Now in a normal, or even temp, table you would get no records back from a subsequent SELECT statement, but run SELECT * FROM @Data after the ROLLBACK statement in this situation, and you will see it returns the data added within the transaction.

Obviously this is a very basic example, but using this within complicated transactions, and the possibilities for auditing and error handling are endless!  Well ... nearly endless ... well ...

xp_cmdshell raises error "a call to LogonUserW failed with error code 1385"

xp_cmdshell requires a couple of permissions to enable non-sysadmin users to run it, and even when you think you have enabled it in surface area config, and assigned an account to the ##xp_cmdshell_proxy_account##, you will frequently find you still encounter the following error:

An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'.

In order to fix this you need to open the Local Security Settings on the host machine.
 
Navigate to Security Settings -> Local Policies -> User Rights Assignment.
 
Now open "Log on as a batch job" and add the user you assigned to the xp_cmdshell proxy account - and you should find it runs fine now.

Reading directly from Excel in SQL

I showed a colleague how to directly query an Excel sheet from SQL the other day who had no idea this was possible and found it very useful - so for anyone else who is also unaware how easy this is, this post is for you.

The key to this is OPENROWSET - which allows SQL to access Excel sheets (amongst other things), either directly, or to load the data into a table for further SQL manipulation at leisure.

Lets say we have an excel sheet "C:\Data\Orders.xls" and want to query the data from a worksheet title "Today".

The version of Excel you have makes a small difference to the code required, but for Excel 97 up to 2003 you can run the following:

SELECT * -- INTO #Orders -- [Use this if you want to store the data into a temp table] FROM OPENROWSET -- [let the magic begin!] ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;Database=C:\Data\Orders.xls', 'SELECT * FROM [Today$]')

The HDR=YES reference implies the first row of the data in the worksheet is a header row, which will be used to name the data columns.

In Excel 2007 and later, you need a slightly different combination of provider name and data source.

Also note that the path path of the file in this statement is local to the SQL Server you are connected to - and not the machine you are actually sat at.
So if the file is on your local workstation, you may need to create a share to enable the SQL Server to access the data.

For example, an Excel 2007 file "Orders.xlsx" is on a local workstation "MachineX", that is in a shared folder called "Data".

This time you can access the data by using:

SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=\\MachineX\Data\Orders.xlsx', 'SELECT * FROM [Today$]')

Certainly easier than messing about with data import wizards.

Kill all connections in a database

Every now and then I've come across situations where you need to drop all connections to a database, so I came up with a little proc to auto generate and execute KILL statements against all processes for a certain database.

My original code for this was titled sp_massmurder_db, and was as follows.

CREATE PROC [dbo].[sp_MassMurder_DB] @DBName varchar(100)  AS    SET NOCOUNT ON    DECLARE @Spid int, @SQL Varchar (1000)    DECLARE DESTROY CURSOR FOR  SELECT spid  FROM master.dbo.sysprocesses (NOLOCK)  WHERE dbid = DB_ID(@DBName)    OPEN DESTROY  FETCH NEXT FROM DESTROY INTO @spid  WHILE @@FETCH_STATUS = 0   BEGIN      SET @SQL = 'KILL ' + CONVERT(Varchar, @SPID)    EXEC (@SQL)    FETCH NEXT FROM DESTROY INTO @spid     END  CLOSE DESTROY  DEALLOCATE DESTROY

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!