How would I go about seting my text box so that it will increase or decrease
in size to fit my text. Here's what expression I got in my textbox:
= First(Fields!NAME.Value, "FIN") & vbcrlf &
First(Fields!ADDR_1.Value, "FIN") & vbcrlf &
First(Fields!ADDR_2.Value, "FIN") & vbcrlf &
First(Fields!ADDR_3.Value, "FIN") & vbcrlf &
First(Fields!CITY.Value, "FIN") & "," & First(Fields!STATE.Value, "FIN") & "
" & First(Fields!ZIPCODE.Value, "FIN") & vbcrlf &
First(Fields!COUNTRY.Value, "FIN")
The thing is that some contacts will not have address 2 and 3. This will
leave the result like this:
ABC Company
123 South Drive
AppleTown, MN 65343
This is the result that I want if there are no address 2 and 3:
ABC Company
123 South Drive
AppleTown, MN 65343Hi,
There is no property to achieve this
Instead you can use an if condition and display the value only if it exists.
Hope this helps.
Ponnurangam.
"chang" <chang@.discussions.microsoft.com> wrote in message
news:7E565BDD-B1C2-48B2-8A6A-81BEE00BA16D@.microsoft.com...
> How would I go about seting my text box so that it will increase or
> decrease
> in size to fit my text. Here's what expression I got in my textbox:
> = First(Fields!NAME.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_1.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_2.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_3.Value, "FIN") & vbcrlf &
> First(Fields!CITY.Value, "FIN") & "," & First(Fields!STATE.Value, "FIN") &
> "
> " & First(Fields!ZIPCODE.Value, "FIN") & vbcrlf &
> First(Fields!COUNTRY.Value, "FIN")
> The thing is that some contacts will not have address 2 and 3. This will
> leave the result like this:
> ABC Company
> 123 South Drive
>
> AppleTown, MN 65343
> This is the result that I want if there are no address 2 and 3:
> ABC Company
> 123 South Drive
> AppleTown, MN 65343|||Remember, the Height property can also be an expression.
Maybe you can come up with some smart logic.sql
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
incorrect syntax question
i have a table of data that the user can enter into, the data type is set to "text" and has worked in some test so far, but when i type data in '' marks such as :
'text here'
it gives me an incorrect syntax error, is there a way around this? or is the '' charectors invalid? thanks John
A single quote is a string separator. So if your data has single quotes you might have to excape it with double quotes: example:Select'test''s'
|||Are you using parameters? If not, you should. It will help avoid this kind of problem. Others might suggest doubling the apostrophes, but while it works, that is not the answer.|||
Mikesdotnetting:
Are you using parameters? If not, you should. It will help avoid this kind of problem. Others might suggest doubling the apostrophes, but while it works, that is not the answer.
Agreed.
parameters will stop me getting this error? awesome! thanks for your help John
I have an insert query which gives me a similar error, I cant see why its not working, the error is
A potentially dangerous Request.Form value was detected from the client (ctl00$ContentPlaceHolder1$CommentBox="<b>test text</b>").
My code is :
Connection.Open();
SqlCommand InsertItem = new SqlCommand("INSERT INTO TestTable(Inserted) VALUES ('@.item')", Connection);
InsertItem.Parameters.Add("@.item", SqlDbType.VarChar).Value = Textbox1.Text;
InsertItem.ExecuteNonQuery();
Connection.Close();
I simply tryed to insert the text string <b>test text</b>
Thanks John
|||You dont need to put quotes if you are using parameterized queries.
SqlCommand InsertItem = new SqlCommand("INSERT INTO TestTable(Inserted) VALUES (@.item)", Connection);
thats awesome thanks, that answered every question i could come up with! haha John
Wednesday, March 21, 2012
Incorrect query stats from full text engine
Hi
We have a table that is approx 800,000 rows with about 6 columns of which 3
are full text indexed.
This table is full text queried then the results are used to filter other
tables in a
"select * from maintable where category=.. and primaryid in (select key from
CONTAINSTABLE(fulltexttable, Keywords, 'FORMSOF(INFLECTIONAL, "glass")'))"
Every so often we get a slow perfoming query with these. Putting them into
Management Studio and asking for a query plan shows its the remote scan
consuming all the time. Hovering the mouse over the "Remote Scan" to get the
statistics gives wildly inaccurate estimates of number of rows, and then also
gives impossible number of "Actual Rows Returned" e.g. on this query I have
just done it has claims to have returned 13,350,965 rows - this from a table
that only has approx 800,000 rows.
The indexed columns may have duplicate words but checking the specific
example the searched for word definately appears less that 800,000 times in
the whole table.
Any one got any suggestions?
Thanks
Chris
Just to say, is MS SQL 2005 Standard Edition SP2 64 bit edition
|||The statistics returned my SQL FTS are not accurate as you have discovered.
You should work on tuning other parts of the query to remove spooling, etc.
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
"chrisredburn" <chrisredburn@.discussions.microsoft.com> wrote in message
news:DC403EF1-81FC-42FE-BF36-AA8457E48755@.microsoft.com...
> Hi
> We have a table that is approx 800,000 rows with about 6 columns of which
> 3
> are full text indexed.
> This table is full text queried then the results are used to filter other
> tables in a
> "select * from maintable where category=.. and primaryid in (select key
> from
> CONTAINSTABLE(fulltexttable, Keywords, 'FORMSOF(INFLECTIONAL, "glass")'))"
> Every so often we get a slow perfoming query with these. Putting them
> into
> Management Studio and asking for a query plan shows its the remote scan
> consuming all the time. Hovering the mouse over the "Remote Scan" to get
> the
> statistics gives wildly inaccurate estimates of number of rows, and then
> also
> gives impossible number of "Actual Rows Returned" e.g. on this query I
> have
> just done it has claims to have returned 13,350,965 rows - this from a
> table
> that only has approx 800,000 rows.
> The indexed columns may have duplicate words but checking the specific
> example the searched for word definately appears less that 800,000 times
> in
> the whole table.
> Any one got any suggestions?
> Thanks
> Chris
|||In the execution plan returned for this query, the remote scan cost is 90% of
the query. The plan says that the next stage that the FTS gets passed to is
a filter that takes 1% and reduces the 13,000,000 down to 110 rows (the final
number of rows returned). The next largest part of the query, at 4%, is an
Index Seek. The rest is made up of 1 merge join, 2 inner joins and a few
filters, but as these are only working on a small (approx 600) number of rows
they aren't appearing to take any time up.
Surely if the statistics are wrong, then the query planneris going to start
making bad choices about how to plan the query?
"Hilary Cotter" wrote:
> The statistics returned my SQL FTS are not accurate as you have discovered.
> You should work on tuning other parts of the query to remove spooling, etc.
> --
> 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
>
> "chrisredburn" <chrisredburn@.discussions.microsoft.com> wrote in message
> news:DC403EF1-81FC-42FE-BF36-AA8457E48755@.microsoft.com...
>
>
|||A profiler trace of the query, showing SQL:BatchCompleted and
SQL:FullTextQuery events for the query, has...
SQL:FullTextQuey Duration = 164596
SQL:BatchCompleted Duration = 165274
|||I'd recommend look at a couple of 2 options:
1) use top_n_by_rank parameter if don't have to retrieve all of the results
but just a window (say top 100). The "n" value for the should be in this case
100*avg selectivity of the non-ft part (or actually smaller - the smaller,
the better)
2) If this is a plan issue, consider using OPTIMIZE FOR parameter to hint
the FT search string to CONTAINSTABLE.
3) make sure your index is not too fragmented; reorganization of an index
will improve card estimates. FTS cardinality estimation typically works ok
for single terms but worse for expressions.
Regards,
-Denis.
"chrisredburn" wrote:
[vbcol=seagreen]
> In the execution plan returned for this query, the remote scan cost is 90% of
> the query. The plan says that the next stage that the FTS gets passed to is
> a filter that takes 1% and reduces the 13,000,000 down to 110 rows (the final
> number of rows returned). The next largest part of the query, at 4%, is an
> Index Seek. The rest is made up of 1 merge join, 2 inner joins and a few
> filters, but as these are only working on a small (approx 600) number of rows
> they aren't appearing to take any time up.
> Surely if the statistics are wrong, then the query planneris going to start
> making bad choices about how to plan the query?
>
> "Hilary Cotter" wrote:
We have a table that is approx 800,000 rows with about 6 columns of which 3
are full text indexed.
This table is full text queried then the results are used to filter other
tables in a
"select * from maintable where category=.. and primaryid in (select key from
CONTAINSTABLE(fulltexttable, Keywords, 'FORMSOF(INFLECTIONAL, "glass")'))"
Every so often we get a slow perfoming query with these. Putting them into
Management Studio and asking for a query plan shows its the remote scan
consuming all the time. Hovering the mouse over the "Remote Scan" to get the
statistics gives wildly inaccurate estimates of number of rows, and then also
gives impossible number of "Actual Rows Returned" e.g. on this query I have
just done it has claims to have returned 13,350,965 rows - this from a table
that only has approx 800,000 rows.
The indexed columns may have duplicate words but checking the specific
example the searched for word definately appears less that 800,000 times in
the whole table.
Any one got any suggestions?
Thanks
Chris
Just to say, is MS SQL 2005 Standard Edition SP2 64 bit edition
|||The statistics returned my SQL FTS are not accurate as you have discovered.
You should work on tuning other parts of the query to remove spooling, etc.
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
"chrisredburn" <chrisredburn@.discussions.microsoft.com> wrote in message
news:DC403EF1-81FC-42FE-BF36-AA8457E48755@.microsoft.com...
> Hi
> We have a table that is approx 800,000 rows with about 6 columns of which
> 3
> are full text indexed.
> This table is full text queried then the results are used to filter other
> tables in a
> "select * from maintable where category=.. and primaryid in (select key
> from
> CONTAINSTABLE(fulltexttable, Keywords, 'FORMSOF(INFLECTIONAL, "glass")'))"
> Every so often we get a slow perfoming query with these. Putting them
> into
> Management Studio and asking for a query plan shows its the remote scan
> consuming all the time. Hovering the mouse over the "Remote Scan" to get
> the
> statistics gives wildly inaccurate estimates of number of rows, and then
> also
> gives impossible number of "Actual Rows Returned" e.g. on this query I
> have
> just done it has claims to have returned 13,350,965 rows - this from a
> table
> that only has approx 800,000 rows.
> The indexed columns may have duplicate words but checking the specific
> example the searched for word definately appears less that 800,000 times
> in
> the whole table.
> Any one got any suggestions?
> Thanks
> Chris
|||In the execution plan returned for this query, the remote scan cost is 90% of
the query. The plan says that the next stage that the FTS gets passed to is
a filter that takes 1% and reduces the 13,000,000 down to 110 rows (the final
number of rows returned). The next largest part of the query, at 4%, is an
Index Seek. The rest is made up of 1 merge join, 2 inner joins and a few
filters, but as these are only working on a small (approx 600) number of rows
they aren't appearing to take any time up.
Surely if the statistics are wrong, then the query planneris going to start
making bad choices about how to plan the query?
"Hilary Cotter" wrote:
> The statistics returned my SQL FTS are not accurate as you have discovered.
> You should work on tuning other parts of the query to remove spooling, etc.
> --
> 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
>
> "chrisredburn" <chrisredburn@.discussions.microsoft.com> wrote in message
> news:DC403EF1-81FC-42FE-BF36-AA8457E48755@.microsoft.com...
>
>
|||A profiler trace of the query, showing SQL:BatchCompleted and
SQL:FullTextQuery events for the query, has...
SQL:FullTextQuey Duration = 164596
SQL:BatchCompleted Duration = 165274
|||I'd recommend look at a couple of 2 options:
1) use top_n_by_rank parameter if don't have to retrieve all of the results
but just a window (say top 100). The "n" value for the should be in this case
100*avg selectivity of the non-ft part (or actually smaller - the smaller,
the better)
2) If this is a plan issue, consider using OPTIMIZE FOR parameter to hint
the FT search string to CONTAINSTABLE.
3) make sure your index is not too fragmented; reorganization of an index
will improve card estimates. FTS cardinality estimation typically works ok
for single terms but worse for expressions.
Regards,
-Denis.
"chrisredburn" wrote:
[vbcol=seagreen]
> In the execution plan returned for this query, the remote scan cost is 90% of
> the query. The plan says that the next stage that the FTS gets passed to is
> a filter that takes 1% and reduces the 13,000,000 down to 110 rows (the final
> number of rows returned). The next largest part of the query, at 4%, is an
> Index Seek. The rest is made up of 1 merge join, 2 inner joins and a few
> filters, but as these are only working on a small (approx 600) number of rows
> they aren't appearing to take any time up.
> Surely if the statistics are wrong, then the query planneris going to start
> making bad choices about how to plan the query?
>
> "Hilary Cotter" wrote:
incorrect query reults
Hi,
another problem occured!-)
I did the following SQL-query:
select * from queue_suchsourceindex
where contains(text, ' "oenorm*" ')
But in the result there are many rows, that do not contain the word "oenorm"
in its text field. First i thought i mixed somethiong up in Application, but
everything is working right there. Just the query return the wrong results.
I have no clue, how to find out what the problem is.
thx in advance, Gerald.
my configuration:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
I forgot to tell, that the query
select id, url from queue_suchsourceindex
where text like '%oenorm%'
returns no rows, as it should.
thx, Gerald.
"Gerald Baeck" schrieb:
> Hi,
> another problem occured!-)
> I did the following SQL-query:
>
> select * from queue_suchsourceindex
> where contains(text, ' "oenorm*" ')
> But in the result there are many rows, that do not contain the word "oenorm"
> in its text field. First i thought i mixed somethiong up in Application, but
> everything is working right there. Just the query return the wrong results.
> I have no clue, how to find out what the problem is.
> thx in advance, Gerald.
> my configuration:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
|||Gerald,
Why would you expect contains(text, ' "oenorm*" ') to return results when
using text like '%oenorm%' does not return results?
While often the T-SQL LIKE will return different results when compared to
CONTAINS or FREETEXT, but if T-SQL LIKE cannot find it with it's "pattern
matching" method and using leading and trailing wildcards (%), then most
likely CONTAINS or FREETEXT will not return results as well as the search
string is most likely not in your column: text.
Regards,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:418794BC-8ED0-4B72-9CC7-0AF3C50C04F5@.microsoft.com...[vbcol=seagreen]
> I forgot to tell, that the query
> select id, url from queue_suchsourceindex
> where text like '%oenorm%'
> returns no rows, as it should.
> thx, Gerald.
>
> "Gerald Baeck" schrieb:
"oenorm"[vbcol=seagreen]
but[vbcol=seagreen]
results.[vbcol=seagreen]
Windows[vbcol=seagreen]
|||John,
its the other way round. The CONTAINS-Query returns many rows and the
LIKE-Query does not. The LIKE-QUery return the correct resultset in
that case.
thx, Gerald.
"John Kane" <jt-kane@.comcast.net> wrote in message news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Gerald,
> Why would you expect contains(text, ' "oenorm*" ') to return results when
> using text like '%oenorm%' does not return results?
> While often the T-SQL LIKE will return different results when compared to
> CONTAINS or FREETEXT, but if T-SQL LIKE cannot find it with it's "pattern
> matching" method and using leading and trailing wildcards (%), then most
> likely CONTAINS or FREETEXT will not return results as well as the search
> string is most likely not in your column: text.
> Regards,
> John
>
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:418794BC-8ED0-4B72-9CC7-0AF3C50C04F5@.microsoft.com...
> "oenorm"
> but
> results.
> Windows
|||Thanks, Gerald,
Could you proved the exact text from a few of the rows that contain the
string 'oenorm', including all punctuation characters?
Is this HTML code or plain text that is stored in a text or varchar column?
Also, what is the language of the text stored in the column?
All of these factors, when considered with the OS platform (in your case
Win2K) can affect the results from the CONTAINS query.
Regards,
John
"Gerald Baeck" <exc3ktest@.hotmail.com> wrote in message
news:ac510211.0411152308.3ec64f25@.posting.google.c om...
> John,
> its the other way round. The CONTAINS-Query returns many rows and the
> LIKE-Query does not. The LIKE-QUery return the correct resultset in
> that case.
> thx, Gerald.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
when[vbcol=seagreen]
to[vbcol=seagreen]
"pattern[vbcol=seagreen]
search[vbcol=seagreen]
Application,[vbcol=seagreen]
14:22:05[vbcol=seagreen]
|||Hi John,
First of all thanks for your patient help!-)
I checked all the rows manually and i am sure that there is no string like
'oenorm'. You can have a look at this File
http://www.webwatch.at/oenorm_example.txt.
The data is stored in plain text and contains no html-tags.
The language of the text-field is german.
regards, Gerald.
"John Kane" wrote:
> Thanks, Gerald,
> Could you proved the exact text from a few of the rows that contain the
> string 'oenorm', including all punctuation characters?
> Is this HTML code or plain text that is stored in a text or varchar column?
> Also, what is the language of the text stored in the column?
> All of these factors, when considered with the OS platform (in your case
> Win2K) can affect the results from the CONTAINS query.
> Regards,
> John
>
> "Gerald Baeck" <exc3ktest@.hotmail.com> wrote in message
> news:ac510211.0411152308.3ec64f25@.posting.google.c om...
> news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...
> when
> to
> "pattern
> search
> Application,
> 14:22:05
>
>
|||You're welcome, Gerald,
I too searched the oenorm_example.txt file and found no hit on oenorm. While
asking for the @.@.version info is a start, sometimes for these more difficult
cases, I've most often ask for additional info as the different results may
be because of the language and possibly not using the correct "Language for
Word Breaker". Could you post the output from the following SQL code?
use <your_database_name_here>
go
SELECT @.@.language
-- may need to set show advanced options
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Depending upon the "Language for Word Breaker" that you are using along with
German text, this may explain the differences in results.
Thanks,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...[vbcol=seagreen]
> Hi John,
> First of all thanks for your patient help!-)
> I checked all the rows manually and i am sure that there is no string like
> 'oenorm'. You can have a look at this File
> http://www.webwatch.at/oenorm_example.txt.
> The data is stored in plain text and contains no html-tags.
> The language of the text-field is german.
> regards, Gerald.
> "John Kane" wrote:
column?[vbcol=seagreen]
compared[vbcol=seagreen]
most[vbcol=seagreen]
message[vbcol=seagreen]
word[vbcol=seagreen]
wrong[vbcol=seagreen]
Edition on[vbcol=seagreen]
|||Hi John,
here is the info:
Deutsch
default full-text language0214748364710311031
5Queue_SuchsourceC:\Programme\Microsoft SQL Server\MSSQL\FTDATA01
dboQueue_SuchsourceIndexPK_Queue_SuchsourceIndex41Queue_Suchsource
dbo1129771082Queue_SuchsourceIndexText2NULLNULL1031
Queue_SuchsourceIndexdbouser table2004-11-15 01:22:52.980
Urlvarcharno255 nononoSQL_Latin1_General_CP1_CI_AS
Texttextno16 yes(n/a)(n/a)German_PhoneBook_CI_AS
Zeitsmalldatetimeno4 no(n/a)(n/a)NULL
IDintno410 0 no(n/a)(n/a)NULL
ID110
No rowguidcol column defined.
PRIMARY
PK_Queue_SuchsourceIndexclustered, unique, primary key located on PRIMARYID
PRIMARY KEY (clustered)PK_Queue_SuchsourceIndex(n/a)(n/a)(n/a)(n/a)ID
WebinvestigatorDB.dbo.Queue_Clippings: FK_Queue_Treffer_Queue_SuchsourceIndex
thx, Gerald.
"John Kane" wrote:
> You're welcome, Gerald,
> I too searched the oenorm_example.txt file and found no hit on oenorm. While
> asking for the @.@.version info is a start, sometimes for these more difficult
> cases, I've most often ask for additional info as the different results may
> be because of the language and possibly not using the correct "Language for
> Word Breaker". Could you post the output from the following SQL code?
> use <your_database_name_here>
> go
> SELECT @.@.language
> -- may need to set show advanced options
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Depending upon the "Language for Word Breaker" that you are using along with
> German text, this may explain the differences in results.
> Thanks,
> John
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...
> column?
> compared
> most
> message
> word
> wrong
> Edition on
>
>
|||I noticed something interesting. I get now hits with the term "oenorm", but
the couple of incorrect hits with "oenorm*".
regards, Gerald.
"John Kane" wrote:
> You're welcome, Gerald,
> I too searched the oenorm_example.txt file and found no hit on oenorm. While
> asking for the @.@.version info is a start, sometimes for these more difficult
> cases, I've most often ask for additional info as the different results may
> be because of the language and possibly not using the correct "Language for
> Word Breaker". Could you post the output from the following SQL code?
> use <your_database_name_here>
> go
> SELECT @.@.language
> -- may need to set show advanced options
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Depending upon the "Language for Word Breaker" that you are using along with
> German text, this may explain the differences in results.
> Thanks,
> John
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...
> column?
> compared
> most
> message
> word
> wrong
> Edition on
>
>
|||Thanks, Gerald,
You're using German (windows collation 1031) for all aspects of your server,
including the @.@.language, default FTS language as well as the "Language for
Word Breaker" for your FT-enabled table (Queue_SuchsourceIndex) Text (text)
column. Additionally, you're using SQL Server 2000 SP3 on NT 5.0 (or Win2K),
so you're using the German infosoft.dll wordbreaker.
Now, the issue is understanding how the Win2K German infosoft.dll
wordbreaker breaks or tokenizes the German *word* oenorm. Getting hits with
"oenorm", but the couple of incorrect hits with "oenorm*", might be at the
heart of this issue with the Win2K German infosoft.dll wordbreaker, as I
neither read or write German, can you tell me what this word means in
English? Is it normally a stand-alone German word or is it a part of a
compound word in German? If so, is it usually used as a the leading part or
the trailing part of the compound word?
FYI, the Win2K German infosoft.dll wordbreaker has been problematic at best
and at worst not very useful and for this and other reasons, MS has
developed a new word breaker (langwbrk.dll) for the WinXP and Win2003 OS
platforms. Although, I'm not sure if for the German language, if this
wordbreaker is any better, I'm sad to say.
Regards,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:5703BB7B-F1FD-43BE-BF4D-BA777577214F@.microsoft.com...
> I noticed something interesting. I get now hits with the term "oenorm",
but[vbcol=seagreen]
> the couple of incorrect hits with "oenorm*".
> regards, Gerald.
> "John Kane" wrote:
While[vbcol=seagreen]
difficult[vbcol=seagreen]
may[vbcol=seagreen]
for[vbcol=seagreen]
with[vbcol=seagreen]
like[vbcol=seagreen]
the[vbcol=seagreen]
case[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
results[vbcol=seagreen]
then[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
2002[vbcol=seagreen]
another problem occured!-)
I did the following SQL-query:
select * from queue_suchsourceindex
where contains(text, ' "oenorm*" ')
But in the result there are many rows, that do not contain the word "oenorm"
in its text field. First i thought i mixed somethiong up in Application, but
everything is working right there. Just the query return the wrong results.
I have no clue, how to find out what the problem is.
thx in advance, Gerald.
my configuration:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
I forgot to tell, that the query
select id, url from queue_suchsourceindex
where text like '%oenorm%'
returns no rows, as it should.
thx, Gerald.
"Gerald Baeck" schrieb:
> Hi,
> another problem occured!-)
> I did the following SQL-query:
>
> select * from queue_suchsourceindex
> where contains(text, ' "oenorm*" ')
> But in the result there are many rows, that do not contain the word "oenorm"
> in its text field. First i thought i mixed somethiong up in Application, but
> everything is working right there. Just the query return the wrong results.
> I have no clue, how to find out what the problem is.
> thx in advance, Gerald.
> my configuration:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
|||Gerald,
Why would you expect contains(text, ' "oenorm*" ') to return results when
using text like '%oenorm%' does not return results?
While often the T-SQL LIKE will return different results when compared to
CONTAINS or FREETEXT, but if T-SQL LIKE cannot find it with it's "pattern
matching" method and using leading and trailing wildcards (%), then most
likely CONTAINS or FREETEXT will not return results as well as the search
string is most likely not in your column: text.
Regards,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:418794BC-8ED0-4B72-9CC7-0AF3C50C04F5@.microsoft.com...[vbcol=seagreen]
> I forgot to tell, that the query
> select id, url from queue_suchsourceindex
> where text like '%oenorm%'
> returns no rows, as it should.
> thx, Gerald.
>
> "Gerald Baeck" schrieb:
"oenorm"[vbcol=seagreen]
but[vbcol=seagreen]
results.[vbcol=seagreen]
Windows[vbcol=seagreen]
|||John,
its the other way round. The CONTAINS-Query returns many rows and the
LIKE-Query does not. The LIKE-QUery return the correct resultset in
that case.
thx, Gerald.
"John Kane" <jt-kane@.comcast.net> wrote in message news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Gerald,
> Why would you expect contains(text, ' "oenorm*" ') to return results when
> using text like '%oenorm%' does not return results?
> While often the T-SQL LIKE will return different results when compared to
> CONTAINS or FREETEXT, but if T-SQL LIKE cannot find it with it's "pattern
> matching" method and using leading and trailing wildcards (%), then most
> likely CONTAINS or FREETEXT will not return results as well as the search
> string is most likely not in your column: text.
> Regards,
> John
>
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:418794BC-8ED0-4B72-9CC7-0AF3C50C04F5@.microsoft.com...
> "oenorm"
> but
> results.
> Windows
|||Thanks, Gerald,
Could you proved the exact text from a few of the rows that contain the
string 'oenorm', including all punctuation characters?
Is this HTML code or plain text that is stored in a text or varchar column?
Also, what is the language of the text stored in the column?
All of these factors, when considered with the OS platform (in your case
Win2K) can affect the results from the CONTAINS query.
Regards,
John
"Gerald Baeck" <exc3ktest@.hotmail.com> wrote in message
news:ac510211.0411152308.3ec64f25@.posting.google.c om...
> John,
> its the other way round. The CONTAINS-Query returns many rows and the
> LIKE-Query does not. The LIKE-QUery return the correct resultset in
> that case.
> thx, Gerald.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
when[vbcol=seagreen]
to[vbcol=seagreen]
"pattern[vbcol=seagreen]
search[vbcol=seagreen]
Application,[vbcol=seagreen]
14:22:05[vbcol=seagreen]
|||Hi John,
First of all thanks for your patient help!-)
I checked all the rows manually and i am sure that there is no string like
'oenorm'. You can have a look at this File
http://www.webwatch.at/oenorm_example.txt.
The data is stored in plain text and contains no html-tags.
The language of the text-field is german.
regards, Gerald.
"John Kane" wrote:
> Thanks, Gerald,
> Could you proved the exact text from a few of the rows that contain the
> string 'oenorm', including all punctuation characters?
> Is this HTML code or plain text that is stored in a text or varchar column?
> Also, what is the language of the text stored in the column?
> All of these factors, when considered with the OS platform (in your case
> Win2K) can affect the results from the CONTAINS query.
> Regards,
> John
>
> "Gerald Baeck" <exc3ktest@.hotmail.com> wrote in message
> news:ac510211.0411152308.3ec64f25@.posting.google.c om...
> news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...
> when
> to
> "pattern
> search
> Application,
> 14:22:05
>
>
|||You're welcome, Gerald,
I too searched the oenorm_example.txt file and found no hit on oenorm. While
asking for the @.@.version info is a start, sometimes for these more difficult
cases, I've most often ask for additional info as the different results may
be because of the language and possibly not using the correct "Language for
Word Breaker". Could you post the output from the following SQL code?
use <your_database_name_here>
go
SELECT @.@.language
-- may need to set show advanced options
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Depending upon the "Language for Word Breaker" that you are using along with
German text, this may explain the differences in results.
Thanks,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...[vbcol=seagreen]
> Hi John,
> First of all thanks for your patient help!-)
> I checked all the rows manually and i am sure that there is no string like
> 'oenorm'. You can have a look at this File
> http://www.webwatch.at/oenorm_example.txt.
> The data is stored in plain text and contains no html-tags.
> The language of the text-field is german.
> regards, Gerald.
> "John Kane" wrote:
column?[vbcol=seagreen]
compared[vbcol=seagreen]
most[vbcol=seagreen]
message[vbcol=seagreen]
word[vbcol=seagreen]
wrong[vbcol=seagreen]
Edition on[vbcol=seagreen]
|||Hi John,
here is the info:
Deutsch
default full-text language0214748364710311031
5Queue_SuchsourceC:\Programme\Microsoft SQL Server\MSSQL\FTDATA01
dboQueue_SuchsourceIndexPK_Queue_SuchsourceIndex41Queue_Suchsource
dbo1129771082Queue_SuchsourceIndexText2NULLNULL1031
Queue_SuchsourceIndexdbouser table2004-11-15 01:22:52.980
Urlvarcharno255 nononoSQL_Latin1_General_CP1_CI_AS
Texttextno16 yes(n/a)(n/a)German_PhoneBook_CI_AS
Zeitsmalldatetimeno4 no(n/a)(n/a)NULL
IDintno410 0 no(n/a)(n/a)NULL
ID110
No rowguidcol column defined.
PRIMARY
PK_Queue_SuchsourceIndexclustered, unique, primary key located on PRIMARYID
PRIMARY KEY (clustered)PK_Queue_SuchsourceIndex(n/a)(n/a)(n/a)(n/a)ID
WebinvestigatorDB.dbo.Queue_Clippings: FK_Queue_Treffer_Queue_SuchsourceIndex
thx, Gerald.
"John Kane" wrote:
> You're welcome, Gerald,
> I too searched the oenorm_example.txt file and found no hit on oenorm. While
> asking for the @.@.version info is a start, sometimes for these more difficult
> cases, I've most often ask for additional info as the different results may
> be because of the language and possibly not using the correct "Language for
> Word Breaker". Could you post the output from the following SQL code?
> use <your_database_name_here>
> go
> SELECT @.@.language
> -- may need to set show advanced options
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Depending upon the "Language for Word Breaker" that you are using along with
> German text, this may explain the differences in results.
> Thanks,
> John
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...
> column?
> compared
> most
> message
> word
> wrong
> Edition on
>
>
|||I noticed something interesting. I get now hits with the term "oenorm", but
the couple of incorrect hits with "oenorm*".
regards, Gerald.
"John Kane" wrote:
> You're welcome, Gerald,
> I too searched the oenorm_example.txt file and found no hit on oenorm. While
> asking for the @.@.version info is a start, sometimes for these more difficult
> cases, I've most often ask for additional info as the different results may
> be because of the language and possibly not using the correct "Language for
> Word Breaker". Could you post the output from the following SQL code?
> use <your_database_name_here>
> go
> SELECT @.@.language
> -- may need to set show advanced options
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Depending upon the "Language for Word Breaker" that you are using along with
> German text, this may explain the differences in results.
> Thanks,
> John
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...
> column?
> compared
> most
> message
> word
> wrong
> Edition on
>
>
|||Thanks, Gerald,
You're using German (windows collation 1031) for all aspects of your server,
including the @.@.language, default FTS language as well as the "Language for
Word Breaker" for your FT-enabled table (Queue_SuchsourceIndex) Text (text)
column. Additionally, you're using SQL Server 2000 SP3 on NT 5.0 (or Win2K),
so you're using the German infosoft.dll wordbreaker.
Now, the issue is understanding how the Win2K German infosoft.dll
wordbreaker breaks or tokenizes the German *word* oenorm. Getting hits with
"oenorm", but the couple of incorrect hits with "oenorm*", might be at the
heart of this issue with the Win2K German infosoft.dll wordbreaker, as I
neither read or write German, can you tell me what this word means in
English? Is it normally a stand-alone German word or is it a part of a
compound word in German? If so, is it usually used as a the leading part or
the trailing part of the compound word?
FYI, the Win2K German infosoft.dll wordbreaker has been problematic at best
and at worst not very useful and for this and other reasons, MS has
developed a new word breaker (langwbrk.dll) for the WinXP and Win2003 OS
platforms. Although, I'm not sure if for the German language, if this
wordbreaker is any better, I'm sad to say.
Regards,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:5703BB7B-F1FD-43BE-BF4D-BA777577214F@.microsoft.com...
> I noticed something interesting. I get now hits with the term "oenorm",
but[vbcol=seagreen]
> the couple of incorrect hits with "oenorm*".
> regards, Gerald.
> "John Kane" wrote:
While[vbcol=seagreen]
difficult[vbcol=seagreen]
may[vbcol=seagreen]
for[vbcol=seagreen]
with[vbcol=seagreen]
like[vbcol=seagreen]
the[vbcol=seagreen]
case[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
results[vbcol=seagreen]
then[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
2002[vbcol=seagreen]
Friday, March 9, 2012
incomplete Full text index for large dbs in sql 2005?
we have created a full text catalogue on an Addresses table with exactly same
configuration on 3 databases. Two of them are on SQL 2005 and one on SQL 2000.
My Addresses table has 13 million rows and the full text was on the Full
Address column.
db is around 250GB
FT Index on SQL 2005 database was created very quickly (within 2 hours)
while SQL 2000 FT took 30 hours.
But my issue is SQL 2005 index does not contain all the words. For an
example following 2 queries gives me the exactly same number on the SQL 2000
database while on SQL 2005, first query only return 11 and the 2nd one 274
(274 is same on 2000 server/database as well)
SELECT count(*)
FROM [dbo].[Addresses]
WHERE CONTAINS(FullAddress, 'WARMINSTER')
GO
SELECT count(*)
FROM [dbo].[Addresses]
WHERE FullAddress like '%WARMINSTER%'
Is this a bug?
When i extracted the records which should have retrived in to a seperate
table (few hundred records) and created a FT index on that it works fine.
CTS DBA
I've had problems on very large databases with this. I think we were over
400,000,000 rows when we first noticed it. A reorganize would normally help.
Can you first run this query?
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
See if perhaps you are getting hits to Warministershireontheavon or
something like that. Note that most punctuation will be ignored.
If you are trully missing hits can you check the gatherer log to see if
there are any error messages?
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
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@.microsoft.com...
> we have created a full text catalogue on an Addresses table with exactly
> same
> configuration on 3 databases. Two of them are on SQL 2005 and one on SQL
> 2000.
> My Addresses table has 13 million rows and the full text was on the Full
> Address column.
> db is around 250GB
> FT Index on SQL 2005 database was created very quickly (within 2 hours)
> while SQL 2000 FT took 30 hours.
> But my issue is SQL 2005 index does not contain all the words. For an
> example following 2 queries gives me the exactly same number on the SQL
> 2000
> database while on SQL 2005, first query only return 11 and the 2nd one 274
> (274 is same on 2000 server/database as well)
> SELECT count(*)
> FROM [dbo].[Addresses]
> WHERE CONTAINS(FullAddress, 'WARMINSTER')
> GO
> SELECT count(*)
> FROM [dbo].[Addresses]
> WHERE FullAddress like '%WARMINSTER%'
> Is this a bug?
> When i extracted the records which should have retrived in to a seperate
> table (few hundred records) and created a FT index on that it works fine.
>
> --
> CTS DBA
|||Thanks Hilary,
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
The Query returns only 4 records which means still so many records missing
in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
CATALOG cat_Addresses REORGANIZE) and update you with the results when it’s
completed.
thanks
CTS DBA
"Hilary Cotter" wrote:
> I've had problems on very large databases with this. I think we were over
> 400,000,000 rows when we first noticed it. A reorganize would normally help.
> Can you first run this query?
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> See if perhaps you are getting hits to Warministershireontheavon or
> something like that. Note that most punctuation will be ignored.
> If you are trully missing hits can you check the gatherer log to see if
> there are any error messages?
>
> --
> 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
>
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@.microsoft.com...
>
>
|||Re-organize has been completed. But still no luck. And there is nothing in
the gatherer logs as well. See below from the gatherer logs for the entries
from re-organize and re-building I did today.
RE_BUILDING
2006-11-29 09:28:15.58 spid20s Informational: Full-text Full population
completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Number of documents
processed: 10038019. Number of documents failed: 0. Number of documents need
retry: 0.
2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.
2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto population
initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Population sub-tasks: 1.
RE-ORGANIZING
2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.
CTS DBA
"CTS_DBA" wrote:
[vbcol=seagreen]
> Thanks Hilary,
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> The Query returns only 4 records which means still so many records missing
> in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
> CATALOG cat_Addresses REORGANIZE) and update you with the results when it’s
> completed.
> thanks
>
> --
> CTS DBA
>
> "Hilary Cotter" wrote:
|||It looks like all the rows were successfully processed. My concern still is
that perhaps there are some preceding or trailing characters which might
account for these rows not showing up. Can you confirm this?
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
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...[vbcol=seagreen]
> Re-organize has been completed. But still no luck. And there is nothing in
> the gatherer logs as well. See below from the gatherer logs for the
> entries
> from re-organize and re-building I did today.
> RE_BUILDING
> 2006-11-29 09:28:15.58 spid20s Informational: Full-text Full
> population
> completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Number of documents
> processed: 10038019. Number of documents failed: 0. Number of documents
> need
> retry: 0.
> 2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
> 2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto
> population
> initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Population sub-tasks:
> 1.
>
>
> RE-ORGANIZING
> 2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
>
> --
> CTS DBA
>
> "CTS_DBA" wrote:
|||yep, cause as mentioned earlier same DB, sql 2000 FT returns the expected
result
the other thing I'm worried is that sql 2005 took 2 hours and 2000 took
around 30 hours, huge different, and of course servers are , you can sya same
hard ware.
CTS DBA
"Hilary Cotter" wrote:
> It looks like all the rows were successfully processed. My concern still is
> that perhaps there are some preceding or trailing characters which might
> account for these rows not showing up. Can you confirm this?
> --
> 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
>
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...
>
>
|||What if you run
SELECT fulladdress FROM [dbo].[Addresses] WHERE
FullAddress LIKE '%WARMINSTER%'
AND NOT CONTAINS(FullAddress, 'WARMINSTER')
do the fulladdress results look funny in any way, or is WARMINSTER at
the start of a word? Maybe a non-ASCII character in there somehow?
Ben Strackany
CTS_DBA wrote:[vbcol=seagreen]
> Thanks Hilary,
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> The Query returns only 4 records which means still so many records missing
> in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
> CATALOG cat_Addresses REORGANIZE) and update you with the results when it's
> completed.
> thanks
>
> --
> CTS DBA
>
> "Hilary Cotter" wrote:
|||This says everything processed successfully.
I am really not sure how to advice you on where to go from here.
One of my lingering concerns is that this query
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
returns 4 results - what are they?
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
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...[vbcol=seagreen]
> Re-organize has been completed. But still no luck. And there is nothing in
> the gatherer logs as well. See below from the gatherer logs for the
> entries
> from re-organize and re-building I did today.
> RE_BUILDING
> 2006-11-29 09:28:15.58 spid20s Informational: Full-text Full
> population
> completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Number of documents
> processed: 10038019. Number of documents failed: 0. Number of documents
> need
> retry: 0.
> 2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
> 2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto
> population
> initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Population sub-tasks:
> 1.
>
>
> RE-ORGANIZING
> 2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
>
> --
> CTS DBA
>
> "CTS_DBA" wrote:
configuration on 3 databases. Two of them are on SQL 2005 and one on SQL 2000.
My Addresses table has 13 million rows and the full text was on the Full
Address column.
db is around 250GB
FT Index on SQL 2005 database was created very quickly (within 2 hours)
while SQL 2000 FT took 30 hours.
But my issue is SQL 2005 index does not contain all the words. For an
example following 2 queries gives me the exactly same number on the SQL 2000
database while on SQL 2005, first query only return 11 and the 2nd one 274
(274 is same on 2000 server/database as well)
SELECT count(*)
FROM [dbo].[Addresses]
WHERE CONTAINS(FullAddress, 'WARMINSTER')
GO
SELECT count(*)
FROM [dbo].[Addresses]
WHERE FullAddress like '%WARMINSTER%'
Is this a bug?
When i extracted the records which should have retrived in to a seperate
table (few hundred records) and created a FT index on that it works fine.
CTS DBA
I've had problems on very large databases with this. I think we were over
400,000,000 rows when we first noticed it. A reorganize would normally help.
Can you first run this query?
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
See if perhaps you are getting hits to Warministershireontheavon or
something like that. Note that most punctuation will be ignored.
If you are trully missing hits can you check the gatherer log to see if
there are any error messages?
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
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@.microsoft.com...
> we have created a full text catalogue on an Addresses table with exactly
> same
> configuration on 3 databases. Two of them are on SQL 2005 and one on SQL
> 2000.
> My Addresses table has 13 million rows and the full text was on the Full
> Address column.
> db is around 250GB
> FT Index on SQL 2005 database was created very quickly (within 2 hours)
> while SQL 2000 FT took 30 hours.
> But my issue is SQL 2005 index does not contain all the words. For an
> example following 2 queries gives me the exactly same number on the SQL
> 2000
> database while on SQL 2005, first query only return 11 and the 2nd one 274
> (274 is same on 2000 server/database as well)
> SELECT count(*)
> FROM [dbo].[Addresses]
> WHERE CONTAINS(FullAddress, 'WARMINSTER')
> GO
> SELECT count(*)
> FROM [dbo].[Addresses]
> WHERE FullAddress like '%WARMINSTER%'
> Is this a bug?
> When i extracted the records which should have retrived in to a seperate
> table (few hundred records) and created a FT index on that it works fine.
>
> --
> CTS DBA
|||Thanks Hilary,
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
The Query returns only 4 records which means still so many records missing
in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
CATALOG cat_Addresses REORGANIZE) and update you with the results when it’s
completed.
thanks
CTS DBA
"Hilary Cotter" wrote:
> I've had problems on very large databases with this. I think we were over
> 400,000,000 rows when we first noticed it. A reorganize would normally help.
> Can you first run this query?
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> See if perhaps you are getting hits to Warministershireontheavon or
> something like that. Note that most punctuation will be ignored.
> If you are trully missing hits can you check the gatherer log to see if
> there are any error messages?
>
> --
> 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
>
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@.microsoft.com...
>
>
|||Re-organize has been completed. But still no luck. And there is nothing in
the gatherer logs as well. See below from the gatherer logs for the entries
from re-organize and re-building I did today.
RE_BUILDING
2006-11-29 09:28:15.58 spid20s Informational: Full-text Full population
completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Number of documents
processed: 10038019. Number of documents failed: 0. Number of documents need
retry: 0.
2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.
2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto population
initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Population sub-tasks: 1.
RE-ORGANIZING
2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.
CTS DBA
"CTS_DBA" wrote:
[vbcol=seagreen]
> Thanks Hilary,
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> The Query returns only 4 records which means still so many records missing
> in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
> CATALOG cat_Addresses REORGANIZE) and update you with the results when it’s
> completed.
> thanks
>
> --
> CTS DBA
>
> "Hilary Cotter" wrote:
|||It looks like all the rows were successfully processed. My concern still is
that perhaps there are some preceding or trailing characters which might
account for these rows not showing up. Can you confirm this?
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
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...[vbcol=seagreen]
> Re-organize has been completed. But still no luck. And there is nothing in
> the gatherer logs as well. See below from the gatherer logs for the
> entries
> from re-organize and re-building I did today.
> RE_BUILDING
> 2006-11-29 09:28:15.58 spid20s Informational: Full-text Full
> population
> completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Number of documents
> processed: 10038019. Number of documents failed: 0. Number of documents
> need
> retry: 0.
> 2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
> 2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto
> population
> initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Population sub-tasks:
> 1.
>
>
> RE-ORGANIZING
> 2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
>
> --
> CTS DBA
>
> "CTS_DBA" wrote:
|||yep, cause as mentioned earlier same DB, sql 2000 FT returns the expected
result
the other thing I'm worried is that sql 2005 took 2 hours and 2000 took
around 30 hours, huge different, and of course servers are , you can sya same
hard ware.
CTS DBA
"Hilary Cotter" wrote:
> It looks like all the rows were successfully processed. My concern still is
> that perhaps there are some preceding or trailing characters which might
> account for these rows not showing up. Can you confirm this?
> --
> 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
>
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...
>
>
|||What if you run
SELECT fulladdress FROM [dbo].[Addresses] WHERE
FullAddress LIKE '%WARMINSTER%'
AND NOT CONTAINS(FullAddress, 'WARMINSTER')
do the fulladdress results look funny in any way, or is WARMINSTER at
the start of a word? Maybe a non-ASCII character in there somehow?
Ben Strackany
CTS_DBA wrote:[vbcol=seagreen]
> Thanks Hilary,
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> The Query returns only 4 records which means still so many records missing
> in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
> CATALOG cat_Addresses REORGANIZE) and update you with the results when it's
> completed.
> thanks
>
> --
> CTS DBA
>
> "Hilary Cotter" wrote:
|||This says everything processed successfully.
I am really not sure how to advice you on where to go from here.
One of my lingering concerns is that this query
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
returns 4 results - what are they?
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
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...[vbcol=seagreen]
> Re-organize has been completed. But still no luck. And there is nothing in
> the gatherer logs as well. See below from the gatherer logs for the
> entries
> from re-organize and re-building I did today.
> RE_BUILDING
> 2006-11-29 09:28:15.58 spid20s Informational: Full-text Full
> population
> completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Number of documents
> processed: 10038019. Number of documents failed: 0. Number of documents
> need
> retry: 0.
> 2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
> 2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto
> population
> initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Population sub-tasks:
> 1.
>
>
> RE-ORGANIZING
> 2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
>
> --
> CTS DBA
>
> "CTS_DBA" wrote:
Incompatible data types in SQL EXP 2005
I'm trying to read a data table with all text fields using C# in ado.net. However, when my query runs, it always returns this error:
"The data types text and varchar are incompatible in the equal to operator. "
The SQL statment is "SELECT field1, field2 FROM table WHERE field1 = 'value'"
If I leave out the WHERE clause, it runs fine. There are NO varchar fields in my entire table (It's a test table). I've tried using both parameter objects and Convert (See commented section) with no luck. A packet trace shows the SQL string is being delivered in tact and the SQL server is returning the error. I don't know if it's a C# client issue, or a SQL Server 2005 Exp issue. The table was created using MS SQL Server Management Studio Express I'm currently using the SqlClient object like this:
<script runat="server">
DataSet dsData = new DataSet();
DataTable dtData = new DataTable();
void Page_Load()
{
string strConn = ConfigurationSettings.AppSettings["authstr"];
string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";
//string strSQL = "SELECT password_fld, pharmacy_fld FROM Esker_Tbl WHERE CONVERT(text, username_fld ) = '@.prmuser'";
//SqlParameter spuser = new SqlParameter("@.prmuser", SqlDbType.Text);
//string struser = "bobhope";
//spuser.Value = struser;
try
{
SqlConnection Conn = new SqlConnection(strConn);
SqlCommand Cmd = new SqlCommand(strSQL, Conn);
SqlDataAdapter Adp = new SqlDataAdapter(Cmd);
Conn.Open();
Adp.Fill(dsData, "USERS1");
dtData = dsData.Tables["USERS1"];
dgData.DataSource = dtData;
dgData.DataBind();
dgData.Visible = true;
}
catch (Exception ex)
{
string strmessage = "";
strmessage = "Unable to access database: " + ex.Message;
lblMessage.Text = strmessage;
}
return;
}
Have you pulled out the query and tried to run it in Management Studio to break out the conversion type differences? I don't have your data structure so I'm not sure what the issue is.
If you're trying to compare two text data types, you should use the CONTAINS or other search parameters. In fact, the TEXT data type is being phased out in favor of nvarchar(max), which can hold gigs of data per row. Check out this article for more:
http://msdn2.microsoft.com/en-us/library/ms187993.aspx
|||Hi Mark:
Not sure if this is an error caused by copy-paste the code into the forum, but if the code sample you provided here is correct, then it seems like you should've seen a compile error here:
string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";
There is an extra " .
if that's an copy-paste typo, could you please provide what is the data type declared for User_ID in User_ID table?
thanks
Incompatible data types in SQL EXP 2005
I'm trying to read a data table with all text fields using C# in ado.net. However, when my query runs, it always returns this error:
"The data types text and varchar are incompatible in the equal to operator. "
The SQL statment is "SELECT field1, field2 FROM table WHERE field1 = 'value'"
If I leave out the WHERE clause, it runs fine. There are NO varchar fields in my entire table (It's a test table). I've tried using both parameter objects and Convert (See commented section) with no luck. A packet trace shows the SQL string is being delivered in tact and the SQL server is returning the error. I don't know if it's a C# client issue, or a SQL Server 2005 Exp issue. The table was created using MS SQL Server Management Studio Express I'm currently using the SqlClient object like this:
<script runat="server">
DataSet dsData = new DataSet();
DataTable dtData = new DataTable();
void Page_Load()
{
string strConn = ConfigurationSettings.AppSettings["authstr"];
string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";
//string strSQL = "SELECT password_fld, pharmacy_fld FROM Esker_Tbl WHERE CONVERT(text, username_fld ) = '@.prmuser'";
//SqlParameter spuser = new SqlParameter("@.prmuser", SqlDbType.Text);
//string struser = "bobhope";
//spuser.Value = struser;
try
{
SqlConnection Conn = new SqlConnection(strConn);
SqlCommand Cmd = new SqlCommand(strSQL, Conn);
SqlDataAdapter Adp = new SqlDataAdapter(Cmd);
Conn.Open();
Adp.Fill(dsData, "USERS1");
dtData = dsData.Tables["USERS1"];
dgData.DataSource = dtData;
dgData.DataBind();
dgData.Visible = true;
}
catch (Exception ex)
{
string strmessage = "";
strmessage = "Unable to access database: " + ex.Message;
lblMessage.Text = strmessage;
}
return;
}
Have you pulled out the query and tried to run it in Management Studio to break out the conversion type differences? I don't have your data structure so I'm not sure what the issue is.
If you're trying to compare two text data types, you should use the CONTAINS or other search parameters. In fact, the TEXT data type is being phased out in favor of nvarchar(max), which can hold gigs of data per row. Check out this article for more:
http://msdn2.microsoft.com/en-us/library/ms187993.aspx
|||Hi Mark:
Not sure if this is an error caused by copy-paste the code into the forum, but if the code sample you provided here is correct, then it seems like you should've seen a compile error here:
string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";
There is an extra " .
if that's an copy-paste typo, could you please provide what is the data type declared for User_ID in User_ID table?
thanks
Friday, February 24, 2012
Inaccurate HTML Rendering
(RS 2000)
I have 6 rows of text boxes in my body header running the width of the
report. All look fine in the report designer and when rendered to Adobe.
When I output to HTML, there are an extra 2 blank rows between the 5 and 6
TB. All the TB properties are exactly the same; I have tried
deleting/recreating, shifting around, nothing seems to help.
Thanks for any suggestionsFor anyone else that has the same problem I ran into where the HTML output
looks different than the designer or other output: I found if I slightly
reduced the vertical height of all the text boxes it fixed the problem.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23zD8P27TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> (RS 2000)
> I have 6 rows of text boxes in my body header running the width of the
> report. All look fine in the report designer and when rendered to Adobe.
> When I output to HTML, there are an extra 2 blank rows between the 5 and 6
> TB. All the TB properties are exactly the same; I have tried
> deleting/recreating, shifting around, nothing seems to help.
> Thanks for any suggestions
>
I have 6 rows of text boxes in my body header running the width of the
report. All look fine in the report designer and when rendered to Adobe.
When I output to HTML, there are an extra 2 blank rows between the 5 and 6
TB. All the TB properties are exactly the same; I have tried
deleting/recreating, shifting around, nothing seems to help.
Thanks for any suggestionsFor anyone else that has the same problem I ran into where the HTML output
looks different than the designer or other output: I found if I slightly
reduced the vertical height of all the text boxes it fixed the problem.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23zD8P27TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> (RS 2000)
> I have 6 rows of text boxes in my body header running the width of the
> report. All look fine in the report designer and when rendered to Adobe.
> When I output to HTML, there are an extra 2 blank rows between the 5 and 6
> TB. All the TB properties are exactly the same; I have tried
> deleting/recreating, shifting around, nothing seems to help.
> Thanks for any suggestions
>
Sunday, February 19, 2012
IN SQL SERVER BCP
Hi,
I have to insert a data from text file to Sql Server Using BULK INSERT ie BCP. Throgh qurey wise it execute fine.
But using stored procedure it does not work..here just i have to pass 1 parameter ie file name. The err is
" Could not bulk insert. File '@.BasicFile' does not exist." How can i solve the prolblem ?
Thanks and Regards,
ArulIt looks like you are trying to use a variable filename for your source file. As far as I know, you will need to use dynamic SQL to accomplish this.
SET @.SQL = "BULK INSERT myTable '"+@.PathFileName+"' WITH (FIELDTERMINATOR = ',') "
EXEC (@.SQL)
Terri
Subscribe to:
Posts (Atom)