query results to excel

Question

Post reply

query results to excel

Trybbe

Hall of Fame

Points: 3967
More actions
June 5, 2009 at 3:58 am

#131633

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

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'

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Trybbe Hall of Fame Points: 3967 More actions · Answer 2

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