5

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.

11

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 '18 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. – MguerraTorres Nov 27 '18 at 22:17
1

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.

  • Yes, I'd like to not use SSIS. Use simple SQL only. – Hikari Jul 22 '16 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 '16 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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