can not invoke sp_update_job from a trigger

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 2000 Forums

SQL Server Administration (2000)

can not invoke sp_update_job from a trigger

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-17 : 09:09:20

chuck writes "I have a problem. I want to create a trigger on msdb.dbo.sysjobs that inserts the name of all inserted dts packages into a table named "tracking_change_owner". This table only has one field "name" to hold this value. Then I have a trigger on that table that I want to invoke the stored procedure "sp_update_job". I have learned from research that a dynamic execute is considered a different "scope" and that any local variables you create cannot be accessed from inside the EXEC string. I have tried every imaginable way to invoke sp_update job from a trigger. Can you think of anything I may want to try?

/*****************************************************************************************************/
CREATE TABLE dbo.tracking_change_owner(
	name                 sysname NOT NULL
)
GO

/*****************************************************************************************************/
CREATE TRIGGER ti_sysjobs
    ON sysjobs
    FOR INSERT

AS

    BEGIN
		INSERT into tracking_change_owner 
		(name) 
		select name FROM inserted 
    END

GO

/*****************************************************************************************************/
CREATE trigger ti_tracking_change_owner

AS

DECLARE @vJob_name sysname,
@vSQL varchar(1000)

SELECT @vJob_name = name from tracking_change_owner 
SELECT @vSQL = 'sp_update_job @job_name = "' + convert(varchar, @vJob_name) + '", @owner_login_name = "sa"' 
exec (@vSQL)
GO
/*****************************************************************************************************/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-17 : 09:52:06

First, you should be very careful adding triggers to system tables. It is a BAD idea!

Having said that, your trigger doesn't actually modify the system tables itself, so it shouldn't cause too many problems. If you can find a method that avoids putting triggers on system tables, even if it's more work, you should use it instead.

I think it might be that you were using double quotes (") instead of single quotes (') to delimit the job name and login in your dynamic SQL. See if this works:

CREATE trigger ti_tracking_change_owner AS
DECLARE @vJob_name sysname, @vSQL varchar(1000)
SELECT @vJob_name = name from tracking_change_owner
SELECT @vSQL = 'sp_update_job @job_name = ''' + convert(varchar(128), @vJob_name) + ''', @owner_login_name = ''sa'''
exec (@vSQL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources