Deploying multiple SQL jobs in the same .sql file

So this is something I already do with Stored Procedures, and a bunch of other database items, and now I’m trying to do it with jobs. I write a bunch of items to a single .sql file. Other programs I use require this format. It looks clean, and it works.

I’m having an issue trying this with jobs, as it seems to not be dumping variable values when I start a new transaction. For example:

USE msdb;
BEGIN TRANSACTION

DECLARE @JobName = 'MyJob'

/*blah blah blah*/

COMMIT TRANSACTION

USE msdb;
BEGIN TRANSACTION

DECLARE @JobName = 'MySecondJob'

/*blah blah blah*/

COMMIT TRANSACTION

But when I run this file I get an error:

The variable name ‘@JobName’ has already been declared. Variable names must be unique within a query batch or stored procedure.

I don’t see how this is possible, as they are separate transactions. I tried clearing the intellisense cache, as I know that can cause issues, but so far no minor fixes have helped. This is in SQL Server 2014.

Answer

Try using GO statements between each execution block. For example:

USE msdb;
BEGIN TRANSACTION

DECLARE @JobName = 'MyJob'

/*blah blah blah*/

COMMIT TRANSACTION

GO

USE msdb;
BEGIN TRANSACTION

DECLARE @JobName = 'MySecondJob'

/*blah blah blah*/

COMMIT TRANSACTION

GO

Per Microsoft SQL documentation, GO signals the end of a batch of Transact-SQL statements to the SQL Server utilities

Advertisement

Answer