Any SQL Server Guru?

Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>

any SQL gurus out there?

Hello, this probably isnt the best place to ask but I can't find a more
suitable sql newsgroup so I hope y'all dont mind too much.

I have 2 tables; Cellar and Colour

CELLAR contains the wine name, its year and the no.of bottles.

Wine Year Bottles
Chardonnay 87 4
Fume Blanc 87 2
Pinot Noir 82 3
Zinfandel 84 9

COLOUR contains wine name and it's colour

Wine Colour
Chardonnay White
Fume Blanc White
Pinot NoirRed
Zinfandel Rose

This is from a past exam paper btw

One of the questions was:
Write the sql to count how many white wines there are in the table cellar.

The solution that the lecturers included is:

SELECT count(wine)

FROM cellar

WHERE colour='White'

Now i havent' been able to try out this sql yet but to me that looks wrong.

My solution would be:

SELECT count(wine)

FROM cellar

WHERE cellar.wine = colour.wine and colour.colour='White'

Can anyone tell me which one is correct, and if mine isn't correct then why
isn't it?

Thanks>> Write the sql to count how many white wines there are in the table
cellar. <<

The first answer is wrong; look at the missing table in the FROM
clause. And the quesiton is vague. Do I want the actual bottle count
or a count by wine_type

SELECT COUNT(DISTINCT type_wine) AS type_count, COUNT(*) AS
bottle_count
FROM Cellar AS C, WineColours AS W
WHERE C.wine_type = W.wine_type
AND W.colour = 'White' ;

I have a total of six bottles of whites in two varieties.|||> The first answer is wrong; look at the missing table in the FROM
> clause. And the quesiton is vague. Do I want the actual bottle count
> or a count by wine_type
> SELECT COUNT(DISTINCT type_wine) AS type_count, COUNT(*) AS
> bottle_count
> FROM Cellar AS C, WineColours AS W
> WHERE C.wine_type = W.wine_type
> AND W.colour = 'White' ;
> I have a total of six bottles of whites in two varieties.

thanks for your answer

yeh the question is poorly worded however I believe it simply refers to the
number of types, i.e 2 (chardonnay, fume blanc).|||Well, you don't need an "SQL guru" for that query. Since the question is
not really clear, you can pick the column you need.

SELECT COUNT(Distinct Wine) NumberOfWhiteWineBrands
, SUM(Bottles) NumberOfBottlesOfWhiteWine
FROM Cellar
INNER JOIN Colour
ON Colour.Wine = Cellar.Wine
WHERE Colour.Colour = 'White'

If it is the column "NumberOfWhiteWineBrands" that you need, then you
could also write

SELECT COUNT(*)
FROM Colour
WHERE Colour = 'White'
AND EXISTS (
SELECT 1
FROM Cellar
WHERE Cellar.Wine = Colour.Wine
)

HTH,
Gert-Jan

Jay wrote:
> Hello, this probably isnt the best place to ask but I can't find a more
> suitable sql newsgroup so I hope y'all dont mind too much.
> I have 2 tables; Cellar and Colour
> CELLAR contains the wine name, its year and the no.of bottles.
> Wine Year Bottles
> Chardonnay 87 4
> Fume Blanc 87 2
> Pinot Noir 82 3
> Zinfandel 84 9
> COLOUR contains wine name and it's colour
> Wine Colour
> Chardonnay White
> Fume Blanc White
> Pinot NoirRed
> Zinfandel Rose
> This is from a past exam paper btw
> One of the questions was:
> Write the sql to count how many white wines there are in the table cellar.
> The solution that the lecturers included is:
> SELECT count(wine)
> FROM cellar
> WHERE colour='White'
> Now i havent' been able to try out this sql yet but to me that looks wrong.
> My solution would be:
> SELECT count(wine)
> FROM cellar
> WHERE cellar.wine = colour.wine and colour.colour='White'
> Can anyone tell me which one is correct, and if mine isn't correct then why
> isn't it?
> Thanks

Any SELECT Statement Gurus Out There?

Hi all,

I'm having real trouble wrapping my newbie brain around this problem. Can
someone please tell me the most efficient (or any!) way to write a SELECT
statement to return a set of rows from 5 tables:

