February 2, 2011 at 5:32 am
hi,
I am using this query to export data to excel 2007.
When I am running a below query I have getting a error message which I have pasted below. My excel sheet location is as follows "d:\contact.xlsx" & there are two column name which are similar to query column name "FirstName", "LastName".
I also wanted to know that file location "d:\contact.xlsx" is to be on client machine or it must be located on main server (I am on client machine).
If someone have a solution. Please give me solution regarding this.
Query
-------
INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=d:\contact.xlsx;',
'SELECT * FROM [Sheet1$]')
SELECT LoginName as FirstName, UserName as LastName
FROM sysuser
GO
Error Message
---------------
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
February 2, 2011 at 9:02 am
If the computer upon which this is run doesn't have Excel 2007 installed, you may have to install the data provider separately. Microsoft has a nice overview of connecting to to Excel from SQL Server. You'll see a link in the middle of this for downloading the OLE DB provider.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply