none
Connecting 64-bit Pervasive Server to 64-bit SQL Server via 32-bit Pervasive ODBC Client Interface Driver as a Linked Server

    Question

  • I am trying to utilize a 32-bit ODBC Client Interface Driver to connect a 64-bit Pervasive SQL Server to a 64-bit MS SQL Server on Windows Server 2008 R2. I can test the connection via the C:\Windows\SysWOW64\odbcad32.exe Data Source Administrator but when I go into SQL Management Studio it will not accept the Linked Server due to the 32-bit vs 64-bit conflicts (i am assuming)... The client doesn't have a 64-bit ODBC driver as of yet also - is there some kind of simple work around to get this working???

     

     

    ===================================

    The test connection to the linked server failed.

    ===================================

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft .SqlServer.ConnectionInfo)

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd)
       at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
       at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()

    ===================================

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "REIAGRIS".
    OLE DB provider "MSDASQL" for linked server "REIAGRIS" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

    ------------------------------
    Server Name: reistageone\stageone
    Error Number: 7303
    Severity: 16
    State: 1
    Procedure: sp_testlinkedserver
    Line Number: 1


    ------------------------------
    Program Location:

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
    Wednesday, May 12, 2010 5:44 PM

Answers

  • Unfortunately, we cannot load a 32-bit ODBC driver in a 64-bit SQL Server process, so unless Pervasive provides a 64-bit ODBC driver or a 64-bit OLEDB provider, you will not be able to create a linked server to it from your 64-bit SQL Server instance.

     

    Depending on your needs for the linked server, maybe you should look into creating an SSIS job to pipe data back and forth between the two servers? Since SSIS jobs can run in their own process and can spawn both 32-bit and 64-bit processes, you should have no problem creating a 32-bit process which can transfer data between SQL Server and Pervasive using the 32-bit drivers for each.


    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, May 14, 2010 4:42 PM

All replies

  • Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "REIAGRIS".
    OLE DB provider "MSDASQL" for linked server "REIAGRIS" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". (.Net SqlClient Data Provider)

    Hi,

    Please use "c:\windows\system32\odbcad32.exe" to create the DSN to connect to a 64-bit data source.

    Reference: Managing Data Sources

    Based on your description, you want to set up a linked server from a 64-bit SQL Server to another 64-bit SQL Server, right?

    If so, I think we can use Microsoft SQL Server Native Client OLE DB Provider (SQLNCLI) instead of Microsoft OLE DB Provider for ODBC (MSDASQL).

    For more information, please see: sp_addlinkedserver (Transact-SQL)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, May 14, 2010 3:31 AM
  • Unfortunately, we cannot load a 32-bit ODBC driver in a 64-bit SQL Server process, so unless Pervasive provides a 64-bit ODBC driver or a 64-bit OLEDB provider, you will not be able to create a linked server to it from your 64-bit SQL Server instance.

     

    Depending on your needs for the linked server, maybe you should look into creating an SSIS job to pipe data back and forth between the two servers? Since SSIS jobs can run in their own process and can spawn both 32-bit and 64-bit processes, you should have no problem creating a 32-bit process which can transfer data between SQL Server and Pervasive using the 32-bit drivers for each.


    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, May 14, 2010 4:42 PM