These tables are part of our medical database. For example, a person seeking
healthcare may want to know the names and contact information for all
doctors in Reno, NV, who do cardiology. Sounds simple, but it isn't.

A medical group can have many doctors.
A doctor may be a member of more than one medical group.
A doctor or group can have multiple specialties.
A group can have multiple facilities (physical locations).

So the tables look like this...

Table: Doctors
-----
DoctorName
DoctorID

Table: Groups
-----
GroupName
GroupID

Table: Docs2Groups (provides many-to-many relationship between Doctors and
Groups)
-------
DoctorID
GroupID

Table: Specialties (provides many-to-many relationshop between Doctors,
Groups, and Specialties)
------
SpecialtyID
SpecialtyDesc
DoctorID
GroupID

Facilities
---
FacilityName (provides one-to-many relationship between Groups and
Facilities)
FacilityID
GroupID
Address
City
State
Zip
Phone
E-mail

Any help would be GREATLY appreciated.

--Eric Robinsonthis table confuses me:

Table: Specialties (provides many-to-many relationshop between Doctors,
Groups, and Specialties)
------
SpecialtyID
SpecialtyDesc
DoctorID
GroupID

Are you saying that a doctor can only specialize in something within
the context of a certain group? In other words Dr.Smith is a pediatric
oncologist, but can only work in pediatrics for Group A and oncology
for group B? Does that happen?

It would be easier if a Group was a collection of doctors who all had
specialties.

SELECT d.DoctorName
FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
JOIN Specialties s ON d.DoctorID = s.DoctorID
JOIN Facilities f ON f.GroupID = d2g.GroupID
WHERE s.SpecialityDesc = 'cardiology'
AND f.City = 'Reno' AND f.State = 'NV'

But, if a Doctor can only specialize in something in the context of a
group, then you could do this:

SELECT d.DoctorName
FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
JOIN Specialties s ON d2g.DoctorID = s.DoctorID
AND d2g.GroupID = s.GroupID
JOIN Facilities f ON f.GroupID = d2g.GroupID
WHERE s.SpecialityDesc = 'cardiology'
AND f.City = 'Reno' AND f.State = 'NV'

Untested.

HTH,
Stu|||Stu,

Each doctor has his or her own primary specialty and 0 or more additional
specialties. These specialties are asociated with the doctor, not the group.
A group's specialty is a function of its participating doctors. If all the
docs for Group A are cardiologists, then Group A is considered a cardiology
group. If the docs are of different specialties, then the group is
considered "multispecialty." In that sense, a group is, as you say, a
collection of doctors.

HOWEVER, there are unusual cases where the group consists of docs with
different specialties, but it still wants to to be known (for the purposes
of our directory) as one certain kind of group. In these cases, the group
itself gets an "overriding" specialty associated with it to keep from being
listed as "multispecialty."

Therefore the Specialties table does double-duty, but there are only a few
records with GroupID <> 0.

Does that clear things up?

--Eric

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126550993.851782.190410@.g43g2000cwa.googlegr oups.com...
> this table confuses me:
> Table: Specialties (provides many-to-many relationshop between Doctors,
> Groups, and Specialties)
> ------
> SpecialtyID
> SpecialtyDesc
> DoctorID
> GroupID
> Are you saying that a doctor can only specialize in something within
> the context of a certain group? In other words Dr.Smith is a pediatric
> oncologist, but can only work in pediatrics for Group A and oncology
> for group B? Does that happen?
> It would be easier if a Group was a collection of doctors who all had
> specialties.
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d.DoctorID = s.DoctorID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> But, if a Doctor can only specialize in something in the context of a
> group, then you could do this:
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d2g.DoctorID = s.DoctorID
> AND d2g.GroupID = s.GroupID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> Untested.
> HTH,
> Stu|||Oops, I mistated the Specialties table. It is actually three tables:

Specialty
---
SpecialtyID
SpecialtyDesc

Doc2Specialty
------
SpecialtyID
DoctorID

Group2Specialty
------
SpecialtyID
GroupID

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126550993.851782.190410@.g43g2000cwa.googlegr oups.com...
> this table confuses me:
> Table: Specialties (provides many-to-many relationshop between Doctors,
> Groups, and Specialties)
> ------
> SpecialtyID
> SpecialtyDesc
> DoctorID
> GroupID
> Are you saying that a doctor can only specialize in something within
> the context of a certain group? In other words Dr.Smith is a pediatric
> oncologist, but can only work in pediatrics for Group A and oncology
> for group B? Does that happen?
> It would be easier if a Group was a collection of doctors who all had
> specialties.
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d.DoctorID = s.DoctorID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> But, if a Doctor can only specialize in something in the context of a
> group, then you could do this:
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d2g.DoctorID = s.DoctorID
> AND d2g.GroupID = s.GroupID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> Untested.
> HTH,
> Stu|||Eric Robinson (eric @. pmcipa..{com}) writes:
> These tables are part of our medical database. For example, a person
> seeking healthcare may want to know the names and contact information
> for all doctors in Reno, NV, who do cardiology. Sounds simple, but it
> isn't.
> A medical group can have many doctors.
> A doctor may be a member of more than one medical group.
> A doctor or group can have multiple specialties.
> A group can have multiple facilities (physical locations).
> So the tables look like this...

It is always preferable to post CREATE TABLE scripts for the table.
That and test data in INSERT statemants, and the desired result of
the test data. That will give you a tested solution.

So this is an untested solution:

SELECT D.DoctorName
FROM Doctors D
WHERE EXISTS (SELECT *
FROM Doc2Groups DG
JOIN Facility F ON F.GroupID = DG.GroupID
WHERE DG.DoctorID = D.DoctorID
AND F.State = 'NV'
AND F.City = 'Reno')
AND EXISTS (SELECT *
FROM Doc2Specialiity DS
JOIN Specialtities S ON DS.SpecialityID = S.SpecialityID
WHERE DS.DoctorID = S.DoctorID
AND S.SpecialityDesc = 'Cardiology')
UNION
SELECT D.DoctorName
FROM Doctors D
WHERE EXISTS (SELECT *
FROM Doc2Groups DG
JOIN Facility F ON F.GroupID = DG.GroupID
JOIN Group2Specialiity GS ON GS.GroupID = DS.GroupID
JOIN Specialtities S ON GS.SpecialityID = S.SpecialityID
WHERE DG.DoctorID = D.DoctorID
AND F.State = 'NV'
AND F.City = 'Reno'
AND S.SpecialityDesc = 'Cardiology')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 12 Sep 2005 12:36:02 -0700, Eric Robinson wrote:

>Oops, I mistated the Specialties table. It is actually three tables:
(snip)

Hi Eric,

I'm glad you posted that, since I was just preparing to telll you how
you should change your design and all <grin>.

Anyway, since we now have doctors who do cardiology, groups that
specialize in cardiology and doctors that belong to groups that do
cardiology, it's not exactly clear what you want your query to return.
I'll assume you want to know what I would be interested in if my heart
starts acting funny while I'm in Reno - I'd like to know where to go
(and quick!).

The query below will return the name of the group and the details of the
location for each group with a location in Reno, NV that either has
cardiology as group specialization, or hosts at least one doctor who
specializes in cardiology.

SELECT g.GroupName, f.FacilityName, f.Address, f.Phone
FROM Groups AS g
INNER JOIN Facilities AS f
ON f.GroupID = g.GroupID
LEFT JOIN Group2Specialties AS g2s
ON g2s.GroupID = g.GroupID
CROSS JOIN (SELECT SpecialtyID
FROM Specialties
WHERE SpecialtyDesc = 'Cardiology') AS s(SpecialtyID)
WHERE f.City = 'Reno'
AND f.State = 'NV'
AND
( g2s.SpecialtyID = s.SpecialtyID
OR EXISTS
(SELECT *
FROM Docs2Groups AS d2g
INNER JOIN Doc2Specialties AS d2s
ON d2s.DoctorID = d2g.DoctorID
WHERE d2g.GroupID = g.GroupID
AND d2s.SpecialtyID = s.SpecialtyID))

(untested - see www.aspfaq.com/5006 if you prefer tested suggestions).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I was offline for a few hours and I see that I have some very throughtful
replies. I will take a closer look at them. In the meantime, based on Stu's
initial suggestion, I came up with the following, which SEEMS to work:

For simplicity's sake, I lied in my original message about the true names of
the tables, so the following query looks slightly different because it
contains real table names. (In the future I'll take Erland's suggestion and
post CREATE TABLE scripts instead.)

SELECT s.Specialty, p.LastName, p.FirstName, p.Degree, g.GroupName,
f.PhysAddr1, f.PhysCity, f.PhysAddr2, f.PhysState, f.Phone1
FROM tblProviders p JOIN tblBindProviderGroup p2g on p.ProviderID =
p2g.ProviderID
JOIN tblGroups g on g.GroupID=p2g.GroupID
JOIN tblFacilities f on f.GroupID=p2g.GroupID
JOIN tblBindProviderSpecialty p2s on p2s.ProviderID=p.ProviderID
JOIN tblSpecialties s on s.SpecialtyID=p2s.SpecialtyID
WHERE f.PhysCity='Reno'
ORDER BY p.LastName

I've run this against the actual data and it SEEMS to return correct
results.

I'm guessing that some of the other approaches people have posted are better
in ways I have not yet thought of.

--Eric

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CFEF79E2BE1Yazorman@.127.0.0.1...
> Eric Robinson (eric @. pmcipa..{com}) writes:
>> These tables are part of our medical database. For example, a person
>> seeking healthcare may want to know the names and contact information
>> for all doctors in Reno, NV, who do cardiology. Sounds simple, but it
>> isn't.
>>
>> A medical group can have many doctors.
>> A doctor may be a member of more than one medical group.
>> A doctor or group can have multiple specialties.
>> A group can have multiple facilities (physical locations).
>>
>> So the tables look like this...
> It is always preferable to post CREATE TABLE scripts for the table.
> That and test data in INSERT statemants, and the desired result of
> the test data. That will give you a tested solution.
> So this is an untested solution:
> SELECT D.DoctorName
> FROM Doctors D
> WHERE EXISTS (SELECT *
> FROM Doc2Groups DG
> JOIN Facility F ON F.GroupID = DG.GroupID
> WHERE DG.DoctorID = D.DoctorID
> AND F.State = 'NV'
> AND F.City = 'Reno')
> AND EXISTS (SELECT *
> FROM Doc2Specialiity DS
> JOIN Specialtities S ON DS.SpecialityID = S.SpecialityID
> WHERE DS.DoctorID = S.DoctorID
> AND S.SpecialityDesc = 'Cardiology')
> UNION
> SELECT D.DoctorName
> FROM Doctors D
> WHERE EXISTS (SELECT *
> FROM Doc2Groups DG
> JOIN Facility F ON F.GroupID = DG.GroupID
> JOIN Group2Specialiity GS ON GS.GroupID = DS.GroupID
> JOIN Specialtities S ON GS.SpecialityID =
> S.SpecialityID
> WHERE DG.DoctorID = D.DoctorID
> AND F.State = 'NV'
> AND F.City = 'Reno'
> AND S.SpecialityDesc = 'Cardiology')
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Any Gurus...?

I asked the following question in the 'MS Access' boards but I am wondering if there is a way to do it on our SQL server as well? Heres the original question:

"Dont ask why, we just need to do something odd...How can you use the system tables in access to do the following:

1. write a query on the fly for each table in the database (without knowing the table names ahead of time)

2. the query also needs to combine all the fields in a table into one single field (ie - queryfield:[field1]&[field2}&[etc...]), again without knowing what the field names are ahead of time

I have searched news groups and web rings all night. Please help!"You'll get the tables by searching the table Sysobjects for all objects whose xtype is "U". Write a SP that does a cursor loop through all records satisfying that search and, for each match, runs that query that you want to run on all tables.

To concatenate all fields, I guess you'll need to first investigate the columns' data types and convert non-string types to strings? And handle null values? And handles non-convertable types?
Start with the Syscolumns column...

Check first if there are any system SP's that could help you avoid having the code Select's directly against system tables.|||I asked the following question in the 'MS Access' boards but

Q1 I am wondering if there is a way to do it on our SQL server as well?

Heres the original question: "Dont ask why, we just need to do something odd...How can you use the system tables in access to do the following: 1. write a query on the fly for each table in the database (without knowing the table names ahead of time) 2. the query also needs to combine all the fields in a table into one single field (ie - queryfield:[field1]&[field2}&[etc...]), again without knowing what the field names are ahead of time I have searched news groups and web rings all night. Please help!"

A1 Yes, it is certainly possible (in Sql Server and in MS Access). The original #2. may entail work arounds (with many very long table names, especially if these include spaces and / or special characters)

The applicable special stored procedures (Sql Server 2000 sp_) would be sp_tables and sp_columns. One may also make use of [INFORMATION_SCHEMA] views to address your tasks.

-- Example useages of sp_tables and sp_columns:

Use Pubs
Go

Exec sp_tables
Exec sp_tables @.table_type = ['Table']
Exec sp_tables @.table_type = ['View']
Exec sp_tables @.table_type = ['System Table']

Exec sp_columns @.table_name = 'Authors'
--------

-- General List: Catalog Special Stored Procedures:
sp_column_privileges
sp_special_columns
sp_columns
sp_sproc_columns
sp_databases
sp_statistics
sp_fkeys
sp_stored_procedures
sp_pkeys
sp_table_privileges
sp_server_info
sp_tables
--------

-- INFORMATION_SCHEMA views (return metadata of DB objects):

CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINES
ROUTINE_COLUMNS
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS

----
Note: Selecting data from information schema views requires using an [INFORMATION_SCHEMA] qualified object name i.e.(in the position where one normally specifies nothing, or the dboo name as appropriate). For example:

SELECT *
FROM Master.[INFORMATION_SCHEMA].COLUMNS
----

-- THE FOLLOWING would fail (unless someone has created a user table / view named 'COLUMNS'):

SELECT *
FROM Master..COLUMNS

SELECT *
FROM Master.dbo.COLUMNS
--------|||Just an example... Improvement and tidying could be made I'm sure!

declare @.id int,
@.name varchar(255),
@.transaction varchar(8000),
@.queryField varchar(8000),
@.col_list varchar(8000),
@.tab_name varchar(255),
@.col_err real,
@.tab_err real

declare table_scan cursor for
select name, id
from sysobjects
where type = 'U'

open table_scan

fetch table_scan into @.tab_name, @.id
set @.tab_err = @.@.FETCH_STATUS
PRINT @.tab_err

while (@.tab_err = 0 )
begin
DECLARE column_scan CURSOR FOR
SELECT name
FROM syscolumns
WHERE ID = @.id

OPEN column_scan
FETCH column_scan into @.name

SELECT @.col_list = @.name
SELECT @.queryField = 'queryfield:['+@.name+']'

SELECT @.col_err = @.@.FETCH_STATUS

WHILE (@.col_err = 0)
begin
SELECT @.col_list = @.col_list +', ' + @.name
SELECT @.queryField = @.queryField + '&['+@.name+']'

FETCH column_scan into @.name
set @.col_err = @.@.FETCH_STATUS
end
SELECT @.transaction = 'SELECT '+ @.col_list + ' FROM ' + @.tab_name
PRINT '================================================= '
PRINT @.transaction
print @.QueryField
PRINT '================================================= '
EXEC(@.transaction)

close column_scan
deallocate column_scan
--
--Table completed
--
fetch table_scan into @.tab_name, @.id
set @.tab_err = @.@.FETCH_STATUS
end

close table_scan

deallocate table_scan

Any full-text gurus - Help writing a query

I'm not sure how I can best phrase this, but I need help writing a query that is inflectional, thesaurus and non word ordered specific. In other words, say a user is looking for a "car wash" but types in "wash auto" or "auto wash" or "vehicle wash" or "cars washed" or "washing cars" or "car and auto wash" or "automobile car wash", etc. I still want a result produced of the entry in the database "car wash", how can I produce this query. Another example, say I want to produce a result of a record in the db "server administrators - sql" and a user searches for "sql server administrator" or "sql administrator", how can I produce the result with the same query as that of the above. The same query should be able to produce both results given any quantity of words searched for or words in the table.

I also have a concern of spelling. Say a user enters "sql srver aministrater", is there any SQL Server 2005 CTP query that can help with this? Will I need a third party tool? If so, how can I implement this.

The UI is an asp.net web based app and I want to target SQL Server 2005.

Thanks in advance everyone! I appreciate what you folks do!

You should be able to do everything you need using the thesaurus feature of SQL 2005 but you will have to do some work to do it.

You just need to define the appropriate thesaurus definition for the language you are querying in.

FREETEXT queries will treat the query string as a bag of words and perform stemming and thesaurus replacement and expansion on all terms (unless you enclose the entrie string in double quotes)

If you are querying in english (1033 = ENU) you would need to edit the file:

C:\program files\microsoft sql server\mssql.1\mssql\FTData\tsENU.xml

by default this is essentially empty since everything is commented out, to get it to work for your exmaple you need to tell it that car and auto and vehicle and automobile are equivalent terms. Stemming already knows that wash, wahses and washing are forms of wash so you don't need to tell it that.

<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
_car
_auto
_automobile
_vehicle
</expansion>
</thesaurus>
</XML>

for your other example you could use the thesaurus to handle typos too

<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<replacement>
<pat>svrer</pat>
<pat>servr</pat>
_Server
</replacement>
<replacement>
<pat>Adminstrater</pat>
_{Administrator}
</replacement>
</thesaurus>
</XML>

Something like that.

Basically you can either expand or replace terms using the thesaurus but you do need to define your own replacements.

You can use thesaurus in two ways:

1. freetext or freetext table queries (remember do no enclose the string in double quotes)
2. contains or containstable using FORMSOF(THESAURUS,queryterm)
Hope this helps

Dave Poole

|||Thanks for the reply Dave, however neither solution works well for me. Neither is intuitive enough...or maybe that's me.

FREETEXT is too "fuzzy". It will produce a result of "car wash" from "auto wash" for example, but the result will also include results of all records that the thesaurus has an expansion on. Every record with car, auto, wash, etc will be included in the result for example.

CONTAINS is more precise however it doesn't work well since you have to provide a THESAURUS of not only synonyms, but INFELCETIONAL words as well, ie., "cars", "automobiles", "servers", "administrators", etc. Creating a THESAURUS is a daunting task by itself let alone having to expand on all inflectionals as well and I "ain't" no english major if you know what I mean.

Is there an existing way to get an inflectional list for a word and then use this result in a query? Same question for synonyms and Spell Check?

If big brother MS has their ears on, would be nice to have functions that will do this. If you need help, let me know$$$. I don't have time to do develop this and it's killing me. Infelections should be programable and not hard coded. Should also be able to define different instances of these as required in the query, database, or app. Same holds true for the Thesaurus. Should have functions that will pull either synonyms or antonyms. I know the question is why, but that's proprietary. Also, give us a Thesaurus and Dictionaries that we can hack as needed.

Also, if someone could tell me how the heck rank and weight are ranked and weighed, I'd appreciate it. The results I'm getting don't seem to be ranked and weighed as I would have thought.

Can't program myself out of a paper bag sometimes but still trying.

Thanks in advance and again.
|||

Perhaps some more complex patterns in the thesaurus may help for you case. I believe the logic in the thesaurus is to d oa longest match first so if you have entries like this:

<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
_{car wash}
_{washing cars}
_{automobile wash}
</expansion>
</thesaurus>
</XML>

It should reduce the noise.

If you query for CONTAINS(*,'FORMSOF(THESAURUS,"car wash")') you should hit docs which contain the alternate forms but will avoid docs with just car or wash or washing etc.

This still doesn't help with your issue of requiring specification of all inflectional forms.

I guess there are really 3 things you are asking in this post, I'll take them one by one.

1. You would like to be able to access inflectional, thesaurus forms from sql programatically so you can do your own processing of queries.

Thanks for your feedback on this. We are listening. There is a high likelihood that this functionality will be included in a post SQL Server 2005 release.

In the SQL 2005 timeframe there is a utility called lrtest.exe which ships with sql server 2005 (in the binn directory) which you can use to invoke the wordbreaker, stemmer and filter on strings or input files. This uses the IWordbreaker, IStemmmer and IFilter interfaces which are documented in MSDN. Have a play with this tool and see if the kind of operations it performs could be useful to you. If so you could write your own wrapper to invoke this type of functionality from your application.

2. You would like a pre-defined thesaurus to ship with sql server which you could then modify to your needs.

Again, thanks for the feedback. This is another feature that will likely show up in a later version of SQL Server.

3. Explanation of ranking and weighting.

There is a very good BooksOnline topic called Understanding Ranking which goes into a lot of detail on the ranking algorithm used Hopefully it will answer your questions.
Hope this helps
Dave Poole
SQL Server Fulltext Team

|||

Thanks again for the reply Dave however this is not really that helpfull to me as it does not provide a solution but I do again appreciate your reply very much. It's also nice to know that big brother has their ears on. Implementing user feedbacks is key to making MS products what they are. While not perfect, the road to it is always under construction as with us all.

Please note the spell check as well. Also, having the functionality of the three; inflectional, thesaurus and spell check, available in one unique predicate would be tremendously helpful and benificial to the MS freetext toolbox as well as possibly expanding immensly on the marketability of these products. This however could make the fuzzy problem worse, but it would be tremendous to have these resources available in one predicate. Expand on this to get the complete jest.

My project is too huge to expand on the Thesaurus in this mannor. I know I need to provide some sort of intuitive logic to hash over the users input, but this is making it a coding nightmare for me. Having to provide a solution for the infinitesimal ways that a user may search for something in this project is really blowing my mind. I've come up with some near solutions, but this again is making it a coding nightmare. I seen a big chunk of the functionality that I'm looking for out on the net so I know it's possible...wish I could find out how the heck they're doing it that's why I posted this here.

You're miles above me when it comes to writing a wrapper as mentioned above, but you've definitely sparked my interest in investigating this further. From first glance it still appears that it will not provide all the functionality that I'm looking for but I'm going to investigate further if I can find the time. If you know of some time saving tips in writing these wrappers and have the time yourself to provide it, I'd love to hear more.

I've got a million of them, but I again wanted to point out that with a thesaurus implemented in this mannor as you mentioned above, this functionality needs to be application independent. In other words, the thesaurus can't be unique to the entire server. There must be a way to implement multiple thesaurus' based on the requirements of the query, db, interface, app, etc. Same holds true for inflectionals. Also, nothing should be hard coded without some form of ability to modify and implement as required on a per interface basis for example. Ie, best shoud not always stem to good.

Thanks again for your time and all my best regards in these endeavors!

Wayne

PS Hope at least some of this makes sense as I am, as always, running on not enough sleep and not enough time.

Any advantage to using named pipes in connecting to a SQL Server?

Gurus,
Is there any advantage to running an application to use named pipes in
connecting to a SQL Server instead of TCP/IP? I would think no, b/c named
pipes is a legacy, slower form of communication, correct?
--
SpinI'm sure if you look hard enough, you could find some advantage of using
named pipes. But that's really pointless. As you said, named pipes are kind
of legacy, and TCP sockets are the way.
SQL Books Online doesn't seem to be quite up to date on this topic. For
instance, it stats that, "Generally, TCP/IP is preferred in a slow LAN, WAN,
or dial-up network, whereas named pipes can be a better choice when network
speed is not the issue, as it offers more functionality, ease of use, and
configuration options." I'm curious as to why named pipes offer more
configuration options. It seems to me that TCP is much more configurable.
Linchi
"Spin" wrote:
> Gurus,
> Is there any advantage to running an application to use named pipes in
> connecting to a SQL Server instead of TCP/IP? I would think no, b/c named
> pipes is a legacy, slower form of communication, correct?
> --
> Spin
>
>

Any advantage to using named pipes in connecting to a SQL Server?

Gurus,
Is there any advantage to running an application to use named pipes in
connecting to a SQL Server instead of TCP/IP? I would think no, b/c named
pipes is a legacy, slower form of communication, correct?
Spin
I'm sure if you look hard enough, you could find some advantage of using
named pipes. But that's really pointless. As you said, named pipes are kind
of legacy, and TCP sockets are the way.
SQL Books Online doesn't seem to be quite up to date on this topic. For
instance, it stats that, "Generally, TCP/IP is preferred in a slow LAN, WAN,
or dial-up network, whereas named pipes can be a better choice when network
speed is not the issue, as it offers more functionality, ease of use, and
configuration options." I'm curious as to why named pipes offer more
configuration options. It seems to me that TCP is much more configurable.
Linchi
"Spin" wrote:

> Gurus,
> Is there any advantage to running an application to use named pipes in
> connecting to a SQL Server instead of TCP/IP? I would think no, b/c named
> pipes is a legacy, slower form of communication, correct?
> --
> Spin
>
>