Solved

Transact SQL JobStep creation: Server not set

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
backup job space check 4 44
Is this spec enough for a developer or is it just blabble ? 1 38
Stored Proc - Rewrite 42 60
Parse this column 6 27
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.

832 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