This took a while to get working so I thought I’d list the steps taken to achieve manipulating an Excel 2010 spreadsheet via a linked server.
The SQL Server I used was SQL Server 2008 R2 (64-bit) and the Excel 2010 spreadsheet had the below data:
First I downloaded and installed the ‘AccessDatabaseEngine_x64.exe’ file on the SQL Server from http://www.microsoft.com/download/en/details.aspx?id=13255. No restart was required.
Next I started SQL Server Management Studio as ‘Administrator’.
Next I enabled the ‘Allow inprocess’ option for the Microsoft.ACE.OLEDB.12.0 provider.
The linked server was created with this script:
EXEC master.dbo.sp_addlinkedserver @server = N'test', @srvproduct=N'ACE 12.0', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'c:\test\test.xlsx', @provstr=N'Excel 12.0;HDR=Yes'
I could now query the spreadsheet with:
select * from test...[sheet1$]
To add data I used:
insert into test...[sheet1$] values ('server2','blue')
To modify data I found that I had to first enable the below:
Then to update a record I used
UPDATE OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0;DATABASE=c:\test\test.xlsx', 'Select * from [Sheet1$]') SET type = 'green' WHERE server = 'server2'
To insert a row I used:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\test\test.xlsx;', 'SELECT server,type FROM [Sheet1$]') VALUES ('server3','yellow')
To delete 1 row I used the below work around which set the column values to null.
UPDATE OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0;DATABASE=c:\test\test.xlsx', 'SELECT server,type from [Sheet1$]') SET server = null, type = null WHERE server like 'server2'