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.