none
Paradox Linked Server Problem

    Question

  • 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.
     
    Any help greatly appreciated.

    Thanks in advance,
    SSLearner

    Wednesday, June 21, 2006 10:02 AM