COALESCE(NULLIF(@intError, 0), @@ERROR)

Anybody know if the COALESCE(NULLIF ...)...) can raise error itself, and if
it safely to use this combination of checking error status
Every time after i calling to some store procedure i use this:
---
DECLARE @.intError int
EXEC @.intError = myProcedure @.param1, @.param2 ....
SELECT @.intError = COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
IF(@.intError =0 )
.....
.....
---
my question is, can the @.@.ERROR variable have an error that COALESCE or
NULLIF function were raise?
Thanks.
P.S. if it's not good idea to use it after calling to stored procedures so
what can u advice me
Message posted via http://www.webservertalk.comHi
I'm not sure I understand you.
Why not just doing the following?
create proc myproc
@.par int
as
--do something here
if @.@.error <>0
return -1
else
resturn 1
go
declare @.err int
select @.err=exec myproc @.par
if @.err =-1
raiserror ('It was an error',16,1)
Read this great article
http://www.sommarskog.se/error-handling-I.html
"E B via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:dc61838570f642498321c727d4c5742b@.SQ
webservertalk.com...
> Anybody know if the COALESCE(NULLIF ...)...) can raise error itself, and
if
> it safely to use this combination of checking error status
> Every time after i calling to some store procedure i use this:
> ---
> DECLARE @.intError int
> EXEC @.intError = myProcedure @.param1, @.param2 ....
> SELECT @.intError = COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
> IF(@.intError =0 )
> .....
> .....
> ---
> my question is, can the @.@.ERROR variable have an error that COALESCE or
> NULLIF function were raise?
> Thanks.
> P.S. if it's not good idea to use it after calling to stored procedures so
> what can u advice me
> --
> Message posted via http://www.webservertalk.com|||However my question is, can the COALESCE or NULLIF functions change the
status of @.@.ERROR variable
Message posted via http://www.webservertalk.com|||Any statement *could* result in an error but that looks pretty unlikely
in this case. Your code is about as safe as any other error-handling
code can be. In general keep your error handling code as simple as
possible.
David Portas
SQL Server MVP
--|||so is it good idea to use COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
after caling to some stored procedure or function in sql
Thanks
Message posted via http://www.webservertalk.com|||what the way i need to check if any error occured after calling to stored
procedure?
Message posted via http://www.webservertalk.com|||An SP won't actually return a value of NULL but if the SP can't be run (mayb
e
it doesn't exist or you don't have EXEC permissions) then the result leaves
the value of @.interror unaffected (NULL in your case). In that situation you
r
code will assign the error code to @.interror, which seems reasonable enough.
David Portas
SQL Server MVP
--
"E B via webservertalk.com" wrote:

> so is it good idea to use COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
> after caling to some stored procedure or function in sql
> Thanks
> --
> Message posted via http://www.webservertalk.com
>|||There is no error event in TSQL so the only way is to check the @.@.ERROR valu
e
after EVERY statement. This doesn't catch all errors though (see the article
that Uri posted). A system I'm using is to put error-handling in its own pro
c
and call that with @.@.ERROR as a parameter:
EXEC @.err = usp_error_handler @.@.ERROR, @.calling_proc, @.user_id
the proc redturns the @.@.ERROR value. If @.@.ERROR is zero the proc just
returns immediately without executing the handling code.
That's reasonable for processes that are long running but maybe not an
overhead you'll want in an SP that's called frequently. TSQL in SQL Server
2000 provides very little scope for good error handling and much of the time
you may find it easier and better to catch errors in your calling code in VB
/ C# or whatever.
David Portas
SQL Server MVP
--

Clustered SQL Server freezes & restarts itself in Enterprise Manager

Hi,
In our large organisation we have SQL Server 2000 running on a 2 node cluster using Windows 2000 Advanced Server Clustering. Each node has 2GB RAM. The server has about 10 databases only one of which is significant size at about 1GB. Our website accesses this database via ASP. The website receives between 10000-40000 hits per hour.
The problem is that periodically (not necessarily during high load periods at all) this single database 'freezes' (not the whole server - the other databases seem fine) and refuses all login attempts from the website. This happened again on the weekend when the database was down for about 6 hours. When we go into Enterprise Manager and click on the instance, there is initially no activity for an unusual amount of time but eventually the instance is selected and the tree expands. When this happens everything is ok again and the website can access the database again.
We have only just enabled failure auditing today, so we don't have any info from there and are at a loss as to whats going on. Has anyone out there had similar experiences or have any ideas?

(We will look at upgrading to 2005 and upgrading the machines too but being a big company that will take a lot of time with red tape etc.)

Many thanks.

Hello,

Hopefully not an offensive question, but is there anything in either the win eventlogs or sql errorlog?

Is the problematic databases' filegroup on a seperate drive (resource) to the other db's?

Cheers

Rob

|||Hi Rob,Thanks for the reply. No there was nothing suspicious in the SQL errorlog and nothing strange in the Windows event log either. All of the databases use the same shared storage.Cheers, Nathan.|||

Hi Nathan,

I would have leant towards either blocking or an inordinate amount of locks, however the latter would affect all databases on the instance. Is there a chance the outage is being caused by the db (auto)growing (either log or data)? I think this would only be presented if it were a "large" db...

In order to narrow it down a bit, I'd be create and run a perfmon log, with all the usual suspects: memory,page reads/faults, cpu, tps, log growth, cache hit ratio, user connections etc. Run the log until you encounter the db freeze, and hopefully the perfmon log will at least give you some clue.

Are you sure it's just the one db that freezes, or have you seen if affect others?

Cheers,

Rob