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 |
|
danyeung
Posting Yak Master
102 Posts |
 Posted - 2006-07-25 : 17:35:00
|
| I created a triggle in a table in SQLServer1 to execute a stored procedure in SQLServer2. The Linked Server "SQLServer2" is created in Security - Linked Servers in SQLServer1. The execute statement in the triggle is "exec SQLServer2.Database2.dbo.StoredProc2"."exec SQLServer2.Database2.dbo.StoredProc2" was executed successfully if I ran it from SQLServer1 SQL Analyzer. But it hung up if I insert a row in the table.Why the execute statement acts differently?Thanks.DanYeung |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-07-25 : 17:55:45
|
| Doing that from a trigger (I assume you mean a trigger, not a triggle) is probably a very bad idea, since it becomes a distributed transaction, and it would fail if the other server in not available.Please explain what you are trying to do, and perhaps someone can suggest an alternative.CODO ERGO SUM |
 |
|
|
danyeung
Posting Yak Master
102 Posts |
Posted - 2006-07-25 : 17:58:40
|
| When a new user is added to a table in SQLServer1, the trigger will execute the StoredProc2 to add the user to a table in SQLServer2. Any better suggestion?Thanks.DanYeung |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-07-25 : 18:08:33
|
| Have the application connect to both servers and call the procedure to add users on each server.orHave the trigger or stored procedure write the user information into a "queue" table, and then schedule a job on server one to move the user info to server two.CODO ERGO SUM |
|
|
|
danyeung
Posting Yak Master
102 Posts |
Posted - 2006-07-25 : 18:11:41
|
| I will consider you suggestion. Thanks. But I am still wondering why the execute statement works on SQL Analyzer but not in the trigger.Thanks.DanYeung |
|
|
|
|
|
|
Execute a stored procedure from the other server.