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.