0

Hello to all and thanks ahead of time for taking the time to look at my question!

I'm trying to do a work-around to access a 32bit Advantage Database Server 8.1 database with 64bit SQL Server 2008R2.

Setup:

  • Windows Server SBS2012
  • SQL Server 2008R2 64bit
  • SQL Server 2012 Express 32bit
  • Sybase Advantage Database Server (ADS) 8.1 32bit (can't change)

What I've done so far is to install a 32bit SQL Server Express on the same machine. I have setup a linked server to the Advantage database on that instance and successfully accessed data. So, I know that much is working fine.

Problem is, when I try to setup a linked server on the 64bit 2008R2 server, it errors out with the following...

OLE DB provider "SQLNCLI10" for linked server "[--MyLinkedServerName--]" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "[--MyLinkedServerName--]" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 65535, Level 16, State 1, Line 0
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Like I said, I can access the data using the 32bit SQL Server Express, so I know that much of it works. I just can't seem to access it from within the 64bit 2008R2. I know the login info is correct.

So, Is it not possible to do?

Improve this question
2
  • I have used the same trick before, but not for sybase, so I suspect that the strategy is sound but there is something about the way the queries are worded or the linked servers are configured. Could you post some simplified queries of what works and what doesn't?
    – Darin Strait
    Oct 30, 2012 at 16:55
  • Thanks, @darin strait, for trying to help! The following works when executed from a connection to the x86 SQL Express: SELECT * FROM OPENQUERY([x86Link2ADS], 'SELECT * FROM [\\HAL2011:6262\HPCommerce\Data\PLC.adt] AS PLC) AS tblHPQoH However, when I try: SELECT * FROM OPENQUERY([Link2x86Link], 'SELECT * FROM [\\HAL2011:6262\HPCommerce\Data\PLC.adt] AS PLC') AS tblHPQoH` on the x64 2008R2 connection, it gives the error listed above. I hope this makes sense.
    – jmcarlic
    Oct 30, 2012 at 17:14

2 Answers 2

Reset to default
2

I found my answer at MS SQL Server Forum: SQL Server 2005 X64 Linked Server error

Turnes out I had "Level zero only" checked

So, it goes something like this: x64 Query > Linked Server to x86 > View > Linked Server to ADS
So, something like this now works...

SELECT * FROM OPENQUERY([HAL2011\SQLEXPRESS], 'SELECT PartNum, OnHand FROM [master].[dbo].[vHPQoH]')

Where [HAL2011\SQLEXPRESS] is the x86 Server with a view [vHPQoH] to a lined server pointing to the Sybase ADS.

But thanks to everyone for your help. I still learned a lot! This place is great!

Improve this answer
0

You could create a database in your x86 SQL db which contains synonyms (or views if you only require read only access) pointing to the Sybase DB, then call those synonyms over your linked server from your x64 SQL db.

Not sure how you'd go about accessing a linked server via a linked server directly though (i.e. without the use of some intermediary object).

Improve this answer
4
  • Thanks! I'll try that in the morning when I get back to work.
    – jmcarlic
    Oct 30, 2012 at 3:55
  • I do only require read-only access for now. So, I setup a view on the SQL Express x86 and it works fine from that server. However, when I try to access that view via a linked server it gives the following error...
    – jmcarlic
    Oct 30, 2012 at 15:59
  • Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI10" for linked server "HAL2011\SQLEXPRESS" reported an error. The provider did not give any information about the error. Msg 7312, Level 16, State 1, Line 1 Invalid use of schema or catalog for OLE DB provider "SQLNCLI10" for linked server "HAL2011\SQLEXPRESS". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
    – jmcarlic
    Oct 30, 2012 at 15:59
  • Still can't connect with the x64 SQL 2008R2 to the view on the x86 Express. I thought it might be the Firewall blocking it since it's getting that timeout error, but I can connect to the View I setup on the x86 Express server that connects to the linked server for the ADS.
    – jmcarlic
    Oct 30, 2012 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.