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
Solved

how to connect sybase database with sqlserver linked server?

Posted on 2002-06-11
3
473 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
  • 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 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