Rewriting Code to call a remote SP

Question

All,

We are planning to migrate our On-Prem SQLServer databases to Azure SQL PaaS DBs with the databases being placed an in Azure Elastic Pool.

In the On-Prem database code, in several Stored Procedures, we call a SP in other databases , ie, Exec LogDB.dbo.usp_log_info and this needs to be rewritten.

Can sp_execute_remote be used to rewrite such code? We will be using External Data Sources and External Tables to handle cross-database queries.

Thanks,
grajee

Answer 1

Hi @grajee , welcome to Microsoft Q&A forum.

From the details provided, it seems you want to execute stored procedure 'LogDB.dbo.usp_log_info' located in one databases through stored procedure presents in other database by using 'sp_execute_remote'. Please refer to below sample code snippets to achieve the same where we are using elastic queries. You can change the logic as per your requirements:

Database 1:

create table test (id int);  

create procedure usp_log  
as  
begin  
insert into test values (1)  
end  

Database 2:

CREATE MASTER KEY  ENCRYPTION BY PASSWORD ='yourpassword'   
--Use the username and password of database you want to connect to  
  CREATE DATABASE SCOPED CREDENTIAL credDatabase  
    WITH IDENTITY = 'yourDatabaseAdminUsername',  
    SECRET = 'YourDatabaseAdminPassword';     

CREATE EXTERNAL DATA SOURCE dbServer WITH  
    (TYPE = RDBMS,  
    LOCATION = 'servername',  
    DATABASE_NAME = 'databasename',  
    CREDENTIAL = credDatabase,  
    ) ;  


EXEC sp_execute_remote  
    N'dbServer',    
    N'usp_log'   

This would execute the stored procedure we created in database 1.

Please let me know if this helps or else we can discuss further on the same.

----------

If answer helps, please select 'Accept answer' as this could help other community members having similar issues.