CODEWITHSUNDEEP
azurestored-proceduresazure-sql-database
Silpa
Silpa

Reputation: 133

call stored procedure from another database in sql 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. I need to call stored_p1 from stored_p2. For calling table from another database i have used external tables. for storedprocedure, do i need to use anything like external table

Upvotes: 1

Views: 5456

Answers (3)

GreenRock
GreenRock

Reputation: 121

Old question but it was still relevant to me. Here is my solution (in my case I am connecting between two different database servers):

  1. In DB2, create the credential you will use to connect to DB1:
    CREATE DATABASE SCOPED CREDENTIAL DB1Credential
    WITH IDENTITY = 'DB1_User',
    SECRET = 'DB1_Password';
  1. In DB2, create the external datasource using your new credential:
    CREATE EXTERNAL DATA SOURCE DB1Server WITH
    (TYPE = RDBMS,
    LOCATION = 'your-db1-server-reference',
    DATABASE_NAME = 'db1',
    CREDENTIAL = DB1Credential,
    ) ;
  1. Execute your remote procedure by naming your remote data source:
    EXEC sp_execute_remote
    N'DB1Server',  
    N'stored_p1'

Parameters can also be included as defined here: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-execute-remote-azure-sql-database?view=azuresqldb-current

Upvotes: 2

Torsten Grabs
Torsten Grabs

Reputation: 481

This requires elastic database queries. Consider using the cross-database functionality documented here: https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-vertical-partitioning/.

It also provides for a function called sp_execute_remote which takes a shardmap name and a T-SQL query as its input.

Upvotes: 1

Patrick
Patrick

Reputation: 5876

This seems to require elastic database queries, potentially with the SP_ EXECUTE_FANOUT command.

Remote stored procedure calls or remote functions invocations that use sp_execute_fanout can now use parameters similar to sp_executesql.

The documntation is here

This may also help

Options without sharding are still apparently "in the works" last I heard.

Upvotes: 2

Related Questions