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.

3 Answers 3


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.

  • Your solution is perfect and the only one that I have found that works. This is, however, extremely messy and Microsoft should be ashamed of themselves for letting this be the correct way to do it.
    – Flat Cat
    Nov 27, 2018 at 19:48
  • 1
    Hahaha thanks @FlatCat ! Honestly I don't disagree with you. The reason I used this method when I first discovered it was because I wanted to delete PII data off of a server (Express) that didn't have SQL Agent. I also didn't want the scheduling server to have any visibility into the data or procedure, so this ended up working out.
    – SQLDevDBA
    Nov 27, 2018 at 22:17

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.

  • 1
    Yes, I'd like to not use SSIS. Use simple SQL only.
    – Hikari
    Jul 22, 2016 at 16:46
  • 1
    You should be able to create a linked server with permissions to execute the stored procedures then. Just make sure that the account that you create it with, has permissions to execute on the destination server.
    – Arthur D
    Jul 22, 2016 at 17:38

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) 
         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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.