Syntax for Across Server query

[tb]

Hi everyone,

I'm in need of some assistance in the above regard.

(Bit of back ground ... SQL 2000 VB GUI)

I have a Database on Server A and a Database on Server B which contains the information I need to combine in a table on the Database on Server A. As far as I know it is not linked servers (if it will help).

I need to do this in the most effecient way ... the way that I have started out doing this is with an Insert statement form Server A to Server A and then an Update from Server B for every record ... but there must be a more effecient way.

Another little hickup is that the Database on Server A can be one of 5 (different companies within the group) - so hard coding the database name is not an option, but 5 different stored procs would be fine.

Can anyone shed some light on my problem?
Any ideas would be much appreciated!

Chau




I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[rwb1912]

You will need to setup linked servers though in order to do this. Linked server will allow you to access table on another server and perform querries, i.e. copying data over from table on server b to server a.

Of course it largely depends on whether you wanted to perform this at any point in time. If it's a one of, you may consider writing stored procedures (using paramters for servernames so that you can dynamically call up the procedure to run for a different server) and use BCP to transfer for retrieving and transferring data. The large problem is trying to coordinate the export from server b and import of server a.

Another option would be to use replication although I think that from your comment about not wanting to use linked servers that you meant replication. Replication is also difficult to manage and can be a real pain in the back side to get running when it fails several times in a row.

Hope this helped.

Cheers

 

[rwb1912]

Sorry forgot the syntax bit for using the "linked" server. Check the online help for SQL Server for setting this up, make sure you got your security settings setup correctly.

The syntax of the query would pretty much look like any other query except that when referencing table on server b you would need to qualify the server to the table for server b. It would look something like

select x,y,z
from table-servera, linkedserverb.dbname.owner.tablename


Hope this helped

 

[tb]

Hi and thanks for your response.

The reason (given to me) for not using linked server is a matter of bandwidth this would've made my life so easy.

We have huge performance issues on the server, thus replication is also not an option for them.

I think the best way to solve this now is to go the route of DTS.

Thanks again.


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!