General Network Error OpenXML

Hi

I am using OpenXML to import data from text file. While it is working

fine with small files...I am getting General Network error while

parsing file of 4.5MB. Could anyone suggest the possible cause for

this. What is interesting is that , I am able to load the same file

using another SQL server. Could someone tell me the possible reason for

this behaviour....

Thanks

AnandThis error typically denotes a problem at the network layer. Can you check the NT event logs of the server for any network card failures? What about the SQL errorlog? Does it have any suspicious messages?sql

Gap between header and Body

Hello every body,

I am facing a smal formatting issue. the issue i have 7 text boxes attached with each other in the header to show the week days ,i.e Sunday,Monday,Tuesday,Wednesday,Thursay,Friday and Saturday

In Body,i have a matrix which will extend to have 7 columns for the weekdays.

Every thing is fine,Except a small gap between header and footer where i want the 7 text boxes to act as column header names.

I tried with various options like removing the header and placing the text boxes in Body followed by the matrix. 2) placing the seven column headers in a dummy table and attaching that with matrix,

But i am unable to remove the gap between them. if any one of you had experienced the same problem or knows how to solve.it's really a great help

Thank you,

Will it be possible for you to use a table instead? Place a table in the body. Use the table heade for the column names and the table details for the values.

|||

NO Tech Quest, the matrix doesn't stop with one week.

For Each week one matrix is reported.Its just like a calendar

Thanks

Raj deep.A

|||

Raj, Is there a reason that you are not using the "Columns" within the matrix for the headers. I know the alignment/ formatting gets harder when you separate them and use the headers and footers for this.

Full-Text-Search

Hi friends

i work with sql server 2005 express(Management Studio)

can i use full text search service with that edition of sql server?

and how i can use that.

thank u.

The Full-Text Search feature is not available in SQL Server 2005 Express.

HTH,
Ryan

|||

thank u for ur answering.

so which edition of sql server support that?

|||

fahima wrote:

so which edition of sql server support that?

The Workgroup, Standard, and Enterprise editions all support Full-text Search.

HTH,
Ryan

|||

ok

is it hard to work with service?

|||

fahima wrote:

is it hard to work with service?

Do you mean is it difficult to get Full-text Search to work? I've never played with Full-text Search, but here's an article that should give you a better understanding of it:Understanding SQL Server Full-Text Indexing.

HTH,
Ryan

|||

the Express Edition does in fact have full text search feature available. to get this you need to download a different file, SQL Server with Advance services -http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

Not sure if this was released since the last post here - just though i would answer seeing as i was floating around.

regards

fulltext statistics

Hi all,
Is it possible to get statistics of any sort from the full text engine?
My understanding is SQL Server treats the the fulltext engine as a remote
server and performs distributed queries against it via oledb. AFAIK, oledb
has optional interfaces to obtain stats (cardinality, distribution) which the
fulltext engine presumably supports since it comes up with reasonable
estimates in estimated query plans involving containstable.
So using an estimated plan is one way to get cardinality estimates for a
particular query, but I'm wondering if there's a more direct approach -- e.g.
I'd really like to be able to just get a histogram of terms. Perhaps it's
possible to connect directly to the full text engine via oledb? If so, what
would the connection string look like? or...?
Thanks for any ideas.
-Geoff
This is supposed to ship in SQL 2008.
http://www.zetainteractive.com - Shift Happens!
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
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:95516FF5-7316-437D-9278-0463823A028B@.microsoft.com...
> Hi all,
> Is it possible to get statistics of any sort from the full text engine?
> My understanding is SQL Server treats the the fulltext engine as a remote
> server and performs distributed queries against it via oledb. AFAIK, oledb
> has optional interfaces to obtain stats (cardinality, distribution) which
> the
> fulltext engine presumably supports since it comes up with reasonable
> estimates in estimated query plans involving containstable.
> So using an estimated plan is one way to get cardinality estimates for a
> particular query, but I'm wondering if there's a more direct approach --
> e.g.
> I'd really like to be able to just get a histogram of terms. Perhaps it's
> possible to connect directly to the full text engine via oledb? If so,
> what
> would the connection string look like? or...?
> Thanks for any ideas.
> -Geoff
>
>
>

Full-text Search, Query returns empty

Hello,
I'm working on a project using SQL server 2000 full text search. My os is
windows XP professional. (And I also tried to remote
connect to a windows 2000 server computer to do the same thing.) The problem
is same: query returns empty rows.
I followed all the steps from msdn website:Administering Full-Text
Features Using SQL Enterprise
Manager(http://msdn.microsoft.com/library/de...ullad_6g1f.asp).
It goes on well. But after running full population, I checked the properties
of the full-text catalog. It shows item count: 6. Unique Key count: 12.
I think something wrong here. Because I have a table with a data column
name File_data is image datatype. And I wrote a C#.net program to insert
several word doc, pdf, jpg file into the table SearchFile. The number of
unique key count should much bigger than 12.
And when I do the query, e.g.
SELECT File_title, File_data
FROM SearchFile
WHERE CONTAINS (File_data, 'cookies');
It returns empty rows.
This is my design table:
Column Name Data Type length
File_id(PK) int 4
file_type nvarchar 50
file_size nvarchar 50
file_title nvarchar 50
File_data image 16
File-time timestamp 8
This is part of the content of the table:
File_id file_type File_size File_title File_data File_time
8 image/pjpeg 4216 image1.jpg 0xFFD8FFE00010... 0x00000000000000D7
9 application/msword 29696 Introduction to ASP.doc 0xD0CF11E0A1B1...
0x00000000000000D9
10 application/pdf 449473 asp_net_whitepaper.pdf 0x255044462D31...
0x00000000000000DF
11 application/msword 26112 How do I use cookies in ASP.doc ...........
I can insert word,pdf,jpg file into SQL server 2000 database, and
retreive it in my asp.net web application successfully.
I also checked the services list for "Microsoft Search". It already
started.
I checked the log file SQL0001800005.1.gthr under Program
Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\GatherLogs.
It shows some error, but I don't know how to handle it.
3/17/2006 4:46:14 PM Add The gatherer has started
3/17/2006 4:46:14 PM Add The initialization has completed
3/17/2006 4:46:26 PM Add Started Full crawl
3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000013 Add
Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done. )
Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available.
No work was done.
3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000015 Add
Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done. )
Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available.
No work was done.
3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000014 Add
Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done. )
Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available.
No work was done.
3/17/2006 4:46:30 PM Add Completed Full crawl
My query for full text search always returns empty. And I'm
sure the string I searched is inside the document.
Can anyone please give me some idea what is wrong here? I really
appreciate any help.
Thanks in advance!
Sincerely,
Sherry
Actually I found out the problem. I should save the file_type just use the
file extention e.g. .doc instead of application/msword.
Thanks,
Sherry
"Sherry" wrote:

> Hello,
> I'm working on a project using SQL server 2000 full text search. My os is
> windows XP professional. (And I also tried to remote
> connect to a windows 2000 server computer to do the same thing.) The problem
> is same: query returns empty rows.
> I followed all the steps from msdn website:Administering Full-Text
> Features Using SQL Enterprise
> Manager(http://msdn.microsoft.com/library/de...ullad_6g1f.asp).
> It goes on well. But after running full population, I checked the properties
> of the full-text catalog. It shows item count: 6. Unique Key count: 12.
> I think something wrong here. Because I have a table with a data column
> name File_data is image datatype. And I wrote a C#.net program to insert
> several word doc, pdf, jpg file into the table SearchFile. The number of
> unique key count should much bigger than 12.
> And when I do the query, e.g.
> SELECT File_title, File_data
> FROM SearchFile
> WHERE CONTAINS (File_data, 'cookies');
> It returns empty rows.
> This is my design table:
> Column Name Data Type length
> File_id(PK) int 4
> file_type nvarchar 50
> file_size nvarchar 50
> file_title nvarchar 50
> File_data image 16
> File-time timestamp 8
> This is part of the content of the table:
> File_id file_type File_size File_title File_data File_time
> 8 image/pjpeg 4216 image1.jpg 0xFFD8FFE00010... 0x00000000000000D7
> 9 application/msword 29696 Introduction to ASP.doc 0xD0CF11E0A1B1...
> 0x00000000000000D9
> 10 application/pdf 449473 asp_net_whitepaper.pdf 0x255044462D31...
> 0x00000000000000DF
> 11 application/msword 26112 How do I use cookies in ASP.doc ...........
> I can insert word,pdf,jpg file into SQL server 2000 database, and
> retreive it in my asp.net web application successfully.
> I also checked the services list for "Microsoft Search". It already
> started.
> I checked the log file SQL0001800005.1.gthr under Program
> Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\GatherLogs.
> It shows some error, but I don't know how to handle it.
> 3/17/2006 4:46:14 PM Add The gatherer has started
> 3/17/2006 4:46:14 PM Add The initialization has completed
> 3/17/2006 4:46:26 PM Add Started Full crawl
> 3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000013 Add
> Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done. )
> Multiple-step OLE DB
> operation generated errors. Check each OLE DB status value, if available.
> No work was done.
> 3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000015 Add
> Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done. )
> Multiple-step OLE DB
> operation generated errors. Check each OLE DB status value, if available.
> No work was done.
> 3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000014 Add
> Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done. )
> Multiple-step OLE DB
> operation generated errors. Check each OLE DB status value, if available.
> No work was done.
> 3/17/2006 4:46:30 PM Add Completed Full crawl
> My query for full text search always returns empty. And I'm
> sure the string I searched is inside the document.
> Can anyone please give me some idea what is wrong here? I really
> appreciate any help.
> Thanks in advance!
> Sincerely,
> Sherry

Fulltext Search, Contains, Varbinary, AdvantureWorks

Hello all

I am trying to full text search in the Documnt table in AdvantureWorks DB.

as in the video sample microsoft provided.

I create the unique index on DocumentID column and created the FullTextIndex:

create fulltext index on Production.Document

(

Document

Type column FileExtension

Language 0X0

)

Key index ui_ProductionDocument on MyFullTextCatalog

With change_tracking auto

Now I am trying to run the next search:

select * from Production.Document

where Contains(Document,'?????')

and I get nothing back, what do I do wrong?

I was having a pending restart that fixed the all thing.

Don't know why....

Full-text search with German umlaut

Hello everyone!
Iâ'm using the full text search with the German language and it works fine in
mapping the German umlaut "ü" into "ue", but unfortunately the other
direction doesnâ't work (the search for words including "ue" does not match
words including "ü").
I have tried to solve it by editing the thesaurus file but I didnâ't find a
possibility to specify only word sequences. It works only for words.
This works for "Mueller"
<expansion>
_Mueller
_Müller
</expansion>
But this doesâ'nt work:
<expansion>
_ue
_ü
</expansion>
How can I get the mapping of "ue" to "ü" within the full text search?
Thanks
RobIt works in SQL Server 2005 if you select the accent insensitive catalog
option.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:41D47CF7-3BCB-45D7-A68F-1ED1DA4C629C@.microsoft.com...
> Hello everyone!
> I'm using the full text search with the German language and it works fine
> in
> mapping the German umlaut "ü" into "ue", but unfortunately the other
> direction doesn't work (the search for words including "ue" does not match
> words including "ü").
> I have tried to solve it by editing the thesaurus file but I didn't find a
> possibility to specify only word sequences. It works only for words.
> This works for "Mueller"
> <expansion>
> _Mueller
> _Müller
> </expansion>
> But this does'nt work:
> <expansion>
> _ue
> _ü
> </expansion>
> How can I get the mapping of "ue" to "ü" within the full text search?
> Thanks
> Rob
>|||Hi Hilary!
Thanks for your answer but unfortunately there is no change of the search
behaviour: Searching for "mueller" does not match "müller".
I tried those configurations (all possible combinations) using SQL Server
Express 9.00.2047:
I used an empty thesaurus file.
Database collations:
German_PhoneBook_CS_AI
German_PhoneBook_CS_AS
Latin1_General_CS_AI
Latin1_General_CS_AS
Fulltext Catalog ACCENT_SENSITIVITY:
ALTER FULLTEXT CATALOG TestFTCatalog REBUILD WITH ACCENT_SENSITIVITY = ON /
OFF
Did I misundertand your advice?
Rob
"Hilary Cotter" wrote:
> It works in SQL Server 2005 if you select the accent insensitive catalog
> option.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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

Full-text search with German umlaut

Hello everyone!
I’m using the full text search with the German language and it works fine
in
mapping the German umlaut "ü" into "ue", but unfortunately the other
direction doesn’t work (the search for words including "ue" does not match
words including "ü").
I have tried to solve it by editing the thesaurus file but I didn’t find a
possibility to specify only word sequences. It works only for words.
This works for "Mueller"
<expansion>
_Mueller
_Müller
</expansion>
But this does’nt work:
<expansion>
_ue
_ü
</expansion>
How can I get the mapping of "ue" to "ü" within the full text search?
Thanks
RobIt works in SQL Server 2005 if you select the accent insensitive catalog
option.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:41D47CF7-3BCB-45D7-A68F-1ED1DA4C629C@.microsoft.com...
> Hello everyone!
> I'm using the full text search with the German language and it works fine
> in
> mapping the German umlaut "" into "ue", but unfortunately the other
> direction doesn't work (the search for words including "ue" does not match
> words including "").
> I have tried to solve it by editing the thesaurus file but I didn't find a
> possibility to specify only word sequences. It works only for words.
> This works for "Mueller"
> <expansion>
> _Mueller
> _Mller
> </expansion>
> But this does'nt work:
> <expansion>
> _ue
> 
> </expansion>
> How can I get the mapping of "ue" to "" within the full text search?
> Thanks
> Rob
>|||Hi Hilary!
Thanks for your answer but unfortunately there is no change of the search
behaviour: Searching for "mueller" does not match "müller".
I tried those configurations (all possible combinations) using SQL Server
Express 9.00.2047:
I used an empty thesaurus file.
Database collations:
German_PhoneBook_CS_AI
German_PhoneBook_CS_AS
Latin1_General_CS_AI
Latin1_General_CS_AS
Fulltext Catalog ACCENT_SENSITIVITY:
ALTER FULLTEXT CATALOG TestFTCatalog REBUILD WITH ACCENT_SENSITIVITY = ON /
OFF
Did I misundertand your advice?
Rob
"Hilary Cotter" wrote:

> It works in SQL Server 2005 if you select the accent insensitive catalog
> option.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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

Full-Text Search is not installed, or a full-text component cannot

I am unable to get Full Text Indexing to work with SQL Server 2000 Developers
Edition. When I execute this command:
EXEC sp_fulltext_database 'enable'
This is what I get:
(1 row(s) affected)
Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database, Line 46
Full-Text Search is not installed, or a full-text component cannot be loaded.
I attempted to install FTS again from the CD, but it indicates that it is
already installed.
Here is the version I am using:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright 1988-2003 Microsoft Corporation Developer Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
When I run this at the command prompt:
net start mssearch
I get this:
The requested service has already been started.
More help is available by typing NET HELPMSG 2182
I’m not sure where to go from here.
Hello,
The possible causes of this kind of issue may be as followings:
* Full-Text Search(FTS) is installed AFTER SQL Service Service Pack(SP).
This is the most common cause. Reapplying the SP fixes FTS.
* Microsoft Search service is not installed or started
* SQL server or Microsoft Search service is not started as a administrative
user
To narrow down the issue, I suggest that you perform the following steps:
1. Make sure SQL server and Microsoft Search service are started as the
same domain\administrative user account
2. Install the latest SQL SP4 or reinstall SP3.
Microsoft SQL Server 2000 Service Pack 4
<http://www.microsoft.com/downloads/d...FC8D-C20E-4446
-99A9-B7F0213F8BC5&displaylang=en#filelist>
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Dale,
Sorry for the late reply on this thread, but this type of question is best
answered in the fulltext newsgroup.
The key bit of info here is the SQL Server edition "SQL Server 2000
Developers Edition" as FTS is not installed by default as it is with Std.
Edition and above. You can "re-install" the FTS components via removing or
renaming the following product tracking registry key (remove :
"<Instance_Name>\" if this is not a named instance):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\<Instance_Name>\Tracking\{E07FDDA7-5A21-11d2-9DAD-00C04F79D434}
Then you will be able to re-install the FTS / MSSearch components. You may
also want to review the procedures in KB article 827449 "How to manually
reinstall the Microsoft Search service for an instance of SQL 2000" at
http://support.microsoft.com/default...b;EN-US;827449
Sophie, the advise that you've provided is wrong and will cause known
problems, i.e.. high CPU utilization (there is a KB article on this).
Specifically, the Microsoft Search service MUST be started and running under
the "System account" (LocalSystem), while the MSSQLServer service can be
started and running under either LocalSystem or DOMAIN\Account (but NOT the
DOMAIN\Administrators account!). All this I've learned from years of
experience with SQL FTS since 1998 SQL 7.0 Beta3 while at MSFT...
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:Lg1hyhFqFHA.2152@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> The possible causes of this kind of issue may be as followings:
>
> * Full-Text Search(FTS) is installed AFTER SQL Service Service Pack(SP).
> This is the most common cause. Reapplying the SP fixes FTS.
> * Microsoft Search service is not installed or started
> * SQL server or Microsoft Search service is not started as a
> administrative
> user
>
> To narrow down the issue, I suggest that you perform the following steps:
> 1. Make sure SQL server and Microsoft Search service are started as the
> same domain\administrative user account
> 2. Install the latest SQL SP4 or reinstall SP3.
> Microsoft SQL Server 2000 Service Pack 4
> <http://www.microsoft.com/downloads/d...FC8D-C20E-4446
> -99A9-B7F0213F8BC5&displaylang=en#filelist>
>
> I hope the information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>

fulltext search HTML problem

Hi,

currently we use text field in table to store HTML, and we use fulltext index to search this field, but we can not always get right results, for example, given text - "<P><STRONG>Access High Resorts<BR>", I can not get result if I search for "Access" or "cce", but if fine if I search for "High" or 'Resor", since I use CONTAINS, and CONTAINS can not search for postfix.

I know I can create another image data type field, and seach will filter HTML tag, but it will take long time to do.

Is there a way I can search postfix or mid-field of word in fulltext search? any idea or suggestion?

Thanks in advancethe sample string should like this - "<P><STRONG>Access High Resorst<BR>"

FULLTEXT SEARCH any drawbacks?

I need to know are there any disadvantages of using Full Text Search with MS SQL 2000. I'm planning to host my website to be developed in asp.net with shared hosting company. Taking this into consideration can there be any problems regarding Full Text Search.

Cheers
Luckyhi there,

there are none i suppose.. all i know is that full text search speeds up data retrieval process.. take a look at the link attached.. take care..

http://www.developer.com/db/article.php/3446891|||Thanks sashi
But I read it somewhere that it puts a lot of extra load on the server. Thats why most of the hosting companies do not provide this service.

Thanks again

Fulltext search always returns no results.

Hello.
I am having a problem with fulltext search whereby it always returns no
data. I have enabled full text search on the table and successfully
created a catalogue, which according to the event log has been
populated.
I have tested with this query (found in this group) via Query Analyzer:
select FulltextCatalogProperty(N'resourceFile', N'PageID')
Which returns null (which i believe is correct).
The query i am using is:
SELECT * FROM tblPages,
FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
Which i also believe is correct. Anyone any ideas?
On an unrelated (or possibly related) subject, i also often get this in
my error logs - anyone know how to fix?
17052 : This SQL Server has been optimized for 8 concurrent queries.
This limit has been exceeded by 1 queries and performance may be
adversely affected.
Thanks.
marc
Is this MSDE? SQL FTS is not supported on MSDE,
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
"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121073540.588455.25420@.z14g2000cwz.googlegro ups.com...
> Hello.
> I am having a problem with fulltext search whereby it always returns no
> data. I have enabled full text search on the table and successfully
> created a catalogue, which according to the event log has been
> populated.
> I have tested with this query (found in this group) via Query Analyzer:
> select FulltextCatalogProperty(N'resourceFile', N'PageID')
> Which returns null (which i believe is correct).
> The query i am using is:
> SELECT * FROM tblPages,
> FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
> WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
> Which i also believe is correct. Anyone any ideas?
> On an unrelated (or possibly related) subject, i also often get this in
> my error logs - anyone know how to fix?
> 17052 : This SQL Server has been optimized for 8 concurrent queries.
> This limit has been exceeded by 1 queries and performance may be
> adversely affected.
> Thanks.
> marc
>
|||Marc,
Could you post the full output of -- SELECT @.@.version -- as this is most
important information when troubleshooting SQL FTS issues! While I suspect
that you're using SQL Server 7.0, I also need the service pack level that
you have installed.
Because of the error message (17052), you may be hitting the issues in the
following KB articles relative to SQL Server 7.0 Full-text Search:
230036 BUG: Heavy Full Text Query Activity Results in Unexpected Timeout
Errors
http://support.microsoft.com/default...;en-us;Q230036
230103 BUG: Cannot Have More than Eight Full Text Joins and Operations
http://support.microsoft.com/default...;en-us;Q230103
Unfortunately and again assuming that you're using SQL Server 7.0, this
error cannot be fixed as it is by design for SQL Server 7.0, and your only
solution is to upgrade to SQL Server 2000. If you are not using SQL Server
7.0, could you provide more details about other SQL Full-text Search queries
you may have executing on your server?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121073540.588455.25420@.z14g2000cwz.googlegro ups.com...
> Hello.
> I am having a problem with fulltext search whereby it always returns no
> data. I have enabled full text search on the table and successfully
> created a catalogue, which according to the event log has been
> populated.
> I have tested with this query (found in this group) via Query Analyzer:
> select FulltextCatalogProperty(N'resourceFile', N'PageID')
> Which returns null (which i believe is correct).
> The query i am using is:
> SELECT * FROM tblPages,
> FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
> WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
> Which i also believe is correct. Anyone any ideas?
> On an unrelated (or possibly related) subject, i also often get this in
> my error logs - anyone know how to fix?
> 17052 : This SQL Server has been optimized for 8 concurrent queries.
> This limit has been exceeded by 1 queries and performance may be
> adversely affected.
> Thanks.
> marc
>
|||one more point SQL 2005 does interpolation, so this will work
declare @.searchTerm varchar(200)
set @.searchTerm="microsoft"
SELECT * FROM tblPages,
FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
In previous versions of SQL Server you would have to do something like this
declare @.searchTerm varchar(200)
set @.searchTerm="microsoft"
declare @.searchphase varchar(2000)
select @.searchphrase= "SELECT * FROM tblPages,FREETEXTTABLE(tblPages, *,"
+char(39) +char(34)+ @.searchphrase
select @.searchphrase=@.searchphrase+ char(34)+char(39)+")searchTable WHERE
[Key] = tblPages.PageID ORDER BY RANK DESC"
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
"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121073540.588455.25420@.z14g2000cwz.googlegro ups.com...
> Hello.
> I am having a problem with fulltext search whereby it always returns no
> data. I have enabled full text search on the table and successfully
> created a catalogue, which according to the event log has been
> populated.
> I have tested with this query (found in this group) via Query Analyzer:
> select FulltextCatalogProperty(N'resourceFile', N'PageID')
> Which returns null (which i believe is correct).
> The query i am using is:
> SELECT * FROM tblPages,
> FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
> WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
> Which i also believe is correct. Anyone any ideas?
> On an unrelated (or possibly related) subject, i also often get this in
> my error logs - anyone know how to fix?
> 17052 : This SQL Server has been optimized for 8 concurrent queries.
> This limit has been exceeded by 1 queries and performance may be
> adversely affected.
> Thanks.
> marc
>
|||Apologies for the lateness of this reply, ive been a bit busy! the
results of select @.@. version are:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Personal
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Also this is the first time ive tried to use full text search so its
the only fulltext search query on the server.
thanks,
marc
|||Hilary.
Your first query still returns the same, the second returns nothing -
not even column names!
ta
marc
|||I am still not further forward with this, i have reduced my query to:
CREATE PROCEDURE spGetResults1
@.searchTerm1 varchar
AS
SELECT * FROM tblPages
WHERE FREETEXT(*,@.searchTerm1)
GO
and still it returns nothing. It is almost as though my table is empty
(which it isnt). My catalogue is also definately populated. Anyone have
any ideas?
Thanks,
Marc
|||"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121782154.811864.158350@.f14g2000cwb.googlegr oups.com...
>I am still not further forward with this, i have reduced my query to:
> CREATE PROCEDURE spGetResults1
> @.searchTerm1 varchar
> AS
> SELECT * FROM tblPages
> WHERE FREETEXT(*,@.searchTerm1)
> GO
> and still it returns nothing. It is almost as though my table is empty
> (which it isnt). My catalogue is also definately populated. Anyone have
> any ideas?
As Hilary pointed out, that syntax only works in SQL Server 2005 - you have
SQL Server 2000 so you cannot use a variable in the FREETEXT call. Also you
didn't specify the size of @.searchTerm1 so it's set to a 1 character string.
Try this:
CREATE PROCEDURE spGetResults1
@.searchTerm1 varchar(20)
AS
DECLARE @.sql nvarchar(100)
SET @.sql = 'SELECT * FROM STK WHERE FREETEXT(*,' + char(39) + char(34) +
@.searchTerm1 + char(34) + char(39) + ')'
EXEC sp_executesql @.sql
GO
This allows up to 20 characters to be passed in as the search term -
obviously you can increase this as needed. If you increase it by a lot make
sure you increase the size of @.sql too or else you'll have errors caused by
truncating the constructed sql.
Dan
|||Ah. The only problem was i had missed out the size of the @.searchTerm1
variable on the original query. This works:
"CREATE PROCEDURE spGetResults
@.searchTerm varchar(20)
AS
SELECT * FROM tblPages,
FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
GO"
Thanks for your help people.
Marc

Full-text search - catalog refuses to populate

Hello

I'm trying to get full-text searching to work on SQL Server 2000 (with SP3 installed).

I have a table containing many text fields that I want to search and so far have successfully gone through the "Full-Text wizard" selecting the text fields from the table I want as searchable. I then try and start full population of the catalog but its always remains zero in size when I check its properties. Hence when I try a query like:

SELECT Name FROM tblTextDocs WHERE CONTAINS(mainbody, 'find this')

It results in the error message:

Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. The catalog does not exist or is currently unavailable. Please retry the action again later and if this symptom persists, contact the system administrator.

It would appear that the catalog simply refuses to populate. I've tried starting population via Enterprise Manager and also by doing sp_fulltext_catalog 'MyCatalog', 'start_full'. The Full-text support service says it is running correctly and no other problems with the server have been experienced.

Any suggestions would be much appreciated.

Regards

SamBy the "Full Text Support Service" do you mean the "Microsoft Search" service?|||Originally posted by MCrowley
By the "Full Text Support Service" do you mean the "Microsoft Search" service?

Yes - the Microsoft Search service. I have no problems in stopping/starting it too.|||This error might occur if changing SQL Server Account to Non-Admin for Full-Text Search Makes Existing Catalogs Unusable.

To access existing full-text search catalogs, rebuild and repopulate them. The existing catalogs can also be accessed by switching back to an administrator account.

Full-Text Search

I am trying to create a full text index on a column in a table and the data type is Image, but the TYPE COLUMN FILE EXTENSION is in another table. there is no way of creating the type in the same table. the wizard will not allow me to pick the data type field from another table. is there a work around this issue?

Emadkb

Adding a column to the table is not an option?|||It is a fram work table and making any changes now would be impossible. Do you see any other solutions to this issue. I really appreciate your quick response. Emadkb|||If you are using SQL 2005, you can make an indexed view and build fulltext index on the view. However that do means you dup the storage for the image columns.|||Thank you so much....I was wishing there is another solution. emadkb|||

hey Feng Tian
I hope you could help me in FullText Search. No one else seems to be knowing about Fulltext Search.
I'm able to create Full text indexing catalogs, populate them etc.. when i login directly from the server using Sql Server Enterprise manager but when I try to use the same login (db owner) remotely from some other machine using SQL Server Enterprise Manager, I see disabled(stopped) Full-text Service and all full-text features disabled. So does it mean i cannot populate or create catalogs remotely using sql server enterprise manager.
I have tried this on two different servers and then accessing from two different remote/client machines. But the problem is there any way.

Server: Mssql 2000 server
Remote/Client system: Win XP service Pack2
Access Software: Microsoft Sql Server 2000 Enterprise Manager.

Thanks
Sattva

|||

Sattva,
The behievor you are experiencing with your SQL Server 2000 Enterprise Manager (EM) is typical and expected. The EM cannnot "see remote" services such as the "Microsoft Search" (MSSearch) service on remote servers when accessed across the internet or the server is registered as an IP address. However, SQL FTS is still functional and you can use the system stored procedures to remotely manage and populate the FT Catalogs, for example:

To Create/Remove the Existing Full-Text Table Index, Catalog
-- If Full-Text Index exists, DROP that Index,
-- If Full-Text Index does not exist, CREATE that Index.
--
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index & Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog, Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
EXEC sp_fulltext_table 'pub_info', 'activate'
END

EXEC sp_fulltext_catalog 'PubInfo', 'start_full' -- "Full Crawl"
Wait for crawl to complete
--
Confirm above results with:
--
SELECT pub_id, pr_info
FROM pub_info WHERE CONTAINS(pr_info, '"book*"')

See my blog entry "SQL Server 2000 Full-Text Search Resources and Links" for more SQL 2000 FTS information.

Hope that helps,
John

SQL Full Text Search Blog
http://jtkane.spaces.live.com/

|||

Thanks John. It was a great help. Now I hav it all working.

Cheers mate.

FullText Search

I have create a table called tblcatalog with colums id(identity,primary key) and contents(varchar(100))

I have then created a full text catalog on that table and populated it.

Then i wrote the following query
"select contents from tblcatalog where contains(contents,'sample data')"
It is fetching 0 records even though u have 5 records with entries "sample data"

Can anyone tell me the solution immediately

bye
shankyImmediately? Have you tried everything you could think of before asking for help? It'll take me 20 minutes or so to setup FullText Search, and will take you probably much less to experiment with it.|||when you say you populated it did you create an initial full population?|||Forgive me if this is basic to you. Just trying to be thorough. :)
In Enterprise Manager, right click on your table. Choose 'Full Text Index Table' (MS Search service must be started for this to appear.) Choose 'Start full population.' Rerun your query. If it works, you must not have really done a full population yet. In that case you may want to set up incremental or full populations using the 'Schedules..' option on the same context menu...
If not, I have no clue what the problem is! :)

Full-Text population problems

Hi

I'm getting the following error when I attempt to populate our Full Text Index on SQL
2000: The crawl seed in project SQL0000500005> cannot be accessed. Error: 800705b4 - This operation returned because
the timeout period expired. .

The crawl for project could not be started,
because no crawl seeds could be accessed. Fix the errors and try the crawl again.

I have seen in other threads and on the MS site how this is caused by the MSSQLService and the MS Search services not being syncronized and it can be fixed by changing the MSSQLService account to use the system account and back again.

The BUILTIN\Administrators group is present in SQL and has OWNER permissions. I even tried giving the SYSTEM group full access to the FTDATA folder.

This did NOT work for me. I am still getting the messages. One thing that is probably unique to my instalation is that it resides on the only server on this network so it is also the primary domain controler. Could this be why I cannot get the FT population to work?

I have another machine on a seperate network that is not a PDC and FT-Indexing works great on it but that doesn't help me for the network I need it to work on.

Does anyone have any other ideas I could try? I have been working on this for close to a week now.

The "Mav"How about the SQL Services account privileges and which accout is used?

Check what is the current state of process for indexing.

Refer to this
KBA (http://support.microsoft.com/default.aspx?scid=KB;en-us;q317746) to resovle the issue.|||I looked through that document and I have tried all that still no dice. The MS Search service is using the Local System account and the MSSQLServer service is using the local server administrator account. I tried changing this to the Local System acount using SQL Server Enterprise Manager(SSEM) this did not work so I changed it back to the local administrator account, once again using the SSEM.

Any other suggestions?

Thanks,
The Mav|||No one has any ideas? =(

full-text population problem

I have a problem in full population for a full text index.
When I run the population; these are the results
- Population duration : 3-5 seconds.
- item count : 0
- catalog size : 0
- unique key count : 1
although I have 7 million of records in that table
I need explanation please.
Than you
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
can you look in the application log for event messages from MSSearch or
MSSCI?
Most often this problem is caused by
1) changing the SQL Server service account through control panel instead of
Enterprise Manager
2) changing MSSearch's service account to a local or domain account instead
of keeping in local system
3) removing the builtin\administrators group from the system administrators
role.
to fix 1 refer to
http://support.microsoft.com/default...b;en-us;277549
to fix 2 click Start, point to settings, click control panel, double click
administrative tools, double click on services, double click Microsoft
Search, click the Logon tab, and select Local System, clear the Allow the
service to interact with the desktop, click on Apply and ok, and accept all
defaults
to fix 3 execute the below tsql in Query Analyzer
--Grant [NT Authority\System] a logon to SQL Server:
EXEC sp_grantlogin [NT Authority\System]
--Add that account to the sysadmins role:
EXEC sp_addsrvrolemember @.loginame = [NT Authority\System] , @.rolename =
'sysadmin'
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mohamed Salah" <msalah_din@.yahoo.com> wrote in message
news:u5Ls8eicEHA.3944@.tk2msftngp13.phx.gbl...
> I have a problem in full population for a full text index.
> When I run the population; these are the results
> - Population duration : 3-5 seconds.
> - item count : 0
> - catalog size : 0
> - unique key count : 1
> although I have 7 million of records in that table
> I need explanation please.
> Than you
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
|||You did build the catalogue first and then try a full-text population?