Thursday, July 20, 2023

SQL Server - Error while creating Excel as Linked Server for SQL Server

1) Created a Linked Server to an Excel file
2) When we try to pull data from linkedserver with select statement , it is working fine
FROM ExcelLinkedServer...[Sheet1$]
--It is giving data without any issue
3) But when we try sp_tables_ex system stored procedure with Linked Server:
 EXEC sp_tables_ex @table_server = 'myExcelLinkedServer'

It is giving below error:
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 82 
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Linked_Report" reported an error. The provider did not give any information about the error.
Msg 7311, Level 16, State 2, Procedure sp_tables_ex, Line 82
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Linked_Report". The provider supports the interface, but returns a failure code when it is used.

1) Go to SQL Server Management Studio
2) Go to Server Objects  then -Linked Servers
3) Click on Providers
4) Select the provider that we need to use and Right click
5) In provider options, check  for Allow inprocess.

No comments:

Post a Comment