April 14, 2011 at 7:04 am
Hi there,
Is it possible to do the following via SQL 2000 Query Analyzer.
I have a bunch of tables via the DataWarehouse database which resides on SQL 2000.
I have another Database on a differnet server - however this is on a SQL 2005 BOX.
Can I use TSQL to write a script that would link the tables from thes different types of database?
Thanks
April 14, 2011 at 7:40 am
Ya, no problem. SQL2000's Query Analyzer can connect directly to a 2005 instance. In your case, since you need data from both instances, you can create a Linked Server from the 2000 box to point to your 2005 instance. Then you can query the remote 2005 database quite easily. Search for Linked Server and you'll see the syntax and how too.
One note about Linked Servers though, be careful about joining remote tables with local tables. At least in SQL2000, the remote table gets pulled over the network entirely and the local 2000 system will then discard the non matching rows. So performance is horrible. Being aware of this, you can create a 2005 view ON the 2005 instance which applies a where clause to discard as many irrelevant rows as possible so a smaller set of data is dragged over the network. Therefore, you should join your 2000 table(s) with the 'reduced-row' 2005 view.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 15, 2011 at 1:50 pm
At least in SQL2000, the remote table gets pulled over the network entirely and the local 2000 system will then discard the non matching rows.
AFAIK, 2005 and 2008 behave the same with pulling the entire table over a linked server when you join it to a local table. I have to deal with that a lot and we're all 2005/2008. There are a lot of ways to "get around" this, but none that are particularly great IMO. (Although using a view as you mentioned is probably the best)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply