Linked Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Linked Server

Author

Topic

san
Starting Member

26 Posts

Posted - 2002-10-22 : 12:26:08

I am facing an issue right now and would like to get your immediate assistance.

My application was running on two diffferent MSSQL databases and I have to run a query comparing the two SERVER.DATABASE by making one server as a linked server in the other. That query is a simple one which is kinda
====

select count(1) from table1 tc where modify_stmp < @check_date and not exists (SELECT * FROM server2.db2.dbo.table2 qc (nolock) where c_type = 'C' and qc.id=tc.id)

====
and is scheduled for once in every half an hour.

Good so far.

Now for some reason, we have changed one of our servers to Sybase and the other server is still MSSQL.

So My question is How can I run a query connecting the sybase server and the SQL server ?

If direct linking is impossible is their any alternative ?

Thanks.
San.

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-10-22 : 12:39:35

You will have to install an ODBC driver for Sybase on the SQL Server. I am not aware if Sybase does or does not have an ODBC driver, I'm sure they do, does anyone know? Once the ODBC driver for Sybase is installed you should be able to then link to it.

**********************************
Death must absolutely come to enemies of the code!

san
Starting Member

26 Posts

Posted - 2002-10-22 : 13:11:59

Roy,

wow..That's a relief for me. If you have sometime, could you please give some more info on how can I do it.

If you have some examples or website urls please pass it across.

Thanks,
san

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-22 : 13:19:24

Check out the links in this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11837

Also check the Sybase web site, they should have an OLE DB or ODBC driver you can use to create a linked server to your Sybase machines.

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-10-22 : 14:04:22

I did a quick search on Sybase's site, and they appear to have ODBC, but not as a seperate download, or at least this is what I believe after a quick search.

***************************************
Death must absolutely come to enemies of the code!

san
Starting Member

26 Posts

Posted - 2002-10-22 : 17:49:06

Thanks Guys.

I got the meat of the stuff and I may have to do somemore reserach finetuning it. Any tips is appreciated.

San.

san
Starting Member

26 Posts

Posted - 2002-10-23 : 12:35:31

I could add a Sybase server as the linked server in my MSSQL7.0 server and browse through the tables in Sybase server thru Enterprise Maanger.

But When I run the query
==
Select * from SybaseSVR.SybaseDB.dbo.SybaseTAB
==
then it is giving me a strange error
==
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
==

Have you come across such scenario any time ? I am using the latest ODBC Driver from Sybase.

Please advise.

-San.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 12:41:32

If you established the linked server to an individual Sybase database, then part of the 4-part name may be superfluous and might confuse the query parser. Try removing either the DB name or the owner name and see if it works:

Select * from SybaseSVR..dbo.SybaseTAB
--or--
Select * from SybaseSVR.SybaseDB..SybaseTAB

If the 4-part name doesn't work, try using OPENQUERY or OPENROWSET and see if you can get results from that. If they don't work either, then there's an incompatibility between the ODBC driver and SQL Server. You might need to update the MDAC components on your SQL Server too. You can get them here:

http://www.microsoft.com/data/download.htm

Get MDAC 2.6 at least, whatever the latest service pack is.

san
Starting Member

26 Posts

Posted - 2002-10-23 : 13:06:01

Thanks Rob.
I tried your suggestions but not lucky.
When I try

Select * from SybaseSVR..dbo.SybaseTAB

It says Invalid schema or catalog specified for provider 'MSDASQL'..

Select * from SybaseSVR.SybaseDB..SybaseTAB

will again throw the same 'Four-part name error'.

I could run it using openquery statement. But I need to join between the SybaseTable and MSSQLtable like the following existing query.

select count(1) from table1 tc where modify_stmp < @check_date and not exists (SELECT * FROM server2.db2.dbo.table2 qc (nolock) where c_type = 'C' and qc.id=tc.id)

Here the problem is TC and QC are in different Servers. I am not sure whether I can re-write this query with the openQuery Statement.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 13:11:44

Just see if either of these work:

Select * from SybaseSVR.dbo.SybaseTAB
--or--
Select * from SybaseSVR.SybaseDB.SybaseTAB

The only other thing I can suggest is to use a native OLE DB provider for Sybase, I'm pretty sure that one exists. Check the links in that thread I posted, one of them has another link in it that lists OLE DB providers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources