I have a SQL Server 2005 linked server object to access a Paradox database. The object's properties are:
General Provider=MSDASQL Product Name=remote server IP address Data source=System DSN name
Security Connections will: Be made without using a security context (BDE is installed on the local machine and synchronised with DSN)
Problem When I use OPENQUERY against this linked server, SQL Server works fine and fetches back the data in no time. If I then run the same query through a SQL Server Agent job I get the following error (from the job's log):
[SQLSTATE 01000] (Error 7412) OLE DB provider "MSDASQL" for linked server "ABC" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
From then on, running the same query in Query Analyser either results in the query running on (despite the source table having only 10 rows and 2 columns) until I cancel it, or in the following error:
Msg 7399, Level 16, State 1, Line 3 The OLE DB provider "MSDASQL" for linked server "ABC" reported an error. The provider reported an unexpected catastrophic failure. Msg 7303, Level 16, State 1, Line 3 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "ABC".
If the machine is rebooted everything works fine again, until I run the query through SQL Server Agent (which uses the local system account). I can run an AS400 openquery anywhere without problems. I can also import Paradox tables directly from SS Management Studio and SS Integration Services (using the same DSN and BDE) without problems.