Distributed Transaction on linked server

[whitewolf33]

Hello,

this may be an interesting case.I have a MS SQL server 2000 (server A) and another SQL 2000 server(server B).
I do not want to replicate the data across from sever A to server B so i am using a stored procedure at a certain point when the server A is updated with data ,to do the distributed transaction to server B(set up as a remote server). It basically copies numerous tables from Server A and inserts into Server B tables which has the same schema as Server A.When the stored procedure is called automatically(from an ASP application) to do the distributed transaction to server B it does not happen where as when the same stoerd procedure is run from the Query analyzer of server A it is working absolutely fine.

I realised that the SERVER A and B should be set up as Linked servers in order to do the distributed transactions.But i am wondering how this is possible when executed through the SQL Query analyzer.

Thanks in advance

[rmiao]

You can access server b from server a with opendatasource instead of linked server, but linked server is easier if you like to do the process repeatedly.