February 8, 2018 at 2:26 pm
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.
February 8, 2018 at 2:34 pm
Can you use dynamic SQL to 'fool' the 'tooling'? (With appropriate injection protection, of course.)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 8, 2018 at 2:37 pm
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.
February 8, 2018 at 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.
February 8, 2018 at 4:58 pm
Lynn Pettis - Thursday, February 8, 2018 4:51 PMYou 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply