Execute stored procedure remotely using linked server

Question

We have 3 SQL Server 2012 instances, 2 of them we're not 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.

Answer 1

Assuming the procedure lives on the remote server: have you tried using the EXECUTE AT command?

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.

Answer 2

Sounds like a job for SSIS. Set up an SSIS package, and use the Execute SQL Task to execute your stored procedures. You can then execute the SSIS packages through Agent jobs.

Answer 3

I found this thread when search for "execute procedure on linked server". but my requirement is to run same procedure that is available in multiple servers, and I came-up with following which may help you or other users:

To answer your question more simpler way: to use 4 part name to execute remote query: Exec ('linkedservername.DBName.dbo.ProcedureName') ;

Following query can be used to execute procedure at linked server (not limited to one) targeting multiple linked servers that are available in the central server (where this script runs from):

 Declare @TargetServers table (LinkedServer nvarchar(50));
 Declare @LinkedServer  varchar(50);

 Insert into @TargetServers 
 select [data_source] from sys.servers where is_linked = 1 and [Provider] = 'SQLNCLI';

 While exists (select * from @TargetServers) 
     begin
         set @LinkedServer = (select top 1 name from sys.servers where is_linked = 1);

         Exec ('['+@LinkedServer + '].DBAClient.dbo.ProcessUnusedIndexInfo') ;
         if @@ERROR = 0
         delete from @TargetServers where LinkedServer = @LinkedServer;
     end