Solved

Transact SQL JobStep creation: Server not set

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

773 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