September 3, 2004 at 1:36 pm
Hi experts,
How to query from 2 different servers, say, server A (SS 2000) and server B (SS 7.0).???
The query will like this:
Select A.HFCMaintenanceHD.Vendor_Store_Table.vendorid,
B.HFC.PM00200.vendname from A.HFCMaintenanceHD.Vendor_Store_Table,B.HFC.PM00200
where A.HFCMainTenanceHD.Vendor_Store_Table.vendorid=B.HFC.PM00200.vendid
Thank you.
September 3, 2004 at 2:03 pm
here's a quick example,note that the commented out version will not work:
Server: Msg 117, Level 15, State 2, Line 7
The number name Server2.Northwind.dbo.Orders' contains more than the maximum number of prefixes. The maximum is 3.
USE Northwind
select *
from Customers
inner join Server2.Northwind.dbo.Orders AS Orders
on Customers.CustomerID = Orders.CustomerID
-- inner join Server2.Northwind.dbo.Orders
-- on Customers.CustomerID = Server2.Northwind.dbo.Orders.CustomerID
Everett Wilson
ewilson10@yahoo.com
September 3, 2004 at 2:53 pm
Are you querying the SQL2K box from the SQL7 box or vice verse?
You will either have to add the the SQL7 box to the SQL2K box as a linked server (preferably) or a remote server if you must.
If you have replication running between the boxes then one will automatically become a remote server.
You may have to run exec sp_serveroption <server2>,'Data Access','true'
September 5, 2004 at 11:55 pm
If you use linkservers (enabling you to use the long prefix syntax server.db.owner.table) then make sure they both have the same sorting options, otherwise you may get an error when doing a join.
If this does occure, look into the command COLLATE. with this you can 'translate' the sorting of one column, so it will not throw the error.
~nano
September 6, 2004 at 3:21 am
On the SQL 2000 server (A), use the sp_addlinkedserver command to allow you to query the SQL 7.0 server (B).
Then :
Select TableA.vendorid, TableB.vendname
from A.HFCMaintenanceHD.Vendor_Store_Table AS TableA, B.HFC.PM00200 AS TableB
where TableA.vendorid=TableB.vendid
should work fine, unless your collations are different. In which case you can use the sp_serveroption command to change the collations between the servers. Read up on the 'use remote collation' and 'collation compatible' and 'collation name' options
Julian Kuiters
juliankuiters.id.au
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply