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.”
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.