Check which node a clustered SQL Instance is on

Sometimes it is useful to know which physical node a SQL instance is running on in a cluster - e.g. for performance monitoring to check CPU attributable to an instance.

You can check this via the cluster failover manager, but you can also run the following SQL statement on the instance in question:

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

This will  return the netbios name of the server the node is currently active on.

I've used this in conjunction with CPU monitors, and setting processor affinity by NUMA node to track CPU activity by instance. 
The assigned NUMA node can be obtained from sys.dm_os_nodes [update] if you are on SQL 2008 R2 such as:

SELECT memory_node_id FROM sys.dm_os_nodes WHERE cpu_affinity_mask = online_scheduler_mask AND node_state_desc = 'ONLINE'

Identify SQL Agent job that runs a Reporting Services subscription

So you want to manually kick off a Reporting Services subscription outside of its usual schedule ... easy you think, I'll just run the SQL Agent job.

Then you find this ...

For some reason, reporting services wants to keep it a secret which job runs which subscription, by using easy to remember GUID based job names.

Fear not ... this SQL will give you a list of the job names against the actual reports

SELECT ScheduleID, Path, Name, s.Description
FROM ReportServer.dbo.Catalog c
JOIN ReportServer.dbo.Subscriptions s ON c.ItemID = s.Report_OID
JOIN ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID

Just find the job with the same name as the ScheduleId of the report subscription you want, and you can manually start it whenever you like.

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 ...

Remote access - requires Network Level Authentication

I recently had the following error while trying to connect to a Windows 2008 server while installing SQL 2008, by remote desktop.

The remote computer requires Network Level Authentication, which your computer does not support. 
For assistance, contact your system administrator or technical support.

This seems to be applicable to Windows XP SP3, and the fix is a couple of registry updates.

• Open regedit, and find subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
• Right click "SecurityPackages" and select modify
• Add the value "tspkg" (don't delete any of the existing ones)
• find subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders
• Right click "SecurityProviders" and select modify
• Add the value "credssp.dll"
• Reboot

This should now allow you to RDP onto the Windows 2008 Server.

UPDATE - Microsoft have released an automated FIX IT link for this: Microsoft Support 951608

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.

Error using linked server from 64bit to 32bit SQL

A colleague came across an error using a linked server connection to retrieve data from another server, which gave an error claming:

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator. Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "". The provider supports the interface, but returns a failure code when it is used.

We knew the table did exist, and the user had permissions.  The same statement also worked from another server, so it wasn't the syntax either.  We assumed it was some issue going from SQL 2008 to SQL 2000, but it seems there is a small issue with using linked servers between 64-bit and 32-bit versions due to the way it queries meta data.

The Microsoft line on fixing this is "To resolve this problem, manually run the Instcat.sql script that is included with SQL Server SP" - which begs the question, why doesn't the SP do this automatically?!

Our fix though was to manually create "sp_tables_info_rowset_64" which the 64-bit server tries to run on the 32-bit box.  There are several blogs that script out the full code for doing this, but this code is almost exactly the same as the proc that already exists on the 32-bit version, namely "sp_tables_info_rowset" - so we found the quickest and tidiest solution to be just to use that, as follows:

USE master GO CREATE PROCEDURE sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = null AS DECLARE @Result INT SET @Result = 0 EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type GO GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

You should now find the error magically goes away.

I stopped being grumpy ... for 15 mins

Needless to say this is not SQL related in any way - but I did stop being grumpy for 15 mins recently!

The cause of this miraculous event was a RaceMaster experience at Brands Hatch.

Now, my son and I like watching motor racing.  As well as watching it on TV, we've been to Rockingham and Brands Hatch several times to watch a variety of types of cars (although nearly always including a mini) going round and round for hours, without a care in the world.
Last weekend we quite happily stood in the rain at Druid's hairpin watching classic F1 cars from the 70's come slithering past while deafening us with engine roar.

This time was different though - this time, I got to drive.  And I'm not talking drive your own car round the track a few times, then go home.
This was 10 laps in a 420bhp BMW M3 with an instructor in the passenger seat giving you advice on cornering and track positioning, before getting to go out in a single seater for another 10 or so laps.

Add to this another 13 drivers on the track, rain, a manual gear box with a right hand shift stick (just to make it even more tricky!), a full complement of track marshalls with relevant flags to wave, a safety car with flashing lights that came out when one guy span off (my son was quite excited to see the safety car race past - then disappointed when I flashed past 20 seconds later as he realised it wasn't me who had crashed!) - and it was an incredible 15 minutes!

Next stop, Monaco!

I have to say - if you have the opportunity, I would highly recommend it - check out their latest packages here: http://www.msvdrivinggifts.com/. I'll definitely be doing it again - maybe see you there!

By the way, that was last week - don't worry, I'm grumpy again now.

Assign shortcut key to "change connection" in SQL MMS

Fed up having to click the "Change Connection Icon" every time you want to change your connection in management studio, and wish there was a shortcut key you could press?  Then worry no more (and try get out more often)!

SQL 2008 Management Tools install fails claiming Express Tools already installed

Quite a few users have had problems installing the SQL 2008 management tools - with an error message saying:

The SQL Server 2005 Express Tools are installed.  To continue, remove the SQL Server 2005 Express Tools.

After lots of playing around, uninstalling/reinstalling, it seems the issue is related to Red Gate tools, such as SQL Prompt and SQL Compare.

First try and uninstall any Red Gate tools you have installed and see if that fixes the issue.

If it still fails after uninstalling them, you may have to search the registry for any orphaned keys relating to the Red Gate tools

It looks like these Red Gate keys go under SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM - so if you still have these keys after uninstalling SQL Prompt, manually deleting these should fix the problem.

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.

Include headers when copying results from SQL Management Studio

A handy little option to enable in Management Studio for users who copy and paste query results from the grid into Excel or other applications, is to include the headers as well as the data.

This can be done from the "Tools" -> "Options..." window.

Expand Query Results, then SQL Server, and finally select Results to Grid.

On the right you should now see the option to "Include column headers when copying or saving the results" - enable this and no longer will you have to type the headers into the spreadsheet again!

SQL 2008 Management Studio shows filename on tab

Another frequent complaint from users moving to the SQL 2008 Management Studio is that the text on the tabs now shows the filename rather than the server and database.

Don't panic!  This can be changed back!!

Simply select the Tools menu, and select "Options..."

Now in the left pane expand the "Text Editor" and select "Editor Tab and Status Bar". 

Finally scroll the right pane down to the bottom so you can see the "Tab Text" heading, and under that you can select what you want included on the tabs.

Cannot change table in SQL 2008 Management Studio

As more users are upgrading to SQL 2008, they are running into some of the small changes in the 2008 management studio.

One of these is an error message that pops up when a user tries to change a table via management studio design view by changing/deleting a column or adding a new column in the middle of the table.  Instead of saving the change, the user gets the following error:

This may fool you or the user into believing there is a permissions issue preventing the table being updated, but it's actually a setting in SSMS - presumably to prevent the application hanging if the table involved was of any significant size.

The fix though is to simply uncheck box in the Options window. 

Select "Tools" from the SSMS menu bar, then "Options..." from the drop down list. 

Expand the "Designers" section, and under the "Table and Database Designers" section, you should find the "Prevent saving changes that require table re-creation" option, which you can disable.

Or you could just learn how to use ALTER TABLE statements in T-SQL!

Fix orphaned SQL user logins on new server

This piece of code helps in the situation where you have attached or restored a database onto a different server or instance from where it originated.

SQL will automatically re-apply permissions for any windows users as the sid is unique across the domain, but for any SQL users you will find that even if the same names exist on the new server, they will not have the required access.

To fix this, you need to use sp_change_users_login with @Action of Auto_fix - this will remap the user sid in the database, to the login sid for this SQL instance.

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

Recover a Suspect SQL 2005 database

One day it will happen - a database will drop off the face of the earth while someone is working on it, or will not appear after a restart, and you get the terrifying words ...

     Database cannot be opened.  It has been marked SUSPECT    

Step One - Don't Panic!! It's easy to do, and it is what most of the rest of your company will be doing, especially if it is a main production database.

Step Two - read step one again!

Now read the error log - there will probably be a clue in there as to why it has been marked SUSPECT, along with the helpful advice of restoring the database from a backup. 

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.