March 25, 2016 at 12:09 pm
Hello --
Thank-you for your posting, I ran the script you posted, substituting the actual patient ID in the appropriate slot. However, the query produced the following error for
each of the databases it queried:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
March 25, 2016 at 1:40 pm
kaplan71 (3/25/2016)
Hello --Thank-you for your posting, I ran the script you posted, substituting the actual patient ID in the appropriate slot. However, the query produced the following error for
each of the databases it queried:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
it worked for me.....can you post the actual code you used....not just the error?
please confirm which version of SQL you are using?
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 28, 2016 at 9:19 am
Hello --
I inserted the syntax UNION ALL in the script, and the following errors appeared on-screen:
Msg 156, Level 15, State 1, Procedure FindPatient, Line 34
Incorrect syntax near the keyword 'UNION'.
Msg 156, Level 15, State 1, Procedure FindPatient, Line 42
Incorrect syntax near the keyword 'UNION'.
March 28, 2016 at 9:23 am
Hello --
I reran the script, and it did produce output. The only thing is, the names of the respective databases were not listed. Is the output based on the order of the databases as
they are shown in the left pane of the studio?
If not, is there a way to include the names of the databases in the output?
March 28, 2016 at 9:24 am
<<deleted>> OP answered whilst posting
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 28, 2016 at 9:25 am
kaplan71 (3/28/2016)
Hello --I inserted the syntax UNION ALL in the script, and the following errors appeared on-screen:
Msg 156, Level 15, State 1, Procedure FindPatient, Line 34
Incorrect syntax near the keyword 'UNION'.
Msg 156, Level 15, State 1, Procedure FindPatient, Line 42
Incorrect syntax near the keyword 'UNION'.
The UNION ALL approach creates a single view in a single database. The view UNION ALLs together the same query against the tables in each one of your databases. If you add another database, you'll have to update the view.
The reason I didn't suggest this approach in the first place is that I was concerned about the number of reads that would result when you query the view. You're essentially reading the entire table from each database and then applying a WHERE clause to it. If you aren't concerned with this then go with it - it'll certainly be simpler to write.
March 28, 2016 at 9:28 am
kaplan71 (3/28/2016)
Hello --I reran the script, and it did produce output. The only thing is, the names of the respective databases were not listed. Is the output based on the order of the databases as
they are shown in the left pane of the studio?
If not, is there a way to include the names of the databases in the output?
Are you talking about the view or the procedure?
March 28, 2016 at 10:19 am
Hello --
Just so we're on the same page. The script in question is the following:
exec sp_MSForEachDB
'
USE ?;
if exists
(select table_name
from information_schema.tables
where table_name = ''RS_Patient_6'')
begin;
select * from RS_Patient_6
where PatientID_6_660 = ''<patient id>''
end;
';
I believe it is the view. Specifically when the output is shown, the fields of the databases are listed, as well as the patient that is the subject of the search. However, the output does not list the names of the databases with the fields.
How can the names of the databases be listed in the output with the fields?
March 28, 2016 at 10:23 am
kaplan71 (3/28/2016)
Hello --...
How can the names of the databases be listed in the output with the fields?
You can add db_name() function to the resultset.
exec sp_MSForEachDB
'
USE ?;
if exists
(select table_name
from information_schema.tables
where table_name = ''RS_Patient_6'')
begin;
select db_name() as dbname, * from RS_Patient_6
where PatientID_6_660 = ''<patient id>''
end;
';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 28, 2016 at 10:29 am
Hello --
That additional line did the trick!
This script will work well for us, and I just want to thank everyone for their help on this.
April 10, 2016 at 9:14 am
''?'' can be used in the SELECT statement to get names of databases.
exec sp_MSForEachDB
'
USE ?;
if exists
(select table_name
from information_schema.tables
where table_name = ''RS_Patient_6'')
begin;
select ''?'' as dbname, * from RS_Patient_6
where PatientID_6_660 = ''<patient id>''
end;
';
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply