Solved

how to connect sybase database with sqlserver linked server?

Posted on 2002-06-11
3
434 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now