Solved

how to connect sybase database with sqlserver linked server?

Posted on 2002-06-11
3
484 Views
Last Modified: 2007-12-19
I have two database ,one is named phs in sqlserver,other is named inmsdb in sybase database server.I have some query  application must use the tables in both database server.So I create a ODBC DSN named 'inms' with WINDOWS2000 ODBC DATA SOURCE TOOL,Then i execute follow sentence to create a linked server in sqlserver database.
SQL SENTENCE :
sp_addlinkedserver @server=inms,@provider ='MSDASQL',@datasrc='inms'
,@srvproduct = 'Microsoft OLE DB Provider for ODBC'
sp_addlinkedsrvlogin inms,'false','sa','inms','PWD'
 
then i execute  follow sentence in sqlserver query anlalyer to query a table in sybase database inmsdb:

select * from  inms.inmsdb.dbo.alm3005_psc

but i receive the error information as follow:
SERVER: MSG 7399CLEVEL 16CSTATE 1CLINE 1
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: SYSTEM ERROR  126 (Sybase System 11)CCan't Load appointed Driver ]

now my problem is how can i link the sybase6.5 database with sqlserver2000 linked server and what' wrong in the sql sentence that i give above.

Thank you!
0
Comment
Question by:qzlmq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7069807
Using OLEdb for odbc drivers coupled with the SQL Server ODBC driver to connect to a Sybase database no longer works.  The newer Microsoft drivers are now optimized for use with SQL Server which renders them useless against Sybase.  Here's the Micrsoft inf on this:

http://search.microsoft.com/gomsuri.asp?n=1&c=rp_Results&siteid=us&target=http://support.microsoft.com/support/kb/articles/Q137/6/37.asp


There are some third party drivers out there for this purpose.  Try this link:

http://www.openlink.co.uk/

From the link above, download the single tier, Universal Data Access Drivers for Windows.


0
 

Author Comment

by:qzlmq
ID: 7069821
when i use sqlserver7.0 instead of the sqlserver2000,i found i can link the sybase database successful.but my database server is  sqlserver2000.
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 200 total points
ID: 7069856
Yes, in light of Microsoft's explanation of this in the link above, this makes sense.  The newer drivers from MDAC 2.6 on will error out when connecting to a Sybase server because they are optimized for sql server.  You may want to consider reverting back to MDAC 2.5 on that one server.  This will allow you to connect to a Sybase server.  

Here's microsoft's MDAC component checker tool that allows you to uninstall an MDAC . . .

http://www.microsoft.com/data/download.htm#CCinfo
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question