EXEC sp_tables_ex random fail

Question

Hi 

I installed ACE driver 2010 64 bits on my SQL 2017 64bits

I created a Linked Server to an Excel file

SELECT * FROM  myExcelLinkedServer...[Sheet1$]   -- Works 100% of the time

but

EXEC sp_tables_ex @table_server='myExcelLinkedServer'  fails randomly one time out of two

I get this error one time out of two

Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 72 [Batch Start Line 8]
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_CRD_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 72 [Batch Start Line 8]
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_CRD_Report". The provider supports the interface, but returns a failure code when it is used.

Works, Works, does not, does not, does not, does, does, does not ...

All this within one minute

---

Marc trudel

Answer 1

Hi Marc,

What's the version of your SQL Server 2017? Could you please provide detailed version by executing the following command?

select @@VERSION

Best Regards,

Will

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Can you try this?

Open management studio, go to "Server Objects"->"Linked Servers"->Providers, select the provider you use, right click it and in provider options, check "Allow inprocess".

and see if it works

Refer the link below for how to set it

https://anishshenoy57.wordpress.com/2013/02/20/allow-inprocess-in-sql-server/

Though the error specified is different, in my case I was able to solve the above error also using the same step

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 3

Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64)
 Jul 21 2018 07:47:45
 Copyright (C) 2017 Microsoft Corporation
 Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

---

Marc trudel

Answer 4

Hi

It is not checked.

I remember having tested with and without this check. The result was the same.

New fact  today Saturday ...

The command works today 100% of the time.

Server is not beeing used right now.

I will retry again during business hours and let you know.

---

Marc trudel

Answer 5

Hi

It is not checked.

I remember having tested with and without this check. The result was the same.

New fact  today Saturday ...

The command works today 100% of the time.

Server is not beeing used right now.

I will retry again during business hours and let you know.

---

Marc trudel

ok 

No worries

Will wait on how you got on with it!

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 6

Excelent its works, thanks a lot