July 2, 2019 at 2:30 pm
I need to be able to return a recordset of server name and database name for all databases that have the 'humres' table which contains a record in the table with a column 'usr_id' with value of a passed in username param.
i.e.
create proc getDBs(username varchar(8))
AS
(
.....
)
July 2, 2019 at 2:46 pm
Is this a one time thing or a regular query? Because searching across all databases and checking if a table exists is going to be bad for performance and not quick. You'd be better to make a procedure that knows where this table exists.
If you are adding and removing databases, then this is a mess, but it's helpful to know more about the problem and situation.
July 2, 2019 at 3:24 pm
We use an app that creates a new db for a every new company, so I don't know how may databases exist. Also, there are databases on the same server that don't relate to the app, so I exclude them by only including databases in the result that have the 'humres' table.
The reason i also need to pass in the username is b/c if the user doesn't exist in the 'humres' table, then I know they don't have access to that company.
Basically, what this procedure is returning is the list of companies(or databases) the user has access to.
July 2, 2019 at 4:07 pm
OK, sharded/federated databases. Is there a reason you search all dbs rather than asking if this user as access in this db/company? I assume that the humres table exists everywhere?
Not dodging your question, but just thinking this through. A cursor can do this, but not sure its' the best solution, and does it really provide you with the data as you will get multiple result sets. A temp table can help here, but before we do that code, let's discuss lightly your issue.
If you're trying to determine everywhere a user has access, is this a lot of data? Will you notice something missing? Or do you need a left join of all companies with places where the user has access or blanks? Or just verifying an issue for a company? There might be better approaches if we game this out a bit.
July 2, 2019 at 5:03 pm
The result set will be the Company name and Company database. I have a vb form that the users run crystal reports from. It has a company dropdown and a report dropdown. So I need to be able to populate the company dropdown with a list of company names and the database name as the value. I want to call a stored procedure to provide that. The database name will get passed into the crystal report they choose. The result set will probably only return up to 10 to 15 companies at most. I've come up with this so far, but I've heard using temp tables is bad. I'm not sure if it's possible to put this into a procedure either.
SET NOCOUNT ON
DECLARE @alldbs table (comp_desc nvarchar(50), db_name nvarchar(15), bedrnr char(3))
INSERT INTO @alldbs (comp_desc, db_name, bedrnr )
EXEC sp_msforeachdb '
if object_id(N''?.dbo.humres'',''U'') IS NOT NULL
AND object_id(N''?.dbo.compfile_sql'',''U'') IS NOT NULL
AND object_id(N''?.dbo.bedryf'',''U'') IS NOT NULL
select ''? - '' + cd.bedrnm AS comp_desc
, ''?'' AS db_name
, cd.bedrnr
from [?].dbo.humres h
cross join [?].dbo.compfile_sql cf
inner join [?].dbo.bedryf cd ON cf.id = cd.id
where h.usr_id = ''@userid''
'
SET NOCOUNT OFF
SELECT comp_desc, db_name FROM @alldbs ORDER BY bedrnr
July 2, 2019 at 5:28 pm
If you're populating the report with the company name, then this is only querying one table, correct?
I assume your context isn't the right database, so you really need a query like this, for company abc
select somecol
from abc.dbo.humres
where this=that
Looking at what you have, I think you don't need to query all databases, but just the one. If I'm wrong, then explain, but really I suspecy you are building a simple report. Now, if users can select "all companies", then that's a difference story. In that case, I'd still build 3 procedures to do this, with 1 switching to the other two.
Your approach seems fine for all databases, though I'm not sure your query makes sense here. Why the cross join?
July 2, 2019 at 5:42 pm
As you can see, there's 3 different tables i have to query to get the company name, db name, and user name. The query I've written so far uses sp_msforeach db to cycle through the databases, check for the existence of those tables ( some dbs don't have them b/c they aren't dbs for the company app ), then get the result set info. There's no way for me to specify the dbs myself b/c there could be new ones created if the owner of the company decides to create a new company or archive one. As for the cross join, I wasn't sure how to do a left join with no condition. There will always only be one company record in the comfile_sql table.
July 2, 2019 at 8:26 pm
You mentioned the user picks the database in the report. Wouldn't that be the value you use to query the db?
If there's one row, then for comfile_sql, use this:
select ''? - '' + cd.bedrnm AS comp_desc
, ''?'' AS db_name
, cd.bedrnr
from [?].dbo.humres h
inner join [?].dbo.compfile_sql cf on 1 = 1
inner join [?].dbo.bedryf cd ON cf.id = cd.id
where h.usr_id = ''@userid''
July 5, 2019 at 3:18 pm
So, the user opens a vb form that has a dropdown that they choose the company from. The vb form passes the company(db) to the report. The stored procedure is used to populate the dropdown for the vb form on the form_load() method. The reason i was working on the stored procedure was to get a list of companies(dbs) the user had access to. I've actually got it working now. I made the change to the compfile_sql that you recommended and it's working fine. Thanks for all the help. Here is the final code:
ALTER PROCEDURE [dbo].[spGetMacolaUserDb] (
@userid char(15)
)
AS
SET NOCOUNT ON
DECLARE @alldbs table (db_desc nvarchar(50), db_name nvarchar(15), bedrnr char(3))
DECLARE @sql nvarchar(464) = '
if db_id(''?'') > 4 --exclude system dbs
BEGIN
if object_id(N''?..humres'',''U'') IS NOT NULL
AND object_id(N''?..compfile_sql'',''U'') IS NOT NULL
AND object_id(N''?..bedryf'',''U'') IS NOT NULL
BEGIN
select ''? - '' + cd.bedrnm AS db_desc
, ''?'' AS db_name
, cd.bedrnr
from [?]..humres h
inner join [?]..compfile_sql ON 1 = 1
inner join [?]..bedryf cd ON cf.id = cd.id
where h.usr_id = ''' + @userid + '''
END
END
'
INSERT INTO @alldbs (db_desc, db_name, bedrnr )
EXEC sp_msforeachdb @sql
SET NOCOUNT OFF
SELECT db_desc, db_name, bedrnr
FROM @alldbs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply