This is the first time I've had to work with an Excel sheet as a linked server, and after a whole ton of struggling with it and solving one issue only to hit another I've decided to reach out. Long story short I have a SP that pulls data from an Excel linked server(this was originally just an open datasource rather than a linked server but I had issues there too) the SP works fine locally, however when I run it remotely, I was just getting error 7302 "can not create instance of provider" then after doing some searching I enabled "allow in process" and "Dynamic Parameter" in the provider settings, and now I'm getting:
Msg 7399...
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "JOBDETAILS" reported an error. The provider did not give any information about the error.
Msg 7303...
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "JOBDETAILS".
I looked through event viewer on both the local and remote servers and the local was logging a Com applications permissions issue when I tried to execute from remote using Windows Credentials(the end result will be running with SQL creds but I also tried Windows for troubleshooting purposes) so I corrected the permissions issue but that had no effect.
