Monday, December 9, 2013

Excel + SQL Server Linked Server Troubleshooting

Following is a great article to troubleshoot errors thrown by OLEDB when connecting an Excel file from OPENROWSET query:

OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Gives step by step resolution to multiple errors like:
- Cannot initialize the data source object of OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".
- Cannot get the column information from OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part
of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.
For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

On client deployments my recurring issue was setting my access to temp folders for service accounts.