Sql Server variable loose it's value in stored procedure

Question

SQL server 2008, I have a table which stored the created Job_id for each job and then for deleting the job I use a stored procedure (below) which gets the ID and tries to delete the job but although I'm sure that the job exists with @ScheduleReportID but the following select always returns null ! Job_id is of type UniqueIdentifier in tbRptSchedule

    declare @job_name nvarchar(max)
        SELECT @job_name = name 
        from msdb.dbo.sysjobs with(nolock) 
        where job_id = (select job_id from dbo.tbRptSchedule with(nolock) where ScheduledReportID = @ScheduledReportID)

        IF @job_name IS NULL
        BEGIN
         RAISERROR ('Cannot find Job identifier',16,1)
        END

Maybe you should use the sp_delete_job http://msdn.microsoft.com/en-us/library/ms187850.aspx — Yaroslav, Jul 23 '12 at 12:17
I'm using it but first I need to find the job name or job id — Asha, Jul 23 '12 at 12:18
Silly question but do you have the relevant permissions to query sysjobs — Kamal, Jul 23 '12 at 12:25
@nunespascal yes I store the JobID and here I want to retrieve it and the problem is that I cannot ! — Asha, Jul 23 '12 at 14:36
Query seems to be okay... Please confirm about the Database name "msdb" from where the job name is selected. — TechDo, Jul 23 '12 at 12:25

score 0 · Answer 1 · edited May 23 '17 at 12:13

0

Maybe the declare @jobname nvarchar(max) has something to do with the error as here and here sysname is equivalent to a NOT NULL varchar(128).

edited May 23 '17 at 12:13

Community

1
1

answered Jul 23 '12 at 12:26

Yaroslav

6,476
10
48
89

Sql Server variable loose it's value in stored procedure

1 Answers1