Why does this trigger not work ??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Why does this trigger not work ???

Author

Topic

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-14 : 07:48:28

Hello guys,
I have a trigger that calls a SP. However the SP takes a while to execute. So the trigger is rejected.

What is wrong

[CODE]USE [sallymac]
GO
/****** Object: Trigger [dbo].[BeginProcessing] Script Date: 10/14/2008 10:23:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BeginProcessing]
ON [dbo].[Mytable]
AFTER INSERT,update
AS
declare @id int,@username varchar(20)
select @id = batchid, @username = username from inserted

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--exec Processing_BULK @id, @username
exec [dbo].[Processing_BULK ] @id, @username

[/CODE]

PROBLEM:

When I run the exec in red, it works ok. But when i run the execute SP in green, it hangs for a while and then rejects it.

I have tried updating manually. Also ran the SP in Management studio, and has got a problem, only problem is that it takes a while to execute

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 07:50:13

Which schema are you using for Processing_BULK stored procedure?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 07:52:14

execute both statements

sp_helptext Processing_BULK
sp_helptext dbo.Processing_BULK

and see if body is different.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 07:55:27

And you trigger code only returns one batchid and username even if many has been updated or inserted.
Are you sure you want that? You can include extra check to reject updates and/or inserts if they affect more than one record.

E 12°55'05.63"
N 56°04'39.26"

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-14 : 08:03:13

quote:
Originally posted by visakh16

execute both statements

sp_helptext Processing_BULK
sp_helptext dbo.Processing_BULK

and see if body is different.

They are both the same schema. both dbo. It was bcos it wasnt being executed, that i tried changing them

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-14 : 08:04:39

quote:
Originally posted by Peso

And you trigger code only returns one batchid and username even if many has been updated or inserted.
Are you sure you want that? You can include extra check to reject updates and/or inserts if they affect more than one record.

E 12°55'05.63"
N 56°04'39.26"

Am just testing it now for one value. And tis still giving problems

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 08:06:57

So Processing_BULK stored procedure has the dbo schema?
What error message do Processing_BULK stored procedure throw?

E 12°55'05.63"
N 56°04'39.26"

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-14 : 08:08:01

It just does not work. IT throws out the insert or update, and says the execution timedout

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 08:09:19

quote:

Originally posted by missMac

 --exec        Processing_BULK @id, @username 
 exec [dbo].[Processing_BULK ] @id, @username

I see now.
When you execute the green line, there is an extra space added to the stored procedure name. And you don't have that stored procedure.
Remove the trailing space in the stored procedure name and you are go.

 --exec        Processing_BULK @id, @username 
 exec [dbo].[Processing_BULK] @id, @username

If you have performance issues with processing_bulk stored procedure, please post a new topic with reference to this topic.

E 12°55'05.63"
N 56°04'39.26"

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-14 : 08:11:33

no no no

You are not understanding me.

When I exec the green line, since the SP takes for ever to execute. It fails and the trigger is rolled back.

Why ? do triggers fail, when a SP takes a long time to execute. The trigger is a backup which takes a while to execute.

Please help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 08:16:04

SQL Server tries to find your misspelled stored procedure for (i think) 30 seconds.
When the stored procedure is not found, you get an error, not a warning.

The error rolls back the current transaction.

Try again and remove the trailing space just before the closing bracket.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 08:17:31

quote:

Originally posted by missMac

 --exec        Processing_BULK @id, @username 
 exec [dbo].[Processing_BULK ] @id, @username

These two stored procedure names are NOT the same. They will NOT execute the same stored procedure.

E 12°55'05.63"
N 56°04'39.26"

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-14 : 08:19:32

I have only one SP in my db called Processing_BULK

And if i use the schema name or not, it will search for the only object called Processing_BULK which is a SP.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 08:22:12

Haven't you read anything I have answered you?
The problem is NOT the schema.
The problem is that you have a trailing space in your stored procedure name in the green line, which you don't have in the red line.
And thus, the two lines will NOT execute the same stored procedure.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 08:23:46

The green line will try to execute a stored procedure which you don't have!
And SQL Server will try for about 30 seconds (I think) to find the stored procedure in

1) Current database with specified schema
2) Current database with dbo schema
3) Master database

or similiar approach.

E 12°55'05.63"
N 56°04'39.26"

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-14 : 08:23:59

Ok.

But what i want to know is why the 2nd SP is not executing. Its executes in QA and management studio without problems

it just takes a while to execute

so it fails when called by the trigger. Why ? The trigger complains of timeouts

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 08:25:50

And you are 100% percent sure you use the exact same procedure name in QA?
With trailing space in stored procedure name?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 08:25:59

exec [dbo].[Processing_BULK<spacehere>] @id, @username

remove space after sp name as peso suggested. thats causing error. it looks for sp name with space at end and cant find it hence the error. this is what Peso has been suggesting in last few threads.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 08:29:00

If the stored procedure is found and executing without errors, it doen't matter how long it takes to complete (unless you have set a command timeout in your client application).
QA doesn't have a command timeout. ADO and OLEDB is defaulted to 30/60 seconds for command timeout.

When finished, the trigger continues to run and eventually let go to inserting/updating code.

E 12°55'05.63"
N 56°04'39.26"

missMac
Posting Yak Master

124 Posts

Posted - 2008-10-14 : 09:11:16

Has nothing to do with the space.I have removed it but same problem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 09:21:34

Ok. Post the code for Processing_BULK.

E 12°55'05.63"
N 56°04'39.26"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources