Chewing on RAISERROR(N'[Text in brackets]ouch!', 11, 1)

Is this supposed to happen' (MSSQL2K Service pack 3/3a)
/*--
RAISERROR(N'[Presto!][Ho ho!]A minor error occurred.', 10, 1)
RAISERROR(N'[Presto!][Ho ho!]A major error occurred.', 11, 1)
--*/
[Presto!][Ho ho!]A minor error occurred.
Server: Msg 50000, Level 11, State 1, Line 2
A major error occurred.> Is this supposed to happen' (MSSQL2K Service pack 3/3a)
IMHO, no. I would expect the same message text from both statements. The
behavior you observed occurs on my SP4 SQL Server too.
Hope this helps.
Dan Guzman
SQL Server MVP
<rja.carnegie@.excite.com> wrote in message
news:1126615038.811015.292510@.f14g2000cwb.googlegroups.com...
> Is this supposed to happen' (MSSQL2K Service pack 3/3a)
> /*--
> RAISERROR(N'[Presto!][Ho ho!]A minor error occurred.', 10, 1)
> RAISERROR(N'[Presto!][Ho ho!]A major error occurred.', 11, 1)
> --*/
> [Presto!][Ho ho!]A minor error occurred.
> Server: Msg 50000, Level 11, State 1, Line 2
> A major error occurred.
>|||I discussed this with some of the other MVPs and it looks like the culprit
is the Query Analyzer 'parse ODBC message prefixes' option. It looks like
it's too aggressive in removing the ODBC noise from messages.
You'll get the expected results if you turn off the option under
Tools-->Options-->Connections. However, the messages will also be prefixed
with the '[Microsoft][ODBC SQL Server Driver][SQL Server]' stuff.
Hope this helps.
Dan Guzman
SQL Server MVP
<rja.carnegie@.excite.com> wrote in message
news:1126615038.811015.292510@.f14g2000cwb.googlegroups.com...
> Is this supposed to happen' (MSSQL2K Service pack 3/3a)
> /*--
> RAISERROR(N'[Presto!][Ho ho!]A minor error occurred.', 10, 1)
> RAISERROR(N'[Presto!][Ho ho!]A major error occurred.', 11, 1)
> --*/
> [Presto!][Ho ho!]A minor error occurred.
> Server: Msg 50000, Level 11, State 1, Line 2
> A major error occurred.
>|||So long as the missing text isn't piling up somewhere and threatening a
huge problem some time in the future somehow :-)
Thanks to all you MVPs for efforts. So it's Query Analyzer itself
doing it... okay. I noticed it when I did something like,
SET @.workstring =
REPLACE(
N'RAISERROR(''@.{tbl} had some kind of error.'', 16,
1)
, N'@.{tbl}', QUOTENAME(@.tablename))
EXEc sp_executesql @.workstring
(Of course QUOTENAME('TableName') is '[TableName]'.)
Incidentally Google Groups (through which I'm sending this) in its v2
beta version, has a similar issue with article headers - and one group
that I use, alt.fan.pratchett, likes to self-classify articles as [R]
(relevant, on-topic), [I] (off-topic), various others (meta-topical).
These go - even if protected by "Re: ", apparently - but some
experiments with ".[I]", etc, make 'em stay. The issue there isn't
just the Google user's experience but the view that other users get
when a Google user participates.
It seems reasonable that leading text, even a space, will continue to
save my leading parenthetical text here, too.
(And I'll be surprised if Google Groups is held on Microsoft SQL
servers, but I guess why not?)
Does this Query Analyzer issue rate making some kind of official bug
report, I wonder? If telling you doesn't already count... I'd rather
work around it than spend money. I was only being cute with quoted
table names anyway - I don't need to quote my names, but better safe
than sorry and it also helps them to stand out. But in this case, not
;-)
Maybe Microsoft knew already; I find it difficult to pick keywords for
a Knowledge Base search on this issue.
Dan Guzman wrote:
> I discussed this with some of the other MVPs and it looks like the culprit
> is the Query Analyzer 'parse ODBC message prefixes' option. It looks like
> it's too aggressive in removing the ODBC noise from messages.
> You'll get the expected results if you turn off the option under
> Tools-->Options-->Connections. However, the messages will also be prefixe
d
> with the '[Microsoft][ODBC SQL Server Driver][SQL Server]' stuff.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <rja.carnegie@.excite.com> wrote in message
> news:1126615038.811015.292510@.f14g2000cwb.googlegroups.com...|||> Does this Query Analyzer issue rate making some kind of official bug
> report, I wonder? If telling you doesn't already count... I'd rather
> work around it than spend money.
I agree with your triage assessment. I'd rather see the devs spend their
time tidying up SQL 2005. In any case, I believe the QA behavior should be
documented in a KB article. BTW, it's a non-issue with SQL 2005 since Query
Analyzer is superseded by SQL Server Management Studio.
Hope this helps.
Dan Guzman
SQL Server MVP
<rja.carnegie@.excite.com> wrote in message
news:1126700741.448066.200040@.o13g2000cwo.googlegroups.com...
> So long as the missing text isn't piling up somewhere and threatening a
> huge problem some time in the future somehow :-)
> Thanks to all you MVPs for efforts. So it's Query Analyzer itself
> doing it... okay. I noticed it when I did something like,
> SET @.workstring =
> REPLACE(
> N'RAISERROR(''@.{tbl} had some kind of error.'', 16,
> 1)
> , N'@.{tbl}', QUOTENAME(@.tablename))
> EXEc sp_executesql @.workstring
> (Of course QUOTENAME('TableName') is '[TableName]'.)
> Incidentally Google Groups (through which I'm sending this) in its v2
> beta version, has a similar issue with article headers - and one group
> that I use, alt.fan.pratchett, likes to self-classify articles as [R]
> (relevant, on-topic), [I] (off-topic), various others (meta-topical).
> These go - even if protected by "Re: ", apparently - but some
> experiments with ".[I]", etc, make 'em stay. The issue there isn't
> just the Google user's experience but the view that other users get
> when a Google user participates.
> It seems reasonable that leading text, even a space, will continue to
> save my leading parenthetical text here, too.
> (And I'll be surprised if Google Groups is held on Microsoft SQL
> servers, but I guess why not?)
> Does this Query Analyzer issue rate making some kind of official bug
> report, I wonder? If telling you doesn't already count... I'd rather
> work around it than spend money. I was only being cute with quoted
> table names anyway - I don't need to quote my names, but better safe
> than sorry and it also helps them to stand out. But in this case, not
> ;-)
> Maybe Microsoft knew already; I find it difficult to pick keywords for
> a Knowledge Base search on this issue.
> Dan Guzman wrote:
>|||Dan Guzman wrote:
> I agree with your triage assessment. I'd rather see the devs spend their
> time tidying up SQL 2005.
I don't mind if Bill Gates's money is spent on my bug. Just not my
money, please. ;-)

> In any case, I believe the QA behavior should be
> documented in a KB article.
You think there is one, or someone should make one?

> BTW, it's a non-issue with SQL 2005 since Query
> Analyzer is superseded by SQL Server Management Studio.
A whole new set of quirks, no doubt! But how soon do you think you'll
get us off of 2000'
Thanks again, meanwhile.

CheckSum in SQL2000

I want to use checksum function for ntext data field in a table. But this
function accepts only varchar. Is there a way i can find checksum for ntext
fields in SQL 2000.
Thanks!!!I think you can cast it to a varchar
ie checksum(cast(field1 as varchar(8000))|||If you absolutely need to use CHECKSUM on the ntext field and, depending on
the size of the ntext values you already have in the specific table, you
might be able to convert the ntext to nchar/nvarchar or char/varchar and
apply checksum afterwards:
create table ENTEXT
(ID int, txt ntext)
insert into ENTEXT
values (1, 'any value')
select CHECKSUM(convert(char(8000),txt))
, CHECKSUM(convert(nchar(4000),txt))
from ENTEXT
drop table ENTEXT
The only limit would be the number of characters you can house temporarily
(4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
"skg" wrote:

> I want to use checksum function for ntext data field in a table. But this
> function accepts only varchar. Is there a way i can find checksum for ntex
t
> fields in SQL 2000.
> Thanks!!!
>
>|||Thanks All!!!
Mostly my text fields are more than 8k. I am just curious will the checksum
work if i do a substring and
compute checksum of individual chunks of 4k and finally call
checksum_agg(checksum(value)) to
get the aggregate checksum for the data field.
TIA
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:86760E51-9059-4609-B55F-6000E5113C1D@.microsoft.com...
> If you absolutely need to use CHECKSUM on the ntext field and, depending
> on
> the size of the ntext values you already have in the specific table, you
> might be able to convert the ntext to nchar/nvarchar or char/varchar and
> apply checksum afterwards:
> create table ENTEXT
> (ID int, txt ntext)
> insert into ENTEXT
> values (1, 'any value')
> select CHECKSUM(convert(char(8000),txt))
> , CHECKSUM(convert(nchar(4000),txt))
> from ENTEXT
> drop table ENTEXT
> The only limit would be the number of characters you can house temporarily
> (4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
>
>
> "skg" wrote:
>|||Would checksuming only the first 8000 characters suffice? For what purpose
are you wanting to use the checksum?
"skg" <skg@.yahoo.com> wrote in message
news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>I want to use checksum function for ntext data field in a table. But this
>function accepts only varchar. Is there a way i can find checksum for ntext
>fields in SQL 2000.
> Thanks!!!
>|||JT thanks!!!
We have pdf documents which are saved in db in text field. we want to remove
duplicates.
thx
"JT" <someone@.microsoft.com> wrote in message
news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
> Would checksuming only the first 8000 characters suffice? For what purpose
> are you wanting to use the checksum?
> "skg" <skg@.yahoo.com> wrote in message
> news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>|||This is the problem with storing unstructured data (or at least data with an
unfamiliar structure) in a relational database, so identifying rows
containing duplicate 'values' is not simple.
Think about how PDF documents are structured internally. I'm not familiar
with the PDF format specifically, but I'm guessing it begins with a header
containing meta data fields about the documents's size, the file name the
originaly saved as, the date/time created and last written to, and probably
even it's own internal checksum values. Therefore, even if the documents are
several hundred KBs or even MBs in size, only the first 8000 or 4000 bytes
or so may uniquely identify them. You don't need to actually parse this
information, just understand it's a stream of unique bytes and create a
checksum value off of it. This would apply not just to PDFs but also to MS
Word, JPGs, ZIPs or most any structured file type. You will need to read up
on the specifications involved and experiment to confirm this theory would
consistently work. I'm interested in the results, becuase I could possibly
use this technique myself, so reply back to the group when you discover
something.
"skg" <skg@.yahoo.com> wrote in message
news:OKPXYxvIGHA.3176@.TK2MSFTNGP12.phx.gbl...
> JT thanks!!!
> We have pdf documents which are saved in db in text field. we want to
> remove duplicates.
> thx
> "JT" <someone@.microsoft.com> wrote in message
> news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
>