June 5, 2009 at 3:58 am
Hi
may you please assist I'm trying to send query results to excel2007 and I seem to be getting it wrong. Here's the query I copied from Pinal Dave SQL Blog, changed the details to my details but I get an error:
INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0;Database=C:\contact.xlsx;','SELECT * FROM [Sheet1$]')
SELECT TOP 5 CallName, Surname
FROM DTIHeadcount
Where Period = 200904
and Headcount = 'Headcount'
I get a syntax error when I run it:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Please help
June 5, 2009 at 4:23 am
It looks like you're missing two quotation marks:
One at the end of 'Microsoft.ACE.OLEDB.12.0 and one at the beginning of Database=C:\contact.xlsx.
Also, there seems to be a mismatch of commas/semicolons as per syntax description from BOL:
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
Something like the following should work (untested):
INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Database=C:\contact.xlsx','SELECT * FROM [Sheet1$]')
SELECT TOP 5 CallName, Surname
FROM DTIHeadcount
Where Period = 200904
and Headcount = 'Headcount'
June 5, 2009 at 4:42 am
Nope. I now get an error:
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.
Ok here the whole thing.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Database=C:\contact.xlsx','SELECT * FROM [Sheet1$]')
SELECT TOP 5 CallName, Surname
FROM DTIHeadcount
Where Period = 200904
and Headcount = 'Headcount'
June 5, 2009 at 4:53 am
Are you sure the provider is installed on your server?
Similar issues have been discussed on different threads before.
You might find a solution at http://www.sqlservercentral.com/Forums/FindPost445277.aspx or http://www.sqlservercentral.com/Forums/FindPost544672.aspx.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply