is it possible to put the raiseerror in a text file or show it to the user after its been

I am in process of building a website where the user can upload files and then those files are loaded in to a sql server database. I am using some sprocs to scrub the data and then insert them into the production database.. And in my sprpc before and after updating or inserting a record or scrubing... i am returning the count by raising an error. or returning the rownumber where the error occured.. is there any way i can get the raise error part or whatever error i get while scrubing the data and relay it back to the user in a user freindly way or in a text file... or the best thing is can i open smalll window where i can show them what processing is goin on and alert them if there are any errors...

Any help will be appreciated.

Regards

Karen

Take a look forSqlException.Errors. Any SqlError object has the number of error and another informations.

PS.: To another databases, take a look forOleDbException.Errors.

|||

Hi Karenros,

Based on my understanding, you want to use Raiserror to generate an error message and pass it to the client user. Client user may get an alerting message or write it to a text file when getting the error message. If I've misunderstood you ,please feel free to tell me, thanks.

You can put your sqlcommand in a try block and in your catch block, write sqlconnection.errors.message to a text file. Please remember do not assign the severity value of your error message more than 19, or else it maybe cause terminate your connection. Sample code is like the following:

 try { con.Open(); cmd.ExecuteNonQuery(); }catch (SqlException ex) {using(StreamWriter sw=new StreamWriter("your text log file path here")) {foreach (SqlError errin ex.Errors) sw.WriteLine(err.Message+"\n"); } }

Hope my suggestion helps

|||

Chen,

Thanks for your answer. yeah and thats exactly that i wanted to do... so that user would know if the import process was successful or not...

I have tried using sqlexception before with no luck.. may be i didnt import the right header files in order for that work and i have also seen on msdn that we need a sqlinfomessage class or something like to do it.. Pls correct me if i am wrong...

anyways i am gonna give it a try and will let you know...

Regards

Karen

|||

Below is an example of how you can use the InfoMessage event handler:

First you'll have to create an event handler for this event like below:

con.Open();
con.InfoMessage +=new SqlInfoMessageEventHandler(con_InfoMessage);// here i've registered for the event
... set up the command object
cmd.NotificationAutoEnlist =true;
cmd.ExecuteNonQuery();

Then you can go on and write any code you want in that event handler method.

private void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
// your file writing code goes here. The eventArgs e holds errors, messages, source etc.
 // you can just use e.ToString() and everything is there for you.
}

Hope this will help.

|||

Hi Karenros,

I've tested the sqlexception code on my local machine and it does work fine. So, maybe you have made some mistakes somewhere else.

However, I think you can also trydhimant 's solution. That's really a good method to solve your problem. thanks

Is it possible to perform terms lookup on unstructured files ?

Hi,
I need to categorize a lot of html or text files according to a list of terms and I wonder if terms lookup is adequate for this. The problem is that terms lookup can only take an Oledb source as input. My files can be up to 80 Kb big and aren't columns structured.

Should I import my files in a table ? But if so, how can I import a column with more than 8000 characters ?

Thank you in advance.

I think you may have this the wrong way around. The list of terms must be stored in an OLE-DB sourced table, but the input is the data you want to examine. This can come from any upstream component. You will still need to get your data into the pipeline, but that is perhaps not quite as hard as OLE-DB. Maybe the Import Column Transform could help?

You mention a 8000 character limit, which is the limit for non-unicode strings in the varchar (T-SQL) or DT_STR (SSIS) data types. Whilst the Term transformations only support unicode data types, with their 4000 character limit, they do support the DT_NTEXT type, equivalent to the T-SQL ntext type, which allows up to 2GB of data.

|||Thank you very much for your quick reply. My mistake, you're right, I'm a new user of SSIS and I misunderstood the explanations on the lookup. I'm digging into this. Thanks again for your help.

Is it possible to output a Field Value based on another field value?

I would like the data value of a text field in a table to based on the
value of another field (also used as a parameter). However, if I try
switch or iif, I get an 'expression expected' error.
For example, if car type is 'used' then output the 'salvage value'
data field. If car type is 'new' output 'retail value' data field.
Any suggestions?You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
>= .5, "Amber", "Red"))a.. The following expression also returns one of
three values based on the value of PctComplete, but uses the Switch function
instead, which returns the value associated with the first expression in a
series that evaluates to true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value >=.5, "Amber", Fields!PctComplete.Value < .5, "Red")In the example it is
setting the color but it could just be a value in the table. If it said it
expected an expression my guess is that you did not have the equal sign.--
Bruce Loehle-Conger MVP SQL Server Reporting Services"ChrisL"
<chrispycrunch@.gmail.com> wrote in message
news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> I would like the data value of a text field in a table to based on the
> value of another field (also used as a parameter). However, if I try
> switch or iif, I get an 'expression expected' error.
> For example, if car type is 'used' then output the 'salvage value'
> data field. If car type is 'new' output 'retail value' data field.
> Any suggestions?|||Sorry for the crummy formatting. I copied and pasted in from Books On-Line.
This should look better.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eehnli2PFHA.648@.TK2MSFTNGP14.phx.gbl...
You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
= .5, "Amber", "Red"))
The following expression also returns one of three values based on the
value of PctComplete, but uses the Switch function instead, which returns
the value associated with the first expression in a series that evaluates to
true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value
>=> .5, "Amber", Fields!PctComplete.Value < .5, "Red")
In the example it is setting the color but it could just be a value in the
table. If it said it expected an expression my guess is that you did not
have the equal sign.
Bruce Loehle-Conger MVP SQL Server Reporting Services
>>"ChrisL" <chrispycrunch@.gmail.com> wrote in message
> news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> > I would like the data value of a text field in a table to based on the
> > value of another field (also used as a parameter). However, if I try
> > switch or iif, I get an 'expression expected' error.
> >
> > For example, if car type is 'used' then output the 'salvage value'
> > data field. If car type is 'new' output 'retail value' data field.
> >
> > Any suggestions?
>

Is it possible to make INSERT/UPDATE operation in SSIS?

Hi!
I use SSIS to insert some data from text sources to SQL server 2005. I use
check constraints option. Is it possible if iserted record has the same
primary key as existing record in table to replace existing record? How to
make it?
Thank you
Igor A. ChechetIgor
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM Table WHERE id=@.id)
BEGIN
UPDATE Table SET col=...,col2...c,ol3=... WHERE id=@.id
END
ELSE
BEGIN
INSERT INTO Table (cols here) VALUES (here)
END
COMMIT TRANSACTION
"Igor A. Chechet" <ichechet@.mail.ru> wrote in message
news:uVHJyuxpGHA.1440@.TK2MSFTNGP03.phx.gbl...
> Hi!
> I use SSIS to insert some data from text sources to SQL server 2005. I use
> check constraints option. Is it possible if iserted record has the same
> primary key as existing record in table to replace existing record? How to
> make it?
> Thank you
> Igor A. Chechet
>|||Igor,
First better to import to a staging table all the records .
You can write a query which checks the existence of a record on Primary
Key
UPDATE TABLE SET COL1= STAGING.A1,
COL2 = STAGING.COL2
...
...
FROM TABLE , STAGING
WHERE TABLE.PK - STAGING.PK
INSERT INTO TABLE
SELECT * FROM STAGING A
WHERE NOT EXISTS ( SELECT 1 FROM TABLE B WHERE B.PK =A.PK)
Note: PK is PRIMARY KEY
M A Srinivas
Igor A. Chechet wrote:
> Hi!
> I use SSIS to insert some data from text sources to SQL server 2005. I use
> check constraints option. Is it possible if iserted record has the same
> primary key as existing record in table to replace existing record? How to
> make it?
> Thank you
> Igor A. Chechet|||There's no need to drop to an intermediary table. You can do this in the
pipeline.
Here's how: http://www.sqlis.com/default.aspx?311
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamiethomson/
<masri999@.gmail.com> wrote in message
news:1152880213.339951.42940@.i42g2000cwa.googlegroups.com...
> Igor,
> First better to import to a staging table all the records .
> You can write a query which checks the existence of a record on Primary
> Key
> UPDATE TABLE SET COL1= STAGING.A1,
> COL2 = STAGING.COL2
> ...
> ...
> FROM TABLE , STAGING
> WHERE TABLE.PK - STAGING.PK
> INSERT INTO TABLE
> SELECT * FROM STAGING A
> WHERE NOT EXISTS ( SELECT 1 FROM TABLE B WHERE B.PK =A.PK)
> Note: PK is PRIMARY KEY
> M A Srinivas
>
>
> Igor A. Chechet wrote:
>

Is it possible to make INSERT/UPDATE operation in SSIS?

Hi!
I use SSIS to insert some data from text sources to SQL server 2005. I use
check constraints option. Is it possible if iserted record has the same
primary key as existing record in table to replace existing record? How to
make it?
Thank you
Igor A. ChechetIgor
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM Table WHERE id=@.id)
BEGIN
UPDATE Table SET col=...,col2...c,ol3=... WHERE id=@.id
END
ELSE
BEGIN
INSERT INTO Table (cols here) VALUES (here)
END
COMMIT TRANSACTION
"Igor A. Chechet" <ichechet@.mail.ru> wrote in message
news:uVHJyuxpGHA.1440@.TK2MSFTNGP03.phx.gbl...
> Hi!
> I use SSIS to insert some data from text sources to SQL server 2005. I use
> check constraints option. Is it possible if iserted record has the same
> primary key as existing record in table to replace existing record? How to
> make it?
> Thank you
> Igor A. Chechet
>|||Igor,
First better to import to a staging table all the records .
You can write a query which checks the existence of a record on Primary
Key
UPDATE TABLE SET COL1= STAGING.A1,
COL2 = STAGING.COL2
...
...
FROM TABLE , STAGING
WHERE TABLE.PK - STAGING.PK
INSERT INTO TABLE
SELECT * FROM STAGING A
WHERE NOT EXISTS ( SELECT 1 FROM TABLE B WHERE B.PK =A.PK)
Note: PK is PRIMARY KEY
M A Srinivas
Igor A. Chechet wrote:
> Hi!
> I use SSIS to insert some data from text sources to SQL server 2005. I use
> check constraints option. Is it possible if iserted record has the same
> primary key as existing record in table to replace existing record? How to
> make it?
> Thank you
> Igor A. Chechet|||There's no need to drop to an intermediary table. You can do this in the
pipeline.
Here's how: http://www.sqlis.com/default.aspx?311
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamiethomson/
<masri999@.gmail.com> wrote in message
news:1152880213.339951.42940@.i42g2000cwa.googlegroups.com...
> Igor,
> First better to import to a staging table all the records .
> You can write a query which checks the existence of a record on Primary
> Key
> UPDATE TABLE SET COL1= STAGING.A1,
> COL2 = STAGING.COL2
> ...
> ...
> FROM TABLE , STAGING
> WHERE TABLE.PK - STAGING.PK
> INSERT INTO TABLE
> SELECT * FROM STAGING A
> WHERE NOT EXISTS ( SELECT 1 FROM TABLE B WHERE B.PK =A.PK)
> Note: PK is PRIMARY KEY
> M A Srinivas
>
>
> Igor A. Chechet wrote:
>> Hi!
>> I use SSIS to insert some data from text sources to SQL server 2005. I
>> use
>> check constraints option. Is it possible if iserted record has the same
>> primary key as existing record in table to replace existing record? How
>> to
>> make it?
>> Thank you
>> Igor A. Chechet
>

Is it possible to get the max length of a TEXT field?

