Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shijil
Starting Member
3 Posts |
 Posted - 2008-12-22 : 05:14:07
|
| Hai All,I am using Linked Server inside triggers for distributed transaction in SQL Server 2005. I have created two linked servers to connect SQL Server in Machine1 to SQL Server in Machine2 and vice-versa. Both machines are configured in the same intra-network.While inserting values to table in Machine1, firing the trigger to insert the values to table in Machine2. Similarly Insertion Trigger fires while inserting values to table in Machine2.While inserting values to table, I am getting the Error 'Transaction context in use by another session'.Please suggest solution for this problemThanks in Advance, |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-22 : 06:15:59
|
quote:
Originally posted by shijil
Hai All,I am using Linked Server inside triggers for distributed transaction in SQL Server 2005. I have created two linked servers to connect SQL Server in Machine1 to SQL Server in Machine2 and vice-versa. Both machines are configured in the same intra-network.While inserting values to table in Machine1, firing the trigger to insert the values to table in Machine2. Similarly Insertion Trigger fires while inserting values to table in Machine2.Thanks in Advance,
Cross-servers trigger are very bad for performance. Use transactional replication or log-shipping instead |
 |
|
|
shijil
Starting Member
3 Posts |
Posted - 2008-12-22 : 06:57:40
|
| Thanks a lot sodeep, for the quick reply.My problem is little different one ...In my DB setup both machines are configured with different DB Schema. So I have to some data manipulation [ like (check for numeric data, if not discard the field),(trim the length of db field, if exceeds some limit),(conversion from datetime to varchar) etc] while exchanging data between two machines. So I think 'transactional replication or log-shipping' will not work here. Any more Solution .. Please |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-22 : 07:55:48
|
quote:
Originally posted by shijil
Thanks a lot sodeep, for the quick reply.My problem is little different one ...In my DB setup both machines are configured with different DB Schema. So I have to some data manipulation [ like (check for numeric data, if not discard the field),(trim the length of db field, if exceeds some limit),(conversion from datetime to varchar) etc] while exchanging data between two machines. So I think 'transactional replication or log-shipping' will not work here.
Yes you can do with transaction replication as well . The best way Is to create SSIS package and do your manipulation and run as job. |
|
|
|
shijil
Starting Member
3 Posts |
Posted - 2008-12-22 : 08:44:26
|
| Thanks a lot again sodeep,I used web service to populate the data inside SQL Server DB in Machine1 and firing the trigger to populate the same data inside SQL Server DB in Machine2 within the different schema. Reverse ... Whenever data is populating in Machine2 the trigger will fire and exchange data to DB in Machine1 and web service will read the data. So I can't run this as a Job. I want to exchange the data b/w machines as soon as it populates, by using Triggers only.Exactly, loop backing is happening while using linked servers inside triggers in SQL Server 2005. It is happening while the compiler reads loopback linked server inside the trigger (even thoughthat code branch would not be reached, since it is inside if condition). I think this is the specific problem in SQL Sercer 2005 and is not happening in 2000 and 2008.Please suggest, way to block this loop backing of triggers in SQL Server 2005. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-12-22 : 10:18:32
|
| you might want to consider using service broker messaging for this.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
|
|
|
|
|
Problem using Linked Server inside triggers in SQL