1

Here is the scenario:

  • Elastic Database A connected to database B
  • Database B contains stored procedure that returns result set needed in database A

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
OrdinaryOrange
  • 2,420
  • 1
  • 16
  • 25

1 Answers1

1

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.

Stuart Ozer
  • 1,354
  • 7
  • 7
  • I've configured the database as Vertically Partitioned. Putting calling the SP directly to the side, is there any workarounds ? eg creating a table valued function in a view which contains all the code of the sp ? – OrdinaryOrange Dec 08 '15 at 01:01
  • A table valued function has limitations as compared to a stored procedure. For instance, you cannot perform any database changes as part of a TVF. As long as you are not hitting any these limitations, this is a valid workaround, though. – Torsten Grabs Dec 09 '15 at 02:41