none
Adding a job to sql agent

    Question

  • I am using SQL Server 2005 and am trying to create sql jobs via sql. Here is the procedure code that is called to create the jobs:

     

    alter PROCEDURE [dbo].[usp_StartListingsMigration_NEW] (@maxJobRunningCount int)
    AS
    BEGIN
    	declare @Count				int
    	select @Count = count(*) from ConversionJobs.dbo.JobQueue where ExecutionStatusID = 2
    	if (@Count >= @maxJobRunningCount) return
    	
    	declare @QueueCount			int
    	declare @x					int
    	declare @y					int
    	declare @q					varchar(2000)
    	select @QueueCount = @maxJobRunningCount - @Count
    	select @x = 1
    	select @y = 0
    	
    	declare @JobQueueID						int
    	declare @job_name						varchar(256)
    	declare @enabled						bit
    	declare @description					varchar(256)
    	declare @owner_login_name				varchar(64)
    	declare @notify_level_eventlog			int
    	declare @notify_level_email				int
    	declare @notify_level_netsend			int
    	declare @notify_level_page				int
        declare @notify_email_operator_name		varchar(64)
    	declare @delete_level					int
    	declare @TargetServer					varchar(64)
    	declare @MLSID							int
    	
    	declare @StepName						varchar(64)
    	declare @ExecutionOrder					int
    	declare @DatabaseName					varchar(128)
    	declare @DatabaseUserName				varchar(128)
    	declare @Subsystem						varchar(16)
    	declare @Command						varchar(2048)
    	declare @OnSuccessAction				int
    	declare @SSISConfigFile					varchar(512)
    	
    	declare @JobID							binary(16)
    	declare @ExecutionDate					datetime
    	
    	select @ExecutionDate = getdate()
    	
    	while @x <= @QueueCount
    	begin
    		-- Retrieve the top 1 job in the queue whose execution status = 1 (queued) order by DateQueued asc (the oldest first)
    		select top 1 @JobQueueID = JobQueueID, @job_name = JobName, @enabled = Enabled, @description = JobDescription, @owner_login_name = OwnerLoginName, 
    				@notify_level_eventlog = NotifyLevelEventLog, @notify_level_email = NotifyLevelEMail, 
    				@notify_level_netsend = NotifyLevelNetSend, @notify_level_page = NotifyLevelPage, 
    				@notify_email_operator_name = NotifyEmailOperatorName, @delete_level = DeleteLevel, @TargetServer = '(local)', 
    				@MLSID = MLSID 
    			from ConversionJobs.dbo.JobQueue 
    			where ExecutionStatusID = 1
    			order by DateQueued asc
    				
    		-- create the job that will execute the HBMListings synchronization and then the UnifiedListings conversion
    		select @job_name = cast(@MLSID as varchar(3)) + '_' + @job_name + '_' + replace(cast(@ExecutionDate as varchar(50)), ' ', '_') + '_' + cast(@JobQueueID as varchar(10))
    		
    		print 'Job Name: ' + @job_name
    		
    		exec msdb.dbo.sp_add_job 
    			@job_name = @job_name, 
    			@enabled = @enabled, 
    			@description = @description, 
    			@owner_login_name = @owner_login_name, 
    			@notify_level_eventlog = @notify_level_eventlog, 
    			@notify_level_email = @notify_level_email, 
    			@notify_level_netsend = @notify_level_netsend, 
    			@notify_level_page = @notify_level_page, 
    			@notify_email_operator_name = @notify_email_operator_name, 
    			@delete_level = @delete_level, 
    			@job_id = @JobID output
    				
    		exec msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
    				
    		update ConversionJobs.dbo.JobQueue set ExecutionStatusID = 2 where JobQueueID = @JobQueueID
    		select @x = @x + 1
    	end
    END

    I execute the procedure using:

    exec

     

    msdb.dbo.[usp_StartListingsMigration_NEW] 5


    I get the following returned to me:

    Job Name: 411_ListingsMigration_Oct_15_2009__7:27PM_4

    (1 row(s) affected)
    Job Name: 394_ListingsMigration_Oct_15_2009__7:27PM_5
    Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
    Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
    Msg 14269, Level 16, State 1, Procedure sp_add_jobserver, Line 101
    Job '411_ListingsMigration_Oct_15_2009__7:27PM_4' is already targeted at server 'HBMSEARCH01'.

    (1 row(s) affected)
    Job Name: 54_ListingsMigration_Oct_15_2009__7:27PM_6
    Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
    Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
    Msg 14269, Level 16, State 1, Procedure sp_add_jobserver, Line 101
    Job '411_ListingsMigration_Oct_15_2009__7:27PM_4' is already targeted at server 'HBMSEARCH01'.

    (1 row(s) affected)
    Job Name: 74_ListingsMigration_Oct_15_2009__7:27PM_7
    Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
    Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
    Msg 14269, Level 16, State 1, Procedure sp_add_jobserver, Line 101
    Job '411_ListingsMigration_Oct_15_2009__7:27PM_4' is already targeted at server 'HBMSEARCH01'.

    (1 row(s) affected)
    Job Name: 231_ListingsMigration_Oct_15_2009__7:27PM_8
    Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
    Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
    Msg 14269, Level 16, State 1, Procedure sp_add_jobserver, Line 101
    Job '411_ListingsMigration_Oct_15_2009__7:27PM_4' is already targeted at server 'HBMSEARCH01'.

    (1 row(s) affected)

    To me, it appears as though the system is willing to place the first job into the queue, but then freaks out on all subsequent job adds. Any help would be greatly appreciated.

    Thank you.

    Wayne E. Pfeffer

    Friday, October 16, 2009 12:43 AM

Answers

  • Yes, I have verified that the script is creating the jobs the same way as I have done so manually. I have also placed print statements at various places in the script to be sure that the the correct commands are being generated.

    If either were the case, wouldn't that cause all 'sp_add_job' calls to fail, not just all those after the first one?

    Wayne E. Pfeffer


    Old thread, but having had the same problem myself thought I would post a solution:

    sp_add_job looks for the @jobid which is created on the first run through

    Add: Set @jobid = NULL

    before calling the sp_add_job and it should loop through :-)


    Tuesday, June 21, 2011 3:01 PM

All replies

  • Related links:

    This usually happens when you change the server name
    http://www.lazydba.com/sql/1__4897.html
    http://fluppe.wordpress.com/2007/04/02/ms-sql-server-error-14724-msx-server/


    Error 14274 Occurs When You Update a SQL Agent Job After Renaming Windows Server
    http://support.microsoft.com/kb/281642
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, October 16, 2009 2:42 AM
    Moderator
  • I know, I think I have read nearly everything out on the internet about that. But, I haven't changed the server name and if that were case why would the first one work but the rest of them not? It will add the first job: 411_ListingsMigration_Oct_15_2009__7:27PM_4, but not the rest of them.

    I have also noticed that every solution I have found references a column named: originating_server in the table msdb.dbo.sysjobs. I am running SQL Server 2005 and the sysjobs table on my server does not containt a column named that. It does contain a column named: originating_server_id that contains an integer value that relates to 'server_id' in 'sys.servers'. According to this, the originating_server_id = 0 and in the sys.servers table the server associated with id = 0, is the correct server.

    Wayne E. Pfeffer
    • Edited by wpfeffer Friday, October 16, 2009 2:19 PM new information added
    Friday, October 16, 2009 1:57 PM
  • What is @@version?

    Are you running this on the server computer?

    Instead of (local) can you use explicit server name?


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, October 16, 2009 2:40 PM
    Moderator
  • @@VERSION = Microsoft SQL Server 2005 - 9.00.3054.00 (X64)   Mar 23 2007 18:41:50   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    I have tried both, running it on the server computer and from SSMS on my workstation, logged into the server as an admin. I just tried running it on the server from SSMS and received the following (which is the same error as before): (I made the change from '(local)' to '<server name>')
    Job Name: 411_ListingsMigration_Oct_16_2009__9:48AM_4
    
    (1 row(s) affected)
    Job Name: 394_ListingsMigration_Oct_16_2009__9:48AM_5
    Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
    Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
    Msg 14269, Level 16, State 1, Procedure sp_add_jobserver, Line 101
    Job '411_ListingsMigration_Oct_16_2009__9:48AM_4' is already targeted at server 'HBMSEARCH01'.
    
    (1 row(s) affected)
    Job Name: 54_ListingsMigration_Oct_16_2009__9:48AM_6
    Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
    Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
    Msg 14269, Level 16, State 1, Procedure sp_add_jobserver, Line 101
    Job '411_ListingsMigration_Oct_16_2009__9:48AM_4' is already targeted at server 'HBMSEARCH01'.
    
    (1 row(s) affected)
    Job Name: 74_ListingsMigration_Oct_16_2009__9:48AM_7
    Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
    Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
    Msg 14269, Level 16, State 1, Procedure sp_add_jobserver, Line 101
    Job '411_ListingsMigration_Oct_16_2009__9:48AM_4' is already targeted at server 'HBMSEARCH01'.
    
    (1 row(s) affected)
    Job Name: 231_ListingsMigration_Oct_16_2009__9:48AM_8
    Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
    Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
    Msg 14269, Level 16, State 1, Procedure sp_add_jobserver, Line 101
    Job '411_ListingsMigration_Oct_16_2009__9:48AM_4' is already targeted at server 'HBMSEARCH01'.
    
    (1 row(s) affected)
    Thanks for your help so far. I'm really at an impasse on this.

    Wayne E. Pfeffer
    • Edited by wpfeffer Friday, October 16, 2009 2:52 PM Added more information
    Friday, October 16, 2009 2:50 PM

  • I have also noticed that every solution I have found references a column named: originating_server in the table msdb.dbo.sysjobs. I am running SQL Server 2005 and the sysjobs table on my server does not containt a column named that.
    Wayne,

    Are you sure about that?  originating_server_id

    One more link: http://blog.sqlauthority.com/2006/12/20/sql-server-fix-error-14274-cannot-add-update-or-delete-a-job-or-its-steps-or-schedules-that-originated-from-an-msx-server-the-job-was-not-saved/

    Can you post the results?

    select TOP (2) * from msdb.dbo.sysjobs
    select * from sys.servers

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, October 16, 2009 3:34 PM
    Moderator
  • I generated the following script from the msdb.dbo.sysjobs table on my server:

    USE [msdb]
    GO
    /****** Object:  Table [dbo].[sysjobs]    Script Date: 10/16/2009 10:37:41 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[sysjobs](
    	[job_id] [uniqueidentifier] NOT NULL,
    	[originating_server_id] [int] NOT NULL,
    	[name] [sysname] NOT NULL,
    	[enabled] [tinyint] NOT NULL,
    	[description] [nvarchar](512) NULL,
    	[start_step_id] [int] NOT NULL,
    	[category_id] [int] NOT NULL,
    	[owner_sid] [varbinary](85) NOT NULL,
    	[notify_level_eventlog] [int] NOT NULL,
    	[notify_level_email] [int] NOT NULL,
    	[notify_level_netsend] [int] NOT NULL,
    	[notify_level_page] [int] NOT NULL,
    	[notify_email_operator_id] [int] NOT NULL,
    	[notify_netsend_operator_id] [int] NOT NULL,
    	[notify_page_operator_id] [int] NOT NULL,
    	[delete_level] [int] NOT NULL,
    	[date_created] [datetime] NOT NULL,
    	[date_modified] [datetime] NOT NULL,
    	[version_number] [int] NOT NULL
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    

    result from 'select top(2) * from msdb.dbo.sysjobs':
    job_id originating_server_id name enabled description start_step_id notify_level_eventlog notify_level_email notify_level_netsend notify_level_page notify_email_operator_id notify_page_operator_id delete_level date_created date_modified version_number
    730C2018-4ACE-42AC-B157-07253CB0244D 0 ListingsMigration_UnifiedListings_2009-10-16T10:32:52.257_104 1 Populated UnifiedListings db for territory104 on date 2009-10-16T10:32:52.257 1 0 0x01050000000000051500000001D66344A8A9E3AAE6779F00350B0000 2 2 0 0 1 0 0 1 2009-10-16 10:32:52.257 2009-10-16 10:32:52.320 3
    7823F851-2A51-4F73-A4C3-0EBAEF35B623 0 ListingsMigration_UnifiedListings_2009-10-16T10:34:26.383_315 1 Populated UnifiedListings db for territory315 on date 2009-10-16T10:34:26.383 1 0 0x01050000000000051500000001D66344A8A9E3AAE6779F00350B0000 2 2 0 0 1 0 0 1 2009-10-16 10:34:26.383 2009-10-16 10:34:26.383 3

    result from 'select * from sys.servers':
    server_id name product provider data_source location provider_string catalog connect_timeout query_timeout is_linked is_remote_login_enabled is_rpc_out_enabled is_data_access_enabled is_collation_compatible uses_remote_Collation collation_name lazy_schema_validation is_system is_publisher is_subscriber is_distributor is_nonsql_subscriber modify_date
    0 HBMSEARCH01 SQL Server SQLNCLI HBMSEARCH01 NULL NULL NULL 0 0 0 1 1 0 0 1 NULL 0 0 0 0 0 0 2009-04-17 08:49:03.083
    1 SQLPROD01 SQL Server SQLNCLI SQLPROD01 NULL NULL NULL 0 0 1 0 0 1 0 1 NULL 0 0 0 0 0 0 2009-05-01 16:06:51.730
    2 SQLPROD02 SQL Server SQLNCLI SQLPROD02 NULL NULL NULL 0 0 1 0 0 1 0 1 NULL 0 0 0 0 0 0 2009-05-05 13:20:13.990

    Thanks again for your help. (I apologize for the use of tables, but it was the only way I could get the information formatted in a semi-readable way.)

    Wayne E. Pfeffer
    • Edited by wpfeffer Friday, October 16, 2009 4:11 PM made it a little bit more readable
    Friday, October 16, 2009 4:10 PM
  • Wayne,

    Can you turn on SQL Profiler (or ask the DBA to do it) and capture the exact statements you are sending to the server.

    Execute the captured job statements in Management Studio Query Editor and see if they work.

    Let us know what happens.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, October 16, 2009 6:04 PM
    Moderator
  • I have been doing some more work on this ... I am able to create the jobs by hand, ie I execute sp_add_job manually for each job needing to be created. These get created without any problem. Is there something about timing that I'm missing here?

    Wayne E. Pfeffer
    Friday, October 16, 2009 7:22 PM
  •  Is there something about timing that I'm missing here?


    That loop is suspect...

    Can you insert a the following 15sec delay into the WHILE loop and test?

    WAITFOR DELAY '00:00:15'

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, October 16, 2009 7:38 PM
    Moderator
  • I had tried that yesterday, but figured I'd give it a try again anyway. I tried it at 15, 30 and 60 seconds, all with the same results, same errors.

    Wayne E. Pfeffer
    Friday, October 16, 2009 8:02 PM
  • How about capturing the scripts in SQL Server Profiler?
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, October 16, 2009 8:05 PM
    Moderator
  • I have looked at the execution in profiler both by executing the stored procedure via:

    exec dbo.usp_StartListingsMigration 5

    and by placing the code of the sproc into a new query window in SSMS and nothing useful comes up (I am using the default template in profiler). All it shows me is either the exec command or a the listing of the sproc.

    There's probably something I'm not capturing in profiler that I should be, I'm just not sure what that is.

    I have even gone so far as to place the sprocs and the database on my local machine (@@VERSION = Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)) and try it, and it is still not working, same errors.

    Wayne E. Pfeffer
    • Edited by wpfeffer Friday, October 16, 2009 8:11 PM readability
    Friday, October 16, 2009 8:09 PM
  • Can you post a complete issue recreate script, including CREATE TABLE(s) and INSERT INTO statements to populate? Naturally with dummy data.

    Thanks.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, October 16, 2009 8:29 PM
    Moderator
  • USE [master]
    GO
    /****** Object:  Database [ConversionJobs]    Script Date: 10/16/2009 15:30:37 ******/
    CREATE DATABASE [ConversionJobs] ON  PRIMARY 
    ( NAME = N'ConversionJobs', FILENAME = N'F:\SQLDAT\ConversionJobs.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
     LOG ON 
    ( NAME = N'ConversionJobs_log', FILENAME = N'F:\SQLDAT\ConversionJobs_log.ldf' , SIZE = 4096KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
    EXEC dbo.sp_dbcmptlevel @dbname=N'ConversionJobs', @new_cmptlevel=90
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [ConversionJobs].[dbo].[sp_fulltext_database] @action = 'disable'
    end
    GO
    ALTER DATABASE [ConversionJobs] SET ANSI_NULL_DEFAULT OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET ANSI_NULLS OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET ANSI_PADDING OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET ANSI_WARNINGS OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET ARITHABORT OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET AUTO_CLOSE OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET AUTO_CREATE_STATISTICS ON 
    GO
    ALTER DATABASE [ConversionJobs] SET AUTO_SHRINK OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET AUTO_UPDATE_STATISTICS ON 
    GO
    ALTER DATABASE [ConversionJobs] SET CURSOR_CLOSE_ON_COMMIT OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET CURSOR_DEFAULT  GLOBAL 
    GO
    ALTER DATABASE [ConversionJobs] SET CONCAT_NULL_YIELDS_NULL OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET NUMERIC_ROUNDABORT OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET QUOTED_IDENTIFIER OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET RECURSIVE_TRIGGERS OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET  ENABLE_BROKER 
    GO
    ALTER DATABASE [ConversionJobs] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET DATE_CORRELATION_OPTIMIZATION OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET TRUSTWORTHY OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET ALLOW_SNAPSHOT_ISOLATION OFF 
    GO
    ALTER DATABASE [ConversionJobs] SET PARAMETERIZATION SIMPLE 
    GO
    ALTER DATABASE [ConversionJobs] SET  READ_WRITE 
    GO
    ALTER DATABASE [ConversionJobs] SET RECOVERY SIMPLE 
    GO
    ALTER DATABASE [ConversionJobs] SET  MULTI_USER 
    GO
    ALTER DATABASE [ConversionJobs] SET PAGE_VERIFY CHECKSUM  
    GO
    ALTER DATABASE [ConversionJobs] SET DB_CHAINING OFF 
    USE [ConversionJobs]
    GO
    /****** Object:  Table [dbo].[ExecutionStatus]    Script Date: 10/16/2009 15:31:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[ExecutionStatus](
    	[ExecutionStatusID] [int] IDENTITY(1,1) NOT NULL,
    	[ExecutionStatusName] [varchar](128) NOT NULL,
     CONSTRAINT [PK_ExecutionStatus] PRIMARY KEY CLUSTERED 
    (
    	[ExecutionStatusID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
     CONSTRAINT [UK_ExecutionStatus_ExecutionStatusName] UNIQUE NONCLUSTERED 
    (
    	[ExecutionStatusName] ASC
    )WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    USE [ConversionJobs]
    GO
    /****** Object:  Table [dbo].[JobQueue]    Script Date: 10/16/2009 15:32:25 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[JobQueue](
    	[JobQueueID] [int] IDENTITY(1,1) NOT NULL,
    	[JobName] [varchar](256) NOT NULL,
    	[Enabled] [bit] NOT NULL CONSTRAINT [DF_JobQueue_Enabled]  DEFAULT ((1)),
    	[JobDescription] [varchar](256) NOT NULL,
    	[OwnerLoginName] [varchar](64) NOT NULL CONSTRAINT [DF_JobQueue_OwnerLoginName]  DEFAULT ('HBM2\hbmwebadmin'),
    	[NotifyLevelEventLog] [int] NOT NULL CONSTRAINT [DF_JobQueue_NotifyLevelEventLog]  DEFAULT ((2)),
    	[NotifyLevelEMail] [int] NOT NULL CONSTRAINT [DF_JobQueue_NotifyLevelEMail]  DEFAULT ((2)),
    	[NotifyLevelNetSend] [int] NOT NULL CONSTRAINT [DF_JobQueue_NotivyLevelNetSend]  DEFAULT ((2)),
    	[NotifyLevelPage] [int] NOT NULL CONSTRAINT [DF_JobQueue_NotifyLevelPage]  DEFAULT ((2)),
    	[NotifyEmailOperatorName] [varchar](64) NOT NULL CONSTRAINT [DF_JobQueue_NotifyEmailOperatorName]  DEFAULT ('Developers'),
    	[DeleteLevel] [int] NOT NULL CONSTRAINT [DF_JobQueue_DeleteLevel]  DEFAULT ((1)),
    	[TargetServer] [varchar](64) NOT NULL,
    	[MLSID] [int] NOT NULL,
    	[DateQueued] [datetime] NOT NULL,
    	[DateStarted] [datetime] NULL,
    	[DateFinished] [datetime] NULL,
    	[Completed] [bit] NOT NULL CONSTRAINT [DF_JobQueue_Completed]  DEFAULT ((0)),
    	[ExecutionStatusID] [int] NOT NULL,
    	[JobID] [binary](16) NULL,
     CONSTRAINT [PK_JobQueue_JobQueueID] PRIMARY KEY CLUSTERED 
    (
    	[JobQueueID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[JobQueue]  WITH CHECK ADD  CONSTRAINT [FK_JobQueue_ExecutionStatus] FOREIGN KEY([ExecutionStatusID])
    REFERENCES [dbo].[ExecutionStatus] ([ExecutionStatusID])
    GO
    ALTER TABLE [dbo].[JobQueue] CHECK CONSTRAINT [FK_JobQueue_ExecutionStatus]
    USE [ConversionJobs]
    GO
    /****** Object:  Table [dbo].[JobSteps]    Script Date: 10/16/2009 15:32:47 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[JobSteps](
    	[JobStepID] [int] IDENTITY(1,1) NOT NULL,
    	[JobQueueID] [int] NOT NULL,
    	[StepName] [varchar](64) NOT NULL,
    	[ExecutionOrder] [int] NOT NULL CONSTRAINT [DF_JobSteps_ExecutionOrder]  DEFAULT ((1)),
    	[DatabaseName] [varchar](128) NOT NULL CONSTRAINT [DF_JobSteps_DatabaseName]  DEFAULT ('msdb'),
    	[DatabaseUserName] [varchar](128) NOT NULL CONSTRAINT [DF_JobSteps_DatabaseUserName]  DEFAULT ('HBM2\hbmwebadmin'),
    	[Subsystem] [varchar](16) NOT NULL CONSTRAINT [DF_JobSteps_Subsystem]  DEFAULT ('TSQL'),
    	[Command] [varchar](2048) NOT NULL,
    	[OnSuccessAction] [int] NOT NULL CONSTRAINT [DF_JobSteps_OnSuccessAction]  DEFAULT ((3)),
    	[SSISConfigFile] [varchar](512) NULL,
    	[DateQueued] [datetime] NULL,
     CONSTRAINT [PK_JobSteps_JobStepID] PRIMARY KEY CLUSTERED 
    (
    	[JobStepID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
     CONSTRAINT [UK_JobSteps_JobQueueID_StepName] UNIQUE NONCLUSTERED 
    (
    	[JobQueueID] ASC,
    	[StepName] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[JobSteps]  WITH CHECK ADD  CONSTRAINT [FK_JobSteps_JobQueue] FOREIGN KEY([JobQueueID])
    REFERENCES [dbo].[JobQueue] ([JobQueueID])
    GO
    ALTER TABLE [dbo].[JobSteps] CHECK CONSTRAINT [FK_JobSteps_JobQueue]
    USE [ConversionJobs]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_ListingPhotoInfo]    Script Date: 10/16/2009 15:33:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[usp_ListingPhotoInfo] 
    	@territoryId int = 3, 
    	@serverName varchar(100) = 'hbmsearch01', 
    	@ConfigFile varchar(512) = 'D:\SSISConfig\ListingPhoto.dtsConfig'
    with execute as owner
    AS
    BEGIN
    
    	DECLARE @startDate varchar(23)
    	DECLARE @jobName varchar(100)
    	DECLARE @desc varchar(100)
    	DECLARE @stepName varchar(100)
    	DECLARE @usp_ListingsMigration varchar(1024)
    	DECLARE @territoryIdStr varchar(4)
    	declare @JobQueueID			int
    
    	SET @territoryIdStr = convert(varchar(3),@territoryId) 
    	SET @startDate = convert(varchar(23), GETDATE(), 126)
    
    	SET @jobName = N'ListingsPhoto'
    	SET @desc = N'Update photo information in UnifiedListings db for territory' + @territoryIdStr + ' on date ' + @startDate
    	SET @stepName = N'Start ListingPhoto SSIS' + @startDate + @territoryIdStr 
    	set @usp_ListingsMigration = N'/SQL "\UpdatePhotoInfo" /SERVER ' + @serverName + ' /CONFIGFILE "' + @ConfigFile + '" /SET \package.variables[vMLSID].Value;"\"' + @territoryIdStr + '"\"'
    
    	INSERT INTO [ConversionJobs].[dbo].[JobQueue] ([JobName],[Enabled],[JobDescription],[OwnerLoginName],[NotifyLevelEventLog],[NotifyLevelEMail],[NotifyLevelNetSend],[NotifyLevelPage],[NotifyEmailOperatorName],[DeleteLevel],[TargetServer],[MLSID],[DateQueued],[DateStarted],[DateFinished],[Completed],[ExecutionStatusID])
        VALUES(@jobName,1,@desc,N'HBM2\hbmwebadmin',2,2,2,2,N'Developers',1,@serverName,@territoryId,getdate(),NULL,NULL,0,1)
    
    	select @JobQueueID = JobQueueID from [ConversionJobs].[dbo].JobQueue where JobName = @jobName
    
    	INSERT INTO [ConversionJobs].[dbo].[JobSteps] ([JobQueueID],[StepName],[ExecutionOrder],[DatabaseName],[DatabaseUserName],[Subsystem],[Command],[OnSuccessAction],[SSISConfigFile],[DateQueued])
        VALUES (@JobQueueID,@stepName,1,'msdb',N'HBM2\hbmwebadmin',N'SSIS',@usp_ListingsMigration,3,@ConfigFile,getdate())
    END
    
    Friday, October 16, 2009 8:34 PM
  • USE [ConversionJobs]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_ListingsMigration]    Script Date: 10/16/2009 15:34:28 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[usp_ListingsMigration] 
    	@territoryId int = 3, 
    	@serverName varchar(100) = 'hbmsearch01', 
    	@ConfigFile varchar(512) = 'D:\SSISConfig\ListingMigration.dtsConfig'
    with execute as owner
    AS
    BEGIN
    	DECLARE @startDate varchar(23)
    	DECLARE @jobName varchar(100)
    	DECLARE @desc varchar(100)
    	DECLARE @stepName varchar(100)
    	DECLARE @usp_ListingsMigration varchar(1024)
    	DECLARE @territoryIdStr varchar(4)
    
    	SET @territoryIdStr	= convert(varchar(3),@territoryId) 
    	SET @startDate		= convert(varchar(23), GETDATE(), 126)
    	
    	declare @JobQueueID			int
    
    	SET @jobName = N'ListingsMigration' 
    	SET @desc = N'Populated UnifiedListings db for territory' + @territoryIdStr + ' on date ' + @startDate
    	
    	INSERT INTO [ConversionJobs].[dbo].[JobQueue] ([JobName],[Enabled],[JobDescription],[OwnerLoginName],[NotifyLevelEventLog],[NotifyLevelEMail],[NotifyLevelNetSend],[NotifyLevelPage],[NotifyEmailOperatorName],[DeleteLevel],[TargetServer],[MLSID],[DateQueued],[DateStarted],[DateFinished],[Completed],[ExecutionStatusID])
        VALUES(@jobName,1,@desc,N'HBM2\hbmwebadmin',2,2,2,2,N'Developers',1,@serverName,@territoryId,getdate(),NULL,NULL,0,1)
    	
    	select @JobQueueID = JobQueueID from [ConversionJobs].[dbo].JobQueue where JobName = @jobName
    	SET @stepName = N'Start syncHBMListingsSearch ' + @startDate + @territoryIdStr 
    	SET @usp_ListingsMigration = N'exec usp_SyncHBMListingsSearch ' + @territoryIdStr
    	
    	INSERT INTO [ConversionJobs].[dbo].[JobSteps] ([JobQueueID],[StepName],[ExecutionOrder],[DatabaseName],[DatabaseUserName],[Subsystem],[Command],[OnSuccessAction],[SSISConfigFile],[DateQueued])
        VALUES (@JobQueueID,@stepName,1,'msdb',N'HBM2\hbmwebadmin',N'TSQL',@usp_ListingsMigration,3,@ConfigFile,getdate())
    	
    	SET @stepName = N'Start ListingMigration SSIS' + @startDate + @territoryIdStr 
    	set @usp_ListingsMigration = N'/SQL "\ListingsMigration" /SERVER ' + @serverName + ' /CONFIGFILE "' + @ConfigFile + '" /CONSOLELOG NCOSGXMT /l "DTS.LogProviderTextFile;ListingsMigration.log" /SET \package.variables[vMLSID].Value;"\"' + @territoryIdStr + '"\" /SET \package.variables[vJobSystemName].Value;"\"' + @jobName + '"\"'
    
    	INSERT INTO [ConversionJobs].[dbo].[JobSteps] ([JobQueueID],[StepName],[ExecutionOrder],[DatabaseName],[DatabaseUserName],[Subsystem],[Command],[OnSuccessAction],[SSISConfigFile],[DateQueued])
        VALUES (@JobQueueID,@stepName,2,'msdb',N'HBM2\hbmwebadmin',N'SSIS',@usp_ListingsMigration,3,@ConfigFile,getdate())
    END
    
    alter PROCEDURE [dbo].[usp_StartListingsMigration] (@maxJobRunningCount int)
    AS
    BEGIN
    	declare @Count				int
    	select @Count = count(*) from ConversionJobs.dbo.JobQueue where ExecutionStatusID = 2
    	if (@Count >= @maxJobRunningCount) return
    	
    	declare @QueueCount			int
    	declare @x					int
    	declare @y					int
    	declare @q					varchar(2000)
    	select @QueueCount = @maxJobRunningCount - @Count
    	select @x = 1
    	select @y = 0
    	
    	declare @JobQueueID						int
    	declare @JobName						varchar(256)
    	declare @Enabled						bit
    	declare @Description					varchar(256)
    	declare @OwnerLoginName					varchar(64)
    	declare @NotifyLevelEventlog			int
    	declare @NotifyLevelEmail				int
    	declare @NotifyLevelNetsend				int
    	declare @NotifyLevelPage				int
        declare @NotifyEmailOperatorName		varchar(64)
    	declare @DeleteLevel					int
    	declare @TargetServer					varchar(64)
    	declare @MLSID							int
    	
    	declare @StepName						varchar(64)
    	declare @ExecutionOrder					int
    	declare @DatabaseName					varchar(128)
    	declare @DatabaseUserName				varchar(128)
    	declare @Subsystem						varchar(16)
    	declare @Command						varchar(2048)
    	declare @OnSuccessAction				int
    	declare @SSISConfigFile					varchar(512)
    	
    	declare @JobID							binary(16)
    	declare @ExecutionDate					datetime
    	
    	select @ExecutionDate = getdate()
    	
    	while @x <= @QueueCount
    	begin
    		-- Retrieve the top 1 job in the queue whose execution status = 1 (queued) order by DateQueued asc (the oldest first)
    		select top 1 @JobQueueID = JobQueueID, @JobName = JobName, @Enabled = Enabled, @Description = JobDescription, @OwnerLoginName = OwnerLoginName, 
    				@NotifyLevelEventlog = NotifyLevelEventLog, @NotifyLevelEmail = NotifyLevelEMail, 
    				@NotifyLevelNetsend = NotifyLevelNetSend, @NotifyLevelPage = NotifyLevelPage, 
    				@NotifyEmailOperatorName = NotifyEmailOperatorName, @DeleteLevel = DeleteLevel, @TargetServer = '(local)', 
    				@MLSID = MLSID 
    			from ConversionJobs.dbo.JobQueue 
    			where ExecutionStatusID <> 2
    			order by DateQueued asc
    				
    		-- create the job that will execute the HBMListings synchronization and then the UnifiedListings conversion
    		select @JobName = cast(@MLSID as varchar(3)) + '_' + @JobName + '_' + replace(cast(@ExecutionDate as varchar(50)), ' ', '_') + '_' + cast(@JobQueueID as varchar(10))
    		
    		print 'Job Name: ' + @JobName
    		
    		exec msdb.dbo.sp_add_job 
    			@job_name = @JobName, 
    			@enabled = @Enabled, 
    			@description = @Description, 
    			@owner_login_name = @OwnerLoginName, 
    			@notify_level_eventlog = @NotifyLevelEventlog, 
    			@notify_level_email = @NotifyLevelEmail, 
    			@notify_level_netsend = @NotifyLevelNetsend, 
    			@notify_level_page = @NotifyLevelPage, 
    			@notify_email_operator_name = @NotifyEmailOperatorName, 
    			@delete_level = @DeleteLevel, 
    			@job_id = @JobID output
    				
    		exec msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
    				
    		update ConversionJobs.dbo.JobQueue set ExecutionStatusID = 2 where JobQueueID = @JobQueueID
    		select @x = @x + 1
    	end
    END
    Friday, October 16, 2009 8:36 PM
  • use ConversionJobs
    go

    insert into ExecutionStatus (ExecutionStatusName) values ('Error Starting Job')
    insert into ExecutionStatus (ExecutionStatusName) values ('Executing')
    insert into ExecutionStatus (ExecutionStatusName) values ('Finished')
    insert into ExecutionStatus (ExecutionStatusName) values ('Queued')
    go

    insert into JobQueue (JobQueueID, JobName, Enabled, JobDescription, OwnerLoginName, NotifyLevelEventLog, NotifyLevelEMail, NotifyLevelNetSend, NotifyLevelPage, NotifyEmailOperatorName, DeleteLevel, TargetServer, MLSID, DateQueued, DateStarted, DateFinished, Completed, ExecutionStatusID, JobID) values (4,"ListingsMigration",1,"Populated UnifiedListings db for territory411 on date 2009-10-15T12:44:42.247"","Domain\user",2,2,2,2,"Developers",1,"<server name>",411,10/15/2009 12:44:42 PM,NULL,NULL,0,4,)

    insert into JobQueue (JobQueueID, JobName, Enabled, JobDescription, OwnerLoginName, NotifyLevelEventLog, NotifyLevelEMail, NotifyLevelNetSend, NotifyLevelPage, NotifyEmailOperatorName, DeleteLevel, TargetServer, MLSID, DateQueued, DateStarted, DateFinished, Completed, ExecutionStatusID, JobID) values (21,"ListingsPhoto",1,"Update photo information in UnifiedListings db for territory179 on date 2009-10-15T13:29:59.040","Domain\user",2,2,2,2,"Developers",1,"<server name>",179,10/15/2009 1:29:59 PM,NULL,NULL,0,4,)

    insert into JobSteps (JobStepID, JobQueueID, StepName, ExecutionOrder, DatabaseName, DatabaseUserName, Subsystem, Command, OnSuccessAction, SSISConfigFile, DateQueued) values (1,4,"Start syncHBMListingsSearch 2009-10-15T12:44:42.247411",1,"msdb",HBM2\hbmwebadmin","TSQL","exec usp_SyncHBMListingsSearch",411,3,"D:\SSISConfig\ListingMigration.dtsConfig",10/15/2009 12:44:42 PM)
    insert into JobSteps (JobStepID, JobQueueID, StepName, ExecutionOrder, DatabaseName, DatabaseUserName, Subsystem, Command, OnSuccessAction, SSISConfigFile, DateQueued) values (2,4,"Start ListingMigration SSIS2009-10-15T12:44:42.247411",2,"msdb",HBM2\hbmwebadmin","SSIS","/SQL "\ListingsMigration" /SERVER hbmsearch01 /CONFIGFILE "D:\SSISConfig\ListingMigration.dtsConfig" /CONSOLELOG NCOSGXMT /l "DTS.LogProviderTextFile;ListingsMigration.log" /SET \package.variables[vMLSID].Value;"\"411"\" /SET \package.variables[vJobSystemName].Value;"\"ListingsMigration_UnifiedListings_2009-10-15T12:44:42.247_411"\"",3,"D:\SSISConfig\ListingMigration.dtsConfig",10/15/2009 12:44:42 PM)
    insert into JobSteps (JobStepID, JobQueueID, StepName, ExecutionOrder, DatabaseName, DatabaseUserName, Subsystem, Command, OnSuccessAction, SSISConfigFile, DateQueued) values (35,21,"Start ListingPhoto SSIS2009-10-15T13:29:59.040179",1,"msdb",HBM2\hbmwebadmin","SSIS","/SQL "\UpdatePhotoInfo" /SERVER hbmsearch01 /CONFIGFILE "D:\SSISConfig\ListingPhoto.dtsConfig" /SET \package.variables[vMLSID].Value;"\"179"\"",3,"D:\SSISConfig\ListingPhoto.dtsConfig",10/15/2009 1:29:59 PM)
    Friday, October 16, 2009 9:26 PM
  • I think all of the above should get you what you need. Let me know if you figure out anything.

    Wayne E. Pfeffer
    Friday, October 16, 2009 9:27 PM
  • I have been doing some more work on this ... I am able to create the jobs by hand, ie I execute sp_add_job manually for each job needing to be created. These get created without any problem. Is there something about timing that I'm missing here?

    Wayne E. Pfeffer

    Have you scripted the jobs you successfully create and compare them to what you are trying to do manually?  (ie. SQL Server Agent -> right click on the job -> Script job as create to...)

    Also, instead of executing sp_add_job, have you used a print statement to confirm that the values are the way they are supposed to be? 
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Friday, October 16, 2009 9:33 PM
    Moderator
  • First two scripts compile OK.  Last script does not compile:

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near 'Domain'.

    Msg 103, Level 15, State 4, Line 2

    The identifier that starts with ',411,10/15/2009 12:44:42 PM,NULL,NULL,0,4,)

    insert into JobQueue (JobQueueID, JobName, Enabled, JobDescription, OwnerLoginNam' is too long. Maximum length is 128.

    Msg 103, Level 15, State 4, Line 4

    The identifier that starts with ',179,10/15/2009 1:29:59 PM,NULL,NULL,0,4,)

    insert into JobSteps (JobStepID, JobQueueID, StepName, ExecutionOrder, DatabaseNam' is too long. Maximum length is 128.

    Msg 102, Level 15, State 1, Line 7

    Incorrect syntax near '\'.

    Msg 132, Level 15, State 1, Line 7

    The label 'T12' has already been declared. Label names must be unique within a query batch or stored procedure.

    Msg 103, Level 15, State 4, Line 7

    The identifier that starts with ',10/15/2009 12:44:42 PM)

    insert into JobSteps (JobStepID, JobQueueID, StepName, ExecutionOrder, DatabaseName, DatabaseUserName,' is too long. Maximum length is 128.

    Msg 132, Level 15, State 1, Line 8

    The label 'T13' has already been declared. Label names must be unique within a query batch or stored procedure.

    Msg 132, Level 15, State 1, Line 8

    The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.

    Msg 1038, Level 15, State 4, Line 8

    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, October 16, 2009 9:46 PM
    Moderator
  • Yes, I have verified that the script is creating the jobs the same way as I have done so manually. I have also placed print statements at various places in the script to be sure that the the correct commands are being generated.

    If either were the case, wouldn't that cause all 'sp_add_job' calls to fail, not just all those after the first one?

    Wayne E. Pfeffer
    Monday, October 19, 2009 1:09 PM
  • I changed various names to protect the innocent and because they would be different from those you would be using. Look for anything enclosed in angle brackets (<>).

    Wayne E. Pfeffer
    Monday, October 19, 2009 1:18 PM
  • Wayne,

    Can you repost the FIXED 3rd script? See above.

    >If either were the case, wouldn't that cause all 'sp_add_job' calls to fail, not just all those after the first one?

    There are many mysteries in Computer Science. We hope yours will get resolved.



    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
     
    Monday, October 19, 2009 1:20 PM
    Moderator
  • Hi
        I had same similar problem in SQL 2008. It seems that scripts creating two or more jobs must be in separate batches.
    everything is a matter of probability...
    Tuesday, October 27, 2009 1:18 PM
  • Yes, I have verified that the script is creating the jobs the same way as I have done so manually. I have also placed print statements at various places in the script to be sure that the the correct commands are being generated.

    If either were the case, wouldn't that cause all 'sp_add_job' calls to fail, not just all those after the first one?

    Wayne E. Pfeffer


    Old thread, but having had the same problem myself thought I would post a solution:

    sp_add_job looks for the @jobid which is created on the first run through

    Add: Set @jobid = NULL

    before calling the sp_add_job and it should loop through :-)


    Tuesday, June 21, 2011 3:01 PM
  • Perfect!

    It solved a problem that drove me nuts for hours :) 

    Thank you.

    Tuesday, September 04, 2012 7:44 PM