Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to connect sybase database with sqlserver linked server?

Posted on 2002-06-11
3
Medium Priority
?
523 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 600 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

579 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