I have a text field and want to know if any of the text exceeds 10,000 characters
I can do a select max(len(rtrim(convert(varchar(8000)))) on the field but I'm not able to do for more than 8000 and you can't manipulate TEXT datay type.
Any ideas?
Thanks!SELECT DATALENGTH(Col1) FROM myTable99

Is it possible to format (Text) in this way

hi,

i want to display something is this fashion

Roth 401(k) Contribution

jfkajfkdjfdjfkdj ldkfdlkfl;dkfldkfld;fkdl

in a table row, but i am not sure how to do it. can some one pls give me some ideas.

Regards,

Karen

If I understand correctly you want the ability to display bold and non-bold text in the same text box?
km
|||yeah thats true|||

I don't think different font properties are possible in a single textbox. Wat you can try doing is put two textboxes (one with Bold font and the other without, as you require) one below the other inside a rectangle and place the rectangle in your Table Row.

-Aayush

|||thanks a lot it works now.

is it possible to do this?

Hi,

I have a table with 2 columns. If the condition for that text box evaluates to true i want to hide the second column and make the first columns width equal to the First and the second... for example this.

Company Match

Years of service Vesting %

its gonna display like this if the condition is false..

if true i want it to display

Company Match

Immediately Eligible (this should be in the middle) right now i can display it like this

Company Match

Immediately elgigible..

Any help will be appreciated.

Regards

Karen

Instead of hiding a column, why dont you use two asp:label and Hide the other if your condition matches. That makes your life more easier.

|||

i want to do this in the report itself?

Regards

Karen

|||

Each colum is act as a textbox

Go to its properties -> Visiblity-> Hidden

The value for Hidden can be an expression like " = iif(CDec(Parameters!xxxVal.Value) > 0.00,false,true)"

|||

Instead of hiding a column, why dont you use two asp:label and Hide theother if your condition matches. That makes your life more easier.

|||

how can i use asp:Label in SSRS? Never mind guys i solved it

Karen

sql

Is it possible to create a fixed column report?

I was just wondering if it was possible to create a fixed column report that would export to a text file as dat file to be consumed by an outside process. By this I mean that my first column would be characters 1 to 8, column 2 would be 8 to 20, column 3 would be 21 to 40, etc...

If so, how would you do it?

Thanks in advance.

David,

I had to do this for a report. I generated an XML file and applied an XSL translation to get it to fixed field ASCII. Not real difficult, but it required a second step. Don't forget to change the mime output type. I saw somewhere that you can specify the XSL file to apply after exporting to XML within Reporting Services,I just don't remember where.

R

Is it Possible to Concatenate a Text Column of a Table

Hi All,
I have a table in which one of the columns is Text.
I need to concatenate a hard coded text with the value of the text column
for each and every row of the table. Is this possible.
Sevugan.CI don't understand why you need to concatenate anything, *especially* the
same value for *every row.*
Can't the consuming application do that?
If you aren't storing > 8000 characters, then you could say:
DECLARE @.constant VARCHAR(12);
SET @.constant = 'some prefix';
SELECT @.constant + CONVERT(VARCHAR(7988), textColumn) FROM table;
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:A1A3FE64-4D11-4217-97AC-A8A6A380F355@.microsoft.com...
> Hi All,
> I have a table in which one of the columns is Text.
> I need to concatenate a hard coded text with the value of the text column
> for each and every row of the table. Is this possible.
>
> --
> Sevugan.C|||Hi
Thanks for your reply.
I am storing more than 8000 chars in the text column. Then, how this could
be resolved.
Sevugan.C
"Aaron Bertrand [SQL Server MVP]" wrote:

> I don't understand why you need to concatenate anything, *especially* the
> same value for *every row.*
> Can't the consuming application do that?
> If you aren't storing > 8000 characters, then you could say:
> DECLARE @.constant VARCHAR(12);
> SET @.constant = 'some prefix';
> SELECT @.constant + CONVERT(VARCHAR(7988), textColumn) FROM table;
>
>
> "Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
> news:A1A3FE64-4D11-4217-97AC-A8A6A380F355@.microsoft.com...
>
>|||> I am storing more than 8000 chars in the text column. Then, how this could
> be resolved.
Some options:
(a) have the consuming application perform the concatenation
(b) move to SQL Server 2005, where you can use VARCHAR(MAX)
(c) store your redundant prefix with the data (you can use UPDATETEXT for
that)|||Hi
I am using the third option suggested by You. But, I am getting the
following error.
What could be the reason. How it should be resolved.
Server: Msg 7123, Level 16, State 1, Procedure sp_SendStockRebalanceMails,
Line 100
Invalid text, ntext, or image pointer value
0x0100010000000000331C2F0C00000000.
The statement has been terminated.
--
Sevugan.C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Some options:
> (a) have the consuming application perform the concatenation
> (b) move to SQL Server 2005, where you can use VARCHAR(MAX)
> (c) store your redundant prefix with the data (you can use UPDATETEXT for
> that)
>
>|||> I am using the third option suggested by You. But, I am getting the
> following error.
> What could be the reason. How it should be resolved.
Who knows? You forgot to include DDL, sample data, and the code in your
procedure.
http://www.aspfaq.com/5006|||> I am using the third option suggested by You. But, I am getting the
> following error.
> What could be the reason. How it should be resolved.
Who knows? You forgot to include DDL, sample data, and the code in your
procedure.
http://www.aspfaq.com/5006

Is it possible to "decorate" rendering?

This is a multi-part message in MIME format.
--=_NextPart_000_0024_01C4B3A9.116B5BF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I was curious if was possible (or someone has at least attempted this) = to hook into the rendering mechanism. I'm interested especially in = being able to do this with charts.
--
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874
--=_NextPart_000_0024_01C4B3A9.116B5BF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I was curious if was possible (or = someone has at least attempted this) to hook into the rendering mechanism. I'm = interested especially in being able to do this with charts.
-- Regards,

Tim Ellison, MCPIronworks Consulting, LLC(m) 804.405.4874
--=_NextPart_000_0024_01C4B3A9.116B5BF0--That's not supported in RS 2000.
There will be options for doing this in charts in future releases.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TIM ELLISON" <TimEllison@.direcway.com> wrote in message
news:O8Tsiq8sEHA.820@.TK2MSFTNGP12.phx.gbl...
I was curious if was possible (or someone has at least attempted this) to
hook into the rendering mechanism. I'm interested especially in being able
to do this with charts.
--
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874|||Thanks. I was looking for a way to hook into the event model to do this but
actually came up with a neat way to "sort of" reproduce it with a finite
list (i.e., all columns known beforehand). I am definitely on the RS
bandwagon. I worked with it when it was bits and it was solid then. This
is perhaps one of the best V1 products I've worked with in my 12 years. The
documentation is even good :)..
--
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eUQnp$9sEHA.2316@.TK2MSFTNGP12.phx.gbl...
> That's not supported in RS 2000.
> There will be options for doing this in charts in future releases.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "TIM ELLISON" <TimEllison@.direcway.com> wrote in message
> news:O8Tsiq8sEHA.820@.TK2MSFTNGP12.phx.gbl...
> I was curious if was possible (or someone has at least attempted this) to
> hook into the rendering mechanism. I'm interested especially in being
able
> to do this with charts.
> --
> Regards,
> Tim Ellison, MCP
> Ironworks Consulting, LLC
> (m) 804.405.4874
>

is it posible to put more than 4000 bytes into one column? ( sql server mobile )

varchar can only hold 4000 bytes
and there is no text column in sql server mobile

Hi,
try 'ntext'

Pete

|||I'm using SQL CE 3.5 with VS 2008 Beta 2:

I have a table with some coulmns of NTEXT type, but when i want to update my dataset to database with a row which has that field more than 4000 charachters I got this error:

"InvalidOperationException was unhandled
@.p4 : String truncation: max=4000, len=4374
...."

Regards,
Parham.
|||NTEXT should accept 536870911 charachters! But why iam getting that Error?!
|||

It is probably a problem with the DataSet designer. Check the designer generated code, and you may find that it has limited the @.p4 length to 4000. You can probably manually change this.

|||I'm having the same issue. I checked through the designer code and found the max length set to the correct length for ntext (536870911). Just to be sure I recreated the database and the dataset, but got the same result. I think this might be a genuine bug.

is it posible to put more than 4000 bytes into one column? ( sql server mobile )

varchar can only hold 4000 bytes
and there is no text column in sql server mobile

Hi,
try 'ntext'

Pete

|||I'm using SQL CE 3.5 with VS 2008 Beta 2:

I have a table with some coulmns of NTEXT type, but when i want to update my dataset to database with a row which has that field more than 4000 charachters I got this error:

"InvalidOperationException was unhandled
@.p4 : String truncation: max=4000, len=4374
...."

Regards,
Parham.
|||NTEXT should accept 536870911 charachters! But why iam getting that Error?!
|||

It is probably a problem with the DataSet designer. Check the designer generated code, and you may find that it has limited the @.p4 length to 4000. You can probably manually change this.

|||I'm having the same issue. I checked through the designer code and found the max length set to the correct length for ntext (536870911). Just to be sure I recreated the database and the dataset, but got the same result. I think this might be a genuine bug.

is it posible to put more than 4000 bytes into one column? ( sql server mobile )

varchar can only hold 4000 bytes
and there is no text column in sql server mobile

Hi,
try 'ntext'

Pete

|||I'm using SQL CE 3.5 with VS 2008 Beta 2:

I have a table with some coulmns of NTEXT type, but when i want to update my dataset to database with a row which has that field more than 4000 charachters I got this error:

"InvalidOperationException was unhandled
@.p4 : String truncation: max=4000, len=4374
...."

Regards,
Parham.
|||NTEXT should accept 536870911 charachters! But why iam getting that Error?!
|||

It is probably a problem with the DataSet designer. Check the designer generated code, and you may find that it has limited the @.p4 length to 4000. You can probably manually change this.

|||I'm having the same issue. I checked through the designer code and found the max length set to the correct length for ntext (536870911). Just to be sure I recreated the database and the dataset, but got the same result. I think this might be a genuine bug.

is it posible to put more than 4000 bytes into one column? ( sql server mobile )

varchar can only hold 4000 bytes
and there is no text column in sql server mobile

Hi,
try 'ntext'

Pete

Is it neccesary to restart sql server engine after modifying a noise word list?

Hello all,

We are using sql server 2005 for full text searching.

I removed some of the words in the noise word file (noiseENU.txt) and rebuilt the catalog. However i find that the changes made to the noise file do not reflect immediately. I had to restart sql server engine before my queries returned results according to the updated noise list. Is there a workaround for this ( wherein there isnt the neccesity of restarting sql server engine....this is becoming a problem on live environments as i cannot restart the server when needed)?

Thanks in advance,

Harish

In fact you will only have to start the FTS again to reflect the changes. There is no (even no one to me) known way around this.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thank You.

But i had tried restarting just the FTS service; didn't work out for me until i restarted the sql server engine again

Is Full Text Struggling...

H there,
We have a query that is taking too long to run, which uses Full Text
(MSSQL2000).
The query below, when using this clause takes between 11 seconds and 10
minutes! QA thnks that the full text search will cost 70% of the query cost.
CONTAINS( Article_text, '("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR
("CJD") OR ("E Coli") OR ("E-Coli") OR ("Food Additives") OR ("Food Safety")
OR ("Genetically Modified Foods") OR ("Kwik Save") OR ("Proof of age card")
OR ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews") AND NOT ("Ahold")
AND NOT ("Beth Israel") AND NOT ("European equity preview") AND NOT
("European stocks may decline") AND NOT ("European stocks may rise") AND NOT
("mediaplex") AND NOT ("UK Stocks Factors")'))
The query below, when using this clause, takes only between 0.02 secs and 2
secs. QA thnks that the full text search will cost 50% of the query cost.
CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
"culture") OR ("alcopops" AND "designated driver initiative") OR ("alcopops"
AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
"price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
driving")')
Why is the second query instant and the first taking ages?
Sometimes, the longer queries such as the first one here can take *much*
longer to run (like, 5 minutes). We're hoping that we can get even the
queries with more expressions to run inside a few seconds. In fact, they did
when we had < 400,000 rows.
Is it possible that our full text indexing is just set up wrong, or that our
hardware isn't sufficient?
Some additional facts that may help...
The table (and FT index) only have 800,000 rows
We run 116 of these queries in a row, directly after each other.
We've cleared out all stop words, since we want to index on anything.
The server has 1GB RAM, single P4 processor, 8GB free space across 2 raid
disks.
Whilst the queries are running, I'm not seeing massive memory use.
Any help much appreciated. Please let me know if you need more info.
Tobes
I would suspect its all the search arguments and Boolean logic you have
which is causing the problems especially the AND NOTs.
Note that this
CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
"culture") OR ("alcopops" AND "designated driver initiative") OR
("alcopops"
AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
"price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
driving")')
is equivalent to the simpler
CONTAINS( Article_text, '"alcopops" AND ("advertising" OR "culture" OR
"designated driver initiative" OR "drink driving" OR "legislation" OR
"price" OR "pricing" OR "underage driving")')
Revisting the AND NOTs, basically the way this is processed is all matches
are returned for the first part
("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR ("CJD") OR ("E Coli") OR
("E-Coli") OR ("Food Additives") OR ("Food Safety") OR ("Genetically
Modified Foods") OR ("Kwik Save") OR ("Proof of age card") OR
("Somerfield") OR ("Supermarkets") OR ("Wine Reviews")
and then you trim rows which contain
AND NOT ("Ahold") AND NOT ("Beth Israel") AND NOT ("European equity
preview") AND NOT ("European stocks may decline") AND NOT ("European stocks
may rise") AND NOT
("mediaplex") AND NOT ("UK Stocks Factors")'))
This trimming is very expensive.
One thing you might do is sp_fulltext_service 'resource_usage' to 5. This
might help slightly.
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
"Tobin Harris" <tobin@._do_not_spam_tobinharris.com> wrote in message
news:42af07ca$0$2588$da0feed9@.news.zen.co.uk...
> H there,
> We have a query that is taking too long to run, which uses Full Text
> (MSSQL2000).
> The query below, when using this clause takes between 11 seconds and 10
> minutes! QA thnks that the full text search will cost 70% of the query
cost.
> CONTAINS( Article_text, '("Food" AND "Supermarkets") OR ("CITIZEN CARD")
OR
> ("CJD") OR ("E Coli") OR ("E-Coli") OR ("Food Additives") OR ("Food
Safety")
> OR ("Genetically Modified Foods") OR ("Kwik Save") OR ("Proof of age
card")
> OR ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews") AND NOT
("Ahold")
> AND NOT ("Beth Israel") AND NOT ("European equity preview") AND NOT
> ("European stocks may decline") AND NOT ("European stocks may rise") AND
NOT
> ("mediaplex") AND NOT ("UK Stocks Factors")'))
> The query below, when using this clause, takes only between 0.02 secs and
2
> secs. QA thnks that the full text search will cost 50% of the query cost.
> CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
> "culture") OR ("alcopops" AND "designated driver initiative") OR
("alcopops"
> AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
> "price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
> driving")')
> Why is the second query instant and the first taking ages?
> Sometimes, the longer queries such as the first one here can take *much*
> longer to run (like, 5 minutes). We're hoping that we can get even the
> queries with more expressions to run inside a few seconds. In fact, they
did
> when we had < 400,000 rows.
> Is it possible that our full text indexing is just set up wrong, or that
our
> hardware isn't sufficient?
> Some additional facts that may help...
> The table (and FT index) only have 800,000 rows
> We run 116 of these queries in a row, directly after each other.
> We've cleared out all stop words, since we want to index on anything.
> The server has 1GB RAM, single P4 processor, 8GB free space across 2 raid
> disks.
> Whilst the queries are running, I'm not seeing massive memory use.
> Any help much appreciated. Please let me know if you need more info.
> Tobes
>
>
|||Hi Hilary,
Thank you for the reply. Yes, the queries run significantly quicker without
the NOTs. I had tried the other boolen expressions in a more compact form,
but that made little difference unfortunately. I guess the full text search
engine may do it's own optimising to clean up our verbose queries!
In two weeks we'll be throwing more hardware at the program (doubling ram,
increasing disk capacity, and introducing dual Xeon processors), so
hopefully that will make the situation better.
Our main problem is that we want scalability. At the moment we have 116
"projects", each with their own queries that run one by one. These are
taking hours to run (some queries quick, some looooong!). Do you think we
may benefit from running more than one query in parrallel? For example, have
two processes executing 58 queries each?
Thanks again for your help.
Tobes
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ex7e8bQcFHA.720@.TK2MSFTNGP15.phx.gbl...
>I would suspect its all the search arguments and Boolean logic you have
> which is causing the problems especially the AND NOTs.
> Note that this
> CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
> "culture") OR ("alcopops" AND "designated driver initiative") OR
> ("alcopops"
> AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
> "price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
> driving")')
> is equivalent to the simpler
> CONTAINS( Article_text, '"alcopops" AND ("advertising" OR "culture" OR
> "designated driver initiative" OR "drink driving" OR "legislation" OR
> "price" OR "pricing" OR "underage driving")')
> Revisting the AND NOTs, basically the way this is processed is all matches
> are returned for the first part
> ("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR ("CJD") OR ("E Coli")
> OR
> ("E-Coli") OR ("Food Additives") OR ("Food Safety") OR ("Genetically
> Modified Foods") OR ("Kwik Save") OR ("Proof of age card") OR
> ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews")
> and then you trim rows which contain
> AND NOT ("Ahold") AND NOT ("Beth Israel") AND NOT ("European equity
> preview") AND NOT ("European stocks may decline") AND NOT ("European
> stocks
> may rise") AND NOT
> ("mediaplex") AND NOT ("UK Stocks Factors")'))
> This trimming is very expensive.
> One thing you might do is sp_fulltext_service 'resource_usage' to 5. This
> might help slightly.
> --
> 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
> "Tobin Harris" <tobin@._do_not_spam_tobinharris.com> wrote in message
> news:42af07ca$0$2588$da0feed9@.news.zen.co.uk...
> cost.
> OR
> Safety")
> card")
> ("Ahold")
> NOT
> 2
> ("alcopops"
> did
> our
>