Field overflow and Log grow

Hi,
I am using SQL Server 2000.
One user database contains a table as following:
CREATE TABLE Events..Audit_Sub (
[RecID] [bigint] NOT NULL ,
[Name] [varchar] (100) NULL ,
[Value] [varchar] (1024) NULL
) ON [PRIMARY]
Sometimes the application writes to the table a record that overflows the
size of the Value field (actually because of an error in the new code, the
appilcation attempts to write about 5Kb to the Valaue field).
The fact is:
- No error is detected on SQL Server, data is written to tha table, it is
visible by select, it is just truncated to the field size (1Kb).
- In the meanwhile it appears that DB log begins growing: the things are not
directly dependent, just somewhat later the log begins growing, but no error
is found in SQL errorlog.
- Later on transaction log cannot be backup up, data are no more written to
DB, but then it is too late to understand the reason why.
The question is:
- In which way may the two things (overflow and log grow) be related?
- What really happens on SQL server when data overflow occurs? How does it
handle?
Thanks in advance,
MRMarco
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - In which way may the two things (overflow and log grow) be related?
It does not matter whether or not overflow occured. the log file grows up
and it is not truncated unless you have SIMPLE recovery mode the database
set
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
create table t (c1 tinyint, c2 varchar (5))
--owerflow on c1 column
insert into t values(4545745454545,'a')
--Server: Msg 8115, Level 16, State 2, Line 1
--Arithmetic overflow error converting expression to data type tinyint.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
--now insert much more characters than you defined for c2 columnn
insert into t values(1,'asgtbvtybvtg')
--Server: Msg 8152, Level 16, State 9, Line 1
--String or binary data would be truncated.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
"Marco Roda" <mrtest@.amdosoft.com> wrote in message
news:e8t656$le6$1@.ss408.t-com.hr...
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written
> to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||Hi
At a guess you have the ANSI_WARNINGS setting off as "When OFF, data is
truncated to the size of the column and the statement succeeds. " e.g
SET ANSI_WARNINGS ON
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
----
Transaction Rolled Back Error Status: 8152
(1 row(s) affected)
col1
--
(0 row(s) affected)
*/
SET ANSI_WARNINGS OFF
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
(1 row(s) affected)
Transaction Comitted
col1
--
A
(1 row(s) affected)
*/
although with your log file growing it may be that you have detected an
error and not rolled back the transaction, use DBCC OPENTRAN to view open
transactions.
John
"Marco Roda" wrote:
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are not
> directly dependent, just somewhat later the log begins growing, but no error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uozopRApGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Marco
> > - In the meanwhile it appears that DB log begins growing: the things are
> > not
> > directly dependent, just somewhat later the log begins growing, but no
> > error
> > is found in SQL errorlog.
> > - In which way may the two things (overflow and log grow) be related?
>
> It does not matter whether or not overflow occured. the log file grows up
> and it is not truncated unless you have SIMPLE recovery mode the database
> set
>
> > - What really happens on SQL server when data overflow occurs? How does
it
> > handle?
>
> create table t (c1 tinyint, c2 varchar (5))
> --owerflow on c1 column
> insert into t values(4545745454545,'a')
> --Server: Msg 8115, Level 16, State 2, Line 1
> --Arithmetic overflow error converting expression to data type tinyint.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
> --now insert much more characters than you defined for c2 columnn
> insert into t values(1,'asgtbvtybvtg')
> --Server: Msg 8152, Level 16, State 9, Line 1
> --String or binary data would be truncated.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
>
The fact is: when the application attempts writing more data, data is REALLY
WRITTEN (even if truncated), and NO ERROR is thrown.
- Why did not get error?
- May the overflow be a reason why the log is growing?

Field overflow and Log grow

Hi,
I am using SQL Server 2000.
One user database contains a table as following:
CREATE TABLE Events..Audit_Sub (
[RecID] [bigint] NOT NULL ,
[Name] [varchar] (100) NULL ,
[Value] [varchar] (1024) NULL
) ON [PRIMARY]
Sometimes the application writes to the table a record that overflows the
size of the Value field (actually because of an error in the new code, the
appilcation attempts to write about 5Kb to the Valaue field).
The fact is:
- No error is detected on SQL Server, data is written to tha table, it is
visible by select, it is just truncated to the field size (1Kb).
- In the meanwhile it appears that DB log begins growing: the things are not
directly dependent, just somewhat later the log begins growing, but no error
is found in SQL errorlog.
- Later on transaction log cannot be backup up, data are no more written to
DB, but then it is too late to understand the reason why.
The question is:
- In which way may the two things (overflow and log grow) be related?
- What really happens on SQL server when data overflow occurs? How does it
handle?
Thanks in advance,
MRMarco
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.

> - In which way may the two things (overflow and log grow) be related?
It does not matter whether or not overflow occured. the log file grows up
and it is not truncated unless you have SIMPLE recovery mode the database
set

> - What really happens on SQL server when data overflow occurs? How does it
> handle?
create table t (c1 tinyint, c2 varchar (5))
--owerflow on c1 column
insert into t values(4545745454545,'a')
--Server: Msg 8115, Level 16, State 2, Line 1
--Arithmetic overflow error converting expression to data type tinyint.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
--now insert much more characters than you defined for c2 columnn
insert into t values(1,'asgtbvtybvtg')
--Server: Msg 8152, Level 16, State 9, Line 1
--String or binary data would be truncated.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
"Marco Roda" <mrtest@.amdosoft.com> wrote in message
news:e8t656$le6$1@.ss408.t-com.hr...
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written
> to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||Hi
At a guess you have the ANSI_WARNINGS setting off as "When OFF, data is
truncated to the size of the column and the statement succeeds. " e.g
SET ANSI_WARNINGS ON
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30)
)
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
----
Transaction Rolled Back Error Status: 8152
(1 row(s) affected)
col1
--
(0 row(s) affected)
*/
SET ANSI_WARNINGS OFF
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30)
)
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
(1 row(s) affected)
Transaction Comitted
col1
--
A
(1 row(s) affected)
*/
although with your log file growing it may be that you have detected an
error and not rolled back the transaction, use DBCC OPENTRAN to view open
transactions.
John
"Marco Roda" wrote:

> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are n
ot
> directly dependent, just somewhat later the log begins growing, but no err
or
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written t
o
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uozopRApGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Marco
>
>
> It does not matter whether or not overflow occured. the log file grows up
> and it is not truncated unless you have SIMPLE recovery mode the database
> set
>
it[vbcol=seagreen]
>
> create table t (c1 tinyint, c2 varchar (5))
> --owerflow on c1 column
> insert into t values(4545745454545,'a')
> --Server: Msg 8115, Level 16, State 2, Line 1
> --Arithmetic overflow error converting expression to data type tinyint.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
> --now insert much more characters than you defined for c2 columnn
> insert into t values(1,'asgtbvtybvtg')
> --Server: Msg 8152, Level 16, State 9, Line 1
> --String or binary data would be truncated.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
>
The fact is: when the application attempts writing more data, data is REALLY
WRITTEN (even if truncated), and NO ERROR is thrown.
- Why did not get error?
- May the overflow be a reason why the log is growing?

field in a table that stores my IN condition (was "In")

Hello,

Alright here is the issue. I have a field in a table that stores my IN condition.

S0 I have a field that contains data like this: 'a','b','c'

basically I now just want that to be my IN clause...any ideas?

I tried something like this
declare @.in as varchar(10)
set @.in = (select field from table)

select * from table
where field in (@.in)

of course that does not work...and many variations of that also.

Let me know if you can lead me down the right path on this.You may consider modifying your design to store 'a', 'b', 'c' as separate rows in your table instead of a string of possible values. That makes the SQL easier and you can do things like joins instead of the IN clause.

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft
> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

fetching unique pins...

Hi,

I have a table which contains a bunch of prepaid PINs. What is the
best way to fetch a unique pin from the table in a high-traffic
environment with lots of concurrent requests?

For example, my PINs table might look like this and contain thousands
of records:

ID PIN ACQUIRED_BY
DATE_ACQUIRED
...
100 1864678198
101 7862517189
102 6356178381
...

10 users request a pin at the same time. What is the easiest/best way
to ensure that the 10 users will get 10 different unacquired pins?

Thanks for any help...Bobus wrote:
> Hi,
> I have a table which contains a bunch of prepaid PINs. What is the
> best way to fetch a unique pin from the table in a high-traffic
> environment with lots of concurrent requests?
> For example, my PINs table might look like this and contain thousands
> of records:
> ID PIN ACQUIRED_BY
> DATE_ACQUIRED
> ...
> 100 1864678198
> 101 7862517189
> 102 6356178381
> ...
> 10 users request a pin at the same time. What is the easiest/best way
> to ensure that the 10 users will get 10 different unacquired pins?

Place a Primary Key or Unique constraint on the PIN column. When a
duplicate error occurs generate a new PIN & try to save the new user row
again. Repeate until success.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Thanks, however, we do not generate the PINs ourselves. We simply
maintain the inventory of PINs which are given to us from a 3rd party.

Is there a way in SQL to update a single row ala the LIMIT function in
MYSQL? Something like:
update tablename set foo = bar limit 1|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Unless you're only using the PIN for a one-time operation - somewhere
you are going to save that PIN (in a table). That table is where you'd
put the Primary Key/Unique constraint.

I don't know what the LIMIT function does. If you want to just update
one row you'd indicate which row in the WHERE clause:

UPDATE table_name SET foo = bar WHERE foo_id = 25

foo_id would be a unique value.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/GTdYechKqOuFEgEQLJ/wCgxLHQiPaeDWXwsi5BxBpg6tlKmFoAn0tv
KM3PLa2qdl2KzW3Lp/XFHbiv
=gfzL
--END PGP SIGNATURE--

Bobus wrote:
> Thanks, however, we do not generate the PINs ourselves. We simply
> maintain the inventory of PINs which are given to us from a 3rd party.
> Is there a way in SQL to update a single row ala the LIMIT function in
> MYSQL? Something like:
> update tablename set foo = bar limit 1|||Maybe this

select top 1 ID, PIN from pin_table where acquired_by = <not acquired
value> (NOTE: this could be expensive if you use null to signify Not
Acquired, perhaps a non-null value with an index would help).

update pin_table set acquired_by = <acquired value> where ID = <ID from
select
commit

--or --

set up one table containing the unused pins and one containing the used
pins

then
select top 1 ID, PIN from unused_pin
insert into used_pin values (ID, PIN)
delete from unused_pin where ID = ID

commit|||I successfully used a transactional message queue for a similar
scenario.

Besides, try this:

create table #pins(id int identity, PIN decimal(10));
insert into #pins(PIN)values(1000000000);
insert into #pins(PIN)values(1000000001);
insert into #pins(PIN)values(1000000002);
go
create table #point_to_pins(id int identity)
go
--to get a PIN
insert into #point_to_pins default values
select @.@.identity

use @.@.identity to get the PIN, you will not get any collisions ever|||On 13 Feb 2006 20:00:07 -0800, Bobus wrote:

>Hi,
>I have a table which contains a bunch of prepaid PINs. What is the
>best way to fetch a unique pin from the table in a high-traffic
>environment with lots of concurrent requests?
>For example, my PINs table might look like this and contain thousands
>of records:
> ID PIN ACQUIRED_BY
>DATE_ACQUIRED
> ...
> 100 1864678198
> 101 7862517189
> 102 6356178381
> ...
>10 users request a pin at the same time. What is the easiest/best way
>to ensure that the 10 users will get 10 different unacquired pins?
>Thanks for any help...

Hi Bobus,

To get just one row, you can use TOP 1. Add an ORDER BY if you want to
make it determinate; without ORDER BY, you'll get one row, but there's
no way to predict which one.

If you expect high concurrency, you'll have to use the UPDLOCK to make
sure that the row gets locked when you read it, because otherwise a
second transaction might read the same row before the first can update
it to mark it acquired.

If you also don't want to hamper concurrency, add the READPAST locking
hint to allow SQL Server to skip over locked rows instead of waiting
until the lock is lifted. This is great if you need one row but don't
care which row is returned. But if you need to return the "first" row in
the queue, you can't use this (after all, the transaction that has the
lock might fail and rollback; if you had skipped it, you'd be processing
the "second" available instead of the first). In that case, you'll have
to live with waiting for the lock to be released - make sure that the
transaction is as short as possible!!

So to sum it up: to get "one row, just one, don't care which", use:

BEGIN TRANSACTION
SELECT TOP 1
@.ID = ID,
@.Pin = Pin
FROM PinsTable WITH (UPDLOCK, READPAST)
WHERE Acquired_By IS NULL
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @.User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @.ID
-- Add error handling
COMMIT TRANSACTION

And to get "first row in line", use:

BEGIN TRANSACTION
SELECT TOP 1
@.ID = ID,
@.Pin = Pin
FROM PinsTable WITH (UPDLOCK)
WHERE Acquired_By IS NULL
ORDER BY Fill in the blanks
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @.User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @.ID
-- Add error handling
COMMIT TRANSACTION

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:

> BEGIN TRANSACTION
> SELECT TOP 1
> @.ID = ID,
> @.Pin = Pin
> FROM PinsTable WITH (UPDLOCK, READPAST)
> WHERE Acquired_By IS NULL
> -- Add error handling
> UPDATE PinsTable
> SET Acquired_By = @.User,
> Date_Acquired = CURRENT_TIMESTAMP
> WHERE ID = @.ID
> -- Add error handling
> COMMIT TRANSACTION
> And to get "first row in line", use:
> BEGIN TRANSACTION
> SELECT TOP 1
> @.ID = ID,
> @.Pin = Pin
> FROM PinsTable WITH (UPDLOCK)
> WHERE Acquired_By IS NULL
> ORDER BY Fill in the blanks
> -- Add error handling
> UPDATE PinsTable
> SET Acquired_By = @.User,
> Date_Acquired = CURRENT_TIMESTAMP
> WHERE ID = @.ID
> -- Add error handling
> COMMIT TRANSACTION

Yet a variation is:

SET ROWCOUNT 1
UPDATE PinsTabel
SET @.ID = ID,
@.Pin = Pin
WHERE Acquired_By IS NULL
SET ROWCOUNT 0

It is essential to have a (clustered) index on Acquired_By.

Which solution that gives best performance it's difficult to tell.
My solution looks shorted, but Hugo's may be more effective.

Note also that if there is a requirement that a PIN must actually
be used, the transaction scope may need have to be longer, so in
case of an error, there can be a rollback. That will not be good
for concurrency, though.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the responses everyone!

MGFoster: I was asking about the "limit" clause so that I could
implement a solution similar to what Erland recommended. This
guarantees no collisions.

Randy: in your solution, I believe there is a chance that two
concurrent requests will end up grabbing the same pin.

Alexander: clever! It's like an Oracle sequence. But, in our
particular case, we could have a problem of unused pins for
transactions which rollback.

Hugo: that should definintely do the trick.

Erland: yours too! I will try them both out.

Thanks for the help!|||Bobus,

When I was solving a similar problem, I did try out the approaches
suggested by Hugo and Erland. I hate to say that, but I was always
getting a bottleneck because of lock contention on PinsTable. Maybe I
was missing something at that time. I had a requirement to produce
hundreds of PINs per minute at peak times, so I decided to allocate a
batch of PINs at a time, instead of distrributing them one at a time -
that took care of lock contention|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> When I was solving a similar problem, I did try out the approaches
> suggested by Hugo and Erland. I hate to say that, but I was always
> getting a bottleneck because of lock contention on PinsTable. Maybe I
> was missing something at that time. I had a requirement to produce
> hundreds of PINs per minute at peak times, so I decided to allocate a
> batch of PINs at a time, instead of distrributing them one at a time -
> that took care of lock contention

Bobus said "But, in our particular case, we could have a problem of unused
pins for transactions which rollback."

This would call for a design where you get a start a transaction, get a
pin, use it for whatever purpose, and then commit. But as you say, you
will get contentions on the PINs here, although it's possible that READPAST
hint could help. I did some quick tests, and it seem to work.

The other option as you say is just to grab a PIN or even a bunch of
them. A later job would then examine which PINs that were taken, and
which were never used, and then mark the latter as unused.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Alexander/Erland: thanks for the tips. I will let you know what issues
we run into, if any.

Best wishes.|||Erland,

I think you are right. I looked up that project, and in fact I did not
try READPAST at all. Having observed poor performance, I implemented
batches, which reduced amount of database calls and as a side effect
took care of lock contention. I immediately got good performance and
did not drill down any furhter.|||Erland's solution seems to work great from our initial tests!

Unrelated question, and probably should be another thread, but have any
of you SQL Server geniuses tried PostgreSQL? Any comments, positive or
negative?|||In 25 words or less: It is nice but has the feel of a college project
where grad students kept addinf things to it based on the last academic
fad or thesis topic. I would go with Ingres, which has a "commercial
feel" and a great optimizer.

Fetch text from HTML [URGENT]

Hello everybody,

I have data in my sql server table that is entered with a rich text editor so it contains html formatting. I need to show this data on the form with the formatting user has specified while entering the data. On the crystal report, I need to show the data not in the html format but simple text. I created a stored procedure to fetch the data and binded the report with the stored procedure. When data is printed on the report, it shows HTML tags with the data. Is there a way to fetch text in SQL so that report will only show text without HTML formatting?

Thanks.

It is stored a just a string of characters. It is retreived as just a string of characters. (Some of those characters are html coding characters.

You may wish to create a User Defined Function (UDF) that will strip out the html characters and can be used when necessary.

Here is an example of how you might create and use such a function. (It is incomplete -I'm sure you will be able to add the additional html coding...)

CREATE FUNCTION dbo.StripHTML
( @.StringIn varchar(500) )
RETURNS varchar(500)
AS
BEGIN
SET @.StringIn = replace( replace( @.StringIn, '

', '' ), '

', '' )
SET @.StringIn = replace( replace( @.StringIn, '<b>', '' ), '</b>', '' )
-- add addition html codes here
RETURN ( @.StringIn )
END
GO

SELECT dbo.StripHTML('

This is a <b>Test</b>

')

|||

I was looking for a quicker and easier way to do it (if there is something). As you said, I am writing my own UDF to parse HTML.

Thanks a lot.

|||

You can use the following code to replace all the HTML tags....

CREATE Function dbo.Html2Text(@.HtmlString As Varchar(8000))
Returns Varchar(8000)
as
Begin
Declare @.TagString As Varchar(8000)
Declare @.TagStart As int
Declare @.TagEnd As int

Select @.TagStart = CharIndex('<', @.HtmlString),
@.TagEnd = CharIndex('>', @.HtmlString)
While @.TagStart <> 0 And @.TagEnd <> 0 And @.TagEnd > @.TagStart
Begin
Select @.TagString = Substring(@.HtmlString, @.TagStart, @.TagEnd - @.TagStart + 1)
Select @.HtmlString = Replace(@.HtmlString, @.TagString, '')
Select @.TagStart = CharIndex('<', @.HtmlString),
@.TagEnd = CharIndex('>', @.HtmlString)
End

Select @.HtmlString = Replace(@.HtmlString, '&nbsp;', ' ')
Select @.HtmlString = Replace(@.HtmlString, '&amp;', '&')
Select @.HtmlString = Replace(@.HtmlString, '&quot;', '''')
Select @.HtmlString = Replace(@.HtmlString, '&#', '#')
Select @.HtmlString = Replace(@.HtmlString, '&lt;', '<')
Select @.HtmlString = Replace(@.HtmlString, '&gt;', '>')
Select @.HtmlString = Replace(@.HtmlString, '%20', ' ')
Select @.HtmlString = Replace(@.HtmlString, Char(10), '')
Select @.HtmlString = Replace(@.HtmlString, Char(13), '')
Select @.HtmlString = LTrim(RTrim(@.HtmlString))

Return @.HtmlString
End

Go

Select dbo.Html2Text('

Test&nbsp;Data

'); -- Result :Test Data

Features of a normal index comapred to a Full text index

Hello everyone,
My company is in the middle of coding a complex search function which
takes over most of the features that full text indexing contains (such
as stemming and stopping).
However from reading about full text indexing it appears that for a
column containing rows of pure text documents, the way that it indexes
the text is as follows: -
word id of row where word can be found
### #################################
car 0, 10, 256, 654
bike 20, 36, 92
skates 65,. 42
If this is the case I can see this improving search speeds for a large
database.
However we don't want any of the other features that full text
catalogue has as we do it ourselves.
My question is can we turn off all the extra features of full text
indexing (so we only left with a text index), or is a normal index
sufficient to provide the above example?
Many thanks for any help or advice
Kind Regards
Philip
With the Contains/ContainsTable keywords you get a strict match which it
sounds like what you are looking for. In otherwords stemming, and
wildcarding are disabled.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408110112.15962c0@.posting.google.co m...
> Hello everyone,
> My company is in the middle of coding a complex search function which
> takes over most of the features that full text indexing contains (such
> as stemming and stopping).
> However from reading about full text indexing it appears that for a
> column containing rows of pure text documents, the way that it indexes
> the text is as follows: -
> word id of row where word can be found
> ### #################################
> car 0, 10, 256, 654
> bike 20, 36, 92
> skates 65,. 42
> If this is the case I can see this improving search speeds for a large
> database.
> However we don't want any of the other features that full text
> catalogue has as we do it ourselves.
> My question is can we turn off all the extra features of full text
> indexing (so we only left with a text index), or is a normal index
> sufficient to provide the above example?
> Many thanks for any help or advice
> Kind Regards
> Philip
|||Hello Hilary,
Thankyou for you help, just to make sure, when I enable full text
catalogs, does it index the text without stemming and stopping the
data.
By this i mean that is I had stored in the table "to be or not to be",
would the full text catalog contain indexes for to, be, or and not or
would it stem and stop the text.
Again thankyou for your help
Kind Regards
Phil
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> With the Contains/ContainsTable keywords you get a strict match which it
> sounds like what you are looking for. In otherwords stemming, and
> wildcarding are disabled.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408110112.15962c0@.posting.google.co m...
|||The answer is complex. First off to,be, or, and not are all noise words and
as such they would not be indexed.
But to answer your question that depends on the word breaker. In general
the words would be indexed as they appear in the content. For some word
breakers different language rules dicate different indexing patterns, for
instance in the French word breaker, marie-claire is indexed as two
different words, marie and claire, however Marie-Claire is indexed as one
word (MarieClaire). The hyphen and the capitalization cause it to be indexed
as one word not two.
It is at query time when the search arguements might will be stemmed (when
you are doing a FreeText query, or an Inflectional query). If you are doing
a Contains query without wildcarding or the FormsOf(Inflectional type
queries you won't get stemming (although there are word breaker specific
some exceptions).
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> Hello Hilary,
> Thankyou for you help, just to make sure, when I enable full text
> catalogs, does it index the text without stemming and stopping the
> data.
> By this i mean that is I had stored in the table "to be or not to be",
> would the full text catalog contain indexes for to, be, or and not or
> would it stem and stop the text.
> Again thankyou for your help
> Kind Regards
> Phil
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
|||Phi!
In addition to what Hilary says, there is another factor here and that is
the OS platform (Win2K vs. Win2003 or WinXP) that you have SQL Server
installed on. Specifically, the Windows 2000 Server (Win2K) wordbreaker -
infosoft.dll - indexes the "-" (dash or hyphen) in "7-UP" as one token,
i.e., as single phrase. A work around for this is to drop and re-create your
FT Catalog and use the Neutral "Language for Word Breaker" for the your
FT-enabled column. However, with the Neutral "Language for Word Breaker",
you will lose the formsof(inflectional) function as the words are "broken"
into tokens based upon the "white space" between words...
However, this is not the case with Windows Server 2003 (Win2003) or Windows
XP (WinXP) as these OS-platforms, ships with a newer (or better, i.e., more
expectant results) wordbreaker - langwrbk.dll which would correctly (or more
expectant results) break 7 and UP into separate tokens. So, in the long run
to get both the correct wordbreaking for you as well as the use of the
formsof(inflectional) function, if this is the functionality you're looking
for, you should consider upgrading to Win2003...
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl...
> The answer is complex. First off to,be, or, and not are all noise words
and
> as such they would not be indexed.
> But to answer your question that depends on the word breaker. In general
> the words would be indexed as they appear in the content. For some word
> breakers different language rules dicate different indexing patterns, for
> instance in the French word breaker, marie-claire is indexed as two
> different words, marie and claire, however Marie-Claire is indexed as one
> word (MarieClaire). The hyphen and the capitalization cause it to be
indexed
> as one word not two.
> It is at query time when the search arguements might will be stemmed (when
> you are doing a FreeText query, or an Inflectional query). If you are
doing[vbcol=seagreen]
> a Contains query without wildcarding or the FormsOf(Inflectional type
> queries you won't get stemming (although there are word breaker specific
> some exceptions).
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
it[vbcol=seagreen]
which[vbcol=seagreen]
(such[vbcol=seagreen]
indexes[vbcol=seagreen]
large
>
|||Hello Hillary,
AFter much testing we have decided to go as follows, we will use Full
Text catalogs and search using the contains keyword.
However to ensure SQL 2000 server does not do anything strange with
the results we have turned the lnaguage setting to neutral. We found
the results were not returned when we had the UK setting on. i.e.
"jpg" would not find "filename.jpg".
For wildcards we will use the LIKEkeyword as the contains does not do
patterm matching with wilcards but tries to find differenet words
instead.
Therefore we hope we have the best of both world, by the using the
contains in most cases but the the LIKE keyword whenever anyone needs
pattern matching (which should be rare).
Again thankyou for your hillary in helping me understanding full text
catlogs
Have a good weekend
Phil
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> The answer is complex. First off to,be, or, and not are all noise words and
> as such they would not be indexed.
> But to answer your question that depends on the word breaker. In general
> the words would be indexed as they appear in the content. For some word
> breakers different language rules dicate different indexing patterns, for
> instance in the French word breaker, marie-claire is indexed as two
> different words, marie and claire, however Marie-Claire is indexed as one
> word (MarieClaire). The hyphen and the capitalization cause it to be indexed
> as one word not two.
> It is at query time when the search arguements might will be stemmed (when
> you are doing a FreeText query, or an Inflectional query). If you are doing
> a Contains query without wildcarding or the FormsOf(Inflectional type
> queries you won't get stemming (although there are word breaker specific
> some exceptions).
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
|||Phi!,
Just FYI, if you were able to read my posting to this thread, you would of
understood why "the results were not returned when we had the UK setting on.
i.e. "jpg" would not find 'filename.jpg'" as it is a bug in the Win2K
wordbreaker dll and if in the future you decide to upgrade to Windows Server
2003 (Win2003) you would not of encountered this bug.
Regards,
John
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408130807.3fa93afe@.posting.google.c om...
> Hello Hillary,
> AFter much testing we have decided to go as follows, we will use Full
> Text catalogs and search using the contains keyword.
> However to ensure SQL 2000 server does not do anything strange with
> the results we have turned the lnaguage setting to neutral. We found
> the results were not returned when we had the UK setting on. i.e.
> "jpg" would not find "filename.jpg".
> For wildcards we will use the LIKEkeyword as the contains does not do
> patterm matching with wilcards but tries to find differenet words
> instead.
> Therefore we hope we have the best of both world, by the using the
> contains in most cases but the the LIKE keyword whenever anyone needs
> pattern matching (which should be rare).
> Again thankyou for your hillary in helping me understanding full text
> catlogs
> Have a good weekend
> Phil
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
and[vbcol=seagreen]
general[vbcol=seagreen]
for[vbcol=seagreen]
one[vbcol=seagreen]
indexed[vbcol=seagreen]
(when[vbcol=seagreen]
doing[vbcol=seagreen]
which it[vbcol=seagreen]
which[vbcol=seagreen]
(such[vbcol=seagreen]
a[vbcol=seagreen]
indexes[vbcol=seagreen]
large[vbcol=seagreen]
|||Hi John!
Thankyou for the tip, will bear it in mind when we upgrade.
I am having trouble getting an newsgroup reader on my desktop at work
(compnay ploicy etc) so have been limited to google at the moment so
did not know you had posted until today!
sorry for any confusion
Phil
"John Kane" <jt-kane@.comcast.net> wrote in message news:<O4zBxiagEHA.3632@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Phi!,
> Just FYI, if you were able to read my posting to this thread, you would of
> understood why "the results were not returned when we had the UK setting on.
> i.e. "jpg" would not find 'filename.jpg'" as it is a bug in the Win2K
> wordbreaker dll and if in the future you decide to upgrade to Windows Server
> 2003 (Win2003) you would not of encountered this bug.
> Regards,
> John
>
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408130807.3fa93afe@.posting.google.c om...
> news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...
> and
> general
> for
> one
> indexed
> (when
> doing
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
> which it
> which
> (such
> a
> indexes
> large

Fast insert and select at the same time?

I have two tables: Account and AccountTransction. Each table contains more
than 20 million records. It one-to-many relationship between account and
AccountTransaction. The new records are constantly loading into each table
through text file by using DTS.
Question:
My team member insists that using cursor to insert record one by one to the
table to avoid affect (lock) the selection on these tables. . There are tons
of indexes on both tables for fast searching. The insertion process is
extremely slow. I recommended batch mode insertion, instead of one by one
using cursor. It is much more faster and efficient in terms of insertion,
but the selection while insertion going on is a little bit slower. What is
your suggestion? How can I achieve the fast insertion and fast selection at
the same time'
Is cursor alway a bad idea in terms of speed and performance?
Thanks a lot,
FlxI would never use a cursor to insert one row of data one by one...
Your colleague is smart to have worries about contention. However... it's
quite easy to do this in a safe manner. My standard technique for manaing a
situation like this is to:
* insert N number of rows per batch through an insert into stmt
* N is tested to ensure
- the insert happens fast enough to have a negligible impact on blocks
for selects
- durtion between batch inserts is long enough to ensure we're not
having a constant impact and quueses aren't growing
- but N is large enough to ensure I can insert enough records fast
enough such that the insert process isn't horrible slow.
I've been able to achieve VERY high insert and select throughput using
techniques like that...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"FLX" <nospam@.hotmail.com> wrote in message
news:e5rEoTiAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I have two tables: Account and AccountTransction. Each table contains more
> than 20 million records. It one-to-many relationship between account and
> AccountTransaction. The new records are constantly loading into each table
> through text file by using DTS.
> Question:
> My team member insists that using cursor to insert record one by one to
the
> table to avoid affect (lock) the selection on these tables. . There are
tons
> of indexes on both tables for fast searching. The insertion process is
> extremely slow. I recommended batch mode insertion, instead of one by one
> using cursor. It is much more faster and efficient in terms of insertion,
> but the selection while insertion going on is a little bit slower. What is
> your suggestion? How can I achieve the fast insertion and fast selection
at
> the same time'
> Is cursor alway a bad idea in terms of speed and performance?
> Thanks a lot,
> Flx
>
>
>
>
>
>
>
>
>
>
>|||Here's an out-of-the-box idea.
Create new tables, same schema, so you have pairs to tables.
These new tables are for 'todays' data.
Create views to cover the pairs of tables. These are what your application/users look at / use.
The DTS populates the 'today' tables.
Once a day, at some light / quite period, stop the DTS. Copy / Move 'todays' data into the oringal, large table
Don't index the 'todays' tables, as they will (hopefully) be small enough to not need them. Or add only essential indexes.
Or only do this for the AccountTransaction table, and use the current method for the Account table.
If possible, you might want to drop the main table indexes just before you load the data from the 'todays' tables.
It depends, of course, on how quite your quite period will be.

Fast insert and select at the same time?

I have two tables: Account and AccountTransction. Each table contains more
than 20 million records. It one-to-many relationship between account and
AccountTransaction. The new records are constantly loading into each table
through text file by using DTS.
Question:
My team member insists that using cursor to insert record one by one to the
table to avoid affect (lock) the selection on these tables. . There are tons
of indexes on both tables for fast searching. The insertion process is
extremely slow. I recommended batch mode insertion, instead of one by one
using cursor. It is much more faster and efficient in terms of insertion,
but the selection while insertion going on is a little bit slower. What is
your suggestion? How can I achieve the fast insertion and fast selection at
the same time'
Is cursor alway a bad idea in terms of speed and performance?
Thanks a lot,
FlxI would never use a cursor to insert one row of data one by one...
Your colleague is smart to have worries about contention. However... it's
quite easy to do this in a safe manner. My standard technique for manaing a
situation like this is to:
* insert N number of rows per batch through an insert into stmt
* N is tested to ensure
- the insert happens fast enough to have a negligible impact on blocks
for selects
- durtion between batch inserts is long enough to ensure we're not
having a constant impact and quueses aren't growing
- but N is large enough to ensure I can insert enough records fast
enough such that the insert process isn't horrible slow.
I've been able to achieve VERY high insert and select throughput using
techniques like that...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"FLX" <nospam@.hotmail.com> wrote in message
news:e5rEoTiAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I have two tables: Account and AccountTransction. Each table contains more
> than 20 million records. It one-to-many relationship between account and
> AccountTransaction. The new records are constantly loading into each table
> through text file by using DTS.
> Question:
> My team member insists that using cursor to insert record one by one to
the
> table to avoid affect (lock) the selection on these tables. . There are
tons
> of indexes on both tables for fast searching. The insertion process is
> extremely slow. I recommended batch mode insertion, instead of one by one
> using cursor. It is much more faster and efficient in terms of insertion,
> but the selection while insertion going on is a little bit slower. What is
> your suggestion? How can I achieve the fast insertion and fast selection
at
> the same time'
> Is cursor alway a bad idea in terms of speed and performance?
> Thanks a lot,
> Flx
>
>
>
>
>
>
>
>
>
>
>|||Here's an out-of-the-box idea.
Create new tables, same schema, so you have pairs to tables.
These new tables are for 'todays' data.
Create views to cover the pairs of tables. These are what your application/u
sers look at / use.
The DTS populates the 'today' tables.
Once a day, at some light / quite period, stop the DTS. Copy / Move 'todays'
data into the oringal, large table
Don't index the 'todays' tables, as they will (hopefully) be small enough to
not need them. Or add only essential indexes.
Or only do this for the AccountTransaction table, and use the current method
for the Account table.
If possible, you might want to drop the main table indexes just before you l
oad the data from the 'todays' tables.
It depends, of course, on how quite your quite period will be.

False hits with Contains query

I'lll try this question here too, since I coudn't get an answer in the T-SQL forum

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=166991&SiteID=1

I cant figure out why a query returns false results.
It is basically:
select *
from sometable
where somecolumn=1
and contains(someothercolumn, 'arti-b')
This query takes forever to process and returns about 100 false hits for every row where someothercolumn actually contains the string 'arti-b'.
Note, I don't get the full set of rows where somecolumn=1, so there is some filtering from the contains clause.
If I use the same query, searching for just 'arti', it works fine. 'whatever-b' seems to work as well, as long as 'whatever' != 'arti'.
Does anyone know what causes this?
SQL server Enterprise edition
version 8 (SP4)
Language: US english
Collation: Finnish_swedish.

Full-text search is a word-based tool for natural language searches. It's not designed to handle punctuation.

As suggested in http://support.microsoft.com/kb/200043/EN-US/:

Where non-alphanumeric character must be used in the search critera (primarily the dash '-' character), use the Transact-SQL LIKE clause instead of the FULLTEXT or CONTAINS predicates.

In other words, use this query:

select * from sometable
where somecolumn=1
and someothercolumn like '%arti-b%'

Steve Kass
Drew University
|||

I see. I never found that KB article.

I don't suppose this behaviour of the full text search engine can be configured somehow?

A LIKE-query is not an option for me. Someothercolumn is text and there are over a million rows in the sometable.

How does the search engine handle hyphens? Is it better to filter out any such strings or is it possible to get something acceptable by querying for CONTAINS(someothertable, "arti b") or perhaps "arti*" ?

/Gustav

False "LIKE" hits

Hi All... We have an application where a table has a field of type text that contains readable text, but some of it may have an HTML tag in it - specifically an HTML <img> tag. For example, it may be something like:

BLAH BLAH BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH.

The problem we're seeing is that when searching that field using LIKE, it's returning records that do in fact satisfy the SQL query, but we'd like it not to. That is, we'd like to exclude those HTML tags from the search.

For example:

SELECT ... WHERE TextField LIKE '%retr%'

is returning records that have those HTML tags. Yes, I know SQL is only doing its job... Anyone have any ideas to exclude those tags from the LIKE search? Thanks! -- Curt

Curt, I'm not clear on if you want to filter out the rows that have ANY html tags? or you're saying that part of a single column has a mixture of data AND html and you'd want to search only the non-html part of the column?

So, if you search this string below for the word "White" you want the row, but if you search on the word "Image" you do NOT want the row?

White Horse <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'>

so, you want to filter out everything between the "<" and ">" ?

You could write a function that parses each line, and searches for your string...

Bruce

|||

If I understand you correctly, you want the return to be:

BLAH BLAH BLAH ... BLAH BLAH BLAH

If that is a correct interpretation, it's not going to be either easy or pretty. By that I mean you will have to parse the data fields on the search which is really going to increase the time required to search. Indexes will not be used.

If this is the path you wish to take, you will need to create a User Defined Function that will take the entire field and strip out all characters between paired angle brackets.

|||

Thanks for the replies, Bruce and Arnie. I'm sorry for not making the issue very clear - believe it or not, it took me some time to figure out the wording I did manage to get down...

Using the statement SELECT * From TheTable WHERE TheField LIKE '%RETR%'

I would want the following record to be included:

BLAH RETR BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH

But I would NOT want the following record included:

BLAH BLAH BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH

In any records that are included, I would want the text returned as it appears - that is nothing filtered out.

You both pointed in the direction of writing a function to filter out that "<....>" data before applying a search. And yeah, that just adds to the overhead of the search... And quite frankly, I'm a bit nervous about that anyway - there's gonna be alot of these records and that LIKE just seems expensive. I've also been looking at indexing the text and using CONTAINS (that sound right?). We're also considering a sort of application-specific index of the text before we put it in the table as alot of the searchs are somewhat predictable.

|||

If there is only a single tag per row, you could have a WHERE clause that includes BOTH the substring that precedes "<" and the substring that follows ">". That should not require a function.

If there are multiple "<...>" entries per row, this method would not work as desired.

Dan

|||

IF, and that is a big IF in my opinion, the data is consistant and your sample correctly reflects the search value, this could work:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
Comment varchar(max)
)

INSERT INTO @.MyTable VALUES ( 'BLAH RETR BLAH <img src='http://pics.10026.com/?src='RetrieveImage.aspx?ImageId=1234''> BLAH BLAH BLAH' )
INSERT INTO @.MyTable VALUES ( 'BLAH BLAH BLAH <img src='http://pics.10026.com/?src='RetrieveImage.aspx?ImageId=1234''> BLAH BLAH BLAH' )

SELECT Comment
FROM @.MyTable
WHERE ( Comment LIKE '% RETR %'
AND Comment NOT LIKE '%=''RETR'
)

And of course, you could 'build up' the search values using parameters and constants.

This feels so 'unclean' that now I have to go take a shower...

|||

slightly shorter version.

Code Snippet

SELECT Comment
FROM @.MyTable
WHERE (Comment LIKE '%[ ]RETR[ ]%')

|||This problem was born for regular expressions:
http://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx

you could also use charindex:

Code Snippet

SELECT *
From TheTable
where charindex('RETR', TheField)

not between charindex('<', TheField)

and charindex('>', TheField)

|||Spent far too long on this but here goes.

The following allows for any number of HTML Tags in the field.

Tested with the following:

Code Snippet

SELECT * into TheTable
From(
select 'BLAH RETR BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH' as TheField
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH'
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH RETR BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAHRETRBLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> RETR BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
) as t1

create a table Numbers with a single field Num populated 1 to x where x = a number larger than the longest field you expect to work with:

Code Snippet

select * into numbers from (
select 1 as Num union all
select 2 as Num union all
select 3 as Num union all
...
select 2999 as Num union all
select 3000 as Num) as T1

Then use this query:

Code Snippet

select distinct TheField
from (
select
TheField,
case
when substring(TheField, num,1) = '>'

or num = 1

then substring(TheField, Num+1, charindex('<',TheField+'<', Num) - Num -1 )
else ''
end as c3
from TheTable, Numbers
) as T1
where c3 like '%RETR%'

Failure Workflow Does Not Fire

Hello,
I have a SQL Server 2000 DTS package in which the first step executes a batch file. The batch file contains FTP commands that log into an FTP server, and pull down whatever file is there.

I set up a failure workflow to send an email if the step fails. When I have a SQL Server job run this package, and there is no file to dowload, the whole package fails without the failure workflow result firing.

For the step (DTSStep_DTSCreateProcessTask_1), I have the 'FailPackageOnError' property set to -1. In the package properties, I have the check box for 'Fail Package on First Error' cleared.

What do I need to do so that the failure workflow occurs when the step fails?

Thank you for your help!

cdun2Are you sure the fact that no file is there will cause the step to error out?|||u need to trap errors from external programs like bat files to have the failed workflow activated. aslo not all external programs returns error code to the calling application. for bat files, u will have to set errorlevel to make the calling program understand the success/failure

for example when using xp_cmdshell, this will call the failure workflow, if present

declare @.err int
exec @.err = master..xp_cmdshell 'C:\xx.bat'
if @.err = 1
RAISERROR ('err',16,1)

the above code will not fire the failure workflow if u just execute
exec master..xp_cmdshell 'C:\xx.bat'
and even if the xx.bat is not present in folder C:\|||Thank you for your help!
cdun2|||Another option is to create an operator when you scheduled the job and an e-mail will be sent out if the job fails.

Good luck