Transact SQL JobStep creation: Server not set
Posted on 2004-04-16
When this code executes, the job is created, but I receive the following error message:
Server: Msg 14256, Level 16, State 1, Procedure sp_start_job, Line 38
Cannot start job 'DTS Pkg' (ID 123A4B33-9A66-4C4E-AB95-BC2A32D59968) because it does not have any job server(s) defined.
If, after this error, I open the Job in the Enterprise Manager and change ANYTHING, then save it, the server is set to (presumably) '(Local)'. NetBios is not used so I don't think the 30 character limitation is in play here.
Other things of note:
If I try to set the jobstep name to '(Local)' in the SQL code, I get an error:
Server: Msg 14234, Level 16, State 1, Procedure sp_verify_jobstep, Line 149
The specified '@server' is invalid (valid values are returned by sp_helpserver).
There is only one server from sp_helpserver: MyServr
In the "General" tab of the JobWindow on EM, I see the following: Source: "MyServr"
the only 'Server' available (from the target multiple servers list)is '(Local)'. The 'target local server' radio is selected. When I click on it as though to select it, "Apply" changes from disabled to enabled.
Once saved, the Job "Runnable" status changes from "No (Add target servers to the job) " to "Yes".
I just can't figure out how to create a job so that a server is targetted at the outset and the jobs do NOT require manual user intervention to run.
Here's the code
DECLARE @job_id_out UNIQUEIDENTIFIER
-- Create the job
EXEC msdb.dbo.sp_add_job @job_name = 'DTS Pkg',
@enabled = 1,
@description = 'This is a job used to run a DTS package',
@start_step_id = 1,
@notify_level_eventlog = 3,
@notify_level_netsend = 1, -- Yes, AUser exists
@notify_netsend_operator_name = 'AUser',
@job_id = @job_id_out OUTPUT
--, @originating_server= N'(Local)' --> Does nothing
-- Add a step to the job just created
EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id_out,
@step_id = 1,
@step_name = 'Start DTS Package',
@subsystem = 'CMDEXEC',
@command = 'Dtsrun /SMyServr /NImportTestFiles /E"',
-- @command = '"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /SMyServr /NImportTestFiles /UAcct /P"My Password"',
@server = N'MyServr'
-- @server = N'(Local)' -- Does not work-> error
-- That does not work, so try changing the server
-- after the fact to see if this helps.
EXEC msdb.dbo.sp_update_jobstep @job_id = @job_id_out, @step_Id = 1, @server = N'MyServr'
-- Set the help -- don't know what this does, I copied it
-- from somewhere else.
EXEC msdb.dbo.sp_help_job @job_name = 'DTS Pkg'
EXEC msdb.dbo.sp_start_job @job_name = 'DTS Pkg'