Solved

Transact SQL JobStep creation: Server not set

Posted on 2004-04-16
3
2,381 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
  • 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now