Solved

Need help with a transact SQL Query that is returning errors!!

Posted on 2008-10-21
10
219 Views
Last Modified: 2013-11-30
I am trying to build a query with a cursor that queries a table with detailed server names.  The idea behind the query is to have the cursor loop through the table, grab the server name and run a stored procedure "msdb.dbo.sp_start_job @job_Name = 'FC Updates', @server_name = @srvname".  I want the stored procedure to start a job remotely on the allocated server in the table named "FC Updates".  I am receiving an error:
"Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 61
The specified @job_name ('FC Updates') does not exist."

Any ideas? Query is attached.
Cursor-FC-Update-Job-.txt
0
Comment
Question by:DADITWebGroup
[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
  • 5
  • 5
10 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22771996
are you running:

exec servername.msdb.dbo.sp_start_job @job_Name = 'FC Updates', @server_name = @srvname

or just

exec msdb.dbo.sp_start_job @job_Name = 'FC Updates', @server_name = @srvname

Because unless the job exists locally on the server you are running it from, with a target server of @srvname, then this won't work.  You will need to execute it via the linked server call like I have shown above.
0
 
LVL 1

Author Comment

by:DADITWebGroup
ID: 22781535
This is what I get when I execute your code...

Could not find server 'srvname' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22781837
you have to add the linked server.
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 1

Author Comment

by:DADITWebGroup
ID: 22781998
I have 40 linked servers that are already configured and are used in several other jobs.  I attached my query.  Thanks for your help.
FC-Update-Job.txt
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22782794
But what I'm saying is that you aren't attempting to run a job that exists on the remote (linked) server with your query, you are attempting to execute a LOCAL job that is TARGETed to a remote server.


For example, I will comment out your start job statements with the correct syntax for executing a remote job below it.


Just so you know, sp_Start_job is asynchronous.  sp_start_job sends an alert to the SQL Agent service telling it to start a job.  The call then returns and you don't have any status update as to when it finished.  So all your "Completion time:" will represent is the time it took to start all the jobs, not the time it took to execute them.

Check out this to see how we tackled the problem of executing synchronous jobs.
http://www.experts-exchange.com/Q_23817564.html

Declare @srvname nvarchar(50), @statuslevel int, @status varchar(100)
,@SQL nvarchar(4000)
Declare SrvName cursor for
	Select Distinct srvname
	from sysservers
	order by srvname    
Open SrvName
Fetch Next From SrvName into @srvname
While @@fetch_status = 0
	Begin
		Print 'Beginning ' + @srvname + ' fc update'
		Exec master.dbo.stp_ServerStatus @srvname, @statuslevel output, @status output
		If @status = 'Running'
			Begin
 
--EXEC msdb.dbo.sp_start_job @job_Name = 'FC Updates', @server_name = @srvname
--^^ this executes a local job to a remote target
--EXEC @srvname.msdb.dbo.sp_start_job @job_Name = 'FC Updates'
--^^ This executes a remote job on a remote server.  
--   But, since you can't put a variable in the server of a 4 part name
--   we need to put it in dynamic SQL like BELOW 
-- \\//
--  \/
--
set @SQL = 'EXEC [' @srvname + '].msdb.dbo.sp_start_job @job_Name = ''FC Updates'' '
 
exec (@SQL)
				Print @srvname + ' fc update of branch numbers is executing.'
			End
		Else
			Begin
				Print @srvname + ' is unavailable thus the fc update has not been completed for this server'
			End
		Print ' '
		Print ' '
		Fetch Next From SrvName into @srvname
	End
	Print ' '
	Print ' '
	Print 'Completion time: ' + convert(varchar(50),getdate())
Close SrvName
Deallocate SrvName
GO

Open in new window

0
 
LVL 1

Author Comment

by:DADITWebGroup
ID: 22787932
Thanks for laying this out for me.  However, I am getting one small syntax error that I can't seem to fix with your code.  Probably something small that I overlooked "as usual".  

Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near '@srvname'.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22787974
On line 25 I forgot the + after exec ['

should be:

set @SQL = 'EXEC [' + @srvname + '].msdb.dbo.sp_start_job @job_Name = ''FC Updates'' '
 
0
 
LVL 1

Author Comment

by:DADITWebGroup
ID: 22790807
Now this error....I must be missing something else.

Could not find server ' ABERDEEN' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 250 total points
ID: 22790836
based upon the error, I think you may have copied or corrected something incorrectly.  This is indicating that you have mistakenly put a space between the [ and the ' on line 25.


I bet you have:
set @SQL = 'EXEC [ ' + @srvname + '].msdb.dbo.sp_start_job @job_Name = ''FC Updates'' '
 
instead of:
set @SQL = 'EXEC [' + @srvname + '].msdb.dbo.sp_start_job @job_Name = ''FC Updates'' '
 
0
 
LVL 1

Author Closing Comment

by:DADITWebGroup
ID: 31508527
Fantastic experience!!  Thanks for the prompt assistance and detailed explainations of what was occuring.  
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

729 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