I have two MS 2000 SQL Servers on the same domain. Both servers have the same databases and table structures setup on them, but with different data. I want to use a query to update one field in a table from one server to the other one.

I am not sure how the actual SQL would work, or how the connection strings to different servers and database works from within a SQL query.

Do I need to specify an IP address for the server? Do I need to specify the database name? Do I need login credentials? What is the syntax for all of that when using it in a SQL query? Etc?

I guess the pseudo code would be something like this:

Update Server2.DatabaseA.TableA.FieldA From Server1.DatabaseA.TableA Where Server2.DatbaseA.TableA.FieldA = "TEST"

As you can probably gather SQL syntax is not my strong point.

Thanks for any help!