Solved

Transact SQL JobStep creation: Server not set

Posted on 2004-04-16
3
2,460 Views
Last Modified: 2007-12-19
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',
  @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'
0
Comment
Question by:ala_frosty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
a1x earned 300 total points
ID: 10843093
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)'

or

exec sp_add_jobserver @job_name='DTS Pkg',  @server_name='MyServr'
0
 
LVL 7

Author Comment

by:ala_frosty
ID: 10844353
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?
0
 
LVL 5

Expert Comment

by:a1x
ID: 10874261
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.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question