Pages Results On Full-text Query - Sql Help Please

Mar 30, 2008

 Hi -
 I am trying to add paging to my stored procedure. The stored procedure successfully executes a full-text search. Unfortunately, the paging routine below the full-text search operates on the articles table after the search has been conducted. This means that it utilizes the row numbers from the entire table rather than the row numbers from the result set. I somehow need to the paging routine starting at "WITH tempArticles AS" to operate on the search results rather than the articles table. I am too new to SQL and can't figure out how to populate a temporary table storing text search results to use in this paging routine. Can anyone help me with this? Thanks!
 
Peter
 
 
 dbo.Search_Articles
    
    @searchText varchar(150),
    @PageSize int,
    @PageNumber int
    
    AS
    
    Declare @RowStart int
    Declare @RowEnd int
    
    if @PageNumber > 0
    Begin
    
    SET @PageNumber = @PageNumber -1;

    SET @RowStart = @PageSize * @PageNumber + 1;
    SET @RowEnd = @RowStart + @PageSize - 1 ;


    CREATE TABLE #results (ArticleID int)
    INSERT INTO #results
    
    SELECT ArticleID
    FROM articles
    WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText)
    UNION
    SELECT ArticleID
    FROM article_pages
    WHERE CONTAINS(Text, @searchText)


    /*this returns all matching records from the text search*/
    SELECT *
    FROM articles, #results
    WHERE #results.ArticleID=articles.ArticleID;
    
    
    
    
     WITH tempArticles AS
    ( SELECT Title,
     PostDate,
     UserID,
     City,
     Country,
     Tags,
     StoryID,
     Approved,

       ROW_NUMBER() OVER (order by PostDate) as RowNumber
       FROM articles WHERE Approved = 1)
       
    SELECT *
    FROM tempArticles
    WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd;
 
    
    


    END

View 4 Replies


ADVERTISEMENT

SQL Server 2012 :: Query To Search Full-text Indexed Table And Return Results

Apr 19, 2014

I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)

Query

DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);

SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');

SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result

[Code] ....

View 2 Replies View Related

How To Integrate Full Text Search With Asp.net Pages

Jun 10, 2006

Hi,
I just designed a website using asp.net 2.0, now my customer hopes I can add Search website feature, he expected that it can search both static text in aspx and dynamic content in SQL Server. The problem is : I don't know how to compose the result URL to the user. Suppose I have a table called "Table1", webform1.aspx and webform2.aspx will retrieve data from this table, and the query string of these two pages are different. After searching the table, I expected that there will be two matching records, the first one is linked to "webpage1.aspx?ID1=xxx" and the second one is linked to "webpage2.aspx?ID2=xxx". Must I hard code the result page for each searchable table? Any other solutions?
Thanks,
Ziff

View 1 Replies View Related

Spreading SELECT Query Results Over Multiple Pages

Dec 20, 2006

Hello.Lets say that there is a table with N rows. now, i want to display the table's data on a web page. one way is to select the whole table and add each row's data to the webpage (something like SELECT * FROM TABLE1).going this way will create a huge page. i want to speared the results over multiple pages - excatly as this forum spread each forum messages over multiple pages.
for this purpose, i need to query Y rows each time, for example, if my table has 20 rows, and say that i want that each page will display 5 rows, then i need to query 5 rows each time. the first 5 rows for the first page, the next 5 rows for the second page and so on...
is there any way to achieve it using an SQL query?
the simplest way is to select the whole table and manaually filter the results. but this way will become slow as the table grows with data... and i dont want to select rows which i wont display anyway.
any suggestions?
Thanks.

View 5 Replies View Related

SQL 2005 Full-Text Performance On Large Results

May 10, 2006

Hello everybody,
I've got a little problem wich i'm trying to solve since 1-2 years and i hoped it would go away with SQL 2005 - but that wasn't the case :(.

Situation:
I've just bought a new Server containing:
SQL 2005
64 Bit Enviroment
4 GB RAM
2x AMD Opteron 2 GHz Prozeccors (Dual Core)
2x RAID Controllers (RAID 1) containing
1.1 System
1.2 Data
2.1 Transaction Logs

I've created a full-text table containing all the search terms i need to search.
Table build:
RecID - int - Primary Key
SrcID - varchar(30)
ArticleID - int - referring to an original table
SearchField - varchar(150) - Containing the search terms
timestamp - timestamp field

Fulltext index:
RecID as Primary Key
SearchField as indexed field - Wordbreaker: Neutral (containing several languages), Accent sensitivity off

Now i've got different tables imported in here resulting in a table size of ~ 13 million rows.

There is no problem with the performance on this catalog if i search a term wich isn't contained in more than 200-300 recordsets - but if i search for a term wich could occur in 200'000 upwards it gets extremely slow.

On the slow query the first records get in after no time, but until the query finished up to 60 seconds pass.
The problem is that i have to sort by a ranking value wich is stored externally - so i need all results to sort them...

current (debugging) query:
SELECT ArticleID FROM fullTextTable AS ft INNER JOIN CONTAINSTABLE(FullTextCatalog,SearchField,'"term*"') AS ftRes ON ftRes.[KEY]=ft.idEntry

Now if i check in the performance monitor:
As soon as i run the query the 'Avg. Disk Read Queue Length' counter on disk D (SQL Data Files) jumps to the top, until the query has finished.
Almost no read/write activity on C: where the Fulltext is stored...

If i rerun the query, after it finished once successfully - it takes place below 1-2 seconds, would be nice to get that result in first place :).

Does anybody know a workaround to this problem?

View 9 Replies View Related

SQL 2012 :: Full-Text Queries Returning No Results

Jun 9, 2014

So I'm trying out full-text indexing for the first time and, in particular, FileTables in SQL Server 2012. I've followed a Microsoft walkthrough and everything seems to be ok. However, when I query the table using the CONTAINS keyword, I get no results (a regular query to make sure there are records in the table returns the expected number of results).

I'm now trying to troubleshoot, and have been using the FULLTEXTCATALOGPROPERTY function, but I don't understand the results.

If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatlogName',N'ItemCount'), I get a result of 51. There are 96 documents in the NTFS folder where the documents are stored, and the table has 96 records, so I don't know where 51 is coming from. 55 of the documents are .DOC files, the rest are .PDF, and some (or maybe all) of the PDFs are scanned images of documents, which I don't expect to be indexed, so maybe that explains it. And in another thread in these forums, a poster suggests that the result for this function should be either 0 or 1, with 0 meaning that no documents are pending indexing, but maybe I've misunderstood that.

If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatalogName',N'UniqueKeyCount'), I get a result of 2. I have got two full-text indexes in this catalog (one on the FileTable, one on a regular table with FT enabled). Is this result therefore expected? Again, reading online seems to suggest that a result of 0 is desirable, but I don't understand why, and if it is I don't understand why my result is 2!

I've now also run SELECT* FROM sys.dm_fts_index_keywords(DB_ID('DatabaseName'), Object_ID('dbo.FileTableName)), which I believe is supposed to list all of the indexed words from the table specified. I get one row returned, as follows:

keyword: 0xFF
display_term: END OF FILE
column_id: 2
document_count: 40

So basically, it's not indexed any words at all. And why is the document count only 40 when there are 96 documents in the folder and table?

View 2 Replies View Related

Full-text Index Search Not Returning Expected Results

Apr 11, 2007

Hello,

My full-text search isn't working at all! I have a temporary table with full-text indexing enabled where files are scanned for social security numbers. If it has one, the user will see a message that it believes it's found a SSN and won't upload it. There is only ever one row in this table, as we overwrite the contents upon each upload.

I'm testing this search, and it doesn't work. The table has the following columns:
attachemtId (int) - primary key
fileContent (image) - contents of the file
fileExtension (varchar) - extension of the file (this is always either ".pdf" or ".doc")


I created a .doc file that simply says "ssn", and then run the following query:

SELECT * FROM TempAttachment
WHERE CONTAINS(fileContent,'ssn')


and nothing is returned! I tried the same thing with a .pdf file, and same results.

I'm not sure if this is related, but earlier I had this issue where I had to reset permissions for the directory. I've tried removing the full-text index and adding it again, but that didn't do anything. I also checked error logs on the server, and there were no messages. Any help would be appreciated! Thank you!

View 1 Replies View Related

No Results Returned For Full Text Search On Varbinary(max) Column

Jul 10, 2007

Hi, I was wondering if any SQL Server gurus out there could help me...I
have a table which contains text resources for my application. The text
resources are multi-lingual so I've read that if I add a html language
indicator meta tag e.g.<META NAME="MS.LOCALE" CONTENT="ES">and
store the text in a varbinary column with a supporting Document Type
column containing ".html" of varchar(5) then the full text index
service should be intelligent about the language word breakers it
applies when indexing the text. (I hope this is correct technique for
best multi-lingual support in a single table?)However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.DECLARE @SearchWord nvarchar(256)SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.

View 1 Replies View Related

No Results Returned For Full Text Search On Varbinary(max) Column

Jul 10, 2007

Hi, I was wondering if any SQL Server gurus out there could help me...

I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)

However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @SearchWord nvarchar(256)
SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)

I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.

Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.

View 1 Replies View Related

FullText Search Not Returning Results After Rebuilding Full Text Index

Aug 6, 2012

We have a table that is Full Text Search index enabled on one column.This table has 200 lakhs of rows(20000000) . ContainsTable() function is searching data with in these 200 lakhs of rows(20000000), if any new rows are inserted then the ContainsTable is not going to search in these recent inserted rows.

We observed when we try for a data to search. it is returning the rows till the rows that are inserted date is less than 30th of march 2012. but not searching in the records that are created after April month , if even the data we are searching is available .

TableFulltextItemCount is around 2.2 crores.

Then we done rebuilt the FT catalog Index. then the TableFulltextItemCount became 0.Again we run the containstable query ,but still it is not getting results.

As the no of rows are very more . so i am not able to show the actual rows from which the data is not coming.

the below query gives 2 results that are from actual base table

HTML Code:
select * from g_case_action_log where cas_details like '%235355%' and product_id = 38810

To search for the same above word using FTS,I have used the query as below

HTML Code:
SELECT Distinct top 50 cal.case_id,cal.cas_details From g_case_action_log cal (READUNCOMMITTED)inner join containstable(es.g_case_action_log, cas_details, ' ("235355" OR "<br>235355" OR "235355<br> ") ') as key_tbl on cal.log_id = key_tbl.[key] Where cal.product_id = 38810 ORDER By cal.case_id DESC

I have attached one sql script file for your ref that contains create logic and index schema properties

Why it is not returning results all the time.

View 1 Replies View Related

Full Text Search Does Not Return Expected Results On Production Server

May 7, 2013

I have a FullTextSQLQuery which I am trying to search a phrase(The Multi-part identifier) on full text indexed table. I am getting expected results on running the below sql query on QA machine and PreProduction servers, but not getting the same results on our development and production servres as even though same code running.

SELECT DISTINCT TOP 50 c.case_id,c.status_id,cal.cas_details
FROM g_case_action_log cal (READUNCOMMITTED)
INNER JOIN g_case c (READUNCOMMITTED) ON (cal.case_id = c.case_id)
INNER JOIN CONTAINSTABLE(es.g_case_action_log, cas_details,
' "The multi-part identifier" OR "<br>The multi-part identifier" OR
"The multi-part identifier<br>" ') as key_tbl
ON cal.log_id = key_tbl.[key]
ORDER By c.case_id DESC

We are using SqlServer 2008 R2 version on all servers.

View 1 Replies View Related

SQL Server Admin 2014 :: Full Text Search Not Returning Certain Results?

Oct 28, 2015

We are running SQL 2014 SP1. We are using defined Full text indexes on several tables in the database. However, on one specific set of servers, a certain search will not return any data. This exact same search works on another set of servers built identically. The first responses I'm sure will be stop list, but I have dropped and recreated the FTI multiple times with different stop lists or no stop list at all and get the same results.

The specific word being searched on is YUM. If I change the value to YUMk, it actually returns, and if I change the data to TUM it returns, but YUM does not. This exact query is working on multiple other systems, so it seems to be something environmental, but I haven't been able to pinpoint it.

View 3 Replies View Related

SQL Search :: Can't Get Expected Results With Contains And Full Text Search?

Nov 1, 2015

I am using Sql Server 2014 Express edition.I have a table with a varchar(max) column. I have created a full text search that use the stoplist "system". column has this struct: xxx.yyy.zzz.... where xxx, yyy, zzz... are numbers, like 123.345.123123.366456...I can have rows like that:

123.345
123.345
123.345.444
123.345.555
123.345.666
123.345.444.777
123.345.444.888
123.345.555.999

I am trying this query:

select * from Mytable where
contains(MyColumn, '123.345.')

I gues the contains would return all the rows with column contains 123.345, but this does not return all the expected rows, only one row.I have tried to replace "." with "-" but the result is the same.I have also tried with '123.345.*. In this case I have got more results, but no all the exptected rows.If I use this query:

select * from MyTable where
MyCOlumn like '123.345.%';

View 12 Replies View Related

Full Text Query

Aug 31, 2001

I have a problem with sql 7 full text query. Everything appears to be in place the database is enabled and the catalogue populated but a query like this
select * from risks_intercat WHERE CONTAINS(RISK_NOTES, ' "bean curd" ')
always returns this;
Search on full-text catalog 'ftc_Risks_ic' for database ID 8, table ID 329768232 with search condition ' "bean curd" ' failed with unknown result (8bcf8cc).
has anybody got any ideas why?

View 1 Replies View Related

Full Text Commands In SQL Query?

Jul 2, 2007

My host does allow me to create full text catalogs. I use SQL Server Management Studio Express, so I'd have to do it "by hand" as opposed to point-clicking menus and going through wizards. Where do I go to find those commands? I've seen them before but I can't remember where to get them. They're the ones that begin with sp_.
I need the commands for:


Creating a full text catalog

View 4 Replies View Related

Full Text Query Problem

Jan 8, 2008

Is there something wrong with my query or my full text catalog? The first query returns data while the second one does not. Does it have something to do with it being a number? I can get the rows to return by searching on alpha text.

select * from gk_info
where info_desc like '%6416%'

select * from gk_info WHERE
CONTAINS (info_desc, '"6416"')

View 1 Replies View Related

SQL Query For Full-text Search!!!

Feb 8, 2008



Hello!!
I want to add the full text search feature into 2 columns of a table in my DB.What will be the query that I should execute.I have aalready the tables existing.So probably I need to ALTER alongwith the new functions!!

Thanks!!!

View 2 Replies View Related

Query Results To Text File...???

Oct 16, 2000

Hi,
I'm using SQL Server 7.0.

I have a query (select * from table1) and I'd like to have the results of this query sent to a text file instead of the results windows when I run it from Query Analyzer.

Any suggestions?

Thanks in advance,
Darrin Wilkinson

View 3 Replies View Related

Query Results To Text File

May 9, 2007

i jave the following query

select *,substring(stafflog,15,11) as test into #t1 from dbo.Customers
where stafflog like '%armagh%'
go
select left(stafflog,4) as Staff,count(left(stafflog,4)) as Total from #t1
where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime)
group by left(stafflog,4)
go
select Title,Address1,Address2,Town,County,Postcode,TelephoneDay,TelephoneWork,TelephoneEvening,
MobileTelephoneNo,Contact,Mail,Telephone,Terms,StaffLog
from #t1
where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime)
go
drop table #t1
go

i need this query to be scheduled to run at a certain time every day and the results to be put in a text file.

is there an easy way to do this or what should i be looking at doing to get it to work

View 6 Replies View Related

Full Text Query With Noise Words (I Think)

Mar 12, 2008

Hope you can ignore your personal music tastes with this post!I have a table of Artists with a Full Text Index on a few columns on that table.  My full text querying against this table works really well apart from it seems for one band - "Take That"!  I'm guessing this is because these words consitute noise words? I parse user's search terms and add an "AND" between each word. So for example my query is essentially:select * from containstable(Artists, *, '"take AND that"') As I say, this works fine for Pink Floyd etc, but not for these guys!  So either SQL Server has a preference on boy bands or I am thinking it is because these words are deemed to be noise. Anybody got any tips on how I could tackle this?  I suppose it is quite possible that there might be another band with the same problem.I do have exclusive access to the SQL box, so perhaps I could edit the noise words file......Thanks 

View 2 Replies View Related

Full Text Query Starts Slow

May 2, 2006

I've created a FullTextCatalog on one of my Databases and added a full text index on one of my text columns.

The first time I run a query against the data it takes roughly 40-45 seconds to return data. After that it blazes and runs in under a second. If I don't query it for 20-30 minutes, it will take 40-45 seconds again and then fly until the next break.

Is there a configuration setting somewhere that I'm missing on this? Currently the Index is only about 5MB so it should take that long to read in when I'm querying it.

I don't think it has anything to do with size because the actual return can contain anywhere from 10-80K rows and the speed is about the same.

I'm using Standard edition on a 2003 Standard Server if that plays into the potential problem at all. We're currently downloading and installing the new Service Pack to see if that fixes it, but for some reason I'm not holding my breath. I'm assuming that there is something we need to change in the configuration.

Let me know if I'm missing any pertinent information. Thanks.

View 2 Replies View Related

Full Text Query Eventually Hangs CPU At 100%

Mar 13, 2008



For one day, this SPROC executes very quickly to return results on a Full Text catalog.





Code Snippet
ALTER Procedure dbo.sp_RSSHarvestedHeadlines_FullTextSearch
(
@ORKeywords varchar(4000) = 'xxxx',
@ANDKeywords varchar(4000) = 'xxxx',
@NOTKeywords varchar(4000) = 'xxxx',
@SourceID int = -1,
@IsHidden bit = null
)
As
set nocount on
SELECT HHL.HeadlineID,
HHL.Title,
HHL.Link,
HHL.[Description],
HHL.PubDate,
HHL.GMTDateAdded,
RSSSources.SourceTitle,
RSSSources.SourceLink
FROM RSSHarvestedHeadlines HHL
INNER JOIN RSSSources ON HHL.SourceID = RSSSources.SourceID
WHERE HHL.PublishedFlag = 0
AND (@IsHidden is null OR HHL.HideFlag = @IsHidden)
AND (@SourceID = -1 OR HHL.SourceID = @SourceID)
AND (@ORKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ORKeywords) OR CONTAINS(HHL.Description, @ORKeywords)))
AND (@ANDKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ANDKeywords) OR CONTAINS(HHL.Description, @ANDKeywords)))
AND (
@NOTKeywords = 'xxxx'
OR (
(NOT CONTAINS(HHL.Title, @NOTKeywords)
AND
NOT CONTAINS(HHL.Description, @NOTKeywords))
)
)
ORDER BY HHL.GMTDateAdded DESC, HHL.PubDate DESC




But somethign happens overnight and in the morning the sproc times out. While running (even from a new query window on the SQL 2005 server itself) it utilizes 100% CPU until it times out.


When I pass default parameters to the sproc (not using any part of the query that uses Full Text) the sproc returns every record in the database very quickly. No hang ups.

But the moment I add any text in say the @ORKeywords parameter, for example, the sproc utilizes 100% CPU for maybe 15 seconds and then times out.

By accident I discovered that I can fix this temporarily by copying the database. I don't use the new copy or anything. Just the act of copying the database fixes it. The sproc then executes normally, and quickly. But the next morning it's back to slow again.

Note, over night I am adding about 1000 records to the table.

Would automatic updates to the FT Catalog choke on 1000 records?

Also note that one of the fields being cataloged is a "Text" field (blob). Would that cause this?

Would what text is being added to the table matter? What if an invalid character was added (like some european character or a control character)? Would FT indexing hang up on that?

I am at a loss. Any ideas?


View 5 Replies View Related

Full Text Timing Out Only On First Query Attempts

Oct 27, 2007

I'm running into a a problem with Full Text searching. I've narrowed my code that produces the error down to this:






Code Block

SELECT Content
FROM Text
JOIN CONTAINSTABLE(Text, Content, 'lake') AS A
ON A.[KEY]=Text.ID;


and here is how I initiated the full text for the table:






Code Block

sp_fulltext_database 'enable'
sp_fulltext_catalog 'theCatalogSearch','create'
sp_fulltext_table 'Text','create','theCatalogSearch','PK_Text'
sp_fulltext_column 'Text','Content','add'
sp_fulltext_table 'Text','activate'
sp_fulltext_table 'Text','start_full'
sp_fulltext_table Text, 'Start_change_tracking'
sp_fulltext_table Text, 'Start_background_updateindex'


The first time I run it in the SQL query analyzer it sits there for about 30 seconds and then gives me "Timeout expired (error - 2147217871)." After the first query attempt I can run it as many times as I want and it will work fine (no errors) ... But if I wait for about 30 minutes and then try it again, it will give the error again just on the first try. I've tried using search words that exist and ones that don't exist in the db and they both give the same error, so it's not that it's trying to return too many rows.

I'm using Microsoft SQL Server 2005. The code I'm writing is pretty basic so maybe it's the way that the database is set up or the way I initiated the full text tables? Any help would be greatly appreciated. Thank you.

View 3 Replies View Related

SQL Server Query Results To Text File

Dec 12, 2000

Is there a way to save the results of a query (run in the Query Analyzer) to a text file?

View 4 Replies View Related

'set' Query Option To Results In Text? Print Ss, Mis?

Jul 17, 2003

In TSQL, is there a way to 'Set' the query option results in text as the head of my sql statement?

kind of tired of hit contol+t to get the text result.

also, when I
print '@dateStart--' + cast(@dateStart as varchar(30))
I don't get seconds and I even need mis.
Is there a simple way instead of a combination of datepart() to get ss, mis?

thanks
David

View 5 Replies View Related

SQL Server Query Results In A Text/xml File

Apr 7, 2006

Hello folks!

I have written a query that I need to save results of. Is there some statement that I can place at the top of my query (like we can do in Oracle through spool command) to specify the directoryfilename to save query results to? Eventually I need to schedule this to run daily via a DTS package.

Please let me know if you've come across this before?

Thanks so much!

-Parul

View 3 Replies View Related

Save Query Results Into Text Or CSV File

Apr 19, 2013

I found this topic from this link: Save MySQL query results into a text or CSV file | a Tech-Recipes Tutorial

I am try to create the text file from query results but it didn't work and got this error: "Incorrect syntax near the keyword 'INTO'.

SELECT sale, del
FROM order
INTO OUTFILE 'C:/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'

View 10 Replies View Related

Full-Text Search Query Question - Performance

Jul 20, 2005

I have a table with 3M rows that contains a varchar(2000) field withvarious keywords. Here is the table structure:PKColumnImageIDFullTextColumnThere is an association table:ImageIDContractIDNow, I want to do a query where the ContractID = x and Contains someword in the FullTextColumn. There is an association table that mapsImages to Contracts - so I can't use the trick of putting the Contractcode in the FullTextColumn.I'm finding that first the FTS service is performing a search on theKeyword (which can take a long time if 100K rows are returned) thenjoining to the association table for the particular contract.Is there anyway to make this faster by telling the FTS service, onlysearch this subset of rows for the keyword based on the contract.Sorry if this sounds convoluted. Appreciate any help you can suggest.Thanks!

View 1 Replies View Related

Full-text Serach Timing Out Only On First Query Attempts

Jan 25, 2008

I'm running into a problem with Full-Text searching. I have a procedure which uses a full-text search.
When I run it in SQL query analyzer €“ it runs immediately.
I exec this procedure from my ASP page and it returns timeout error.
After the first query attempt I can run it (executing the ASP-page) as many times as I want with different search words and it will work fine (no errors) ... But if I wait for about 30 minutes and then try it again, it will give the error again just on the first try.
I've tried using search words that exist and ones that don't exist in the db and they both give the same error, so it's not that it's trying to return too many rows.

I'm using Microsoft SQL Server 2005.
Any help would be greatly appreciated.
Thank you.

View 2 Replies View Related

SQL Server 2005: Full Text Query Failed

Sep 3, 2007

Hi guys,

I am getting a really weird error message when executing a full-text query on SQL server 2005:

-------------------------
Microsoft OLE DB Provider for SQL Server error '80040e14'
The execution of a full-text query failed. "The form specified for the subject is not one supported or known by the specified trust provider."
-------------------------

Just to give a bit of background: we recently moved our database from a machine with SQL Server 2003 to a different computer with SQL Server 2005. This is when the error started showing up.

The query is not particular complex:

SELECT * FROM myTable M INNER JOIN FREETEXTTABLE(myTable, *, 'keyword') ct ON ct.[KEY] = M.Resource_ID

It's the "Freetexttable" bit that creates the error message. I have done some research on google, but I can't seem to find a solution.

Has anybody come across this error before? Any ideas on how I could fix it?

View 14 Replies View Related

PDF Export Results In Extra Pages.

Jul 20, 2007

I am experiencing some quirks when exporting to PDF.
I have read all the other posts about page sizes, margins, etc and my situation seems to be a bit different.

PageWidth = 21cm
PageHeight = 29.7cm
Margins = 1cm

Body.Width = 19cm

I have a report that contains a bar chart (Height = 15cm, Width = 19cm) and below that a rectangle that contains two text boxes with totals information (Left = 11.6cm, Width = 7.4cm, Height 0.5cm)

Now most of the time it exports to PDF perfectly - all on one page as would be expected

If however the chart has more than say 20 items on the y-axis then the rectangle gets pushed onto an new page.

For extra information, but not to confuse the issue I also have a page header and footer that have items lined up right to the edge (left + width = 19cm) and they all display fine on the one page. It is just this rectangle that is having the problem.

When I move the rectangle to the left a bit (11.4cm) so 0.2cm away from the right hand edge then it works fine.

It is just very strange because I cant see how it is getting shifted only when the chart contains a lot of items.

Any ideas?

View 1 Replies View Related

Disable Noise Words Checking In Full-text Query

Sep 8, 1999

how to completly disable noise words checking in full-text query?
(noise dictionaries already cleared!)

View 1 Replies View Related

Full-text Query (Freetexttable) Returning Duplicate Rows

Aug 31, 2007

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:




Code Snippet
SELECT *
FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL
ORDER BY RANK DESC






vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved