• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2563
  • Last Modified:

Transact SQL JobStep creation: Server not set

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

-- 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',
  @owner_login_name= N'AnSaAccount',
  @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'
  • 2
1 Solution
I would run

exec sp_help_job @job_name = 'DTS Pkg', @job_aspect='TARGETS'

to verify that your server MyServr is a valid target server for the job.

If it is not then I would try running

exec sp_add_jobserver @job_name='DTS Pkg',  @server_name='(local)'


exec sp_add_jobserver @job_name='DTS Pkg',  @server_name='MyServr'
ala_frostyAuthor Commented:
exec sp_help_job @job_name = 'DTS Pkg', @job_aspect='TARGETS'

returned no records

exec msdb.dbo.sp_add_jobserver @job_name='DTS Pkg',  @server_name='(local)'

WORKED!!! Hurray. You get the points.

I haven't seen a solitary bit of documentation regarding needing a jobserver and that error message sure as heck didn't seem to indicate to me that was the problem. Thanks so much a1x.

May I ask what references I might read where I would learn more about such things?
I found a website that addressed your problem indirectly on Google. Of course, now I can't find it!

 I don't really know a good DTS reference.  I've learned it by trial and (sometimes frustrating) error.  

You could post another question on a good DTS reference.  or ask arbert.  he is very knowledgable.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now