Dealing with two databases

Liquidloop

Hello,

Question 1:

In my senario i've developed a system which utilizes 2 database, i've writen queries like db1.dbo.table1 join db2.dbo.table2 etc... Now that db2 is getting huge, client wants to shift it to another server.

I don't know how to modifiy my queries to cope with such situation. Could somebody plz tell me on how to you write queries involving two databases from different servers.

Question 2:

I'm maintaining second database (db2) to keep track of records of db1 which have been processed by my software, so that when db1 gets added with more records i can compare db2 table with db1 table to identify which records are new.

db1 is not my database and i don't have any control over that, (it's some erp db), is there any way of identifying which rows have been processed. Can the need for db2 be eliminated

I'm using SQL Server 2005

Thank You,

Sandeep.

richbrownesq

Question 1:

You should take a look at Linked Servers and Ad Hoc Distributed Queries such as OPENROWSET and OPENQUERY (though the option to run Ad Hoc DIstinbuted Queries is turned off by default in SQL2005 but can be changed using the SQL Surface Area Configuration Tool). With a linked server you can then access the data on ServerB from ServerA using 4 part naming:

SELECT *
FROM ServerA.YourDB.dbo.YourTable

Question 2:

You don't really have any way of seeing what tables are new/processed unless there is some identifing field on the record such as a date stamp which you can 100% trust. I'm not sure i understand your requirement for tracking processed data but i think you would be looking at maintaing copies of the data either by logshipping, replication or triggers (if you are able to add these to the source database).

HTH!