Linked Server from SQL Server 2005 (x64) to Sybase

Hi,

We have been accessing databases on ASE 12.5 (on HPUX) from MS SQL Server 2000 (x32) successfully for years. We do this via linked servers we create on MS SQL Server. When creating the linked server, we would specify the Microsoft OLE Provider for ODBC as the provider, and the data source is the name of a DSN created with the Sybase ASE ODBC driver (4.20.00.67) that points the the HPUX box on which are the Sybase databases.

We have also gotten this to work just fine on MS SQL Server 2005 (x32) Standard Edition SP1. However, we cannot seem to get this to work on MS SQL Server 2005 (x64) Enterprise Edition SP1. The Microsoft OLE DB Provider is not available as an option when creating the linked server on MS SQL Server 2005 (x64). We have tried specifying the SQL Native Client as the provider, but this results in no connection being made and reports the following error:

OLE DB provider "SQLNCLI" for linked server "GCMTESTSQLNATIVE" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "GCMTESTSQLNATIVE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53].

Do we need to use a different ASE driver when accessing Sybase databases fom the MS SQL Server (x64) platform? Or is there a way to successfully create a linked server on MS SQL Server 2005 (x64) that can communicate with the 32-bit ASE ODBC driver?

Thanks,

Rocco M.

This post should help you out. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=381849&SiteID=1|||

Hello,

Thanks for the pointer.

So, it appears that it is not possible to have a linked server created in SQL Server 2005 x64 use a 32-bit DSN (i.e. created with a 32 bit driver). The answer is that I need to create a 64-bit DSN in order to create a linked server in SQL Server 2005 x64. The problem is that many vendors (i.e. Sybase) do not have a 64-bit driver.

Looks like our migration is SQL Server 2005 x64 will have to wait unless we can get this to work with perhaps a third party vendore such as Data Direct. Any one using Data Direct to connect to Sybase from SQL Server 2005 x64?

Thanks,

Rocco M.

|||

There exists a workaround that you can use until Sybase releases an OLE DB Provider for x64 (they currently have a 64 Bit ODBC Driver but you cannot use it with SqlServer 64 because there is no 64 bit MSDASQL)

Install a 32-bit Named Instance of SQL Server 2005 on the same machine.

Setup your Sybase Linked Server on the 32 bit instance and then setup the 32 Bit named instance as a Linked Server on the 64 Bit instance and be prepared to get creative with defining SYNONYM's and liberal user of EXEC () AT {Machinename}

basically something like this

64Bit SqlServer --> 32 Bit Sql Server 2005 -> Sybase ASE

|||

If anyone has found a way (using a 3rd-party solution or whatever) to get a linked server to Sybase from an x64 SQL Server installation, I'd love to hear about it.

Barring that, can ehosca (or someone) provide more detail about the workaround?

sql