Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

JobName Not found error Expand / Collapse
Author
Message
Posted Friday, December 21, 2012 1:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 01, 2014 5:00 PM
Points: 193, Visits: 621
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
Post #1399267
Posted Friday, December 21, 2012 2:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 5,074, Visits: 8,910
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
Post #1399276
Posted Friday, December 21, 2012 5:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1399348
Posted Friday, December 21, 2012 8:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:22 PM
Points: 3,729, Visits: 7,067
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"
Post #1399442
Posted Friday, December 21, 2012 9:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1399474
Posted Friday, December 21, 2012 9:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:22 PM
Points: 3,729, Visits: 7,067
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"
Post #1399483
Posted Friday, December 21, 2012 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1399488
Posted Friday, December 21, 2012 9:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:22 PM
Points: 3,729, Visits: 7,067
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"
Post #1399495
Posted Friday, December 21, 2012 9:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1399498
Posted Friday, December 21, 2012 4:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 01, 2014 5:00 PM
Points: 193, Visits: 621
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..

Post #1399625
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse