T-SQL: Excel 2007 & OPENROWSET

I do a lot of work with Excel spreadsheets and often need to import data from them on an adhoc basis. I typically use the Import/Export wizard to do this, although depending on how i'm feeling and the requirement I may go with one of the functions which gets data from a remote source such as OPENROWSET.

On this occasion, I was working with an Excel 2007 (.xlsx) workbook so I went straight to my favourite search engine to get a site which would remind myself of the syntax i'd need (how the internet makes one lazy on remembering syntax!) and it didn't disappoint. The first thing I noticed was that this wasn't using the old trusty Jet driver that I would use when working with Excel 2003. Using the Jet provider gives a fairly unhelpful:

OLE DB provider "Microsoft.Jet.OLEDB.4.0"FOR linked server "(null)" returned message "Unspecified error"
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"


Instead, you need to use the Access Database Engine driver:

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\ExcelImport\MyData.xlsx', 'SELECT * FROM [Sheet1$]')



So we're all set to get that data. Or so I thought as on executing the query, I got an error:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

The errors here are a bit of a red herring as the directory in question is open to Everyone. Also, it says that it is unable to get the column information but strangely, the column set was returned (albeit empty) in the results tab. Odd.

This problem can be averted by simply running the following queries to set some OLEDB properties:

USE [master]
GO

EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO



NB: a simple gotcha is remember to have your spreadsheet closed when trying to access it using this method otherwise you'll get another access denied error.

A couple of things I noticed but was unable to reproduce on a consistent basis, is that after running these queries, the excel spreadsheets appear to become corrupt for a length of time. Also, the changes did not always appear to be instant and on my machine it sometimes took 10 minutes for the behaviour to change.

T-SQL: Version your database with Extended Properties

Recently i've had a requirement to version a database build in a simliar way to AdventureWorks which uses a version table called AWBuildVersion. Of course, that is a perfectly acceptable option but as with all things SQL, there is more than one way to skin a cat. Extended properties are not a new feature to SQL Server but they might as well be for the number of times i've used them but the thought came to me that they could well be suitable for keeping version properties of a database.

Here is the code to generate the same database version info that is kept in the AdventureWorks build table.

-- add a version to your database
EXECUTE sys.sp_addextendedproperty @name = N'SystemInformationID', @value = N'1';
EXECUTE sys.sp_addextendedproperty @name = N'Database Version', @value = N'10.00.80404.00';
EXECUTE sys.sp_addextendedproperty @name = N'VersionDate', @value = N'2008-04-04';
EXECUTE sys.sp_addextendedproperty @name = N'ModifiedDate', @value = N'2008-04-04';

-- read out the version
SELECT *
FROM fn_listextendedproperty (NULL, NULL, NULL, NULL, NULL, NULL, NULL);

This returns:


Will I use this going forward? The thing with extended properties is that they aren't a widely used feature and having the data in a table makes it much more accessible. As a result, its much easier to remember the syntax to update a database table than an extended property. Also, a database table is more "visible" so is less likely to be forgotten when you come to update the version.

T-SQL: Enforce uniqueness with Filtered Indexes

A common design I see is tables designed with an Is_Live flag. I don't have any problem with this in general although it does throw up a slight issue when trying to enforce uniqueness within the table e.g when you're trying to ensure that there is only one "Live" record across a unique set of columns. Previous to SQL 2008 your only option was to use a trigger to enforce the uniqueness of the data but now you can harness the power of Filtered Indexes to achieve this goal.

Allow me to demonstrate:

CREATE TABLE dbo.Marriages
(
   Husband NVARCHAR(255),
   Wife NVARCHAR(255),
   Is_Current BIT NOT NULL DEFAULT(0)
)
GO
INSERT INTO dbo.Marriages
VALUES
   ('Liam Gallagher', 'Patsy Kensit', 0), ('Liam Gallagher', 'Nicole Appleton', 1),
   ('Dan Donovan', 'Patsy Kensit', 0), ('Jim Kerr', 'Patsy Kensit', 0), ('Jeremy Healy', 'Patsy Kensit', 0),
   ('Andre Agassi', 'Brooke Shields', 0), ('Andre Agassi', 'Steffi Graf', 1),
   ('Peter Andre', 'Katie Price', 0), ('Alex Reid', 'Katie Price', 0),
   ('Prince Charles', 'Diana Spencer', 0), ('Prince Charles', 'Camilla Parker Bowles', 1)
GO
-- this fails as some husbands have multiple marriages
ALTER TABLE dbo.Marriages ADD CONSTRAINT UQ_Marriages_HusbandIsCurrent UNIQUE (Is_Current)
GO
The CREATE UNIQUE INDEX statement terminated because a duplicate KEY was found FOR the object name 'dbo.Marriages' AND the INDEX name 'UQ_Marriages_HusbandIsCurrent'. The duplicate KEY >value IS (0).

-- this succeeds because we're only interested in ensuring someone only has 1 current wife
CREATE UNIQUE NONCLUSTERED INDEX UIDX_Marriages_WifeIsCurrent_Filt ON dbo.Marriages(Wife, Is_Current) WHERE Is_Current = 1
GO
-- so now we can insert a new marriage
INSERT INTO dbo.Marriages SELECT 'Jay-Z', 'Beyonce', 1

-- but if I try and marry Beyonce...
INSERT INTO dbo.Marriages SELECT 'Richard Brown', 'Beyonce', 1

Cannot INSERT duplicate KEY row IN object 'dbo.Marriages' WITH UNIQUE INDEX 'UIDX_Marriages_WifeIsCurrent_Filt'. The duplicate KEY value IS (Beyonce,1).

-- (although I can be a divorcee!!)
INSERT INTO dbo.Marriages SELECT 'Richard Brown', 'Beyonce', 0