"On Error Resume Next" in SQL Server

I am executing a stored procedure that uses a cursor to do
a CONTAINS search for each record and to produce a result
set from that for output. The trouble is that if a good
old "ignored words" error occurs for one of the records
the procedure stops running. I need this to carry on
running regardless of this as the only reason an error
would occur would be due to bad user input which does not
bother me and can therefore be "ignored".
I have followed the advice in the KB article at
http://support.microsoft.com/default.aspx?scid=kb;en-
us;246800 for formatting input for CONTAINS searches but
there is always some nasty ASCII/UNICODE character that
slips through.
Is there a method to isolate the full-text search (or any
part of the procedure for that matter) and to guarantee
that my stored procedure will run to the end of the cursor?
Any help would be much appreciated.Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Andy Wakeling" wrote:

> I am executing a stored procedure that uses a cursor to do
> a CONTAINS search for each record and to produce a result
> set from that for output. The trouble is that if a good
> old "ignored words" error occurs for one of the records
> the procedure stops running. I need this to carry on
> running regardless of this as the only reason an error
> would occur would be due to bad user input which does not
> bother me and can therefore be "ignored".
> I have followed the advice in the KB article at
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;246800 for formatting input for CONTAINS searches but
> there is always some nasty ASCII/UNICODE character that
> slips through.
> Is there a method to isolate the full-text search (or any
> part of the procedure for that matter) and to guarantee
> that my stored procedure will run to the end of the cursor?
> Any help would be much appreciated.
>|||When you are running your query in query analyzer, does it stop running? If
so, then please post the code. If it doesn't, then it is your code that is
causing it to stop. Just have your calling code ignore the errors and
continue on.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:203501c50ad3$6ee202d0$a601280a@.phx.gbl...
>I am executing a stored procedure that uses a cursor to do
> a CONTAINS search for each record and to produce a result
> set from that for output. The trouble is that if a good
> old "ignored words" error occurs for one of the records
> the procedure stops running. I need this to carry on
> running regardless of this as the only reason an error
> would occur would be due to bad user input which does not
> bother me and can therefore be "ignored".
> I have followed the advice in the KB article at
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;246800 for formatting input for CONTAINS searches but
> there is always some nasty ASCII/UNICODE character that
> slips through.
> Is there a method to isolate the full-text search (or any
> part of the procedure for that matter) and to guarantee
> that my stored procedure will run to the end of the cursor?
> Any help would be much appreciated.|||Louis,
I won't post the actual code as it is a massive SP and
besides, we've tried various tests in QA as well but the
gist is as follows:
DECLARE TestCursor CURSOR FOR /*WHATEVER*/
OPEN TestCursor
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM TestCursor INTO @.SearchText
SET @.SearchText = FormatSearchText(@.SearchText)
/*
This is a UDF with output as per KB article as mentioned.
If anything goes wrong this returns '' as I am not
bothered if it cannot resolve input but note: This can
still output junk that can break the CONTAINS search.
*/
INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
/*
When run in QA, if this query causes an ignored-word error
the SP stops dead. I need it to carry on to the end of the
cursor.
*/
END, CLOSE, DEALLOCATE etc.
SELECT * FROM #TEMPTABLE /* Output of entire cursor */
That's pretty much what I'm trying to achieve. What do you
reckon?
Cheers
Andy

>--Original Message--
>When you are running your query in query analyzer, does
it stop running? If
>so, then please post the code. If it doesn't, then it is
your code that is
>causing it to stop. Just have your calling code ignore
the errors and
>continue on.
>--
>----
--
>Louis Davidson - drsql@.hotmail.com
>SQL Server MVP
>Compass Technology Management - www.compass.net
>Pro SQL Server 2000 Database Design -
>http://www.apress.com/book/bookDisplay.html?bID=266
>Blog - http://spaces.msn.com/members/drsql/
>Note: Please reply to the newsgroups only unless you are
interested in
>consulting services. All other replies may be ignored :)
>"Andy Wakeling" <anonymous@.discussions.microsoft.com>
wrote in message
>news:203501c50ad3$6ee202d0$a601280a@.phx.gbl...
do
result
not
any
cursor?
>
>.
>|||No idea, as I don't use full text search at all. However, if the
formatSearchText can output stuff to cause it to fail, is this text
something that would obviously make it fail? Such that you could clean it
up in the UDF? Hopefully someone else who has used full text search can see
the problem with it. Maybe posting some of the values that cause it to
fail?
Sorry I am not much help on this subject.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:21c301c50cff$5894aea0$a401280a@.phx.gbl...
> Louis,
> I won't post the actual code as it is a massive SP and
> besides, we've tried various tests in QA as well but the
> gist is as follows:
> DECLARE TestCursor CURSOR FOR /*WHATEVER*/
> OPEN TestCursor
> WHILE (1 = 1)
> BEGIN
> FETCH NEXT FROM TestCursor INTO @.SearchText
> SET @.SearchText = FormatSearchText(@.SearchText)
> /*
> This is a UDF with output as per KB article as mentioned.
> If anything goes wrong this returns '' as I am not
> bothered if it cannot resolve input but note: This can
> still output junk that can break the CONTAINS search.
> */
> INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
> WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
> /*
> When run in QA, if this query causes an ignored-word error
> the SP stops dead. I need it to carry on to the end of the
> cursor.
> */
> END, CLOSE, DEALLOCATE etc.
> SELECT * FROM #TEMPTABLE /* Output of entire cursor */
> That's pretty much what I'm trying to achieve. What do you
> reckon?
> Cheers
> Andy
>
> it stop running? If
> your code that is
> the errors and
> --
> interested in
> wrote in message
> do
> result
> not
> any
> cursor?

"for xml raw, element" output format

This query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml raw('theme'), elements
gives me this output:
<theme>
<themekey>airborne geophysical survey</themekey>
</theme>
<theme>
<themekey>airborne geophysics</themekey>
</theme>
<theme>
<themekey>diamonds</themekey>
</theme>
<theme>
<themekey>drilling</themekey>
</theme>
<theme>
<themekey>geochemical survey</themekey>
</theme>
<theme>
<themekey>geology</themekey>
</theme>
How do I get the format to look as follows:
<theme>
<themekey>airborne geophysical survey</themekey>
<themekey>airborne geophysics</themekey>
<themekey>diamonds</themekey>
<themekey>drilling</themekey>
<themekey>geochemical survey</themekey>
<themekey>geology</themekey>
</theme>
Thanks,
Lee AnneYou did not mention the version of SQL Server (is it 2000 or 2005).
In SQL Server 2005 you can query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml path(''), root('theme')
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne|||Ok, that's SQL Server 2005 :-) I should look at given query more carefully
:-)
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne|||Thank you, that was exactly what I needed!!!
Lee Anne
"Pawel Potasinski" wrote:

> Ok, that's SQL Server 2005 :-) I should look at given query more carefully
> :-)
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa3 w
> wiadomo?ci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
>
>

"for xml raw, element" output format

This query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml raw('theme'), elements
gives me this output:
<theme>
<themekey>airborne geophysical survey</themekey>
</theme>
<theme>
<themekey>airborne geophysics</themekey>
</theme>
<theme>
<themekey>diamonds</themekey>
</theme>
<theme>
<themekey>drilling</themekey>
</theme>
<theme>
<themekey>geochemical survey</themekey>
</theme>
<theme>
<themekey>geology</themekey>
</theme>
How do I get the format to look as follows:
<theme>
<themekey>airborne geophysical survey</themekey>
<themekey>airborne geophysics</themekey>
<themekey>diamonds</themekey>
<themekey>drilling</themekey>
<themekey>geochemical survey</themekey>
<themekey>geology</themekey>
</theme>
Thanks,
Lee Anne
You did not mention the version of SQL Server (is it 2000 or 2005).
In SQL Server 2005 you can query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml path(''), root('theme')
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne
|||Ok, that's SQL Server 2005 :-) I should look at given query more carefully
:-)
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne
|||Thank you, that was exactly what I needed!!!
Lee Anne
"Pawel Potasinski" wrote:

> Ok, that's SQL Server 2005 :-) I should look at given query more carefully
> :-)
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa3 w
> wiadomo?ci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
>
>

CDATA doesn't seem to be working

I have this bit of SQL
NULL as [license_Overflow!3!!cdata]
unioned to another query that populates it
cr.licenseOverflow
However my output doesn't include CDATA.
<resLicense>
<license_Overflow><P>I am in a P
tag</P></license_Overflow>
</resLicense>
Am I doing something wrong?john.wilker@.gmail.com wrote:
> I have this bit of SQL
> NULL as [license_Overflow!3!!cdata]
> unioned to another query that populates it
> cr.licenseOverflow
> However my output doesn't include CDATA.
> <resLicense>
> <license_Overflow><P>I am in a P
> tag</P></license_Overflow>
> </resLicense>
> Am I doing something wrong?
FAQ. See http://xml.silmaril.ie/authors/cdata/ and
the following question http://xml.silmaril.ie/authors/html/
///Peter|||Thanks Peter. I actually need cdata to handle markup from an older
system that allowed markup in fields.
Is there an option aside of making the XSL handle all the possible
markup tags we're likely to see. We have no control over what gets put
in, so it's be a royal pain to try to handle it all in XSL, no?
Shouldn't !!cdata do what I want? Or am I not understanding how it
works?|||FOR XML EXPLICIT is supposed to produce a CDATA section given a !cdata
directive.
However, if you use SQL Server 2005 and FOR XML produces XML type (has
"TYPE" directive) or assigned to XML-typed variable/parameter/column then it
is expected. XML data type does not preserve CDATA sections. If it's not
your case please post a repro.
Thanks,
Eugene
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<john.wilker@.gmail.com> wrote in message
news:1144970072.177577.118700@.e56g2000cwe.googlegroups.com...
>I have this bit of SQL
> NULL as [license_Overflow!3!!cdata]
> unioned to another query that populates it
> cr.licenseOverflow
> However my output doesn't include CDATA.
> <resLicense>
> <license_Overflow><P>I am in a P
> tag</P></license_Overflow>
> </resLicense>
> Am I doing something wrong?
>|||John wrote:
> Thanks Peter. I actually need cdata to handle markup from an older
> system that allowed markup in fields.
That can be a pain all right.

> Is there an option aside of making the XSL handle all the possible
> markup tags we're likely to see. We have no control over what gets put
> in, so it's be a royal pain to try to handle it all in XSL, no?
Once you have template matching everything *else*, add
<xsl:template match="*">
<xsl:copy>
<xsl:for-each select="@.*">
<xsl:copy-of select="."/>
</xsl:for-each>
<xsl:apply-templates/>
</xsl:copy>
</xsl:template>
This will recursively copy everything otherwise unmatched,
preserving both the element and attribute markup.

> Shouldn't !!cdata do what I want? Or am I not understanding how it
> works?
CDATA sections should be avoided unless *absolutely* necessary.
///Peter