0

I'm getting an exception of "Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"." when running my stored procedure through a VB front end. The procedure works fine when run through SSMS. Initially I had the same error running the procedure through SSMS but running SSMS as an administrator made it work, then turning off UAC allowed me to run the procedure without having SSMS being run as an administrator.

I have added full control permissions for my account to C:\Users\MSSQL$ServerInstance\AppData\Local

I have "Microsoft.Ace.OLEDB.12.0" as a provider under linked servers and have "Dynamic parameters" and "Allow inprocess" both enabled.

I also have 'Show advanced options' and 'Ad Hoc Distributed Queries' enabled.

Thanks for any help.

Here is part of the procedure that's relevant

Set @sql = 'INSERT INTO TempTbl ( [Items] )
SELECT [Items]
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', 
''Excel 12.0 Xml; 
Database='+@path+';HDR=YES'', 
''SELECT [Items] FROM ['+@sheet+'$]'');'

Exec(@sql)
Mason
  • 1
  • 2
  • [Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “(null)”](http://stackoverflow.com/questions/1362108/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked?rq=1) – Ňɏssa Pøngjǣrdenlarp Nov 17 '15 at 18:13
  • Thanks, I have given my account full access permission to the temp folder C:\Users\MSSQL$SQL2012_Server\AppData\Local\Temp But the error still persists. Thanks again for your help – Mason Nov 17 '15 at 23:25
  • I also have full access permission to C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp and C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp – Mason Nov 18 '15 at 02:02

0 Answers0