June 23, 2008 at 3:22 pm
Okey. here is what i want to make happen 🙂
We have an SQL 2005 server that contains about 45 databases.
Then we have an application that connects to that server and is able to use about 5 of those databases.
Now on our login box we want to have the option to select the databases to use. Easy enough right,
But what if we want to filter the databases that the users sees down to only valid application databases.
What we thought about doing was checking if a table that we know all databases have (all the databases are about 97% same tables). Here comes the tricky part. We actually found no sensible way of doing this with a single SQL query or stored procedure. Without using cursors and executing dynamic SQL statements build to a string.
So if anyone has a brilliant solution for this pleas replay 🙂
kgunnarsson
Mcitp Database Developer.
June 23, 2008 at 3:42 pm
I used SMO objects in similar scenario. I connected to server,enumerated databases and for each checked if given object exists in it. If it did, database was added to the dropdown items.
But you can use SQL to get same result, separate query to each database.
Piotr
...and your only reply is slàinte mhath
June 23, 2008 at 4:02 pm
Have you thought about a view?
Within a view you can have derived tables, unions, cte's etc...
In one of my apps I have a view that gives me default and custom workshop assignment. Using the custom workshop if one is defined.
ALTER VIEW [dbo].[vxappxJobToWorkshopToSource]
AS
WITH cteShop AS
(
SELECT DISTINCT 0 dftCstm , mlt.xappxJobID, mlt.job, swrk.id xappxWorkshopID, swrk.workshop, swrk.defaultxappxSourceID xappxSourceID
FROM vxappxMultiSourceJobs mlt
CROSS JOIN xappxWorkshop swrk
WHERE ISNULL(swrk.defaultxappxSOurceID, 0) > 0
UNION ALL
SELECT DISTINCT 1 , mlt.xappxJobID, mlt.job, cstm.xappxWorkshopID, swrk.workshop, cstm.xappxSourceID
FROM vxappxMultiSourceJobs mlt
INNER JOIN xappxWorkshopSubToxappxJob cstm ON mlt.xappxJobID = cstm.xappxJobID
INNER JOIN xappxWorkshop swrk ON cstm.xappxWorkshopID = swrk.id
)
SELECT cteShop.dftCstm isCustomAssign,
cteShop.xappxJobID,
cteShop.job,
cteShop.xappxWorkshopID,
cteShop.Workshop,
cteShop.xappxSourceID
FROM cteShop
INNER JOIN
(SELECT xappxJobID, xappxWorkshopID, MAX(dftCstm) dftCstm
FROM cteShop
GROUP BY xappxJobID, xappxWorkshopID
) maxShop ON cteShop.xappxJobID = maxShop.xappxJobID
AND cteShop.xappxWorkshopID = maxShop.xappxWorkshopID
AND cteSHop.dftCstm = maxShop.dftCstm
good luck
Daryl
June 23, 2008 at 4:10 pm
intresting...... these are some good ideas i'll try to check this out.
kgunnarsson
Mcitp Database Developer.
June 23, 2008 at 4:43 pm
Completely off the wall and not a T-SQL solution, but an idea that may spark something additional.
1. Create a 46th db.
2. Have the application open that db when opening the first form.
3. Create a single table in this 46th db.
4. Table should consist of 4 columns, although more could be added if more than 1 application opens one/some/all of the db in this table.
5 Create Table "Path" with
First column as an identity, 2nd column to hold the db name or acronym for the db. 3rd column the connection string to the db. The 4th column contains the application name. (app.exe name)
6. Application logs into the 46th db, opens the table and reads the contents of the 2nd column into your login forms list or combo box.
7. Application closes the 46th db.
8. Application proceeds with selected db(s).
If more than one application must login in to different dbs then use 2 tables in 46th db with appropriate foreign keys.
Admittedly this is NOT a T-SQL, but rather a manual solution to your problem hopefully it will give you a new path to think about, and maybe adapt to your particular situation. The only rule it follows is "KISS" (Keep It Simple St----)
June 24, 2008 at 3:58 am
Actually yes this i quit an good solution. tho i would rather put this in a table in the master database 🙂
thanx for the ideas
kgunnarsson
Mcitp Database Developer.
June 24, 2008 at 5:32 am
Try this:
--======your variables: fill these in!
declare @MySchema SYSNAME, @MYTable SYSNAME
Select @MySchema = 'dbo', @MyTable = 'TestTable'
declare @sql nvarchar(max)
Set @sql = 'SELECT * FROM ('
--====== build command string with all of the databases
Select @sql = @sql + '
SELECT * From ['+[name]+'].INFORMATION_SCHEMA.TABLES UNION ALL'
From sys.Databases
--====== remove the extra UNION ALL
Select @sql = Left(@sql, len(@sql)-10)
--====== Add the Filter
Select @sql = @sql + ') T
WHERE TABLE_SCHEMA='''+@MySchema+'''
AND TABLE_NAME='''+@MyTable+'''
'
EXEC (@sql)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply