December 21, 2012 at 1:37 am
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
December 21, 2012 at 2:17 am
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
December 21, 2012 at 5:16 am
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
December 21, 2012 at 8:20 am
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; Theyll drag you down to their level and beat you with experience
December 21, 2012 at 9:10 am
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
December 21, 2012 at 9:19 am
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; Theyll drag you down to their level and beat you with experience
December 21, 2012 at 9:24 am
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
December 21, 2012 at 9:34 am
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; Theyll drag you down to their level and beat you with experience
December 21, 2012 at 9:41 am
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
December 21, 2012 at 4:11 pm
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..
December 21, 2012 at 5:19 pm
I need the OP and those who have successfully executed a job over a linked server to get your linked server configurations and post them here. Maybe something is different between the one(s) that work and the one that doesn't.
I know I did it at a previous employer, I had triggers setup on an FTP log table to fire off import jobs upon successful transfer of files. Worked like a charm.
December 21, 2012 at 10:44 pm
I am pretty certain this will work, specify the @job_name parameter...
EXEC ('msdb.dbo.sp_start_job @job_name=N''stakeholderTEST''') AT [TK5BITOOLSVM01];
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 22, 2012 at 2:17 am
HI Jessie,
Sorry but it did not work. Is still get an error of of Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name ('8A427655-9A6E-43F0-9A28-95C7A8F781B1') does not exist.
Below id the Linked server config details
EXEC master.dbo.sp_addlinkedserver @server = N'TK5BITOOLSVM01', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @provstr=N'PROVIDER=SQLOLEDB;SERVER=TK5BICBIUSQL01.partners.extranet.microsoft.com\BICBIUATSQL01,59480'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TK5BITOOLSVM01',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'dist', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'pub', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'sub', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TK5BITOOLSVM01', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
Its getting heated up not really able to figure out why am I not able to kick off a SSRS Subscription job through linked server
December 22, 2012 at 7:38 am
Wow, I must have missed that you were trying to start a reporting service subscription :w00t: The error you posted has nothing to do with the fact you can't kick it off, it simply cannot find it. Subscriptions use the the subscription GUID as the job name. It looks like you were attempting to pass in the report name 'stakeholderTEST' as the job name and this is not the case.
Try this (or simply go to this article and read about how they do it):
EXEC msdb..sp_start_job @job_name = 'YOUR GUID FROM THE REPORTSERVER'
I tested this on my own server so I know it works...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 22, 2012 at 10:11 pm
apparently thats not true at all times. If you see the error message I pasted to you in my earlier reply, I used the same way to kickoff the subscription and it did not work.
I used to rename the subscription job and was able to kick it off using linked server when I was using a different reporting server. On this server, I installed SSRS recently and configured SSRS Recently.
Can some please help me kicking off the job using linked server.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply