No Error Number for certain error messages? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

No Error Number for certain error messages?

Author

Topic

kev_pru
Starting Member

2 Posts

Posted - 2006-03-29 : 06:22:55

Hi all,
A newbie here at this site, but have a few years of SQL under my belt.
I am using SQL Server 2000 and I am trying to catch errors in my stored procedures.

In my sp, I use the following code and get the string 'An error occurred loading the Unique ID' printed out as the data in the db should cause an error. The values look something like this VPUniqueID_temp = 526146

if @VPUniqueID_temp is null
begin
set @test_date = NULL
end
else
begin
set @test_date = cast(@VPUniqueID_temp as datetime)

set @err_number = @@error

PRINT '@@error is ' + ltrim(str(@@error)) + '.'

IF @err_number <> 0
BEGIN

PRINT 'An error occurred loading the Unique ID'

END
end

so, thats fine. However when I run the following on another field which contains the value ExpirationDate_temp = 'E'

if @ExpirationDate_temp is null
begin
set @ExpirationDate = NULL
end
else
begin

set @test_date = cast(@ExpirationDate_temp as datetime)

PRINT '@@error is ' + ltrim(str(@@error)) + '.'

set @err_number = @@error

IF @err_number <> 0
BEGIN

PRINT 'An error occurred loading the expiration'

END

I get this:
Server: Msg 241, Level 16, State 1, Line 861
Syntax error converting datetime from character string and no error number and no 'An error occurred loading the expiration' string.

I don't seem to be able to get an error number for this error, eventhough I can see it in Query Analyzer. I need the error number to remove problematic rows and logging of exceptions.

I have really looked everywhere for an answer to this and the only thing I can find is to do an isDate before I do the cast.

Any help is much much appreciated.
Aloha
K

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-29 : 06:28:27

I think this is because SQL will try to turn an int into a datetime, so the error hasn't got a very high severity, where as trying to turn a character into a datetime will cause a higher severity of error, one of sufficient severity that the process can not continue..

kev_pru
Starting Member

2 Posts

Posted - 2006-03-29 : 06:31:12

Hey!
Thanks for the quick answer.
Any ideas on how to get around this...aside from the isDate thing?

If I used nvarchars woudl it make a difference do you think?

Aloha
k

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-29 : 06:56:32

Probably not, you may be able to get around it by trying to catch 241, but not too sure..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 08:58:15

RickD is correct about the severity level of the error raised by the attempted cast is stopping your subsequent code from executing. The only way to catch that error in your sql code is to test for a valid datetime value before attempting the cast.

>>I have really looked everywhere for an answer to this and the only thing I can find is to do an isDate before I do the cast.
Why don't you want to use isDate()?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources