We have a situation where our application calls some stored procedures on a sql 2000 server. Now we must get some of the data from another sql 2000 box connected by a vpn.
What would the syntax look like for performing CRUD operations from one sql server to another sql server?
Both database servers are SQL 2000 and running Windows 2003.
You could use the Linked Server feature of SQL Server.
A linked server configuration allows Microsoft SQL Server to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:
Remote server access
The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
The ability to address diverse data sources similarly.
A query could look like this:
SELECT * FROM MyRemoteServer.MyDB.dbo.MyTable
You simply refer to the data in the linked server using fully-qualified names ie: SELECT Columns FROM ServerName.DatabaseName.dbo.TableName
http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1155184,00.html
On a side note -
Ensure that distributed transactions coordinator service is running on the clients and the servers DTC.