Import Excel 2007 files into SQL 2005

Recently, I had to import an Excel 2007 file into SQL 2005.

First pre-requisite is that you have a copy of this provider:

"Microsoft Office 12.0 Access Database Engine OLE DBProvider"

If your machine has Office 2007 installed you may have it.

If not, this is how I got it:

http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

If the link is dead search for "2007 Office System Driver: Data Connectivity Components" on your favorite search engine.

Run SQL Server Import and Export Wizard:

1. Choose "Microsoft Office 12.0 Access Database Engine OLE DBProvider" as the data source
2. Specify the Excel file path as your Data Source.
3. Under All, look for "Extended Properties" and specify "Excel 12.0"

That should do the trick.

How to create logins with the same SID and password on two servers

Recently, I had to setup a standby/reporting server using log shipping (using 'restore' with the STANDBY option) for a client who relied strictly on SQL Server security for access to the database. As a result, the accounts created on the original database instance became orphaned upon restore.

The article below shows you how to create logins with the original SID and password on the destination server:

http://support.microsoft.com/kb/246133/

Personally, I would recommend using integrated security to avoid such issues.

Also, a better option for a reporting server would have been to use replication, but we were pressed for time.

How do you find out programmatically how many processors you have via TSQL?

Run this command:

exec master..xp_msver 'processorcount', 'processortype'

A good site for SQL tips

I came across this site in my browsing today. Since I don't update my blog as often, this is another resource you may want to utlized:

http://www.chriskempster.com/tipsandhints.html

Moving system databases

Have you ever had to move system databases to a new physical location on your disk? Well, it turns out to be a somewhat complicated endevour. The master database log and data file locations are dictated by startup parameters passed to SQL Server at boot time, -l for log and -d for data, that is where the location of the SQL Error Log is specified. Tempdb can be moved via an alter database command. Model and msdb require a special switch "-T3608" to provided on startup, after which you're allowed to detach/move/reattach each database. This is a very short overview. The more detailed explanation is contained in this KB:

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

If you plan on moving all 4 databases, I recommend starting SQL Server in command mode, so you can kill it via CTRL-C, because you'll need to restart each time you want to change the startup parameters and after you perform ALTER on tempdb. I would not recommend attempting this on a production machine before having a well tested failover plan, which includes another fully setup SQL Server, in case you manage to destroy the installation while moving the system databases.

The mysterious disappearance of the value behind @@servername

Have you ever ran "select @@servername" and to your dismay received the value NULL?

This short post is for you. Apparently, if you rename your server or do some other horrible thing to your machine, @@servername may begin to return NULL. I have not researched the root cause of this issue and am only offering a fix below.

You can run the following statements and get your @@servername back:

sp_dropserver 'servername'
go
sp_addserver 'servername', local

Print without delay (aka print with flush)

Have you ever wanted to debug a long running TSQL script? Perhaps a script that has a loop in it and you'd like to get periodic status updates? The problem with the built-in 'print' command in SQL Server is that it doesn't always flush the output to the console. A neat trick to get around this limitation is to use the RaisError function:

declare @Msg varchar(200)
declare @i int
declare @seconds int
declare @StartTime datetime

set @i = 0
set @StartTime = getdate()

while @i < 100
begin
    waitfor delay '00:00:10'
    set @seconds = datediff(second, @StartTime, getdate())
    -- This can be anything
    set @Msg = 'total running time: ' + cast(@seconds as varchar) + ' seconds'
    RAISERROR(@msg,0,1) WITH NOWAIT
    set @i = @i + 1
end