SELECT * FROM dbo.ExternalTable x INNER JOIN dbo.LocalTable l on x.ID = l.ID;
/*** RUN THIS ON YOUR CENTRAL AZURE SQL DATABASE ***/
-- create a master key, a credential, and an external data source
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strongpassword';
GO
CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred WITH IDENTITY = 'username', SECRET = 'strongpassword';
GO
CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
(TYPE = RDBMS,
LOCATION = 'yourserver.database.windows.net',
DATABASE_NAME = 'YourExternalDatabase',
CREDENTIAL = ElasticDBQueryCred,
) ;
GO
/*** RUN THIS ON YOUR EXTERNAL AZURE SQL DATABASE ***/
-- create a table for the rows to be returned to the central database
CREATE TABLE dbo.AccountDetails (
AccountID INT PRIMARY KEY NOT NULL,
AccountName VARCHAR(50) NULL,
MailAddress VARCHAR(50) NOT NULL,
SubscriptionID UNIQUEIDENTIFIER NOT NULL
);
INSERT INTO dbo.AccountDetails (AccountID, AccountName, MailAddress, SubscriptionID) VALUES (1, 'Wizard 1', 'some@where.com', '11111111-2222-3333-4444-000000000001');
INSERT INTO dbo.AccountDetails (AccountID, AccountName, MailAddress, SubscriptionID) VALUES (2, 'Wizard 2', 'over@rainbow.com', '11111111-2222-3333-4444-000000000002');
GO
-- create the stored procedure that will return the rows to the central database
CREATE PROCEDURE [dbo].[sp_GetAccountDetails10]
(
@SubscriptionID uniqueidentifier = NULL,
@1SubscriptionID uniqueidentifier = NULL,
@2SubscriptionID uniqueidentifier = NULL,
@3SubscriptionID uniqueidentifier = NULL,
@4SubscriptionID uniqueidentifier = NULL,
@5SubscriptionID uniqueidentifier = NULL,
@6SubscriptionID uniqueidentifier = NULL,
@7SubscriptionID uniqueidentifier = NULL,
@8SubscriptionID uniqueidentifier = NULL,
@9SubscriptionID uniqueidentifier = NULL
)
AS
begin
SET NOCOUNT ON
declare @SubIDs table (SubscriptionID uniqueidentifier);
if @SubscriptionID is not null INSERT INTO @SubIDs (SubscriptionID) VALUES (@SubscriptionID);
if @1SubscriptionID is not null INSERT INTO @SubIDs (SubscriptionID) VALUES (@1SubscriptionID);
if @2SubscriptionID is not null INSERT INTO @SubIDs (SubscriptionID) VALUES (@2SubscriptionID);
if @3SubscriptionID is not null INSERT INTO @SubIDs (SubscriptionID) VALUES (@3SubscriptionID);
if @4SubscriptionID is not null INSERT INTO @SubIDs (SubscriptionID) VALUES (@4SubscriptionID);
if @5SubscriptionID is not null INSERT INTO @SubIDs (SubscriptionID) VALUES (@5SubscriptionID);
if @6SubscriptionID is not null INSERT INTO @SubIDs (SubscriptionID) VALUES (@6SubscriptionID);
if @7SubscriptionID is not null INSERT INTO @SubIDs (SubscriptionID) VALUES (@7SubscriptionID);
if @8SubscriptionID is not null INSERT INTO @SubIDs (SubscriptionID) VALUES (@8SubscriptionID);
if @9SubscriptionID is not null INSERT INTO @SubIDs (SubscriptionID) VALUES (@9SubscriptionID);
select AccountID, AccountName, MailAddress, SubscriptionID from dbo.AccountDetails where SubscriptionID in (select SubscriptionID from @SubIDs);
end;
GO
-- Test:
-- exec [sp_GetAccountDetails10] '11111111-2222-3333-4444-000000000001', '11111111-2222-3333-4444-000000000002', '4B1DE4FD-9051-4839-86BA-0000A5CCF12A'
/*** RUN THIS ON THE CENTRAL DATABASE: ***/
-- create a temporary table that holds the subscription IDs of interest
-- these values would be joined to the external table
declare @Subscriptions TABLE (SubscriptionID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL, SubscriptionName NVARCHAR(256) NOT NULL);
insert into @Subscriptions (SubscriptionID, SubscriptionName) values ('11111111-2222-3333-4444-000000000001', 'Subscription1');
insert into @Subscriptions (SubscriptionID, SubscriptionName) values ('11111111-2222-3333-4444-000000000002', 'Subscription2');
insert into @Subscriptions (SubscriptionID, SubscriptionName) values ('2150482E-D354-4E61-AF4D-38705F095C2C', 'Subscription3');
-- prepare the parameter string
declare @subid_list nvarchar(500) = NULL, @cmd nvarchar(3000) = ''
select top 10 @subid_list =
case when @subid_list is null then '''' + cast(SubscriptionID as nvarchar(36)) + ''''
else @subid_list + ', ''' + cast(SubscriptionID as nvarchar(36)) + ''''
end
from @Subscriptions
-- prepare the command string
select @cmd = N'sp_GetAccountDetails10 ' + @subid_list
-- select @cmd
-- returns: sp_GetAccountDetails10 '11111111-2222-3333-4444-000000000001', '11111111-2222-3333-4444-000000000002', '2150482E-D354-4E61-AF4D-38705F095C2C'
-- call the remote stored procedure
EXEC sp_execute_remote @data_source_name = N'MyElasticDBQueryDataSrc', @stmt = @cmd;
-- alternate solution: run the select statement directly at the external database
select @cmd = N'select AccountID, AccountName, MailAddress, SubscriptionID from dbo.AccountDetails where SubscriptionID in (' + @subid_list + ')'
-- select @cmd
-- returns: select AccountID, AccountName, MailAddress, SubscriptionID from dbo.AccountDetails where SubscriptionID in ('11111111-2222-3333-4444-000000000001', '11111111-2222-3333-4444-000000000002', '2150482E-D354-4E61-AF4D-38705F095C2C')
EXEC sp_execute_remote @data_source_name = N'MyElasticDBQueryDataSrc', @stmt = @cmd;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.