CODEWITHSUNDEEP
sqlstored-proceduresazure-sql-database
OrdinaryOrange
OrdinaryOrange

Reputation: 2722

Call stored procedure from Elastic Database in Azure

Here is the scenario:

As far as I can tell the title can't be done. Only tables or views seem to work from my testing.

So the next though was to yield the stored procedure as a view in database B then call the view from DB A.

But views cant call stored procedures, even tried looking into table valued functions between the view and the stored procedure, but that's not permitted either.

How can I get the result set from a stored procedure in DB B into DB A?

Upvotes: 1

Views: 1805

Answers (1)

Stuart Ozer
Stuart Ozer

Reputation: 1384

Currently the only way to execute remote Stored Procedure calls using Elastic Query is when your External Data Source is defined using a "sharded" setup. E.g. you have defined an external source with

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
  (TYPE = SHARD_MAP_MANAGER, ...  )

In that case, you have access to a utility procedure called SP_Execute_Fanout which can be used to invoke a stored procedure (or perform ANY SQL operation) on each shard, and return a UNION ALL result set. This utility proc is detailed here

This capability is not yet available with Elastic Query's "Vertically Partitioned" scenario (e.g. TYPE = RDBMS), however we will be adding this type of functionality as the preview continues, so that invoking a remote stored proc in a single DB becomes simple.

Upvotes: 1

Related Questions