December 22, 2012 at 11:10 pm
Forgetting the linked server issue, can you run it directly on the reporting server from a job?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 23, 2012 at 12:02 am
Ya Its running fine when I kick it off directly.
December 23, 2012 at 8:33 pm
What is the login account permissions
December 24, 2012 at 1:37 am
Sorry Did not quite get your question.
Were you asking, the Service Account that the SSRS is running in has SysAdmin access on the SQL Instance where the Subscription job is hosted as well. Hope I answered your question correctly. Also the the same service account has sysAdin access on the SQL Instance from where I'm trying to fire the job from.
December 24, 2012 at 9:51 am
I think where jyuan68 is going, and I agree with checking it out, is that if the login used by the Linked Server on the remote server has execute permissions on the proc msdb.dbo.sp_start_job but does not have membership in a SQL Agent Fixed Database Role in msdb (e.g. SQLAgentOperatorRole) or membership in a Fixed Server Role (e.g. sysadmin) that would allow the login to execute the job then you might see the error you're seeing:
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name ('Job Name') does not exist.
You can try to recreate the error this way:
1. Log into the remote server as yourself (a member of sysadmin I presume).
2. Run this SQL code:
EXECUTE AS LOGIN = 'login the Linked Server uses'
GO
SELECT SUSER_SNAME()
GO
EXEC dbo.sp_start_job
@job_name = 'problematic job name';
-- should see error here
GO
REVERT
GO
SELECT SUSER_SNAME()
GO
If you see the error then you can add the login to the SQLAgentOperatorRole Role in msdb and try again.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 24, 2012 at 8:15 pm
I have done a test.
There has two servers 192.168.1.129 and 192.168.1.173
1.Create an account and grant the db_owner permission to it. Then create a job on 192.168.1.173,but its owner is sysadmin.
The command as below.
--192.168.1.173
use [master]
create login __joblogin with password='__joblogin'
use [msdb]
create user __joblogin for login __joblogin
EXEC sp_addrolemember N'db_owner', N'__joblogin'
GO
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'__test',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sqldba', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'job step',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'select getdate()',
@database_name=N'master',
@flags=0
GO
Using sysadmin to connect to 192.168.1.173
exec msdb.dbo.sp_start_job '__test'
go
execute as login='__joblogin'
go
exec msdb.dbo.sp_start_job '__test'
revert
you may get the message:
?? '__test' ??????
?? 14262,?? 16,?? 1,?? sp_verify_job_identifiers,? 52 ?
??? @job_name ('__test')????
Using __joglogin to connect to 192.168.1.173
exec msdb.dbo.sp_start_job '__test'
you will get the message:
?? 14262,?? 16,?? 1,?? sp_verify_job_identifiers,? 52 ?
??? @job_name ('__test')????
2.Create a linkedserver on 192.168.1.129 which is used to connect to the remote server(192.168.1.173).BTW, please use __joblogin to connect to 1.173
--192.168.1.129
EXEC master.dbo.sp_addlinkedserver @server = N'192.168.1.173', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'192.168.1.173', @optname=N'use remote collation', @optvalue=N'true'
Now if you start remote job, you will get error message also.
Exec [192.168.1.173].msdb.dbo.sp_start_job '__test'
or
exec('msdb.dbo.sp_start_job ''__test''' ) at [192.168.1.173]
?? 14262,?? 16,?? 1,?? sp_verify_job_identifiers,? 52 ?
??? @job_name ('__test')????
So I guess that your job owner is different from the linkedserver account.
Please check it, thanks.
December 24, 2012 at 10:22 pm
I ran the below script and got the Network related error respectively. I wonder why it takes 2 mins to runt his small script.
EXECUTE AS LOGIN = 'Redmond\MyServiceAccount'
GO
SELECT SUSER_SNAME()
GO
EXEC [MyServerName].MSDB.dbo.sp_start_job @job_name = '22274680-BFBF-4191-913E-40491393C490';
-- should see error here
GO
REVERT
GO
SELECT SUSER_SNAME()
GO
In messages I got.
(1 row(s) affected)
OLE DB provider "SQLNCLI10" for linked server "MyServerName" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "MyServerName" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.
OLE DB provider "SQLNCLI10" for linked server "MyServerName" returned message "Invalid connection string attribute".
(1 row(s) affected)
In Results I got
REDMOND\MyServiceAccount
REDMOND\MyServiceAccount
December 26, 2012 at 9:22 am
Mac1986 (12/24/2012)
I ran the below script and got the Network related error respectively. I wonder why it takes 2 mins to runt his small script.
EXECUTE AS LOGIN = 'Redmond\MyServiceAccount'GO
SELECT SUSER_SNAME()
GO
EXEC [MyServerName].MSDB.dbo.sp_start_job @job_name = '22274680-BFBF-4191-913E-40491393C490';
-- should see error here
GO
REVERT
GO
SELECT SUSER_SNAME()
GO
In messages I got.
(1 row(s) affected)
OLE DB provider "SQLNCLI10" for linked server "MyServerName" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "MyServerName" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.
OLE DB provider "SQLNCLI10" for linked server "MyServerName" returned message "Invalid connection string attribute".
(1 row(s) affected)
In Results I got
REDMOND\MyServiceAccountREDMOND\MyServiceAccount
No, no, no. Please look at my code sample again. I want you to log into the instance where the job resides and execute it locally, not using a Linked Server. Your code sample uses a Linked Server. Basically what I am trying to simulate is the call that would be made over the Linked Server connection, but when locally logged into the instance where the job resides, i.e. let's take the Linked Server out of the equation in an attempt to recreate the error. Once you can recreate the error locally (which I think is a permissions error) you can look to resolve the error there and then introduce the Linked Server again by trying to call the job from the remote instance.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 27, 2012 at 1:46 am
I just fired your query locally ad removed Linked server from the equation. The script ran in fraction of second and the subscription job got kicked off successfully.
So its not the permission issue. Do you think Server DNS Mapping to the IP Address could be the issue? I think the network is not able to recognize the name of the server. What do you think.
December 27, 2012 at 8:21 am
Progress. From the remote server what do you get when you run these:
EXEC('SELECT ORIGINAL_LOGIN() as logged_in_login;') AT [ServerName];
EXEC('SELECT * FROM msdb.dbo.sysjobs WHERE name = ''8A427655-9A6E-43F0-9A28-95C7A8F781B1'';') AT [ServerName];
Did the first one return what you expected in terms of the login name? Did the second one succeed and find one row?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 27, 2012 at 11:46 am
thanks . Below is the update.
EXEC('SELECT ORIGINAL_LOGIN() as logged_in_login;') AT [LocalServerName];
Result: REDMOND\Mycredential
EXEC('SELECT * FROM msdb.dbo.sysjobs WHERE name = ''22274680-BFBF-4191-913E-40491393C490'';') AT [TK5BITOOLSVM01]:
This query return 0 records. Job is not visible.
But I saw an intresting thing though. May be it can help us.
When I run this locally on the server where the job is hosted, I get the below error.
EXEC('SELECT ORIGINAL_LOGIN() as logged_in_login;') AT [LocalServerName];
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'LocalServerName' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name. .
Also select * from master..sysservers
Shows that IsRemote Column as 1 for that server.
and sp_configure for remote admin connections is set to 0.
Please suggest what can be done next.
December 28, 2012 at 10:22 am
Why locally on the server where the job is hosted? That does not prove anything.
Please run the code I posted on the remote server where [ServerName] is the name of the Linked Server pointing to server where the job resides and post the results.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 28, 2012 at 12:16 pm
thanks . Below is the result when i ran it remotely using linked server.
EXEC('SELECT ORIGINAL_LOGIN() as logged_in_login;') AT [LocalServerName];
Result: REDMOND\Mycredential
EXEC('SELECT * FROM msdb.dbo.sysjobs WHERE name = ''22274680-BFBF-4191-913E-40491393C490'';') AT [TK5BITOOLSVM01]:
This query return 0 records. Job is not visible.
December 28, 2012 at 1:35 pm
Mac1986 (12/28/2012)
thanks . Below is the result when i ran it remotely using linked server.
EXEC('SELECT ORIGINAL_LOGIN() as logged_in_login;') AT [LocalServerName];Result: REDMOND\Mycredential
EXEC('SELECT * FROM msdb.dbo.sysjobs WHERE name = ''22274680-BFBF-4191-913E-40491393C490'';') AT [TK5BITOOLSVM01]:This query return 0 records. Job is not visible.
LocalServerName? Why not TK5BITOOLSVM01 for both?
What you are reporting is not computing. Are you actually receiving this message from SQL Server?
This query return 0 records. Job is not visible.
I setup a Linked Server on SQL 2008 R2 with the exact same options you showed earlier and can execute a job remotely.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 28, 2012 at 3:48 pm
I used TK5BITOOLSVM01 for both the queries.
The first query returned with the account name as expected.
2nd query did not give any results. Looks like I',m not able to access the server remotely properly.
U know what I mean.
When I select the sysjobs table in MSDB locally I get 29 records. But when I run the query remotly using linked server it does not give records nor any errors. Strange. Its got to do something with my server where the job is hosted.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply