Triggers and Stored procedures -- sql server new users

Mike Epprecht (SQL MVP)
12/23/2004 7:42:46 AM

Hi

Post the code here so we can help you better.
Check that you have error handling. You need to evaluate @@error after each
statement. You might be getting an error, but your code is not detecting it.
Most linked server problems are caused by security. Check that the user
account that is executing the Insert into A has permissions to execute the
SP on the other server.

When you say that you run sp_D with success, are you connected to the 1st or
the 2nd server. Are you executing it on the first server with the naming
style of servername.database.owner.StoredProcedureName?

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

[quoted text, click to view]

Dear all:
I have three table named A, B and C. If any data inserted into or
updated to table A, a trigger of A will deal with data extraction and write
extracted data to B and C.
And, B has a trigger to deal with other data consistency job, a stored
procedure named sp_D. This stored procedure, sp_D, will performs some query
and determine which other jobs will be performed.
The stored procedure, sp_D, will execute a remote stored procedure
located in other database server.

My question is ,
while execute this remote stored procedure in Query Analyzer, the
job done well.
If this remote stored procedure running in a trigger of B, the job
will fail with no response.

Is there any detail things that I need to consider? Or, is there any
trigger and stored procedure concept that I need to know??

Best regards and Merry X'max

Andrew Lin

Shinsoft CO., LTD. Taiwan.

sql server new users : Triggers and Stored procedures