Distributed Transaction on linked server
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