Cannot get the column information from OLE DB provider “Microsoft.Ace.OLEDB.12.0” for linked server “(null)”.
Encountered this little gem while performing a migration today. Turns out the SQL Server service account is used when establishing Linked Server Connection to a csv file and as a result the OLEDB connection fails with messages like could not read column information since user doesn’t have permissions or the error above.
The workaround (not fix) is to use a Local account instead of the NTService Managed account to startup MS SQL Server Services. A side not to the above issue is when the query ran the files could not be loaded the files were locked by the SQL Server process and wouldn’t open anymore. To find and kill the OS process that has locked the file you use Resource Monitor and under CPU there is the Associated Handles option where you can enter the file name.
References
http://stackoverflow.com/questions/3565218/how-to-know-what-process-is-using-a-given-file
Please Consider Subscribing
