CODEWITHSUNDEEP
azure
skp
skp

Reputation: 322

Call stores procedure from another database and insert the data in the remote database in Azure

I have 2 databases db1 and db2 in azure. And db1 is having stored procedure stored_p1, db2 is having stored procedure stored_p2. stored_p1 is called from stored_p2. For calling tables from another database used external source and external tables. External tables are only for DDL. but when calling stored_p1 in stored_p2 need to insert the data into the table in the db1. The table on the remote database(db1) had identity column.

As external tables are DDL is there any way to insert the data into the actual table not the external table.

Upvotes: 0

Views: 1752

Answers (1)

Ian
Ian

Reputation: 249

If I understand your question correctly you could use the sp_execute_remote function that was introduced as part of Elastic Databases.

The documentation is here: https://msdn.microsoft.com/en-us/library/mt703714.aspx

An example of how sp_execute_remote can be used in what I understand as your set up is set out below:

Subscription 1, DB1 has this Table & stored procedure:

CREATE TABLE [dbo].[tblNames](
    [id] [smallint] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [Surname] [varchar](255) NULL,
 CONSTRAINT [PK_tblNames] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

CREATE PROCEDURE [dbo].[sp_Insert_New_Name] @FirstName varchar(50), @Surname varchar(255)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO tblNames VALUES (@FirstName, @Surname)

END

GO

Subscription 2, has DB2 this Stored Procedure:

CREATE PROCEDURE sp_Insert_Name_via_Remote_SP
    @FN varchar(50), @SN varchar(255)
AS
BEGIN

    SET NOCOUNT ON;
EXEC sp_execute_remote @data_source_name  = N'dsRemoteDbinRemoteSub', 
@stmt = N'sp_Insert_New_Name @FirstName, @Surname', 
@params = N'@FirstName varchar(50), @Surname varchar(255)',
@FirstName = @FN, @Surname = @SN

END
GO

In Subscription 2, DB2 I run this command:

exec sp_insert_name_via_remote_sp 'FN5', 'SN5'

This successfully inserts into the table in Subscription 1 and DB1.

N.B. My first efforts here made use of output variables so you could return the inserted identity but output variables do not appear to be supported for sp_execute_remote.

For completeness the code below was used in the second DB to configure the remote data source:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Password>';

 CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
 WITH IDENTITY = '<DB username>',
 SECRET = '<DB password>';


 CREATE EXTERNAL DATA SOURCE dsRemoteDbinRemoteSub
    WITH (   
        TYPE = RDBMS,  
        LOCATION = '<DB Server Name>.database.windows.net',  
        DATABASE_NAME = '<DB Name>',  
        CREDENTIAL = ElasticDBQueryCred 
    )

Hope this helps

Upvotes: 0

Related Questions