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.

• Dan Guzman - Not the MVP

  Hall of Fame

  Points: 3757

  More actions

  August 5, 2014 at 4:42 pm

  #1735429

  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

  August 5, 2014 at 6:12 pm

  #1735446

  The Access Database Engine 2010 Redistributable is not intended:

• 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]