October 2, 2015 at 10:28 am
How to call database from another server while trying to set up a job on the reports server.
I currently have a job set up on on Server S now I exactly want the same job set up on the reports server to get some consistency.
Is there a way I can call database from another server while creating a job on reports server?
October 2, 2015 at 10:57 am
The following is the step 1 on the job on Server S
SELECT LoanID
FROM ACHWebPayments Where (DraftDate > CONVERT (date, GETDATE()) and exported is null) and (DateRequested > CONVERT (date, GETDATE()) and exported is null)
GROUP BY LoanID HAVING ( COUNT(*) > 1 )
if @@ROWCOUNT > 0
begin
USE msdb
EXEC sp_start_job @job_name = 'D26B10DC-96B4-XXXX-XXXX-99CB2085CA01'
end
job: 'D26B10DC-96B4-XXXX-XXXX-99CB2085CA01' also happens to be on the server S
October 6, 2015 at 8:51 am
Does anyone have any ideas or suggestions?
October 6, 2015 at 4:54 pm
I'm not sure I follow 100%, but at the very least, you'll need a linked server and to fully qualify your table names in your sql statements. Even the EXEC statement on msdb will need fully qualified with the linked server name. I'm not sure this will work, but it's worth a try on your end.
October 7, 2015 at 8:29 am
John,
I have added linked Server S with report server. Can you please give an example of what would be considered as a fully qualified code.
Also when I write the code in Step 1, what database should I select? (model, master, msdb, etc)
October 7, 2015 at 9:09 am
SQLPain (10/7/2015)
John,I have added linked Server S with report server. Can you please give an example of what would be considered as a fully qualified code.
Also when I write the code in Step 1, what database should I select? (model, master, msdb, etc)
Fully qualified query:
SELECT jb.*
FROM [MyLinkedServer].msdb.dbo.sysjobs jb
where jb.job_name = 'D26B10DC-96B4-XXXX-XXXX-99CB2085CA01'
--openquery version:
SELECT * FROM OPENQUERY([MyLinkedServer],'SELECT * FROM msdb.dbo.sysjobs where job_name = ''D26B10DC-96B4-XXXX-XXXX-99CB2085CA01'' ;')
--run a remote command
EXEC ('EXEC msdb.dbo.sp_start_job @job_name = ''D26B10DC-96B4-XXXX-XXXX-99CB2085CA01'' ;') AT [MyLinkedServer]
Lowell
October 7, 2015 at 12:52 pm
Once you have your link server created you can just select catalogs for the linked server you created and drag and drop to the window. It must be in the order shown in the previous comment.
October 7, 2015 at 1:55 pm
Thank you guys, where do I drag and drop the Catalog folder?
October 7, 2015 at 2:14 pm
He just means that you can use the object browser to find your table under the linked server. When you drag an item from the object browser into the query window, it gives you a fully qualified object name.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply