patternsqlModerate
Execute stored procedure remotely using linked server
Viewed 0 times
storedprocedureusingserverexecutelinkedremotely
Problem
We have 3 SQL Server 2012 instances, 2 of them we're not
I'd like to centralize the execution of these (our) stored procedures in our main instance, and from it execute these stored procedures remotely. I tried using SQL Server Agent jobs, but it requires some configuration I'm not comfortable doing, and probably won't have access anyway.
Is there any way to remotely execute a stored procedure, preferably using linked server?
Edit: we can use linked server,
db_owner. Some of our stored procedures use DMV and sys views and stored procedures that only work when executed locally.I'd like to centralize the execution of these (our) stored procedures in our main instance, and from it execute these stored procedures remotely. I tried using SQL Server Agent jobs, but it requires some configuration I'm not comfortable doing, and probably won't have access anyway.
Is there any way to remotely execute a stored procedure, preferably using linked server?
Edit: we can use linked server,
openrowset, anything that can be run inside simple SQL. I'd rather not use SSIS, because doing so would separate part of the process from simple SQL and require multiple platforms to run it.Solution
Assuming the procedure lives on the remote server: have you tried using the EXECUTE AT command?
That's what I have used successfully in the past. I usually surround the declare/exec in a try catch just in case, but it's currently working on my systems every hour.
DECLARE @RunStoredProcSQL VARCHAR(1000);
SET @RunStoredProcSQL = 'EXEC [Database].[dbo].[StoredProcName]';
--SELECT @RunStoredProcSQL --Debug
EXEC (@RunStoredProcSQL) AT [LinkedServerName];
Print 'Procedure Executed';That's what I have used successfully in the past. I usually surround the declare/exec in a try catch just in case, but it's currently working on my systems every hour.
Code Snippets
DECLARE @RunStoredProcSQL VARCHAR(1000);
SET @RunStoredProcSQL = 'EXEC [Database].[dbo].[StoredProcName]';
--SELECT @RunStoredProcSQL --Debug
EXEC (@RunStoredProcSQL) AT [LinkedServerName];
Print 'Procedure Executed';Context
StackExchange Database Administrators Q#144660, answer score: 19
Revisions (0)
No revisions yet.