How to update from a database on another server?

[Jenns]

I need to update data in a database on Server A with data in a database on Server B.

How do I reference Server B in the query in my DTS package on Server A?. Server A is SQL2000, Sever B is SQL7 (if that makes a difference).


Thanks!
Jenn

 

[JayKusch]

A fully qualified name ... as in ...

[ServerB].[dbo].[TableName]

or if you are processing on the field level ...


[ServerB].[dbo].[TableName].[ColumnName]

Thanks

J. Kusch

 

[JayKusch]

OOPS ... Was wrong there ... forgot the DB portion ...


[ServerB].[DBName].[dbo].[TableName]



Thanks

J. Kusch

 

[tb]

How do you guys do it?

Don't you use the Microsoft OLE DB Provider for SQL Server (as the Connection to the appropriate servers)?
Where you spesify the exact server & db with the authentication.

Then use a Transformation Data Task to link to the other server's Microsoft OLE DB Provider for SQL Server (connection)

In the properties the use the sql query to extract the data required and spesify the Destination table on the Destination tab and ont the Transformation tab see if all the fields match up . .

Am I missing something . . . or is there such a vast majority of ways to do it?

"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[Jenns]

Thank you J.

All I needed to do was run sp_addlinkedserver in the master database and then I was able to referenece the server like you suggested.



Thanks!
Jenn