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] )
''Excel 12.0 Xml;
''SELECT [Items] FROM ['[email protected]+'$]'');'