I have a SQL Server 2005 linked server object to access a Paradox database. The object's properties are:
Product Name=remote server IP address
Data source=System DSN name
Connections will: Be made without using a security context
(BDE is installed on the local machine and synchronised with DSN)
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.
Any help would be greatly appreciated.
Thanks in advance,