2

I have a situation where a linked server to an access DB is crashing my SQL server.

Crashed here means adding the linked server causes all other linked servers using that provider to stop working. Any queries to those linked servers hang and don't complete. This situation persists until the server is restarted.

However, when I use OPENDATASOURCE to connect to the same data source I don't have this problem.

Why would one work and the other crash?

The provider I'm using is Microsoft.ACE.OLEDB.12.0. I have Dynamic parameter and Allow inprocess enabled. Example query below

select 
*
from OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 
    'Data Source=\\networkPath\Reporting.accdb')...MyAccessTable
Hannah Vernon
  • 3,367
  • 1
  • 26
  • 48
FistOfFury
  • 6,735
  • 7
  • 49
  • 57
  • 1
    When you say "crashed" what precisely do you mean? – Hannah Vernon Oct 03 '13 at 13:48
  • Adding the linked server causes all other linked servers using that provider to stop working. Any queries to those linked servers hang and don't complete. This situation persists until the server is restarted. – FistOfFury Oct 03 '13 at 13:49

1 Answers1

-1

SQL Server is crashing probably because the SQLSERV.EXE Call stack got filled by OLEDB object or because the data access library configuration is not correct.

Try this :

  1. Make sure Allow inprocess option is activated on your provider
  2. Start SQLSERV.EXE with a bigger Call Stack using -g 512 startup parameters

Activate Allow inprocess

PollusB
  • 1,726
  • 2
  • 22
  • 31
  • Maximum server memory was 10240 MB, minimum memory per query in KB was 1024. As stated above, Allow inprocess was enabled. – FistOfFury Oct 07 '13 at 15:08
  • Have you tried -g 512 as startup parameter? Max Server Memory does not affect the call stack size. All OLE objects are allocated in call stack and not in the Buffer Pool. – PollusB Oct 08 '13 at 15:38