many-to-many search

Hi,
I've currently got three tables, with the second being a link table to
establish a many-to-many relationship, and I'm trying to do a full-text
search. I want it to bring up instances in the first table (only one row of
the left table per search) where the text is found, and increase the rank of
the search results, where the search string occurs in the related table.
I'm currently doing a subquery that adds the sum of the rank of the linked
tables to create a total rank (example below), but I'm concerned that since
the two full-text queries aren't linked, the two ranks aren't relevant to
each other.
Has anyone else found a good/better way to do this?
Regards,
Dunc
--/ snip /--
SELECT * FROM (
SELECT VenueDetailID, VenueName, Addr1, Addr3, Addr4,
ISNULL(B.RANK, 0) + ISNULL(
(SELECT SUM(Rank)
FROM VenueExtras Y
INNER JOIN LinkExtrasVenue
ON Y.VenueExtrasID = LinkExtrasVenue.VenueExtrasID
AND LinkExtrasVenue.VenueID = A.VenueDetailsID
INNER JOIN CONTAINSTABLE(VenueExtras, ExtraName, 'ISABOUT ("cocktails"
WEIGHT(0.1))') AS Z
ON Y.VenueExtrasID = Z.[Key]), 0)
AS Rank
FROM VenueDetail AS A
LEFT JOIN CONTAINSTABLE(VenueDetail , VenueName, 'ISABOUT ("cocktails"
WEIGHT(0.1))') AS B
ON A.VenueDetailID = B.[Key]
WHERE A.Status > 0
) SubQuery
ORDER BY Rank DESC, VenueName
can you post the schema of all related tables?
Is it possible to consolidate VenueExtras and VenueDetails into a single
table?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
news:esp%234lheEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've currently got three tables, with the second being a link table to
> establish a many-to-many relationship, and I'm trying to do a full-text
> search. I want it to bring up instances in the first table (only one row
of
> the left table per search) where the text is found, and increase the rank
of
> the search results, where the search string occurs in the related table.
> I'm currently doing a subquery that adds the sum of the rank of the linked
> tables to create a total rank (example below), but I'm concerned that
since
> the two full-text queries aren't linked, the two ranks aren't relevant to
> each other.
> Has anyone else found a good/better way to do this?
> Regards,
> Dunc
> --/ snip /--
> SELECT * FROM (
> SELECT VenueDetailID, VenueName, Addr1, Addr3, Addr4,
> ISNULL(B.RANK, 0) + ISNULL(
> (SELECT SUM(Rank)
> FROM VenueExtras Y
> INNER JOIN LinkExtrasVenue
> ON Y.VenueExtrasID = LinkExtrasVenue.VenueExtrasID
> AND LinkExtrasVenue.VenueID = A.VenueDetailsID
> INNER JOIN CONTAINSTABLE(VenueExtras, ExtraName, 'ISABOUT ("cocktails"
> WEIGHT(0.1))') AS Z
> ON Y.VenueExtrasID = Z.[Key]), 0)
> AS Rank
> FROM VenueDetail AS A
> LEFT JOIN CONTAINSTABLE(VenueDetail , VenueName, 'ISABOUT ("cocktails"
> WEIGHT(0.1))') AS B
> ON A.VenueDetailID = B.[Key]
> WHERE A.Status > 0
> ) SubQuery
> ORDER BY Rank DESC, VenueName
>
|||In the name of keeping things a little secure, I've changed the names to
protect the innocent (table and field names, that is) on the example. I've
actually normalised them out of being one table (it used to contain a bitmap
field that related to each extra (which used to be stored in a global
array)) so I can search on them with a ranking; also there may eventually be
as many as 100 different extras.
In the example, I've simplified the scheme to the following:
Table: VenueDetails
VenueDetailsID
VenueName
Addr1
Addr2
Addr3
Addr4
Description
Table: LinkExtrasVenue
VenueID
VenueExtrasID
Table: VenueExtras
VenueExtrasID
ExtraName
A venue listing may have a name of "Bob's Cocktail Palace", which may have
extras of "Cocktails" and "Cocktails recommended". This needs to be ranked
above "Bill's Bar" which has an extra of "Cocktails", which in turn needs to
be ranked higher than "Brian's bar" which is situated on "15 Cocktail St"
Confused yet? I am!
At the risk of looking like I'm having a free plug, so you can see how it
works - check out:
http://www.fluidfoundation.com/venue...sp?BarRef=1405 - you'll notice
this one has the two cocktails flags set, whereas:
http://www.fluidfoundation.com/venue...sp?BarRef=2820 doesn't, so
*should* come second in the rankings when you search on "Cocktails". But
doesn't.
Dunc
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OOec7vheEHA.2812@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> can you post the schema of all related tables?
> Is it possible to consolidate VenueExtras and VenueDetails into a single
> table?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
> news:esp%234lheEHA.3928@.TK2MSFTNGP11.phx.gbl...
row[vbcol=seagreen]
> of
rank[vbcol=seagreen]
> of
linked[vbcol=seagreen]
> since
to
>

Management Studio startup error: splash screen then nothing

I have got sql 2005 installed and the management studio has stopped working.
Now when I try and open it up, I see the splash screen for a split second and
then nothing.
I tried uninstalling BOL (july 2006) which some posts suggested but it still
doesn't work.
Any ideas?
After the splash screen goes away, do you see sqlWb.exe in Task Manager's
process list?
"adolf garlic" <adolfgarlic@.discussions.microsoft.com> wrote in message
news:0AAA71A7-C4C2-447F-81D1-727DC5C475B0@.microsoft.com...
>I have got sql 2005 installed and the management studio has stopped
>working.
> Now when I try and open it up, I see the splash screen for a split second
> and
> then nothing.
> I tried uninstalling BOL (july 2006) which some posts suggested but it
> still
> doesn't work.
> Any ideas?
|||No.
"Aaron Bertrand [SQL Server MVP]" wrote:

> After the splash screen goes away, do you see sqlWb.exe in Task Manager's
> process list?
>
>
> "adolf garlic" <adolfgarlic@.discussions.microsoft.com> wrote in message
> news:0AAA71A7-C4C2-447F-81D1-727DC5C475B0@.microsoft.com...
>
>
|||Okay, anything in the event log? Do you have error reporting enabled? Are
you running as a local administrator, or a less privileged account?

> No.
|||There is nothing in the event log (warnings or errors) that looks sql related.
I'll see if I can switch on error reporting (if it isn't already on)
I am running as local admin.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Okay, anything in the event log? Do you have error reporting enabled? Are
> you running as a local administrator, or a less privileged account?
>
>
>
|||adolf garlic (adolfgarlic@.discussions.microsoft.com) writes:
> There is nothing in the event log (warnings or errors) that looks sql
> related. I'll see if I can switch on error reporting (if it isn't
> already on) I am running as local admin.
Did you ever have the Express version of Mgmt Studio on your machine?
The Express version and the full version don't go well together.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I did have the express version of SQL Server with advanced services but
uninstalled it before embarking on the normal version.
"Erland Sommarskog" wrote:

> adolf garlic (adolfgarlic@.discussions.microsoft.com) writes:
> Did you ever have the Express version of Mgmt Studio on your machine?
> The Express version and the full version don't go well together.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||adolf garlic (adolfgarlic@.discussions.microsoft.com) writes:
> I did have the express version of SQL Server with advanced services but
> uninstalled it before embarking on the normal version.
And "embarking" means that you installed the normal version?
Clutching at straws, you could try uninstall the tools and then reinstall.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Yup. The whole nutbag (analysis services, reporting services, ssis, sql)
except notification services.
I'll try uninstalling the tools and reinstalling (I think I already tried
this).
Thanks for everyone's suggestions so far
"Erland Sommarskog" wrote:

> adolf garlic (adolfgarlic@.discussions.microsoft.com) writes:
> And "embarking" means that you installed the normal version?
> Clutching at straws, you could try uninstall the tools and then reinstall.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Add remove programs
Remove client tools
reboot
add remove programs
sql server 2005
add new component
navigate to setup.exe
add all client tool components, sample dbs etc
it works
what a pain, this doesn't make sense (working, stops working, working...)
just hope it doesn't happen again
"adolf garlic" wrote:
[vbcol=seagreen]
> Yup. The whole nutbag (analysis services, reporting services, ssis, sql)
> except notification services.
> I'll try uninstalling the tools and reinstalling (I think I already tried
> this).
> Thanks for everyone's suggestions so far
> "Erland Sommarskog" wrote: