Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

Question

Post reply

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

halifaxdal

SSCoach

Points: 19741
More actions
November 23, 2014 at 6:47 pm

#295366

I know this question was discussed lots but seems still cannot figure it out, here is the issue:
My system:
Windows server 2003(server)
SQL 2008 R2
no office on server
I want to use a query to export a select result to a csv and save it locally
Here is the query:
insert into openrowset ('Microsoft.ACE.OLEDB.12.0', 'Text; data=c:\log.csv; HDR=YES;FMT=Delimited', 'Select * From [log.csv]')
select top 100 * From EventLog
Here is the error message:
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.
if doable, I want to use date format like 20141123.csv for the file name.
Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

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

JimmyJones SSCrazy Points: 2178 More actions · Answer 1

Hi,

I had to build a similar solution some time ago.

Maybe it's a little overkill and the real Experts will tell a much easier solution, however here it goes:

I'm using two files: Query.sql and a batch file

Query.sql:

SET nocount ON

SELECT [name],[database_id],[state_desc] FROM [master].[sys].[databases]

GO

Batch file (collect.bat):

sqlcmd -S. -i "Query.sql" -o o_%date:~6,4%%date:~3,2%%date:~0,2%.csv -W -s ","

findstr /R /C:"^[^-^]" o_%date:~6,4%%date:~3,2%%date:~0,2%.csv > %date:~6,4%%date:~3,2%%date:~0,2%.csv

del o_%date:~6,4%%date:~3,2%%date:~0,2%.csv

Basically all these files placed locally in one directory. The batch file opens the Query.sql (connects to default instance using windows authentication and will produce the csv with the date in the name as you have requested.)

The other two rows are needed because for some reason the first csv will contain dashes in the second row, so you need to remove them.

In short: You can run the batch file just as above and put your t-sql query in the Query.sql file.

Final output from my example:

20141124.csv

name database_idstate_desc

master 1 ONLINE

tempdb 2 ONLINE

model 3 ONLINE

msdb 4 ONLINE

Hope that helps.

---------------------------------

First entry. Please be patient.

________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

Have you tried to install

http://www.microsoft.com/en-us/download/details.aspx?id=13255

?

Note MS says it's for 2003R2.

halifaxdal SSCoach Points: 19741 More actions · Answer 3

Thanks for that, l am sure it works in your situation but rather like to have a one query like openrowset solution.

halifaxdal SSCoach Points: 19741 More actions · Answer 4

serg-52 (11/24/2014)
Have you tried to install
http://www.microsoft.com/en-us/download/details.aspx?id=13255
?
Note MS says it's for 2003R2.

Tried that already and it threw some other pre-req

JimmyJones SSCrazy Points: 2178 More actions · Answer 5

halifaxdal (11/24/2014)
serg-52 (11/24/2014)
Have you tried to install
http://www.microsoft.com/en-us/download/details.aspx?id=13255
?
Note MS says it's for 2003R2.
Tried that already and it threw some other pre-req

I've found some interesting thread here:

http://stackoverflow.com/questions/3169220/export-query-result-to-csv-file-in-sql-server-2008

The .CSV file will have to exist already. If you're using headers (HDR=YES), make sure the first line of the .CSV file is a delimited list of all the fields.

I've tested it, if you have installed Microsoft.ACE.OLEDB.12.0 provider and the csv file is created with the column names before the OPENROWSET query run, it works fine.

However this will not solve that you want your csv to be named as the actual date and you have to create manually a new file every time.

________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."

halifaxdal SSCoach Points: 19741 More actions · Answer 6

r.szasza (11/24/2014)
halifaxdal (11/24/2014)
serg-52 (11/24/2014)
Have you tried to install
http://www.microsoft.com/en-us/download/details.aspx?id=13255
?
Note MS says it's for 2003R2.
Tried that already and it threw some other pre-req
I've found some interesting thread here:
http://stackoverflow.com/questions/3169220/export-query-result-to-csv-file-in-sql-server-2008
The .CSV file will have to exist already. If you're using headers (HDR=YES), make sure the first line of the .CSV file is a delimited list of all the fields.
I've tested it, if you have installed Microsoft.ACE.OLEDB.12.0 provider and the csv file is created with the column names before the OPENROWSET query run, it works fine.
However this will not solve that you want your csv to be named as the actual date and you have to create manually a new file every time.

Thanks. The constraint is I can't install anything on the server including Microsoft.ACE.OLEDB.12.0 provider