Jun 222010
 

No more than an output to me, but it sure was to SQL Server!  This issue kept me busy over the weekend, and most definitely gave me a case of Saturday Night SQL Fever (as opposed to “SQL Saturday Night Fever”, for which the only cure is more karaoke).

When executing a stored procedure with an OUTPUT parameter, I always believed that the pre-execution state of a variable being written to by the output parameter didn’t matter, as it would be overwritten. A quick poll on Twitter showed me that I’m not alone, however I recently discovered this isn’t always the case.

Business requirements at my workplace are such that when a SQL Server Agent job needs to be modified, a script must be generated that does not update the job, but rather drops it and re-creates a new job. I was working on such a script (simplified and shown below) which uses the sp_delete_job and sp_add_job stored procedures to drop and add the job respectively.  Here’s what I came up with:

SET NOCOUNT ON;
DECLARE @jobId uniqueidentifier;
DECLARE @JobName NVARCHAR(128);
DECLARE @ReturnCode INT;
DECLARE @errCode INT;
SET @JobName = 'AJob';

BEGIN TRANSACTION;
SET @jobId = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName);

-- delete the job if it exists
IF(@jobId IS NOT NULL)
BEGIN
   EXEC @ReturnCode = msdb.dbo.sp_delete_job @job_id=@jobId
   IF(@@ERROR != 0 OR @ReturnCode != 0)
   BEGIN
      SET @errCode = @@ERROR;
      GOTO QuitWithRollback;
   END
   PRINT 'deleted job';
   --SET @jobId = NULL;
END

-- create the job
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
      @enabled=0,
      @job_id = @jobId OUTPUT
IF(@@ERROR != 0 OR @ReturnCode != 0)
BEGIN
   SET @errCode = @@ERROR;
   GOTO QuitWithRollback;
END
PRINT 'added job';

COMMIT TRANSACTION;
RETURN;

QuitWithRollback:
IF(@@TRANCOUNT > 0)
   ROLLBACK TRANSACTION;

PRINT 'Err: ' + CAST(@errCode AS varchar(10)) + ' ret: ' + cast(@ReturnCode as varchar(10));

If you run this script on SQL Server 2008 SP1, you’ll see that the first time it’s run the job is added successfully. Running it again shows that the delete succeeds but the add fails and returns error 14274: “Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.”

Outputs

Multiple Outputs

Scouring the web for information on error 14274, I found plenty of articles & blog posts with regard to SQL 2000 talking about how this error occurs when you change the name of a machine, and that you need to fix it by altering values in the ‘originating_server’ column of the msdb.dbo.sysjobs table. The problem is that the machine definitely didn’t change names since the job was created, and in SQL 2008 there is no ‘originating_server’ column, as it has apparently been replaced by ‘originating_server_id’ instead.

After much head scratching, I found that the cause of this error was the state of the @jobId variable prior to the execution of sp_add_job. If you uncomment the “SET @jobId = NULL” line in the above example and run again, you’ll see that it completes successfully the first time and every time thereafter.

Checking out the T-SQL code that’s under the hood of sp_add_jobs (which admittedly I should have done sooner), I found this:

IF (@job_id IS NULL)
BEGIN
   -- Assign the GUID
   SELECT @job_id = NEWID()
END
ELSE
BEGIN
   -- A job ID has been provided, so check that the caller is SQLServerAgent (inserting an MSX job)
   IF (PROGRAM_NAME() NOT LIKE N'SQLAgent%')
   BEGIN
      RAISERROR(14274, -1, -1)
      RETURN(1) -- Failure
   END
END

There are 2 cases for @job_id: either it’s NULL and a GUID needs to be assigned, or it’s not NULL and an error will be thrown if it wasn’t called by the SQL Server Agent.

Looking at the sp_add_job entry in Books Online, it shows that the @job_id parameter is an OUTPUT parameter with no mention of an input value being used, hence why I assumed any value stored in the @jobId variable would simply be overwritten. BOL does mention “with a default of NULL” but that wording didn’t get the point across (to me at least). If one creates a variable with a default of NULL and assigns a value later the default is still NULL, how does that violate the documentation’s wishes?

In my opinion, there’s 2 ways to reduce confusion on this issue:

1) Modify BOL to mention a little more forcefully that @job_id must be NULL when adding a new job. Something like “passing a non-null variable to @job_id is for internal use only” would be a great improvement.

2) Return a more descriptive error code when @job_id is not NULL.  While I’m sure the error code makes sense if you have complete understanding of what’s happening inside sp_add_job, it’s not very helpful if all you’ve done is read BOL.

  5 Responses to “More Than An Output”

  1. Good one, it’s easy to forget OUTPUT can be used for both input and output. The parameter direction attribute in C# helps remind me of this sometimes. When I see others be overly paranoid about setting variables to null I think really carefully before suggesting it’s not needed, just because of stuff like this, or when they’re being used in loops and may not be overwritten the way one might think.

  2. You should file a bug on connect.microsoft.com to get the error message changed.

    • I was thinking of doing that, but at this point I’m not sure if it’s a bug or if I’m just interpreting it incorrectly.

  3. Bob – I was running into the same problem with a script I wanted to use to check for the existence of multiple jobs and recreate them in they are missing. Your article saved me hours of time and frustration, I’m sure. Thanks for the information.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)