Linked Server to csv on SQL Server 2012

Question

Linked Server to csv on SQL Server 2012

Dan Guzman - Not the MVP

Hall of Fame

Points: 3757
More actions
August 5, 2014 at 4:33 pm

#294024

I upgraded my 2008 R2 instance to 2012 and the Linked Server I use for aconnect to a csv file stopped working.
Create script:
EXEC sp_addlinkedserver @server ='Stats_CA',
@srvproduct='',
@provider ='Microsoft.ACE.OLEDB.12.0',
@datasrc='D:\RLAPPS\Stats\Stats_CA\',
@provstr='Text'
Error message:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Stats_CA" returned message "Unspecified error".
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 "Stats_CA".
I uninstalled and reinstalled the AccessDatabaseEngine - no change.
The provider is displayed, so it's not that.
I veriified temp and csv location folder permissions
I tried the -g512 startup option
The csv has a schema.ini as required
It was an 'in place' upgrade, so nothing else has change.
What am I missing?!?!?!
So frustrated.
As a general replacement for Jet (If you need a general replacement for Jet you should use SQL Server Express Edition).
As a replacement for the Jet OLEDB Provider in server-side applications.
As a general word processing, spreadsheet or database management system -To be used as a way to create files. (You can use Microsoft Office or Office automation to create the files that Microsoft Office supports.)
To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services.

That said, it is possible you need the 64bit version : http://www.microsoft.com/en-gb/download/details.aspx?id=13255

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Forum Etiquette: How to post Reporting Services problems

[/url]Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]How to Post Performance Problems - by Gail Shaw
[/url]

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 1

And to add:

I've also logged in directly to the server, and run SSMS there as the SQLServer Service user, and as a Domain and SQL Server admin, and run the select, and got the same message.

I've also restarted the SQL Server Service many times, and rebooted the whole server twice.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

The Access Database Engine 2010 Redistributable is not intended:

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

Dan Guzman - Not the MVP (8/5/2014)
I upgraded my 2008 R2 instance to 2012 and the Linked Server I use for aconnect to a csv file stopped working.
Create script:
EXEC sp_addlinkedserver @server ='Stats_CA',
@srvproduct='',
@provider ='Microsoft.ACE.OLEDB.12.0',
@datasrc='D:\RLAPPS\Stats\Stats_CA\',
@provstr='Text'
Error message:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Stats_CA" returned message "Unspecified error".
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 "Stats_CA".
I uninstalled and reinstalled the AccessDatabaseEngine - no change.
The provider is displayed, so it's not that.
I veriified temp and csv location folder permissions
I tried the -g512 startup option
The csv has a schema.ini as required
It was an 'in place' upgrade, so nothing else has change.
What am I missing?!?!?!
So frustrated.

Assuming that the correct provider is installed (32 or 64 bit), I suspect that you will need to set a couple properties on the provider to make this work.

Try running the following...

USE [master]

GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

GO

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 4

Already tried that, sorry didn't mention it above.

Also, provider is x64 for my Win 2008 R2 x64 OS on the server.

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 5

My reading, and attempted installs show that Jet doesn't work for x64, so I had to go with ACE x64.

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 6

Dan Guzman - Not the MVP (8/6/2014)
My reading, and attempted installs show that Jet doesn't work for x64, so I had to go with ACE x64.

Also, it's as single use file, just connecting long enough to read the data for a compare, then come back and 'INSERT' it into another table, with conversions and such in an SP, so none of those other items really apply. Plus it worked up until a few weeks ago.