Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


JobName Not found error


JobName Not found error

Author
Message
Mac1986
Mac1986
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 787
HI All,
I'm seeing a intresting error on one of my SQL Instance.

Below is the script I'm running,, connecting to the SQL Instance where the job exists.
Exec msdb.dbo.sp_start_job 'My_Job_Name'

But when I call the same job as a linked server, I get an error that Job Name does not exists.
Exec ServerName.msdb.dbo.sp_start_job 'My_Job_Name'

Please suggest What can be done here
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7443 Visits: 15126
I would guess that you can't start a job remotely via a linked server. You could try using Powershell or SSIS instead. Or you could set up some triggers on the remote server so that they fire jobs when you update a table.

John
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
Have you tried EXEC() AT?

EXEC('EXEC msdb.dbo.sp_start_job ''My_Job_Name''') AT [ServerName];



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4905 Visits: 7365
John Mitchell-245523 (12/21/2012)
I would guess that you can't start a job remotely via a linked server. You could try using Powershell or SSIS instead. Or you could set up some triggers on the remote server so that they fire jobs when you update a table.

John
Incorrect. You can very easily start a job remotely on a linked server. I would however, recommend you that you use the job uniqueidentifier rather than passing in the job name itself (although it should work either way) - what options did you configure for your linked server?
Example:

DECLARE @job_id uniqueidentifier
SET @job_id = '80209C18-A8AD-48BA-8D6B-10D785FE4383'
EXECUTE [LinkedServer].msdb.dbo.sp_start_job @job_id



______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
MyDoggieJessie (12/21/2012)
John Mitchell-245523 (12/21/2012)
I would guess that you can't start a job remotely via a linked server. You could try using Powershell or SSIS instead. Or you could set up some triggers on the remote server so that they fire jobs when you update a table.

John
Incorrect. You can very easily start a job remotely on a linked server. I would however, recommend you that you use the job uniqueidentifier rather than passing in the job name itself (although it should work either way) - what options did you configure for your linked server?
Example:

DECLARE @job_id uniqueidentifier
SET @job_id = '80209C18-A8AD-48BA-8D6B-10D785FE4383'
EXECUTE [LinkedServer].msdb.dbo.sp_start_job @job_id



I am not sure how that would work well in a multi-DBA environment. If someone drops and recreates the job it should not break other processes, provided the same name is retained. How would you always know the unique id?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4905 Visits: 7365
Yes, that would be a concern, however, I alleviate this issue by using a scalar function to look it up ;-)
CREATE FUNCTION [dbo].[fx_FetchSQLAgentJobID] (
@JobName char(250)
)
RETURNS uniqueidentifier AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose : Returns the job_id for the @job_id parameter of the msdb.dbo.sp_start_job procedure
Department : DBA
Created For :
----------------------------------------------------------------------------------------------------------------
NOTES : Pass in 'DAILY - CA OpisPriceTable Job', Returns: 50E2E4DD-711C-49B9-B29F-D1977FB5C8E6
----------------------------------------------------------------------------------------------------------------
Created On : 05/13/2011
Created By : MyDoggieJessie
----------------------------------------------------------------------------------------------------------------
select dbo.fx_FetchSQLAgentJobID('DAILY - CA OpisPriceTable Job)
*/
BEGIN

RETURN (
SELECT job_id from msdb.dbo.sysjobs WHERE name = @JobName
)
END



______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
That still does not resolve the issue of someone having a job_id parked in some code somewhere and that job_id ceasing to exist when a job is dropped and recreated. Maybe I am confused...why would you ever be working directly with a job_id to call a job remotely? I would not bother with an extra function or round trip to resolve an ID. This should suffice and survive the scenario where someone drops and recreates a job:

DECLARE @job_name SYSNAME;
SET @job_name = N'My Job Name';
EXECUTE [LinkedServer].msdb.dbo.sp_start_job @job_name = @job_name;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4905 Visits: 7365
That still does not resolve the issue of someone having a job_id parked in some code somewhere and that job_id ceasing to exist when a job is dropped and recreated
Very true, but that's up to the DBA-team to enforce this type of "coding practice", ie (hard-coding anything like that in code)

Maybe I am confused...why would you ever be working directly with a job_id to call a job remotely? I would not bother with an extra function or round trip to resolve an ID. This should suffice and survive the scenario where someone drops and recreates a job:
I agree with you 100% here...and actually prefer calling it via the job name (in case the UID changes, you're still covered) however, to assist with the OP not getting it to work, I've also had similar situations where the job would not start using the job name...so thought I'd offer one alternative

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
Maybe I am confused...why would you ever be working directly with a job_id to call a job remotely? I would not bother with an extra function or round trip to resolve an ID. This should suffice and survive the scenario where someone drops and recreates a job:
I agree with you 100% here...and actually prefer calling it via the job name (in case the UID changes, you're still covered) however, to assist with the OP not getting it to work, I've also had similar situations where the job would not start using the job name...so thought I'd offer one alternative
I once was asked to troubleshoot a case where the job name was not working and it turned out to be a case where the DBA that created the job copied the name from a Word document and the name included an em dash (or maybe was it an en dash). The caller was supplying a hyphen (i.e. minus sign on keyboard) which is only natural. We ended up renaming the job.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Mac1986
Mac1986
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 787
I Have tried the below this but have no luck. I feel I'm missing something really silly.
1)
EXEC ('msdb.dbo.sp_start_job ''stakeholderTEST''') AT [TK5BITOOLSVM01];


2) Dropped and recreated the job.

The job that is been called in the target server,is SSRS Report subscription job. But either ways non of the jobs on the Target SQL Instance are been able to be recognized by server where I'm firing the query.

What can be done in these cases..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search