SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Database Selection


Dynamic Database Selection

Author
Message
mmorales 85175
mmorales 85175
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 12
So i'm a bit stuck here.


I have a pair of database projects that are being deployed that I'm trying to keep clean and only have view/table creation done via data tier app upgrades.

the dev team has multiple instances of these databases deployed to one server and I need to share a bit of data between the databases. (the devs tooling will not allow for cross database queries)


I'm trying to do something like this :


CREATE FUNCTION [dbo].[GetAuthUsers]()
RETURNS @returntable TABLE
(
UserId uniqueidentifier,
DisplayName NVarChar(100),
UserName NVarChar(100),
Sid NVarChar(100),
DomainName NVarChar(255)
)
AS
BEGIN
DECLARE @DatabaseName NVARCHAR(MAX);
Declare @DomainSlug NVARCHAR(255);
select @DatabaseName = value from Configurations where slug = 'AuthDatabaseName';
select @DomainSlug = value from Configurations where slug = 'DomainSlug';

EXECUTE('USE ' + @DatabaseName);
INSERT @returntable
select
users.[Id] as UserId,
users.[DisplayName],
Accounts.Username,
Accounts.Sid,
Domains.name as DomainName
from users
INNER JOIN Accounts
ON Accounts.UserId = Users.Id
INNER JOIN Domains
ON Domains.Id = Accounts.DomainId
WHERE Domains.Domain = @DomainSlug;
RETURN
END



a function that will read some configuration variables (one of which being the data base name) and run a query against the appropriate database.

Now I know I can't run the EXEC function in a function, but I need the database entity to be usable as a table.

I've looked at synonyms (or a view solution would be very simular) doing something like

DECLARE @DatabaseName NVARCHAR(MAX);
select @DatabaseName = value from Configurations where slug = 'AuthDatabaseName';
EXECUTE('CREATE SYNONYM dbo.Users FOR '+@DatabaseName+'.dbo.Users');
EXECUTE('CREATE SYNONYM dbo.Accounts FOR '+@DatabaseName+'.dbo.Accounts');
EXECUTE('CREATE SYNONYM dbo.Domains FOR '+@DatabaseName+'.dbo.Domains');

as a post deployment script, but I would either
1) have to have a deployment script per user to set up their configuration file or
2) they would have to update their config files drop the synonyms and recreate them(violating the data tier app integrity(

Another option I've seen is to write this as a stored procedure, then create linked server back to my localhost and use OPENQUERY to pull the data against the linked server... but that sounds like madness.

I'm basically at the end of my known paths and am looking for help/ideas/brainstorming/solutions...really anything.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206874 Visits: 24169
Can you use dynamic SQL to 'fool' the 'tooling'? (With appropriate injection protection, of course.)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
mmorales 85175
mmorales 85175
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 12
I don't think. It has to be in table format at the end of the day, so a table or a function need to be my resultant.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)

Group: General Forum Members
Points: 379022 Visits: 42177
You should look at using a stored procedure instead of a function as you are going to need to use dynamic SQL to accomplish this task. You can't use dynamic SQL in a function.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
mmorales 85175
mmorales 85175
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 12
Lynn Pettis - Thursday, February 8, 2018 4:51 PM
You should look at using a stored procedure instead of a function as you are going to need to use dynamic SQL to accomplish this task. You can't use dynamic SQL in a function.



If I am not mistaken this won’t allow me to get a result in a table styled object like a function will it?

I think I need a function/view so it can be joined against/selected from. Without it being in that format the developers tooling will not accept the data
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search