Trigger calling stored procedure
-
Hi,
I have a stored procedure called sp_imp - it works fine from query analyser or when executed using ADO etc..
I wanted to run it away from the client because it took 16 seconds + so I did the following
Created a new table called tbl_trigger and put a trigger on it that basiclly runs the first stored procedure on INSERT
CREATE TRIGGER trg_Import ON dbo.tbl_trigger
FOR INSERT
AS
DECLARE @BatchNo2 VARCHAR(9)
DECLARE @get_uid VARCHAR(9)
SELECT @get_UID = (SELECT @@IDENTITY)
/*PRINT(@GET_UID + ' TEST')*/
SELECT @BatchNo2 = (SELECT BATCH_NO from Tbl_Trigger WHERE ID = @GET_UID)
/*PRINT(@batchNo2 + ' TEST2')*/
EXEC prc_import_Order @batchNo = @batchNo2
I then made the client call a new stored procedure called sp_ins does the following
CREATE PROCEDURE dbo.sp_ins (@batchNo varchar(9))
AS
DECLARE @TSQL varchar(4000)
SET @TSQL = 'INSERT INTO tbl_trigger (batch_no) VALUES (' + @BATCHNO + ')'
EXEC (@TSQL)
GO
When I run the stored procedure on its own it works fine, from teh access client, from query analyser etc etc. However when it is run from the trigger it fails
<---PRINT put in the trigger to make sure the the correct parameter is flowing through from trigger to SP-->
INSERT INTO tbl_trigger (batch_no) VALUES (132209)
12 TEST
132209 TEST2
132209
INSERT INTO [gload].[dbo].[tbl_OHeader] (....) SELECT .... FROM Openquery(BS01BAA, 'SELECT * FROM GML01A.YSBTCHP_LINK TM INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO INNER JOIN GML01A.GLOAD_CUST T2 ON T1.ECSTN9 = T2.ECSTNC AND T1.EDTNO5 = T2.EDTNOC WHERE TM.BATCH_NUMBER = 132209') ORDER BY EORNO
<---PRINT put in the trigger to make sure the the correct parameter is flowing through from trigger to SP-->
<----The error---->
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. One or more arguments were reported invalid by the provider.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 90 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x80070057: One or more arguments were reported invalid by the provider.].
<--end error-->
Help me
Time to make a change -
The stored procedure I am executing from the trigger is found here
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=366303
I beleive now after doing a simple stored procedure it has something to do with not liking
openquery going through a linked server..
Anyone have any thoughts on this?
Time to make a change -
/bump
If anyone can help
Time to make a change -
What do you mean you wanted to run it away from the client because it takes 16 seconds?
Triggers in SQL 2000 are not asynchronous - if the procedure takes 16 seconds, then your trigger will take 16 seconds, thus - your insert will take 16 seconds and your client will still be waiting 16 seconds (plus the time to do the actual insert).
-
The stored procedure executed directly using ADO from the client takes 16 seconds to run....so I was investigating hwo to run the procedure in the background (on the server) without lagging out the clients access app for the same time..
If I just ran an insert on 1 table and fired off the procedure from a trigger..that would give the client control as quick as it took the insert to run...and the rest would happen as part of the trigger...right?
Time to make a change -
No. The trigger would wait for the stored procedure to complete. It is no asyncronous. If the stored procedure fails, the trigger fails and everything has to roll back. Remember the ACID principle.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply