FTP file using DTS package

I have a DTS package which does the following
1. EXECUTE SQL TASK (query in it)
on success
2. Microsoft OLE DB (database connection)
transform data task
3. Text File Destination (creates a text file TEST in a folder)
on success
4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
The problem is I click on the FILE TRANSFER PROTOCOL TASK
and its asks me to enter the FTP SITE USERNAME AND PASSWORD
and then the destination DIRECTORY PATH.
I don't know the path. What do I need to mention here
Also when I click on file and select a Blank Text file from there
and check overwrite still after the task is complete data does not
appears in that.
ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
Directory path BLANK.
Does anyone knows how to figure this out. I am sure its a simple
solution.VJ:
What do you mean you do not know the path? If you were to manually upload
the file to an ftp site you would type in the ftp site. For example:
ftp.access.com. If this was a valid ftp site then I would be taken to the
folder where I needed to send my files. This is usually a setting that is
set up by the company in charge of the ftp site.
So when you set up the FTP on DTS you would chose internet as the source,
enter your username and password. On the path you would use :ftp.access.com.
I would need to know more information on your file move or overwrite
problem. Does the problem exist when you move the file from your file that
you created to the ftp site? Maybe once you get the FTP path configured
correctly you will not have this problem.
--
Thanks Kllyj64
"VJ" wrote:
> I have a DTS package which does the following
>
> 1. EXECUTE SQL TASK (query in it)
> on success
> 2. Microsoft OLE DB (database connection)
> transform data task
> 3. Text File Destination (creates a text file TEST in a folder)
> on success
> 4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
> The problem is I click on the FILE TRANSFER PROTOCOL TASK
> and its asks me to enter the FTP SITE USERNAME AND PASSWORD
> and then the destination DIRECTORY PATH.
> I don't know the path. What do I need to mention here
> Also when I click on file and select a Blank Text file from there
> and check overwrite still after the task is complete data does not
> appears in that.
> ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
> Directory path BLANK.
>
> Does anyone knows how to figure this out. I am sure its a simple
> solution.
>|||VJ wrote:
> I have a DTS package which does the following
>
> 1. EXECUTE SQL TASK (query in it)
> on success
> 2. Microsoft OLE DB (database connection)
> transform data task
> 3. Text File Destination (creates a text file TEST in a folder)
> on success
> 4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
> The problem is I click on the FILE TRANSFER PROTOCOL TASK
> and its asks me to enter the FTP SITE USERNAME AND PASSWORD
> and then the destination DIRECTORY PATH.
> I don't know the path. What do I need to mention here
> Also when I click on file and select a Blank Text file from there
> and check overwrite still after the task is complete data does not
> appears in that.
> ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
> Directory path BLANK.
>
> Does anyone knows how to figure this out. I am sure its a simple
> solution.
>
Hi
I think the problem is that the FTP task is only only able to download
files from an FTP site - not upload.
Instead you can create a bat file with the commands for the files you
want to upload and then execute this bat file from your DTS package.
That's the way I'm doing it and that works fine.
Regards
Steen Schlüter Persson
DBAsql

FTP file using DTS package

I have a DTS package which does the following
1. EXECUTE SQL TASK (query in it)
on success
2. Microsoft OLE DB (database connection)
transform data task
3. Text File Destination (creates a text file TEST in a folder)
on success
4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
The problem is I click on the FILE TRANSFER PROTOCOL TASK
and its asks me to enter the FTP SITE USERNAME AND PASSWORD
and then the destination DIRECTORY PATH.
I don't know the path. What do I need to mention here
Also when I click on file and select a Blank Text file from there
and check overwrite still after the task is complete data does not
appears in that.
ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
Directory path BLANK.
Does anyone knows how to figure this out. I am sure its a simple
solution.VJ:
What do you mean you do not know the path? If you were to manually upload
the file to an ftp site you would type in the ftp site. For example:
ftp.access.com. If this was a valid ftp site then I would be taken to the
folder where I needed to send my files. This is usually a setting that is
set up by the company in charge of the ftp site.
So when you set up the FTP on DTS you would chose internet as the source,
enter your username and password. On the path you would use :ftp.access.com
.
I would need to know more information on your file move or overwrite
problem. Does the problem exist when you move the file from your file that
you created to the ftp site? Maybe once you get the FTP path configured
correctly you will not have this problem.
--
Thanks Kllyj64
"VJ" wrote:

> I have a DTS package which does the following
>
> 1. EXECUTE SQL TASK (query in it)
> on success
> 2. Microsoft OLE DB (database connection)
> transform data task
> 3. Text File Destination (creates a text file TEST in a folder)
> on success
> 4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
> The problem is I click on the FILE TRANSFER PROTOCOL TASK
> and its asks me to enter the FTP SITE USERNAME AND PASSWORD
> and then the destination DIRECTORY PATH.
> I don't know the path. What do I need to mention here
> Also when I click on file and select a Blank Text file from there
> and check overwrite still after the task is complete data does not
> appears in that.
> ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
> Directory path BLANK.
>
> Does anyone knows how to figure this out. I am sure its a simple
> solution.
>|||VJ wrote:
> I have a DTS package which does the following
>
> 1. EXECUTE SQL TASK (query in it)
> on success
> 2. Microsoft OLE DB (database connection)
> transform data task
> 3. Text File Destination (creates a text file TEST in a folder)
> on success
> 4. FTP ( I WANT TO FTP THIS FILE TO A SITE)
> The problem is I click on the FILE TRANSFER PROTOCOL TASK
> and its asks me to enter the FTP SITE USERNAME AND PASSWORD
> and then the destination DIRECTORY PATH.
> I don't know the path. What do I need to mention here
> Also when I click on file and select a Blank Text file from there
> and check overwrite still after the task is complete data does not
> appears in that.
> ALTHOUGH IT SAYS TASK SUCCESSFUL even if I leave the destination
> Directory path BLANK.
>
> Does anyone knows how to figure this out. I am sure its a simple
> solution.
>
Hi
I think the problem is that the FTP task is only only able to download
files from an FTP site - not upload.
Instead you can create a bat file with the commands for the files you
want to upload and then execute this bat file from your DTS package.
That's the way I'm doing it and that works fine.
Regards
Steen Schlter Persson
DBA

FTI and multiple columns - another question, but opposite behaviour?

I have a table with 2 FTI columns, but my query results seems strange. If I
run this query:
AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING" AND "FIFE"' )
it returns no rows.
AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING"' )
this returns one row as both words are in the same FTI column.
AND CONTAINS( J.*, '"FIFE"' )
returns the same row, as FIFE exists in the other FTI column.
So, why doesn't the top query return the row in the results? Is this normal
behaviour? Is there anything I can do to make it work?
Thanks.
A contains query can't look across columns, a freetext query can. So if your
hits come from different columns a freetext query will return a hit, whereas
a contains won't.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe Bloggs" <Joe.Bloggs@.acme.com> wrote in message
news:%23C6lxLZcFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I have a table with 2 FTI columns, but my query results seems strange. If
I
> run this query:
> AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING" AND "FIFE"' )
> it returns no rows.
> AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING"' )
> this returns one row as both words are in the same FTI column.
> AND CONTAINS( J.*, '"FIFE"' )
> returns the same row, as FIFE exists in the other FTI column.
> So, why doesn't the top query return the row in the results? Is this
normal
> behaviour? Is there anything I can do to make it work?
> Thanks.
>

FT query plan

The following query:
SELECT PatientGUID
FROM PATIENT_SEARCH PS
WHERE PS.LicenseID = '465f20fc-8bd5-4802-a3f5-2a5f702be128'
AND CONTAINS (PS.SEARCHCOL , ' "patient*" ')
Shows 2500 rows qualified by LicenseID =
'465f20fc-8bd5-4802-a3f5-2a5f702be128', but 5000000 rows for "Remote
Scan". Takes about 30 secs to complete which is a lot slower than even
LIKE '%patient%''
I would expect it to only do FT on those 2500.
Thank you,
Igor
*** Sent via Developersdex http://www.codecomments.com ***
Statistics coming back from the remote scan of the full-text catalog are
frequently not accurate.
The real problem here is that you are doing trimming where the complete
results set that matches the wild card search on patient has to be returned
from full-text to SQL Server and then only rows which contains the licenseid
of '465f20fc-8bd5-4802-a3f5-2a5f702be128' are then returned.
"mEmENT0m0RI" <nospam@.devdex.com> wrote in message
news:uaslhAqhHHA.4064@.TK2MSFTNGP02.phx.gbl...
> The following query:
> SELECT PatientGUID
> FROM PATIENT_SEARCH PS
> WHERE PS.LicenseID = '465f20fc-8bd5-4802-a3f5-2a5f702be128'
> AND CONTAINS (PS.SEARCHCOL , ' "patient*" ')
> Shows 2500 rows qualified by LicenseID =
> '465f20fc-8bd5-4802-a3f5-2a5f702be128', but 5000000 rows for "Remote
> Scan". Takes about 30 secs to complete which is a lot slower than even
> LIKE '%patient%''
> I would expect it to only do FT on those 2500.
>
> Thank you,
> Igor
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hilary,
Tha is my problem exactly. How can I force it to do the lookup on
LicenseID and only then apply the FT to the remaining rows?
Thank you,
Igor
*** Sent via Developersdex http://www.codecomments.com ***
|||If licenseID is discrete enough you could partition or use a full-text index
on an indexed view.
Otherwise you might be able to store it in the SearchCol column as well and
then search on patient* and your licenseId.
The problem with this approach is the more search terms you have the worse
your search. The performance degradation going from one to two terms is not
that significant however.
"mEmENT0m0RI" <nospam@.devdex.com> wrote in message
news:ObdYRu2hHHA.3960@.TK2MSFTNGP02.phx.gbl...
> Hilary,
> Tha is my problem exactly. How can I force it to do the lookup on
> LicenseID and only then apply the FT to the remaining rows?
> Thank you,
> Igor
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||I don't understand.
LicenseID is on the same table right now as the FT column and LicenseID
is indexed. How would an indexed view help the performance in this
situation?
*** Sent via Developersdex http://www.codecomments.com ***
sql

Front Page 2002 and SQL Query

I am using a very basic query in Front Page 2002 against a SQL table in
SQL 2000

SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)

and f_docnumber is a search form field.

When I run I get

Description: The precision is invalid.
Number: -2147467259 (0x80004005)

The field is defined in the table as numberic 9(10,0)

Any ideas?(webforcam@.hotmail.com) writes:
> I am using a very basic query in Front Page 2002 against a SQL table in
> SQL 2000
> SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)
> and f_docnumber is a search form field.
> When I run I get
> Description: The precision is invalid.
> Number: -2147467259 (0x80004005)
> The field is defined in the table as numberic 9(10,0)

I have used FrontPage to author the articles on my web site, but I
have ot idea how you query databases from the tool. So maybe you ask
this question in a FrontPage newsgroup. That much I can say is that
the error does not come SQL Server, but from the OLE DB provider.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

From Query parameters slows query

I went from having no "From Query" parameters to having about twenty, should
this degrade the performance of the query... It sits and spins for quite
awhile longer now than before.. What exactly is it doing to get these
parameters populated with data...? It's taking about 15 times longer to run
the query with the parameters as opposed to running without...
Any ideas...?Not sure what you mean. Adding additional parameters to the where clause
should not increase query time, in fact it should generally decrease it. If
you are saying that you are retrieving valid value lists for 20 parameters,
then yes, overall report execution time will decrease. You are doing quite a
bit more work than before.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:uox%23mLmfEHA.3320@.TK2MSFTNGP11.phx.gbl...
>I went from having no "From Query" parameters to having about twenty,
>should
> this degrade the performance of the query... It sits and spins for quite
> awhile longer now than before.. What exactly is it doing to get these
> parameters populated with data...? It's taking about 15 times longer to
> run
> the query with the parameters as opposed to running without...
> Any ideas...?
>|||In the Report->Report Parameters property page there are two types of
parameters Non-Queried and From Query. Non-Queried appears to prompt the
user for its value while the From Query parameter appears to be populated
from the query as it's name implies by assigning a column from the dataset.
I've created several of these type of parameters because I need a way to
have a page header with data from the query repeat on every page since I
cannot put Fields in the page header and using a table with the
repeatonnewpage doesn't seem to work this was my only viable solution. If
you look at my posts for the last couple days you'll get an idea of what I'm
trying to do. So my question remains why would creating From Query
parameters slow the execution of my report...? Am I using them incorrectly
is there another direction I can take to to achieve my goal...
Thanks,
Dan
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OA1O%236pfEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Not sure what you mean. Adding additional parameters to the where clause
> should not increase query time, in fact it should generally decrease it.
If
> you are saying that you are retrieving valid value lists for 20
parameters,
> then yes, overall report execution time will decrease. You are doing quite
a
> bit more work than before.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:uox%23mLmfEHA.3320@.TK2MSFTNGP11.phx.gbl...
> >I went from having no "From Query" parameters to having about twenty,
> >should
> > this degrade the performance of the query... It sits and spins for quite
> > awhile longer now than before.. What exactly is it doing to get these
> > parameters populated with data...? It's taking about 15 times longer to
> > run
> > the query with the parameters as opposed to running without...
> >
> > Any ideas...?
> >
> >
>

From DataSet To SqlDataReader in a CLR Stored Procedure

Hi all,
I'm writing a CLR stored procedure that just execute a query using 2 parameters.

SqlContext.Pipe.Send can send a SqlDataReader, but if I've got a DataSet?
How can I obtain a SqlDataReader from a DataSet?

Dim command As New SqlCommand(.......)
.....
Dim ds As New DataSet()
Dim adapter As New SqlDataAdapter(command)

adapter.Fill(ds, "MyTable")

... 'manipulating the ds.Tables("MyTable")

At this moment I have to send the table...but
ds.Tables("MyTable").CreateDataReader()
just give me a DataTableReader, and i can't send it with SqlContext.Pipe.Send(...

Help me please!
The DataSet.CreateDataReader method can be used to generate a data reader that reads data from a dataset.|||DataSet.CreateDataReader() is the same of dataSet.Table(x).CreateDataReader()...it returns a DataTableReader!
There is also an overload for dataSet.CreateDataReader that get an array of dataTable as parameter!|||Sorry about that. For some reason, I was quite convinced that SqlPipe.Send accepted an IDataReader rather than a SqlDataReader. Looks like you'll need to use SqlPipe.SendResultsRow. See http://msdn2.microsoft.com/en-US/library/microsoft.sqlserver.server.sqlpipe.sendresultsrow(VS.80).aspx for details and sample code.|||Thank you, but i don't think that can be the "best practice", it's not in .net style!!!
I'll search better!|||? It used to accept IDataReader during the betas, all the way up to one of the final CTPs -- then that functionality was removed for some reason :( -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Nicole Calinoiu@.discussions.microsoft.com> wrote in message news:7725b945-d2c1-44d0-a698-fa9a344a48d4@.discussions.microsoft.com...Sorry about that. For some reason, I was quite convinced that SqlPipe.Send accepted an IDataReader rather than a SqlDataReader. Looks like you'll need to use SqlPipe.SendResultsRow. See http://msdn2.microsoft.com/en-US/library/microsoft.sqlserver.server.sqlpipe.sendresultsrow(VS.80).aspx for details and sample code.|||

Well, at least I wasn't imagining things. ;)

Based on the current implementation, I'd have to guess that the change may have been made for 2 main reasons: improved performance and enhanced metadata extraction. However, I can't see any reason why an overload that accepts IDataReader couldn't have been left in since the "improved" SqlDataReader implementation would still be used where possible. A wee bit odd...

Frequency Query

on a SQL 7.0 database is it possible to do a query to analyze frequency of the entries in the database?in other words I want to know which entries have the most instances.

Hi,

You can use the count method in combination with a group by clause.
For example: SELECT ProductName, COUNT(*) FROM Orders GROUP BY ProductName

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

thanks for your reply, the column name in the table is Tagname, would it be possible to count all entries for a specific time period? alarmlog is the table name, eventstamp is the time stamp of the entry.

like this:

select *, count(*)

from alarmlog

where eventstamp > '02/01/2007 00:00:00' and

eventstamp < '02/02/2007 00:00:00

thanks

Gary

|||

Yes but you should do it like this:

select TagName, count(*)
from alarmlog
where eventstamp > '02/01/2007 00:00:00' and eventstamp < '02/02/2007 00:00:00'
group by TagName

FYI: This is a good site with basic SQL syntax: http://www.w3schools.com/sql/default.asp

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

thanks again.

Gary

|||

Sorry one more quick question, for the results can I add another column to appear? there is a comment column for each entry that has the description in it that would be useful in the resluts.

Gary

|||

You can add as much columns as you want but the column must be included in the group by clause OR included in an aggregate function like (SUM, AVG, MIN, MAX, COUNT, ...)

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

FREETEXTTABLE question

i never seem to get the basics, but once i do it all falls into place.
this query is simple, but doesn't work and i'm sure someone will tell me why.
declare @.term as varchar(50)
set @.term = 'car repair'
select u.usrCompany
from usr u
inner join FREETEXTTABLE(usrBusDesc, 'car repair') ft on u.usrid = ft.usrid
the error is: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'car repair'.
just can't seem to get it. Must be a Yew Years Thing
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
and... This always has a habit of happening
I figure it out within a min. of my posting!
I needed to include the table name!!!!
select u.usrCompany
from usr u
inner join FREETEXTTABLE(usr, usrBusDesc, 'car repair') ft on u.usrid =
ft.[KEY]
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"WebBuilder451" wrote:

> i never seem to get the basics, but once i do it all falls into place.
> this query is simple, but doesn't work and i'm sure someone will tell me why.
> declare @.term as varchar(50)
> set @.term = 'car repair'
> select u.usrCompany
> from usr u
> inner join FREETEXTTABLE(usrBusDesc, 'car repair') ft on u.usrid = ft.usrid
> the error is: Msg 170, Level 15, State 1, Line 10
> Line 10: Incorrect syntax near 'car repair'.
> just can't seem to get it. Must be a Yew Years Thing
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes

freetexttable query continued

Still working on this query:
select *
from products
inner join freetexttable(products,*,@.searchstr) ft1
on ft1.[key] = products.p_id
left join sku
on sku.p_id= products.p_id
inner join freetexttable(sku,*,@.searchstr) ft2
on ft2.[key] = sku.sku
I replaced the inner join with a left join on the second table.
I still get no results if the searchstr exists in the first table but not in
the second table. Full join doesn't work either.
Did I modify the wrong join? If I left-join on the FTcat I get all the rows
in both tables.
TIA!
can you post the schemas of all related tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
> Still working on this query:
> select *
> from products
> inner join freetexttable(products,*,@.searchstr) ft1
> on ft1.[key] = products.p_id
> left join sku
> on sku.p_id= products.p_id
> inner join freetexttable(sku,*,@.searchstr) ft2
> on ft2.[key] = sku.sku
> I replaced the inner join with a left join on the second table.
> I still get no results if the searchstr exists in the first table but not
> in the second table. Full join doesn't work either.
> Did I modify the wrong join? If I left-join on the FTcat I get all the
> rows in both tables.
> TIA!
>
>
|||> can you post the schemas of all related tables?
Products:
p_id varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
p_name varchar no 255 yes no yes SQL_Latin1_General_CP1_CI_AS
p_desc varchar no 1024 yes no yes SQL_Latin1_General_CP1_CI_AS
p_man int no 4 10 0 no (n/a) (n/a) NULL
row_id int no 4 10 0 no (n/a) (n/a) NULL
date_created datetime no 8 no (n/a) (n/a) NULL
date_modified datetime no 8 no (n/a) (n/a) NULL
(p_id is PK)
Manufacturers:
m_name varchar no 100 no no no SQL_Latin1_General_CP1_CI_AS
m_id int no 4 10 0 no (n/a) (n/a) NULL
(m_id is PK)
SKUs:
p_id varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
sku varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
sku_weight decimal no 9 18 1 yes (n/a) (n/a) NULL
attribute1 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
attribute2 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
status int no 4 10 0 yes (n/a) (n/a) NULL
wholesale_cost decimal no 5 7 2 no (n/a) (n/a) NULL
special_order bit no 1 no (n/a) (n/a) NULL
date_available datetime no 8 yes (n/a) (n/a) NULL
(sku is PK)
where
m_id in manufacturers = p_man in products
and p_id in sku = p_id in products

> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "geek-y-guy" <noone@.nowhere.org> wrote in message
> news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
>

FREETEXTTABLE

The following FREETEXTTABLE query works, but I'm getting all rows returned.
The highest ranked are
at the top like expected, but it's also returning all rows in the Stock
table that have no match whatsoever. In my FREETEXT query (bottom), all the
results
had a match. I don't get the concept of the FREETEXTTABLE. In my mind, I'm
expecting a temp table to be created with the results. However, the code
below is actually joining the created table. I don't get it.
How do I get only matching results in the FREETEXTTABLE?
thanks!
-- This FREETEXTABLE query returns all row in the Stock table. Regardless of
any matches.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = 'midler'
SELECT
Stock.OrderNo,
Stock.Description,
Stock.Category,
Stock.s_Type,
Stock.Manuf,
Stock.Label,
Titles.Title,
Titles.Artist,
Hardware.m_Specs,
Stock.ManCode
FROM
Stock
LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
ORDER BY
FS_TABLE.Rank DESC
--This FREETEXT query works as expected
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler" '
SELECT Stock.OrderNo, Stock.Description, Stock.Category,
Stock.s_Type, Stock.Manuf, Stock.Label, Titles.Title,
Titles.Artist, Hardware.m_Specs, Stock.ManCode
FROM Stock LEFT OUTER JOIN
Titles ON Stock.OrderNo = Titles.OrderNo LEFT OUTER JOIN
Hardware ON Stock.OrderNo = Hardware.OrderNo
WHERE FREETEXT(Stock.OrderNo,@.SearchCriteria) OR
FREETEXT(Stock.Description,@.SearchCriteria) OR
FREETEXT(Stock.Category,@.SearchCriteria) OR
FREETEXT(Stock.s_Type,@.SearchCriteria) OR
FREETEXT(Stock.Manuf,@.SearchCriteria) OR
FREETEXT(Stock.Label,@.SearchCriteria) OR
FREETEXT(Stock.ManCode,@.SearchCriteria) OR
FREETEXT(Hardware.m_Specs,@.SearchCriteria) OR
FREETEXT(Titles.Title,@.SearchCriteria) OR
FREETEXT(Titles.Artist,@.SearchCriteria)
FREETEXTTABLE returns a table of all the keys and their ranking for your
search. Based on what you are saying, I am guessing that it also returns
items that don't match and they probably have a ranking of 0.
So you can filter out those by adding a WHERE clause like this:
WHERE
FS_TABLE.RANK > 0
For me, I use where it's greater than 20 since I only care about the 80%
that are relevant (in my case).
Hope that helps.
Brien King
"shank" <shank@.tampabay.rr.com> wrote in message
news:%23y$G1uFYEHA.1264@.TK2MSFTNGP11.phx.gbl...
> The following FREETEXTTABLE query works, but I'm getting all rows
returned.
> The highest ranked are
> at the top like expected, but it's also returning all rows in the Stock
> table that have no match whatsoever. In my FREETEXT query (bottom), all
the
> results
> had a match. I don't get the concept of the FREETEXTTABLE. In my mind, I'm
> expecting a temp table to be created with the results. However, the code
> below is actually joining the created table. I don't get it.
> How do I get only matching results in the FREETEXTTABLE?
> thanks!
> -- This FREETEXTABLE query returns all row in the Stock table. Regardless
of
> any matches.
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = 'midler'
> SELECT
> Stock.OrderNo,
> Stock.Description,
> Stock.Category,
> Stock.s_Type,
> Stock.Manuf,
> Stock.Label,
> Titles.Title,
> Titles.Artist,
> Hardware.m_Specs,
> Stock.ManCode
> FROM
> Stock
> LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
> LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
> LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
> ORDER BY
> FS_TABLE.Rank DESC

freetextable and multiple tables

In the below FREETEXTTABLE query, I have the SELECT fields indexed in the FT
catalog. I should be getting results and I'm not. The problem, as I see it,
is that I need to be searching all 3 tables when actually I'm only searching
1 table in this expression: FREETEXTTABLE(ItemStock, *, @.SearchCriteria). At
the very bottom, I removed 2 tables and the query works. How do I search all
3 tables and get results?
thanks
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
========================================
--This one works...
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
--ItemTitles.Title,
--ItemTitles.Artist,
--ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
you can't query multiple tables in a single full text query unless there is
some sort of a parent child relationship between ItemStock.OrderNo and
ItemHardware, and ItemTitles.
Here is an example of such a parent child relationship:
CREATE TABLE [dbo].[Parent] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Size] [int] NULL ,
[CreateDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TextTable] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I am storing the text data in the child table and both the parent and the
child share a common pk. There is no DRI constraint.
This makes the ContainsTable query a little more complex. Here is my stored
procedure that I use to query this table.
CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
AS
SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
'+char(39)+char(34)
SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
FTTABLE.[key]'
SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
desc,[KEY] '
exec (@.@.strSearch)
usage is:
EXEC search 'microsoft sql server',''
Notice that what we are doing is returning the results set from MSSearch as
a derived table and joining this derived table (named FTTABLE) against the
parent table. The child table which contains our text information is not
queried as all. Also notice that I am not doing any error checking or
returning any return codes from this stored procedure. My experience is that
building the search string can be made error free, and the errors generated
by MSSearch will be handled by the calling application and in some cases
will not be trappable with the @.@.error system variable.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"shank" <shank@.tampabay.rr.com> wrote in message
news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> In the below FREETEXTTABLE query, I have the SELECT fields indexed in the
FT
> catalog. I should be getting results and I'm not. The problem, as I see
it,
> is that I need to be searching all 3 tables when actually I'm only
searching
> 1 table in this expression: FREETEXTTABLE(ItemStock, *, @.SearchCriteria).
At
> the very bottom, I removed 2 tables and the query works. How do I search
all
> 3 tables and get results?
> thanks
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> ItemTitles.Title,
> ItemTitles.Artist,
> ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN ItemTitles ON ItemStock.OrderNo =
ItemTitles.OrderNo
> INNER JOIN ItemHardware ON ItemStock.OrderNo =
> ItemHardware.OrderNo
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
> ========================================
> --This one works...
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> --ItemTitles.Title,
> --ItemTitles.Artist,
> --ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
>
|||Hi Hilary,
While I respectively disagree that a "parent child relationship" must exist
to use multiple FT-enabled tables with FREETEXTTABLE or CONTAINSTABLE
query, I suspect that what you really meant was that a Primary key / Foreign
key relationship must exist between the tables to allow a join. It's a small
point, but not all PK / FK relationships have to be "parent child"
relationships. For example take the two tables Employees and
EmployeeTerritories in the Northwind database, while they are not in a
classic parent/child relationship, they do in fact share a PK/FK
relationship on their respective EmployeeID columns, specifically:
use Northwind
go
exec sp_help EmployeeTerritories -- PK is PK_EmployeeTerritories
(EmployeeID, TerritoryID)
exec sp_help Employees -- PK is PK_Employees (EmployeeID)
select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID
-- --
1 06897
1 19713
*/
select EmployeeID, LastName, FirstName, Title, Address from Employees where
EmployeeID = 1
/* returns:
EmployeeID LastName FirstName Title
Address
-- -- -- -- -
1 Davolio Nancy Sales Representative
507 - 20th Ave. E.
*/
In order for the EmployeeTerritories to be FT Indexed, the table must be
altered to add a single column, non-nullable key and non-clustered, unique
index, for example:
ALTER TABLE EmployeeTerritories ADD ET_Ident int identity (1, 1) NOT NULL
go -- returns: 49 rows affected.
select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID ET_Ident
-- -- --
1 06897 1
1 19713 2
*/
CREATE UNIQUE INDEX ET_Ident_IDX on EmployeeTerritories(ET_Ident)
go
Then you can create a FT Catalog and FT-enable both tables - Employees and
EmployeeTerritories - and then run a Full Population on both tables and you
can execute a multiple FT-enabled FTS query, such as the one below:
SELECT distinct e.LastName, e.FirstName from Employees AS e,
EmployeeTerritories t,
containstable(Employees, FirstName, 'Nancy') as A,
containstable(EmployeeTerritories, TerritoryID, '06897') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
/* -- returns:
LastName FirstName
-- --
Davolio Nancy
(1 row(s) affected)
*/
Shank, you should now be able to take the above example code and alter it to
fit your tables and selection criteria.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uzlWtk6ZEHA.3476@.tk2msftngp13.phx.gbl...
> you can't query multiple tables in a single full text query unless there
is
> some sort of a parent child relationship between ItemStock.OrderNo and
> ItemHardware, and ItemTitles.
> Here is an example of such a parent child relationship:
>
> CREATE TABLE [dbo].[Parent] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Size] [int] NULL ,
> [CreateDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[TextTable] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> I am storing the text data in the child table and both the parent and the
> child share a common pk. There is no DRI constraint.
>
> This makes the ContainsTable query a little more complex. Here is my
stored
> procedure that I use to query this table.
>
> CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
> AS
> SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
> createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
> '+char(39)+char(34)
> SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
> SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
> FTTABLE.[key]'
> SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
> desc,[KEY] '
> exec (@.@.strSearch)
>
> usage is:
>
> EXEC search 'microsoft sql server',''
>
> Notice that what we are doing is returning the results set from MSSearch
as
> a derived table and joining this derived table (named FTTABLE) against the
> parent table. The child table which contains our text information is not
> queried as all. Also notice that I am not doing any error checking or
> returning any return codes from this stored procedure. My experience is
that
> building the search string can be made error free, and the errors
generated[vbcol=seagreen]
> by MSSearch will be handled by the calling application and in some cases
> will not be trappable with the @.@.error system variable.
>
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
> FT
> it,
> searching
@.SearchCriteria).
> At
> all
> ItemTitles.OrderNo
>
|||Not sure if this going to help or not. Below are my 3 tables. I have a PK/FK
relationship between ItemStock.OrderNo = ItemTitles.OrderNo and also
ItemStock.OrderNo = ItemHardware.OrderNo. After setting up the indexes, I
rebuilt the catalog and still not getting results. Any further help would be
appreciated. This is my query...
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
CREATE TABLE [dbo].[ItemStock] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Label] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SoftHard] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[d_Update] [datetime] NULL ,
[Exclusive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Affiliate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Inv] [decimal](5, 0) NULL ,
[d_Date] [datetime] NULL ,
[Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SingleArtist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TypeDescrip] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cased] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Packed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Manuf] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManSort] [int] NULL ,
[Weight] [decimal](5, 2) NULL ,
[Icons75] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons100] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons200] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons300] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemTitles] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Artist] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SortKey] [decimal](5, 0) NOT NULL ,
[Disc_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MP3Files] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemHardware] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[s_page] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Thumb] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_pic] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFScale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[m_Specs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFSpecs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Media] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cass] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VCD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DVD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
|||Shank,
I've been able to use the below table structures and inserted some sample
data and test your query along with the below modified queries. I've also
attached a .SQL script file - Containstable_multiple_tables.sql that
contains the below queries as well as sample data.
I believe that the issue here is two fold - 1) using a common variable
(@.SearchCriteria) where a single word must exist in all tables columns, and
2) your SQL FTS FREETEXTTABLE query only references one table (ItemStock),
while the below Modified Original query using a common word (row) return the
search word from all three tables and is more flexible as you can use both
an OR as well as an AND condition between the freetexttable or containstable
clauses. Please, review carefully the attached sql file, the data as well
as the queries and experiment with them on either Win2003 or WinXP machines
as you may get different results, due to the os-supplied wordbreaker
depending upon your actual data.
-- Original problem query, works with the above data, but only with a common
word "row" is queried from all three tables.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- primary search column is ItemStock.Label
(other columns, left NULL)
SELECT ItemStock.OrderNo,ItemStock.Label
FROM ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo = ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS FS_TABLE ON
FS_TABLE.[KEY] = ItemStock.OrderNo
ORDER BY FS_TABLE.Rank DESC
-- 1st test: 4 rows becasue only ItemStock is referenced in FREETEXTTABLE
clasue
-- Modifed Original query
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- Test with both containstable & freetext
AND OR Betewen for expected results
SELECT distinct e.OrderNo, e.Label -- disctinct requried to get 5 rows,
still not exactly same as above...
from ItemStock AS e, ItemTitles t, ItemHardware h,
freetexttable(ItemStock, Label, @.SearchCriteria) as A,
freetexttable(ItemTitles, Title, @.SearchCriteria) as B,
freetexttable(ItemHardware, s_page, @.SearchCriteria) as C
where
A.[KEY] = e.OrderNo and
B.[KEY] = t.OrderNo and
C.[KEY] = h.OrderNo
-- 1st test: NOT the same as above as this query gets 5 rows, additional row
is "This is Label 0005 of row five Interface"
Regards,
John
"shank" <shank@.tampabay.rr.com> wrote in message
news:NKEIc.15669$KP6.716193@.twister.tampabay.rr.co m...
> Not sure if this going to help or not. Below are my 3 tables. I have a
PK/FK
> relationship between ItemStock.OrderNo = ItemTitles.OrderNo and also
> ItemStock.OrderNo = ItemHardware.OrderNo. After setting up the indexes, I
> rebuilt the catalog and still not getting results. Any further help would
be
> appreciated. This is my query...
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> ItemTitles.Title,
> ItemTitles.Artist,
> ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN ItemTitles ON ItemStock.OrderNo =
ItemTitles.OrderNo
> INNER JOIN ItemHardware ON ItemStock.OrderNo =
> ItemHardware.OrderNo
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.1 (Build 2600: Service Pack 1)
> CREATE TABLE [dbo].[ItemStock] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Label] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SoftHard] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ManCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [d_Update] [datetime] NULL ,
> [Exclusive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Affiliate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NewRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Inv] [decimal](5, 0) NULL ,
> [d_Date] [datetime] NULL ,
> [Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SingleArtist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TypeDescrip] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Cased] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Packed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Manuf] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ManSort] [int] NULL ,
> [Weight] [decimal](5, 2) NULL ,
> [Icons75] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons100] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons200] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons300] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemTitles] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Title] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Artist] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SortKey] [decimal](5, 0) NOT NULL ,
> [Disc_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MP3Files] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemHardware] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [s_page] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_Thumb] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_pic] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PDFScale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [m_Specs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PDFSpecs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Media] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Cass] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CDG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [VCD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DVD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
begin 666 Containstable_mutiple_tables.sql
M#0HM+0EF:6QE;F%M93H@.0V]N=&%I;G-T86)L95]M=71I<&QE7W1A8FQE<RYS
M<6P-"BTM"7!U<G!O<V4Z('1O(&1O8W5M96YT(&AO=R!T;R!U<V4@.8V ]N=&%I
M;G-T86)L92!A;F0@.9G)E971E>'1T86)L92!I;B!M=71I<&QE($944 R!Q=65R
M:65S#0HM+0EM;V1I9FEE9#H@.,3 Z,#4@.4$T@.-R\Q,B\R,# T#0H-"G5S92!S
M<6QF=',-"F=O#0IS96QE8W0@.0$!V97)S:6]N("TM($UI8W)O<V]F="!344P@.
M4V5R=F5R(" R,# P("T@.."XP,"XW-C @.;VX@.16YT97)P<FES92!%9&ET:6]N
M(&]N(%=I;F1O=W,@.3E0@.-2XR("A"=6EL9" S-SDP.B I#0HO*@.T*+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+ 2TM#0I-:6-R
M;W-O9G0@.4U%,(%-E<G9E<B @.,C P," M(#@.N,# N-S8P("A);G1E;"!8.#8I
M("T@.1&5V96QO<&5R($5D:71I;VX@.;VX@.5VEN9&]W<R!.5" U+C$@.*$)U:6QD
M(#(V,# Z(%-E<G9I8V4@.4&%C:R Q*0T*4$LO1DL@.<F5L871I;VYS:&EP(&)E
M='=E96X@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M5&ET;&5S+D]R9&5R3F\@.
M86YD(&%L<V\@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M2&%R9'=A<F4N3W)D
M97).;RX@.#0HJ+PT*#0HM+2!$969I;F4@.=&%B;&5S+BXN#0I#4 D5!5$4@.5$%"
M3$4@.6V1B;UTN6TET96U3=&]C:UT@.* T*(%M/<F1E<DYO72!;=F%R8VAA<ET@.
M*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3D]4
M($Y53$P@.+" M+2!02R!)=&5M4W1O8VL@.+2!U;FEQ=64@./PT*(%M,86)E;%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y/5"!.54Q,("P-"B!;4V]F=$AA<F1=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;36%N0V]D95T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ
M7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$97-C<FEP=&EO;ET@.6W9A
M<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MD7U5P9&%T95T@.6V1A=&5T:6UE72!.54Q,("P-"B!;
M17AC;'5S:79E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6T%F9FEL:6%T95T@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y53$P@.+ T*(%M.97=296QE87-E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%
M(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U-T871U
M<UT@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M);G9=(%MD96-I;6%L72@.U+" P*2!.54Q,
M("P-"B!;9%]$871E72!;9&%T971I;65=($Y53$P@.+ T*(%M#871E9V]R>5T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M3:6YG;&5!<G1I<W1=(%MV87)C:&%R72 H,RD@.
M0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*
M(%MS7U1Y<&5=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'
M96YE<F%L7T-0,5]#25]!4R!.54Q,("P-"B!;5'EP941E<V-R:7!=(%MV87)C
M:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3E5,3" L#0H@.6T-A<V5D72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!A8VME9%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M-86YU9ET@.6W9A<F-H87)=("@.U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-86Y3
M;W)T72!;:6YT72!.54Q,("P-"B!;5V5I9VAT72!;9&5C:6UA;%TH-2P@.,BD@.
M3E5,3" L#0H@.6TEC;VYS-S5=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TEC;VYS,3 P
M72!;=F%R8VAA<ET@.*#$P,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M)8V]N<S(P,%T@.6W9A<F-H87)=("@.Q,# I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;26-O;G,S,#!=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TE$72!;;G5M97)I8UTH
M,3 L(# I($E$14Y42519("@.Q+" Q*2!.3U0@.3E5,3" M+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM/B!O<B!02R _(&IU<W0@.9F]R($94($EN9&5X(#\-
M"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"3$4@.271E; 5-T;V-K(%=)
M5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.0TQ54U1%4D5$("A/<F1E<DYO
M*0T*9V\-"BTM($EN<V5R="!T97-T('-A;7!L92!D871A#0II;G-E<G0@.:6YT
M;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES($QA8F5L(# P
M,#$@.;V8@.<F]W(&]N92!":6QL>2!*;V5L)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,B<L
M("=4:&ES(&ES($QA8F5L(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E; 5-T;V-K('9A
M;'5E<R H)S P,#,G+" G5&AI<R!I<R!,86)E;" P,# S(&]F(')O=R!T:')E
M92!344P@.4V5R=F5R)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PI#0II;G-E
M<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P-"<L("=4:&ES(&ES($QA
M8F5L(# P,#0@.;V8@.<F]W(&9O=7(@.3U)!0TQ%)RQ.54Q,+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PI#0HM+2!R;W<@.3D]4(&EN($ET96U4:71L97,L(&)U="!I<R!I
M;B!)=&5M2&%R9'=A<F4-"FEN<V5R="!I;G1O($ET96U3=&]C:R!V86QU97,@.
M*"<P,# U)RP@.)U1H:7,@.:7,@.3&%B96P@.,# P-2!O9B!R;W<@.9FEV92!);G1E
M<F9A8V4G+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"D-"F=O#0H-"D-214%4
M12!404),12!;9&)O72Y;271E;51I=&QE<UT@.* T*(%M/<F1E<DYO72!;=F%R
M8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*(%M4:71L95T@.6W9A<F-H87)=("@.W,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y/5"!.54Q,("P-"B!;
M07)T:7-T72!;=F%R8VAA<ET@.*#8P*2!#3TQ,051%(%-13%],871I;C%?1V5N
M97)A;%]#4#%?0TE?05,@.3D]4($Y53$P@.+ T*(%M,;V-A=&EO;ET@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y/5"!.54Q,("P-"B!;4V]R=$ME>5T@.6V1E8VEM86Q=*#4L(# I($Y/5"!.
M54Q,("P-"B!;1&ES8U])1%T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?
M3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-4#-&:6QE<UT@.
M6W9A<F-H87)=("@.Q,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0
M,5]#25]!4R!.54Q,("P-"B!;241=(%MN=6UE<FEC72@.Q,"P@.,"D@.241%3E1)
M5%D@.*#$L(#$I($Y/5"!.54Q,#0HI($].(%M04DE-05)970T*1T\-"D%,5$52
M(%1!0DQ%($ET96U4:71L97,@.5TE42"!.3T-(14-+($%$1"!04DE-05)9($M%
M62!#3%535$52140@.*$]R9&5R3F\I#0IG;PT*+2T@.26YS97)T('1E<W0@.<V%M
M<&QE(&1A=&$-"FEN<V5R="!I;G1O($ET96U4:71L97,@.=F%L=65S("@.G,# P
M,2<L)U1H:7,@.:7,@.5&ET;&4@.,# P,2!O9B!R;W<@.;VYE(%-T<F%N9V5R)RPG
M0FEL;'D@.2F]E;"<L)R!3;VUE<&QA8V4@.:&5R92<L,2Q.54Q,+$Y53$PI#0I I
M;G-E<G0@.:6YT;R!)=&5M5&ET;&5S('9A;'5E<R H)S P,#(G+"=4:&ES(&ES
M(%1I=&QE(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L)U-T:6YG)RPG(%-O;65P
M;&%C92!T:&5R92<L.2Q.54Q,+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M5&ET
M;&5S('9A;'5E<R H)S P,#,G+"=4:&ES(&ES(%1I=&QE(# P,#,@.;V8@.<F]W
M('1H<F5E(%-13"!397)V97(G+"=-:6-R;W-O9G0G+"<@.4V]M97!L86-E(&5L
M<V4G+#@.L3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;51I= &QE<R!V86QU
M97,@.*"<P,# T)RPG5&AI<R!I<R!4:71L92 P,# T(&]F(')O=R!F;W5R($]2
M04-,12<L)T]R86-L92<L)R!3;VUE<&QA8V4@.96QS92<L-"Q.54Q,+$Y53$PI
M#0IG;PT*#0HM+2!)=&5M4W1O8VLN3W)D97).;R ]($ET96U(87)D=V%R92Y/
M<F1E<DYO+B -"D-214%412!404),12!;9&)O72Y;271E;4AA<F1W87)E72 H
M#0H@.6T]R9&5R3F]=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN
M,5]'96YE<F%L7T-0,5]#25]!4R!.3U0@.3E5,3" L#0H@.6W-?<&%G95T@.6W9A
M<F-H87)=("@.V,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MS7U1H=6UB72!;=F%R8VAA<ET@.*#$U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%MS7W!I
M8UT@.6W9A<F-H87)=("@.Q-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L
M7T-0,5]#25]!4R!.54Q,("P-"B!;4$1&4V-A;&5=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;;5]3<&5C<UT@.6W9A<F-H87)=("@.X,# P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!$1E-P96-S72!;=F%R
M8VAA<ET@.*#@.P,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#
M25]!4R!.54Q,("P-"B!;3F]T97-=(%MV87)C:&%R72 H.# P,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-961I
M85T@.6W9A<F-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#
M4#%?0TE?05,@.3E5,3" L#0H@.6T-A<W-=(%MV87)C:&%R72 H,RD@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M#1$==
M(%MV87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M60T1=(%MV87)C:&%R72 H,RD@.0T],3$%412!3
M44Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$5D1=(%MV
M87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%M)1%T@.6VYU;65R:6-=*#$P+" P*2!)1$5.5$E462 H
M,2P@.,2D@.3D]4($Y53$P-"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"
M3$4@.271E;4AA<F1W87)E(%=)5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.
M0TQ54U1%4D5$("A/<F1E<DYO*0T*9V\-"B\J("TM($=O="!T:&4@.9F]L;&]W
M:6YG('=A<FYI;F<@.=VAE;B!C<F5A=&EN9R!T86)L93H@.271E; 4AA<F1W87)E
M#0I787)N:6YG.B!4:&4@.=&%B;&4@.)TET96U(87)D=V%R92<@.: &%S(&)E96X@.
M8W)E871E9"!B=70@.:71S(&UA>&EM=6T@.<F]W('-I>F4@.*#(T-3,R*2!E>&-E
M961S('1H92!M87AI;75M(&YU;6)E<B!O9B!B>71E<R!P97(@.< F]W("@.X,#8P
M*2X@.#0I)3E-%4E0@.;W(@.55!$051%(&]F(&$@.<F]W(&EN('1H:7,@.=&%B;&4@.
M=VEL;"!F86EL(&EF('1H92!R97-U;'1I;F<@.<F]W(&QE;F=T:"!E>&-E961S
M(#@.P-C @.8GET97,N#0HJ+PT*+2T@.26YS97)T(%-A;7!L92!D871A#0II;G-E
M<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES
M($QA8F5L(# P,#$@.;V8@.<F]W(&]N92!3=')A;F=E<B<L3E5,3"Q.54Q,+$Y5
M3$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3 "Q.54Q,*0T*
M:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A;'5E<R H)S P,#(G+" G5&AI
M<R!I<R!,86)E;" P,# R(&]F(')O=R!T=V\@.4W1I;F<G+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"D-
M"FEN<V5R="!I;G1O($ET96U(87)D=V%R92!V86QU97,@.*"<P, # S)RP@.)U1H
M:7,@.:7,@.3&%B96P@.,# P,R!O9B!R;W<@.=&AR964@.4U%,(%-E<G9E<B<L3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A; '5E<R H)S P
M,#0G+" G5&AI<R!I<R!,86)E;" P,# T(&]F(')O=R!F;W5R($]204-,12<L
M3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.5 4Q,+$Y53$PL
M3E5,3"Q.54Q,*0T*+2T@.<F]W($Y/5"!I;B!)=&5M5&ET;&5S+"!B=70@.:7,@.
M:6X@.271E;5-T;V-K#0II;G-E<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S
M("@.G,# P-2<L("=4:&ES(&ES($QA8F5L(# P,#4@.;V8@.<F]W(&9I=F4@.26YT
M97)F86-E)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y5
M3$PL3E5,3"Q.54Q,+$Y53$PI#0IG;PT*#0HM+2!C;VYF:7)M( '-A;7!L92!D
M871A(&%N9"!&5"UE;F%B;&4@.=&%B;&5S('9I82!%;G1E<G!R: 7-E($UA;F%G
M97(N+BX-"G-E;&5C=" J(&9R;VT@.271E;5-T;V-K#0HM+2!T<G5N8V%T92!T
M86)L92!)=&5M4W1O8VL-"G-E;&5C=" J(&9R;VT@.271E;51I=&QE<PT*+2T@.
M=')U;F-A=&4@.=&%B;&4@.271E;51I=&QE<PT*<V5L96-T("H@.9G)O;2!)=&5M
M2&%R9'=A<F4-"BTM('1R=6YC871E('1A8FQE($ET96U(87)D=V%R90T*9V \-
M"@.T*+2T@.#0HM+2!/<FEG:6YA;"!P<F]B;&5M('%U97)Y+"!W;W)K<R!W:71H
M('1H92!A8F]V92!D871A+"!B=70@.;VYL>2!W:71H(&$@.8V]M;6]N('=O<F0@.
M(G)O=R(@.:7,@.<75E<FEE9"!F<F]M(&%L;"!T:')E92!T86)L97,N#0I$14-,
M05)%($!396%R8VA#<FET97)I82!V87)C:&%R*#$P,"D-"E-%5"! 4V5A<F-H
M0W)I=&5R:6$@./2 G(")R;W<J(B G("TM('!R:6UA<GD@.<V5A<F-H(&-O;'5M
M;B!I<R!)=&5M4W1O8VLN3&%B96P@.*&]T:&5R(&-O;'5M;G,L(&QE9G0@.3E5,
M3"D-"E-%3$5#5"!)=&5M4W1O8VLN3W)D97).;RQ)=&5M4W1O8VLN3&%B9 6P@.
M#0I&4D]-($ET96U3=&]C:PT*("!)3DY%4B!*3TE.($ET96U4:71L97,@.("!/
M3B!)=&5M4W1O8VLN3W)D97).;R ]($ET96U4:71L97,N3W)D97).;PT*("!)
M3DY%4B!*3TE.($ET96U(87)D=V%R92!/3B!)=&5M4W1O8VLN3W)D97).;R ]
M($ET96U(87)D=V%R92Y/<F1E<DYO#0H@.($E.3D52($I/24X@.1E)%151%6%14
M04),12A)=&5M4W1O8VLL("HL($!396%R8VA#<FET97)I82D@.0 5,@.1E-?5$%"
M3$4@.3TX@.1E-?5$%"3$4N6TM%65T@./2!)=&5M4W1O8VLN3W)D97).;PT*(" @.
M("!/4D1%4B!"62!&4U]404),12Y286YK($1%4T,-"BTM(#%S="!T97-T.B T
M(')O=W,@.8F5C87-U92!O;FQY($ET96U3=&]C:R!I<R!R969E<F5N8V5D(&EN
M($92145415A45$%"3$4@.8VQA<W5E#0H-"@.T*+2T@.36]D:69E9"!/<FEG:6YA
M;"!Q=65R>0T*1$5#3$%212! 4V5A<F-H0W)I=&5R:6$@.=F%R8VAA<B@.Q,# I
M#0I3150@.0%-E87)C:$-R:71E<FEA(#T@.)R B<F]W*B(@.)R @.+2T@.5&5S="!W
M:71H(&)O=&@.@.8V]N=&%I;G-T86)L92 F(&9R965T97AT($%.1"!/4B!"971E
M=V5N(&9O<B!E>'!E8W1E9"!R97-U;'1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/
M<F1E<DYO+"!E+DQA8F5L("TM(&1I<V-T:6YC="!R97%U<FEE9"!T;R!G970@.
M-2!R;W=S+"!S=&EL;"!N;W0@.97AA8W1L>2!S86UE(&%S(&%B;W9 E+BXN#0IF
M<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S('0L($ET96U(87)D=V%R
M92!H+ T*(" @.("!F<F5E=&5X='1A8FQE*$ET96U3=&]C:RP@.3&%B96PL($!3
M96%R8VA#<FET97)I82D@.87,@.02P-"B @.(" @.9G)E971E>'1T86)L92A)=&5M
M5&ET;&5S+"!4:71L92P@.0%-E87)C:$-R:71E<FEA*2!A<R!"+ T*(" @.("!F
M<F5E=&5X='1A8FQE*$ET96U(87)D=V%R92P@.<U]P86=E+"! 4V5A<F-H0W)I
M=&5R:6$I(&%S($,-"B @.(" @.("!W:&5R90T*(" @.(" @.(" @.02Y;2T5972 ]
M(&4N3W)D97).;R!A;F0-"B @.(" @.(" @.($(N6TM%65T@./2!T+D]R9&5R3F\@.
M86YD#0H@.(" @.(" @.("!#+EM+15E=(#T@.:"Y/<F1E<DYO#0HM+2 Q<W0@.=&5S
M=#H@.3D]4('1H92!S86UE(&%S(&%B;W9E(&%S('1H:7,@.<75E<GD@.9V5T< R U
M(')O=W,L(&%D9&ET:6]N86P@.<F]W(&ES(")4:&ES(&ES($QA8F5L(# P,#4@.
M;V8@.<F]W(&9I=F4@.26YT97)F86-E(@.T*#0H-"BTM(%1E<W0@.=VET:"!B;W1H
M(&-O;G1A:6YS=&%B;&4@.)B!F<F5E=&5X="!!3D0@.3U(@.0F5T97=E; B!E86-H
M(0T*4T5,14-4(&1I<W1I;F-T(&4N3W)D97).;RP@.92Y,86)E; T*9G)O;2!)
M=&5M4W1O8VL@.05,@.92P@.271E;51I=&QE<R!T+ T*(" @.("!C;VYT86EN<W1A
M8FQE*$ET96U3=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O
M;G1A:6YS=&%B;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A; F=E<B<I(&%S
M($(@.+2T@.;F]W(&=E='1I;F<@.<F5S=6QT<R A($UU<W0@.:&%V92!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F0A#0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.
M($$N6TM%65T@./2!E+D]R9&5R3F\@.;W(@.+2T@.3U(@.+2T@.9V5N97)A=&5S(&QA
M<F=E<B!R97-U;'0L('-O('5S92!D:7-T:6YC="!E+D]R9&5R3F\L(')E='5R
M;G,@.-2!R;W=S('=I=&@.@.9&ES=&EN8W0-"B @.(" @.(" @.($(N6TM%65T@./2!T
M+D]R9&5R3F\-"@.T*<V5L96-T("H@.9G)O;2!)=&5M4W1O8VL@.=VAE<F4@.8V]N
M=&%I;G,H*BPG0FEL;'DG*2 M+2!R971U<FYS(#$@.<F]W+@.T*#0H-"BTM($UO
M9&EF:65D($]R:6=I;F%L('%U97)Y+"!R96UO=F5D('1H92! 4V5A<F-H0W)I
M=&5R:6$@.=F%R:6%B;&4@.=&\@.=&5S="!W:71H('1A8FQE(&%N9 "!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/<F1E
M<DYO+"!E+DQA8F5L#0IF<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S
M('0L($ET96U(87)D=V%R92!H+ T*(" @.("!C;VYT86EN<W1A8FQE*$ET96U3
M=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O;G1A:6YS=&%B
M;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A;F=E<B<I(&%S( $(L#0H@.(" @.
M(&-O;G1A:6YS=&%B;&4H271E;4AA<F1W87)E+"!S7W!A9V4L("=R; W<G*2!A
M<R!##0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.($$N6TM%65T@./2!E+D]R9&5R
M3F\@.86YD("TM($]2(#T@.9V5N97)A=&5S(&UU=&EP;&4@.<F]W<RP@.86YD('1H
M97)E9F]R92!N965D<R @.9&ES=&EN8W0@.92Y/<F1E<DYO+@.T*(" @.(" @.(" @.
M0BY;2T5972 ]('0N3W)D97).;R!A;F0-"B @.(" @.(" @.($,N6TM%65T@./2!H
M+D]R9&5R3F\-"BTM(#%S="!T97-T.B P(')O=W,L('-A;64@.87,@.86)O=F4-
9"@.T*+2T@./&5O9CX-"@.T*#0H-"@.T*#0H-"@.``
`
end

FREETEXT vs FREETEXTTABLE

1) The following query works fine and gives me the results I want. Is there
a better more efficient way of writing this?
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler" '
SELECT Stock.OrderNo, Stock.Description, Stock.Category,
Stock.s_Type, Stock.Manuf, Stock.Label, Titles.Title,
Titles.Artist, Hardware.m_Specs, Stock.ManCode
FROM Stock LEFT OUTER JOIN
Titles ON Stock.OrderNo = Titles.OrderNo LEFT OUTER JOIN
Hardware ON Stock.OrderNo = Hardware.OrderNo
WHERE FREETEXT(Stock.OrderNo,@.SearchCriteria) OR
FREETEXT(Stock.Description,@.SearchCriteria) OR
FREETEXT(Stock.Category,@.SearchCriteria) OR
FREETEXT(Stock.s_Type,@.SearchCriteria) OR
FREETEXT(Stock.Manuf,@.SearchCriteria) OR
FREETEXT(Stock.Label,@.SearchCriteria) OR
FREETEXT(Stock.ManCode,@.SearchCriteria) OR
FREETEXT(Hardware.m_Specs,@.SearchCriteria) OR
FREETEXT(Titles.Title,@.SearchCriteria) OR
FREETEXT(Titles.Artist,@.SearchCriteria)
2) As far as I can tell, RANK is not available with FREETEXT, but is
available with FREETEXTTABLE. How can I convert the above to make use of
FREETEXTTABLE?
thanks!
This should do it: NOTE: This assumes you want to search ALL fields that
are set up in the FULL-TEXT Search for that table.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = 'midler'
SELECT
Stock.OrderNo,
Stock.Description,
Stock.Category,
Stock.s_Type,
Stock.Manuf,
Stock.Label,
Titles.Title,
Titles.Artist,
Hardware.m_Specs,
Stock.ManCode
FROM
Stock
LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
ORDER BY
FS_TABLE.Rank DESC
"shank" <shank@.tampabay.rr.com> wrote in message
news:esmKmpsXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> 1) The following query works fine and gives me the results I want. Is
there
> a better more efficient way of writing this?
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler" '
> SELECT Stock.OrderNo, Stock.Description, Stock.Category,
> Stock.s_Type, Stock.Manuf, Stock.Label, Titles.Title,
> Titles.Artist, Hardware.m_Specs, Stock.ManCode
> FROM Stock LEFT OUTER JOIN
> Titles ON Stock.OrderNo = Titles.OrderNo LEFT OUTER JOIN
> Hardware ON Stock.OrderNo = Hardware.OrderNo
> WHERE FREETEXT(Stock.OrderNo,@.SearchCriteria) OR
> FREETEXT(Stock.Description,@.SearchCriteria) OR
> FREETEXT(Stock.Category,@.SearchCriteria) OR
> FREETEXT(Stock.s_Type,@.SearchCriteria) OR
> FREETEXT(Stock.Manuf,@.SearchCriteria) OR
> FREETEXT(Stock.Label,@.SearchCriteria) OR
> FREETEXT(Stock.ManCode,@.SearchCriteria) OR
> FREETEXT(Hardware.m_Specs,@.SearchCriteria) OR
> FREETEXT(Titles.Title,@.SearchCriteria) OR
> FREETEXT(Titles.Artist,@.SearchCriteria)
> 2) As far as I can tell, RANK is not available with FREETEXT, but is
> available with FREETEXTTABLE. How can I convert the above to make use of
> FREETEXTTABLE?
> thanks!
>
|||The query works, but I'm getting all rows returned. The highest ranked are
at the top like expected, but it's also returning all rows in the Stock
table that have no match whatsoever. In my FREETEXT query, all the results
had a match. I don't get the concept of the FREETEXTTABLE. In my mind, I'm
expecting a temp table to be created with the results. However, the code
below is actually joining the created table. I don't get it.
How do I get only matching results in the FREETEXTTABLE?
thanks!
"news.microsoft.com" <spammehere@.arcaderestoration.com> wrote in message
news:e4dx4uuXEHA.3420@.TK2MSFTNGP12.phx.gbl...
> This should do it: NOTE: This assumes you want to search ALL fields that
> are set up in the FULL-TEXT Search for that table.
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = 'midler'
> SELECT
> Stock.OrderNo,
> Stock.Description,
> Stock.Category,
> Stock.s_Type,
> Stock.Manuf,
> Stock.Label,
> Titles.Title,
> Titles.Artist,
> Hardware.m_Specs,
> Stock.ManCode
> FROM
> Stock
> LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
> LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
> LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
> ORDER BY
> FS_TABLE.Rank DESC
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:esmKmpsXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> there
>

FREETEXT results

Using the following query I get my desired results. However, in the
resulting recordset, how can I show for example 10 words before and 10 words
after the keyword or phrase that was searched upon? If I get 20 resulting
records I think it would be easier for the user to decide which they want to
view. I know Google does this. Is this an SQL issue or ASP issue?
thanks
DECLARE @.SearchString varchar(100)
SET @.SearchString = ' "stress" '
SELECT KEY_TBL.RANK, Title, Body
FROM Articles INNER JOIN
FREETEXTTABLE(Articles,*, @.SearchString) AS KEY_TBL
ON Articles.ID = KEY_TBL.[KEY]
ORDER BY Rank DESC
string handling is more efficiently done on the client level as opposed to
within SQL Server.
"shank" <shank@.tampabay.rr.com> wrote in message
news:Obj%23ddqZEHA.1508@.TK2MSFTNGP09.phx.gbl...
> Using the following query I get my desired results. However, in the
> resulting recordset, how can I show for example 10 words before and 10
words
> after the keyword or phrase that was searched upon? If I get 20 resulting
> records I think it would be easier for the user to decide which they want
to
> view. I know Google does this. Is this an SQL issue or ASP issue?
> thanks
> DECLARE @.SearchString varchar(100)
> SET @.SearchString = ' "stress" '
> SELECT KEY_TBL.RANK, Title, Body
> FROM Articles INNER JOIN
> FREETEXTTABLE(Articles,*, @.SearchString) AS KEY_TBL
> ON Articles.ID = KEY_TBL.[KEY]
> ORDER BY Rank DESC
>
|||Hilary,
While in some cases, client-side process might be better, for example
client-side paging and sorting of results, however, in this case, I must
respectively disagree...
Shank, you can use Substring and PatIndex along with your FREETEXTTABLE
query and get the "Goggle like" results that you have requested.
Specifically, the following SQL FTS query on the pubs table pub_info will
return rows that match the FTS search word (books) and display the near by
words from 20 characters before the searched keyword(books) for a total
length of 100 characters.
SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
WHERE Contains(pr_info, 'books')
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:#eM2eLrZEHA.644@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> string handling is more efficiently done on the client level as opposed to
> within SQL Server.
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:Obj%23ddqZEHA.1508@.TK2MSFTNGP09.phx.gbl...
> words
resulting[vbcol=seagreen]
want
> to
>
|||I really don't think so. In fact I know so. You can easily demonstrate this
within vbscript.
In fact I have written and ISAPI extension that does this - and posted it
here. Thinking about doing it within a database is simply not a good choice.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%2364LxM9ZEHA.2792@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hilary,
> While in some cases, client-side process might be better, for example
> client-side paging and sorting of results, however, in this case, I must
> respectively disagree...
> Shank, you can use Substring and PatIndex along with your FREETEXTTABLE
> query and get the "Goggle like" results that you have requested.
> Specifically, the following SQL FTS query on the pubs table pub_info will
> return rows that match the FTS search word (books) and display the near by
> words from 20 characters before the searched keyword(books) for a total
> length of 100 characters.
> SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
> FROM pub_info
> WHERE Contains(pr_info, 'books')
> Regards,
> John
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:#eM2eLrZEHA.644@.tk2msftngp13.phx.gbl...
to
> resulting
> want
>
|||Hi Hilary,
Then I guess we can agree that we disagree <G>, as I did say in " some
cases" and not in all cases, so we can agree, on a case-by-case basis. Are
you saying that in all cases, that all types of "string handling" are "bad"
if handled on the server-side?
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ue$hecFaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> I really don't think so. In fact I know so. You can easily demonstrate
this
> within vbscript.
> In fact I have written and ISAPI extension that does this - and posted it
> here. Thinking about doing it within a database is simply not a good
choice.[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%2364LxM9ZEHA.2792@.TK2MSFTNGP09.phx.gbl...
will[vbcol=seagreen]
by[vbcol=seagreen]
opposed[vbcol=seagreen]
> to
10
>

Framework 1:1 net

If you connect to a server and open a query in SQL Managment studio (2005) and looses connection and then regain the connection, when you try to run the query it gives me a "TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host."

This happens against both 2005 and 2000 servers.

If I re-run the query it works.

However, this happens in our VB.NET app as well for clients with Wireless connections.

So, something has changed in Framework 2.0 regarding this, does anyone know how to tell the client to not throw this error and just try connecting the server instead which will work?

Hi,

The SqlClient provider does not retry operations if the underlying connection is "broken" (e.g. closed by the server or by a glitch in the network). SQL Server Management Studio retries by trying to open the connection again.

One factor that affects this behavior is whether or not you have connection pooling enabled. Did you include "pooling=false" in your connection string?

This behavior should not have changed from 1.1 to 2.0. Did your 1.1 application behave differently? What error did you get (if any) on 1.1?

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.|||I have not changed anything from 1.1 to 2.0.
(its a 1.1 converted project to 2.0)

No, I did not get any errors like this in 1.1

I dont have Pooling=False, Neither do I want it that way (large app with tons of connections).

as stated it happens BOTH in Sql Management Studio as well as in any VB.NET app, it happens on several diffrent companies on diffrent continents, against diffrent SQL Servers.

VPN Clients, WIFI Clients are hit hardest (they have tiny drops in connections often)|||Bumping this.

Any resolution yet?

Basically, I cannot run any long running queries on computers with SQL Management Studio + Framework 2.0 installed over VPN, Not even in the OLD Query Analyzer anymore(diffrent error, same meaning).

However, on my untouched laptop, that has the old SQL Server Client Tools installed, it works fine on the same VPN connection.

Even on LAN it happens quite frequently, I got my app on about 150 clients, they catched this error about 40 times today.|||This looks really weird. I'll talk to a few other folks in the protocols team to see if I get more ideas to help you out.

There is one bit that particularly gets my attention: the OLD query analyzer uses the old native data-access stack; the SQL Server 2005 setup, or the VS 2005 setup, do not touch any of those bits at all. So, if you're getting the same behavior from the old query analyzer, that may indicate that there was something else that changed and may be causing this glitch. Do you get failures in QA as often as you get with the other clients? What error do you get in the QA case?

I'll reply again to this thread once I have more details or have someone do it.

Regards,
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation|||Hi Fredrik,

It appears that Sqlclient grabs a connection from the pool that has already been closed because of a network error and reports an error when it tries to write to the socket. I can't understand why the 1.1 framework would be less susceptible to these network errors than the 2.0 framework. Would you be able to take a client-side trace that captures the error and send it to me along with the server's error log? You can email me the files at ilsung@.microsoft.com. The instructions for setting up the trace are here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/tracingdataaccess.asp . Also, if you know of a way for us to repro this, that would be great.

Thanks,

Il-Sung Lee
Program Manager, SQL Server Protocols
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.|||I will test the tracing on my home computer which is connected over VPN.

However, Reproducing it in 2.0 enviroment is easy.

Open a SQL Query in SQL Management studio to a server.

Enter a simple query, Run the query, when its completed, disable the network connection, and immediatly enable it again, re-run the query when the network is back up again and you will get the error:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

- Fredrik|||The link you gave refers to Beta 1, is there a updated document for the release version of Framework 2.0?|||I just received this same error today in our production environment. Had not appeared in dev, test or uat. Our application was built in asp.net 2.0 beta 2 and later upgraded to RTM. Besides changing pooling, any suggestions on how to prevent this?|||

If I remember correctly, the same procedure should work for RTM bits. Is it not working for you?

Thanks,
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation

|||

No, I did not get the debug trace function to work, it simply dont dump any data, dont know why.

Anyway.

I found a setting in the SQL Configuration Manager, on TCP/IP Protocol (native) there is KeepAlive and KeepAliveInterval that I am testing a little with.

I sat those settings to zero, eg no keepalive checking I guess, will see how that turns out.

|||Please let everyone know how this goes. I'm having the exact same issue with my application. Thanks!|||

So far 100% success, I left my app running yesterday, and it hadnt crashed still today, one day later. Before the change it only took about 10 minutes on my WiFi before I got a error.

Question is how you can change those values for clients only have .NET framework 2.0 installed (there is no registry entry for that one pre-defined). Registry entry is only present on those clients that have SQL 2005 Client tools installed.

I gonna look a little further with Regmon later this week and see if its just enough to create the registry entry on a "normal" client.

|||

I get the same error. It even occurs between my sql server management studio and the sql instance on the network.

The client has the release version while the sql instance is sql server 2005 beta2. Could this be the issue?

Error connecting to SQLSERVER1\ANINSTANCE. Err: A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

This is a project i converted from 1.1 to 2.0.

The old saved 1.1 project connects without any problems.

It really looks like a framework 2.0 issue.

|||

It would be good if you could try this against a final version of SQL Server. The fact that the beta 2 server is dropping the connection during login is not that surprising, it may be due to a protocol version mismatch detected during the login handshake.

So I wouldn't consider this and the other issue discussed in this thread to be the same thing.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation

Fragmented Indexes

I have a query to find all indexes that are fragmented over 10%. The query
also excludes any where the page count is less than 8 and the index size is
smaller than 64k. Immediately after running my maintenance script that
reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
returns quite a few fragmented indexes. I've also insured that all tables
have a clustered index (most of them on an identity column). Why do I still
show fragmented indexes? Is there some reason they all won't defragment?
Any help is greatly appreciated!Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
calculated, things like extent order etc. As the index gets to some realistic size, you will not be
affected by this "misreport".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>I have a query to find all indexes that are fragmented over 10%. The query
> also excludes any where the page count is less than 8 and the index size is
> smaller than 64k. Immediately after running my maintenance script that
> reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> returns quite a few fragmented indexes. I've also insured that all tables
> have a clustered index (most of them on an identity column). Why do I still
> show fragmented indexes? Is there some reason they all won't defragment?
> Any help is greatly appreciated!|||Thanks for your quick response! Any chance you have a link to a page that
shows the 1000 pages MS recommends? Any other MS recommendation pages?
"Tibor Karaszi" wrote:
> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
> calculated, things like extent order etc. As the index gets to some realistic size, you will not be
> affected by this "misreport".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >I have a query to find all indexes that are fragmented over 10%. The query
> > also excludes any where the page count is less than 8 and the index size is
> > smaller than 64k. Immediately after running my maintenance script that
> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> > returns quite a few fragmented indexes. I've also insured that all tables
> > have a clustered index (most of them on an identity column). Why do I still
> > show fragmented indexes? Is there some reason they all won't defragment?
> > Any help is greatly appreciated!
>|||Below might have to 1000 pages recommendation:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> Thanks for your quick response! Any chance you have a link to a page that
> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>
> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation
>> is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will not
>> be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!|||Or then again, you might check this paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
It specifically says:
Generally, you should not be concerned with fragmentation levels of indexes
with less than 1,000 pages. In the tests, indexes containing more than
10,000 pages realized performance gains, with the biggest gains on indexes
with significantly more pages (greater than 50,000 pages).
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Below might have to 1000 pages recommendation:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is due
>> to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do I
>> > still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>|||Thank you both for your input! I'll change my maintenance script, and I'm
sure you've just saved us several thousand reindexing jobs that aren't
necessary on a weekly basis! Thanks again!
"Kalen Delaney" wrote:
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes
> with less than 1,000 pages. In the tests, indexes containing more than
> 10,000 pages realized performance gains, with the biggest gains on indexes
> with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> > Below might have to 1000 pages recommendation:
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Tony" <Tony@.discussions.microsoft.com> wrote in message
> > news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> >> Thanks for your quick response! Any chance you have a link to a page
> >> that
> >> shows the 1000 pages MS recommends? Any other MS recommendation pages?
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Ignore indexes with less than at least 500 pages (MS recommends 1000
> >> pages). You can, with only two
> >> extents, still have reported fragmentation after a rebuild. This is due
> >> to the way fragmentation is
> >> calculated, things like extent order etc. As the index gets to some
> >> realistic size, you will not be
> >> affected by this "misreport".
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> >> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >> >I have a query to find all indexes that are fragmented over 10%. The
> >> >query
> >> > also excludes any where the page count is less than 8 and the index
> >> > size is
> >> > smaller than 64k. Immediately after running my maintenance script
> >> > that
> >> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
> >> > query still
> >> > returns quite a few fragmented indexes. I've also insured that all
> >> > tables
> >> > have a clustered index (most of them on an identity column). Why do I
> >> > still
> >> > show fragmented indexes? Is there some reason they all won't
> >> > defragment?
> >> > Any help is greatly appreciated!
> >>
> >
>
>|||Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I have no idea why I posted
an URL to the I/O basic paper...
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000
> pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with
> the biggest gains on indexes with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way
>> fragmentation is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will
>> not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!
>>
>|||You probably have them all in a list, just like I do, and you grabbed the
wrong one. I just made absolutely sure that I grabbed the right one!
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23JZtwKMeIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I
> have no idea why I posted an URL to the I/O basic paper...
> :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
>> Or then again, you might check this paper:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> It specifically says:
>> Generally, you should not be concerned with fragmentation levels of
>> indexes with less than 1,000 pages. In the tests, indexes containing more
>> than 10,000 pages realized performance gains, with the biggest gains on
>> indexes with significantly more pages (greater than 50,000 pages).
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is
>> due to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do
>> > I still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>>
>>
>