Selecting Excel data from a SQL Server 2005 environment.

[cantademorna]

Hi There,

I have this peice of code:
SELECT * FROM OPENROWSET
('Microsoft.ACE.OLEDB.12.0'
,'Excel 12.0;HDR=YES;IMEX=1
;Database=C:\Documents and Settings\Andrew\My Documents\Test_Worksheet.xls;'
,'Select * From [Sheet1$]')

and am getting this error:
OLE DB provider "Microsoft.ACE.OLEDB.12.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.ACE.OLEDB.12.0" for linked server "(null)".

The server that the SQL Server is sitting on is a 64 bit machine, we have already installed the
AccessDatabaseEngine_x64.exe on the server.
The excel sheet is definitely closed and since the server is a 64 bit job then the Microsoft.ACE.OLEDB.12.0 is
certainly relevant for this query.

What am I missing and is there anything that I should check and/or do to get this working?

Many Thanks from a fustrated

Andrew

 

[TheBugSlayer]

The problem seems to be impersonation. When you run an OPENROWSET statement, it is executed on the server using the account of the user that ran it. This account must have permission to write in the TEMP folder for the account running the SQL Server service. Also, why not JET as the provider?

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)