May 10, 2012 at 9:56 am
I need to look into only certain db and look into a specific column in a speific table that is the same for all the db.
So I need to retrieve the db names and the name of the client for that db.
I have a db MAIN that contains high level information i.e. table CASE containing CaseNo, CaseName etc (Need CaseNo).
Then another table named CLIENT containing ClientNo, ClientName etc (Need ClientName).
Then there are subsequent db that hold only data speific to that case like table FILE that contains the locations of the physical files.
The naming convention is the server it resides on, the client name and then docs i.e. \\ServerName\ClientName\Docs\FileName.ext
The reason I need this data is that subsequent databases are named after the CaseNo
DB - MAIN
TB - CASE
CL - CaseNo,CaseName,ClientNo
101,Spartan,1
102,Wolverine,2
103,Badger,2
TB - CLIENT
CL - ClientNo,ClientName,Status
1,State,1
2,University,1
This is where I need to use the CaseNo to access the right database and then the ClientName is used in part of the location of the file(s):
DB - CA101
TB - FILE
CL - FileNo,Location
1,\\ServerX\State\Docs\
2,\\ServerX\State\Docs\
DB - CA102
TB - FILE
CL - FileNo,Location
1,\\ServerX\University\Docs\File1.xls
2,\\ServerX\University\Docs\File2.doc
The reason for this is at times the file location has be changed, not complete whatever the issue it can't pull up the file;
sometimes the location can look like \File1.xls so I want to find the excepts amoungst each database.
I heard cursors are not good but I don't know how to step through each rown in MAIN db to find all the db names that need to be checked since it can change on a daily basis.
Thanks!!
May 10, 2012 at 10:08 am
It would help if you provided some DDL for the tables and list the relationships that are defined.
If I read this correctly, you have multiple databases that are named for the case, so a separate database for each case?
Is this a large list of things? I suppose there is a way with dynamic SQL, but if you are just querying for the files, a cursor might work.
I'm guessing that something like this gets you the cases you need:
select m.caseno, m.casename, c.client
from Main m
inner join client c
on m.clientno = c.clientno
Then you need to query the file as:
select file
from [m.caseno].dbo.file
Is that right?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 10, 2012 at 1:09 pm
First, I'd recommend redesigning your data t be more relational. One database per Case is pretty awful and will constantly be pushing into bad decisions like Cursors to compensate for it.
Failing that, I'd recommend taking a look at this script [/url], which uses dynamic SQL to do this kind of thing for you.
[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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply