False number of rows in sp_spaceused and sysindexes

Hi,
On a server with WS2003 / SQL2000 SP3, I must run everyday DBCC UpdateUsage
(0) With Count_Rows to correct the number of rows returned by sp_spaceused,
Enterprise Manager or in table sysindexes.
For example, I can have 318000 instead of 318256, 3456 instead of 3458
There was not this problem on a server with W2000 / SQL2000 SP1.
Anyone has an idea ?
Thanks,
SebastienI think you must have had statistics set to auto-update on the 2000 server,
and no longer have this set on the 2003 server.
I've never relied on sp_spaceused unless I also update stats before I run
it.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sebastien" <nospam@.nospam.com> wrote in message
news:uXpFj4YoEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> On a server with WS2003 / SQL2000 SP3, I must run everyday DBCC
UpdateUsage
> (0) With Count_Rows to correct the number of rows returned by
sp_spaceused,
> Enterprise Manager or in table sysindexes.
> For example, I can have 318000 instead of 318256, 3456 instead of 3458
> There was not this problem on a server with W2000 / SQL2000 SP1.
> Anyone has an idea ?
> Thanks,
> Sebastien
>
>|||Hi Sebastien,
Thanks for your post.
As MVP Aaron said, there are some known issues in sp_spaceused, however,
you'd better take the following steps to see whether it make more effect.
First of all, take the latest updates of SQL Server. Some known issues are
fixed. For example,
FIX: Statistics Maintenance Creates Significant Bottleneck on SQL Servers
that Use 16 or More CPUs
http://support.microsoft.com/?id=293849
Secondly, run the command sp_spaceused like below
sp_spaceused @.updateusage = true
Note that when you include the "@.updateusage=true" parameter for
sp_spaceused, the sp will run DBCC UPDATEUSAGE, which will update the
allocation information in sysindexes to insure that it provides accurate
information. Running DBCC UPDATEUSAGE on the whole database is a
resource-intensive task and can have a significant impact on the
performance of a system. Note that sysindexes is also updated at
checkpoints (though not as thoroughly), so during peak load times you may
want to invesitgate omitting the @.updateusage parameter and running
"CHECKPOINT" before sp_spaceused instead.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||No, auto-update statistics option is ON on the 2 server for the database,
and auto-create is OFF.
sp_spaceused mytable,@.updateusage=true or update statistics don't correct
that.
Only DBCC UpdateUsage (0,mytable) With Count_Rows can correct !!!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a écrit dans le message de
news:u0KceEZoEHA.1644@.tk2msftngp13.phx.gbl...
> I think you must have had statistics set to auto-update on the 2000
server,
> and no longer have this set on the 2003 server.
> I've never relied on sp_spaceused unless I also update stats before I run
> it.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Sebastien" <nospam@.nospam.com> wrote in message
> news:uXpFj4YoEHA.1272@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > On a server with WS2003 / SQL2000 SP3, I must run everyday DBCC
> UpdateUsage
> > (0) With Count_Rows to correct the number of rows returned by
> sp_spaceused,
> > Enterprise Manager or in table sysindexes.
> >
> > For example, I can have 318000 instead of 318256, 3456 instead of 3458
> >
> > There was not this problem on a server with W2000 / SQL2000 SP1.
> >
> > Anyone has an idea ?
> >
> > Thanks,
> > Sebastien
> >
> >
> >
>|||Thanks for your reply.
The server run french version of SQL2000 with SP3a and only have 4 CPU Xeon
HT.
sp_spaceused @.updateusage = true or sp_spaceused mytable,@.updateusage =true don't solve the problem
only the dbcc command with Count_Rows can correct that !!!
Sebastien
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> a écrit dans le
message de news:NWreWReoEHA.3212@.cpmsftngxa06.phx.gbl...
> Hi Sebastien,
> Thanks for your post.
> As MVP Aaron said, there are some known issues in sp_spaceused, however,
> you'd better take the following steps to see whether it make more effect.
> First of all, take the latest updates of SQL Server. Some known issues are
> fixed. For example,
> FIX: Statistics Maintenance Creates Significant Bottleneck on SQL Servers
> that Use 16 or More CPUs
> http://support.microsoft.com/?id=293849
> Secondly, run the command sp_spaceused like below
> sp_spaceused @.updateusage = true
> Note that when you include the "@.updateusage=true" parameter for
> sp_spaceused, the sp will run DBCC UPDATEUSAGE, which will update the
> allocation information in sysindexes to insure that it provides accurate
> information. Running DBCC UPDATEUSAGE on the whole database is a
> resource-intensive task and can have a significant impact on the
> performance of a system. Note that sysindexes is also updated at
> checkpoints (though not as thoroughly), so during peak load times you may
> want to invesitgate omitting the @.updateusage parameter and running
> "CHECKPOINT" before sp_spaceused instead.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>|||Hi Sebastien,
Thanks for your further explaination.
Executing sp_spaceused computes the amount of disk space used for data and
indexes, and the disk space used by a table in the current database. If no
table (objname) is given, sp_spaceused reports on the space used by the
entire current database.
After you drop an index, sp_spaceused reports inaccurate information. This
is a known limitation. However, sp_spaceused provides a quick, rough
estimate. If you notice incorrect values when you execute sp_spaceused, run
DBCC UPDATEUSAGE to correct the inaccuracies. Because this statement takes
some time to run on large tables or databases use it only when you suspect
incorrect values being returned or when other users are not active.
I am afraid that you will have to run that DBCC command if sp_spaceused
@.updateusage = true doesn't make sence:(
I am sorry for the inconvenience it may caught. Thank you for your patience
and corperation. If you have any questions or concerns, don't hesitate to
let me know.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

False number of rows in sp_spaceused and sysindexes

Hi,
On a server with WS2003 / SQL2000 SP3, I must run everyday DBCC UpdateUsage
(0) With Count_Rows to correct the number of rows returned by sp_spaceused,
Enterprise Manager or in table sysindexes.
For example, I can have 318000 instead of 318256, 3456 instead of 3458
There was not this problem on a server with W2000 / SQL2000 SP1.
Anyone has an idea ?
Thanks,
Sebastien
I think you must have had statistics set to auto-update on the 2000 server,
and no longer have this set on the 2003 server.
I've never relied on sp_spaceused unless I also update stats before I run
it.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sebastien" <nospam@.nospam.com> wrote in message
news:uXpFj4YoEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> On a server with WS2003 / SQL2000 SP3, I must run everyday DBCC
UpdateUsage
> (0) With Count_Rows to correct the number of rows returned by
sp_spaceused,
> Enterprise Manager or in table sysindexes.
> For example, I can have 318000 instead of 318256, 3456 instead of 3458
> There was not this problem on a server with W2000 / SQL2000 SP1.
> Anyone has an idea ?
> Thanks,
> Sebastien
>
>
|||Hi Sebastien,
Thanks for your post.
As MVP Aaron said, there are some known issues in sp_spaceused, however,
you'd better take the following steps to see whether it make more effect.
First of all, take the latest updates of SQL Server. Some known issues are
fixed. For example,
FIX: Statistics Maintenance Creates Significant Bottleneck on SQL Servers
that Use 16 or More CPUs
http://support.microsoft.com/?id=293849
Secondly, run the command sp_spaceused like below
sp_spaceused @.updateusage = true
Note that when you include the "@.updateusage=true" parameter for
sp_spaceused, the sp will run DBCC UPDATEUSAGE, which will update the
allocation information in sysindexes to insure that it provides accurate
information. Running DBCC UPDATEUSAGE on the whole database is a
resource-intensive task and can have a significant impact on the
performance of a system. Note that sysindexes is also updated at
checkpoints (though not as thoroughly), so during peak load times you may
want to invesitgate omitting the @.updateusage parameter and running
"CHECKPOINT" before sp_spaceused instead.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||No, auto-update statistics option is ON on the 2 server for the database,
and auto-create is OFF.
sp_spaceused mytable,@.updateusage=true or update statistics don't correct
that.
Only DBCC UpdateUsage (0,mytable) With Count_Rows can correct !!!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le message de
news:u0KceEZoEHA.1644@.tk2msftngp13.phx.gbl...
> I think you must have had statistics set to auto-update on the 2000
server,
> and no longer have this set on the 2003 server.
> I've never relied on sp_spaceused unless I also update stats before I run
> it.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Sebastien" <nospam@.nospam.com> wrote in message
> news:uXpFj4YoEHA.1272@.TK2MSFTNGP09.phx.gbl...
> UpdateUsage
> sp_spaceused,
>
|||Thanks for your reply.
The server run french version of SQL2000 with SP3a and only have 4 CPU Xeon
HT.
sp_spaceused @.updateusage = true or sp_spaceused mytable,@.updateusage =
true don't solve the problem
only the dbcc command with Count_Rows can correct that !!!
Sebastien
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> a crit dans le
message de news:NWreWReoEHA.3212@.cpmsftngxa06.phx.gbl...
> Hi Sebastien,
> Thanks for your post.
> As MVP Aaron said, there are some known issues in sp_spaceused, however,
> you'd better take the following steps to see whether it make more effect.
> First of all, take the latest updates of SQL Server. Some known issues are
> fixed. For example,
> FIX: Statistics Maintenance Creates Significant Bottleneck on SQL Servers
> that Use 16 or More CPUs
> http://support.microsoft.com/?id=293849
> Secondly, run the command sp_spaceused like below
> sp_spaceused @.updateusage = true
> Note that when you include the "@.updateusage=true" parameter for
> sp_spaceused, the sp will run DBCC UPDATEUSAGE, which will update the
> allocation information in sysindexes to insure that it provides accurate
> information. Running DBCC UPDATEUSAGE on the whole database is a
> resource-intensive task and can have a significant impact on the
> performance of a system. Note that sysindexes is also updated at
> checkpoints (though not as thoroughly), so during peak load times you may
> want to invesitgate omitting the @.updateusage parameter and running
> "CHECKPOINT" before sp_spaceused instead.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
|||Hi Sebastien,
Thanks for your further explaination.
Executing sp_spaceused computes the amount of disk space used for data and
indexes, and the disk space used by a table in the current database. If no
table (objname) is given, sp_spaceused reports on the space used by the
entire current database.
After you drop an index, sp_spaceused reports inaccurate information. This
is a known limitation. However, sp_spaceused provides a quick, rough
estimate. If you notice incorrect values when you execute sp_spaceused, run
DBCC UPDATEUSAGE to correct the inaccuracies. Because this statement takes
some time to run on large tables or databases use it only when you suspect
incorrect values being returned or when other users are not active.
I am afraid that you will have to run that DBCC command if sp_spaceused
@.updateusage = true doesn't make sence
I am sorry for the inconvenience it may caught. Thank you for your patience
and corperation. If you have any questions or concerns, don't hesitate to
let me know.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

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 error when trying to return data in datagrid

VB.NET 2003 / SQLS2K

The Stored Procedure returns records within Query Analyzer.
But when the Stored Procedure is called by ADO.NET ~ it produced the
following error message.

--------
Exception Message: Failed to enable constraints. One or more rows
contain values violating non-null, unique, or foreign-key constraints.
--------

--------
Exception Source: System.Data
--------

If I click OK past the error messages I will get data filling the
datagrid. However not as I would like to see it.

Even though it returns the proper data rows and includes all the
columns I asked for, it also returns plenty of columns I didn't ask for
(all the columns of the main table) and all those columns are filled
with "null"

In addition each row header contains a red exclaimation mark whch when
hovered over reads;

"Column 'cmEditedBy' does not allow DBNull.Values."

An interesting thing about this column 'cmEditedBy' is that there is
noting wrong with it and all rows for that column contain data.

I believe this error is a mistake! But it probably indicates some other
problem. How should I track its cause?

M O R E ...
Below is the code in the data layer, the stored procedure, and the data
returned within query analyzer.

\\
'DataAdapter
Friend daView041CmptCyln As New SqlDataAdapter

'SqlCommand
Private daView041CmptCyln_CmdSel As New SqlCommand

'Add the command
daView041CmptCyln.SelectCommand = daView041CmptCyln_CmdSel

'Select
With daView041CmptCyln_CmdSel
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_View_041Cmpt_ByJobCyln"
.Connection = sqlConn
With daView041CmptCyln_CmdSel.Parameters
.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.Int, _
4, ParameterDirection.ReturnValue, False, CType(0,
Byte), _
CType(0, Byte), "", DataRowVersion.Current, Nothing))
'Criteria
.Add("@.fkJob", SqlDbType.Text).Value = _
"48c64a55-874d-40d0-addc-7245f5d9c118"
'.Add("@.fkJob", SqlDbType.Text).Value = f050View.jobID
End With
End With
//

\\
ALTER PROCEDURE usp_View_041Cmpt_ByJobCyln
(@.fkJob char(36))
AS SET NOCOUNT ON;

SELECT
JobNumber,
DeviceName,
ComponentName,

Description,
Quan,
Bore,
Stroke,
Rod,
Seconds,
CylPSI,
PosA,
PosB,
PosC,
PosD,
PosE,
HomeIsRet,
RetIsRetrac,
POChecks,
Regulated,
FlowControl,
PortSize,
LoadMass

FROM tbl040cmpt
INNER JOIN tbl030Devi ON fkDevice = pkDeviceId
INNER JOIN tbl020Proc ON fkProcess = pkProcessId
INNER JOIN tbl010Job ON fkJob = pkjobId
INNER JOIN lkp202ComponentType ON fkComponenttype = pkComponentTypeId
INNER JOIN lkp201DeviceType ON fkDeviceType = pkDeviceTypeId
INNER JOIN lkp101PortSize on cmSmallint05 = pkPortSizeId

WHERE
(fkJob = @.fkJob)
--fkJob = '48c64a55-874d-40d0-addc-7245f5d9c118'
AND fkComponentType = 2

GO
//

(note - columns are wrapped)
\\
F1111Clip DriverCylinderClip Driver_2 - Top -
Cylinder91.2502.250.8752.250NULL01101110011/8 NPTNULL
F1111Punch MechCylinderPunch Mech_1 -
Cylinder_222.1002.0001.0001.234NULL11000110011/8
NPTNULL
F1111Clip
DriverCylinderBottom92.1002.0001.0001.000NULL11010110011/4
NPTNULL
F1111Punch MechCylinderPunch Mech_1 -
Cylinder_122.1002.0001.0001.000NULL01000110011/8
NPTNULL
F1111DegateCylinderDegate 1 -
Cylinder21.1882.500.8751.000NULL11000110011/8 NPTNULL
F1111Clip DriverCylinderClip Driver 1 -
Bottom11.1801.250.8751.000NULL00011110011/4 NPTNULL
//dbuchanan (dbuchanan52@.hotmail.com) writes:
> VB.NET 2003 / SQLS2K
> The Stored Procedure returns records within Query Analyzer.
> But when the Stored Procedure is called by ADO.NET ~ it produced the
> following error message.
> --------
> Exception Message: Failed to enable constraints. One or more rows
> contain values violating non-null, unique, or foreign-key constraints.
> --------

Nah, it sounds as if that message is produced by .Net Framework. The
stored procedure pleads innocense.

> Even though it returns the proper data rows and includes all the
> columns I asked for, it also returns plenty of columns I didn't ask for
> (all the columns of the main table) and all those columns are filled
> with "null"
> In addition each row header contains a red exclaimation mark whch when
> hovered over reads;
> "Column 'cmEditedBy' does not allow DBNull.Values."

Well, that column is not in the result set, so obviously when you try
to populated the DataSet, NULL values is all you get. And apparently
they are not permitted.

I don't have that much experience of ADO .Net, but it sounds to me that
you have run some wizard that has constructed your dataset, and you then
have not been careful which columns to include. (Personally, if I were
to work with data sets, I would probably construct them manually.) Or is
there some thought behind of including columns that are not reported by the
query?

While not relevant to your problem, permit me to point an issue of style
with your query: you table includes six tables, no column is prefixed
with any alias (or the table name). This makes it very difficult for
anyone who looks at query to tell which table, the columns are coming
from. This also mean that if the DBA adds, say, "Description" to one
more table, the procedure will no longer compile because that column name
is now ambiguous.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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%'