Exchange data between databases on two servers?

Dear all

I have tow server on the same intranet. One server has a sql server 2000 database and the other one has SQL server 2005 databse.

The sql 2000 database has a table called employee. When ever a new employee is inserted in the database i would like the same values to be sent to the sql 2005 database. But this cant be done on the application level. It has to be done in the database. The application level can not be changed.

I was thinking a trigger but how to achieve the writing from one database to another. If they were on the same server then it would be easier but because they are on different servers i dont know how to do it.

Has anyone had similar issue before?

Any help is apreciated.

Sincerely

Dan

First make sure 2 server are trusted.

Then link 2 servers using:EXEC sp_addlinkedserver 'server2'.

Quantifier of the table is: [server2].dbo.[db].[table], 'dbo' is the default security schema, use a proper schema name if it's different.

If you used trigger on the table, and the transaction failed, you may want to read this discussion: http://forums.databasejournal.com/showthread.php?t=6701

|||

Thank you for the response.

For everyone else that might come across this post the way to go is using linked servers.

Once you create the linked server (as a security Object in SQL05) you can use 4 part naming. In a normal scenario only 3 part naming is suported. The forth part is the linked servr name.

Please make sure that the DTC service is turned on. Check for proxies and firewals becasue if you have those they will have to be configured properly or othervise it will not work.

Last thing you should check is that you are running the latest version of the MDAC's. I belive at the time i make this post the most current version is 2.8. This will ensure that you have all the latest providers.

Cheers

Dan