Can we handle ALL errors within a stored proceudre?

Hello, friends,
We call stored procedures from our app (asp.net), and use Try...Catch to
handle any possible DB error. But, can we handle all errors in a stored
proceudre? In another word, all DB errors should be caught within a stored
procedure, and return back to callers gracefully, as if nothing wrong.
We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
Violation were still caught by our app, not sp.
Any ideas, reference papers, sample source code?
Thanks a lot.http://www.sommarskog.se/error-handling-II.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:59F7E8E6-1477-4A42-9B42-BB34D0136102@.microsoft.com...
> Hello, friends,
> We call stored procedures from our app (asp.net), and use Try...Catch to
> handle any possible DB error. But, can we handle all errors in a stored
> proceudre? In another word, all DB errors should be caught within a stored
> procedure, and return back to callers gracefully, as if nothing wrong.
> We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
> Violation were still caught by our app, not sp.
> Any ideas, reference papers, sample source code?
> Thanks a lot.

Can we handle all errors within a stored proceudre.

Hello, friends,
We are calling stored procedures (sp) from our web app (asp.net). Since
using Try...Catch... in asp.net with C# or VB.net is very expensive, we are
considering to handle all database error in sp so that web app won't see any
DB error.
Do we have a way to catch all erros in sp and return back to callers
gracefully, as if nothing wrong in DB?
We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
Unique contraint violation, etc.
Any ideas, reference papers, sample source code?
Thanks a lotexamnotes <Andrew@.discussions.microsoft.com> wrote in
news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:

> We are calling stored procedures (sp) from our web app (asp.net).
> Since using Try...Catch... in asp.net with C# or VB.net is very
> expensive, we are considering to handle all database error in sp so
> that web app won't see any DB error.
> Do we have a way to catch all erros in sp and return back to callers
> gracefully, as if nothing wrong in DB?
> We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
> Unique contraint violation, etc.
> Any ideas, reference papers, sample source code?
In SQL Server 2005 you can, I've used the following code sample to
demonstrate it in my classes (Using the pubs database):
set XACT_ABORT on
begin try
begin transaction
declare @.id int;
insert into jobs values ('Testjobb',10,10)
set @.id = @.@.identity;
insert into employee values ('123456789','Gates',null,'Bill',@.id+
1,10,'0877',GetDate());
commit transaction
end try
begin catch
select error_message();
rollback transaction
end catch
I'll leave it up to you to implement this in a stored procedure.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||What about SQL Server 2000? That is the one we are using.
"Ole Kristian Bang?s" wrote:

> examnotes <Andrew@.discussions.microsoft.com> wrote in
> news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:
>
> In SQL Server 2005 you can, I've used the following code sample to
> demonstrate it in my classes (Using the pubs database):
> set XACT_ABORT on
> begin try
> begin transaction
> declare @.id int;
> insert into jobs values ('Testjobb',10,10)
> set @.id = @.@.identity;
> insert into employee values ('123456789','Gates',null,'Bill',@.id+
> 1,10,'0877',GetDate());
> commit transaction
> end try
> begin catch
> select error_message();
> rollback transaction
> end catch
> I'll leave it up to you to implement this in a stored procedure.
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||examnotes <Andrew@.discussions.microsoft.com> wrote in
news:C25DD758-622D-4CB5-A870-F112B1C337C6@.microsoft.com:

> What about SQL Server 2000? That is the one we are using.
Sorry. This feature was introduced in SQL Server 2005. In SQL Server 2000
you have to check for errors after each and every command that may cause an
error (which may happen to be almost everyone)
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messagingsql

can server logs catch who performs critical actions?

By critical actions, I mean some operations like dumping database, changing
data etc.
Can SQL server logs catch those actions or do I need to turn on some
switches to be able to do so?
Thanks in advance for any help.
Bing
bing
I have to set recovery mode to FULL or Bulk-Logged.
For more info please refer to the BOL
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing
|||Thanks, Uri. But I don't see how that full or bulk-logged model related to
my question. The database is using FULL model, by the way. What I was
looking for was who accessed which databases when and performed what
operations. I don't see much of that in SQL server logs. Maybe I have to
use some 3rd party tools, e.g. Log Explorer?
Bing
"Uri Dimant" wrote:

> bing
> I have to set recovery mode to FULL or Bulk-Logged.
> For more info please refer to the BOL
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> changing
>
>
|||Look at Profiler to see if it does what you want. If you need more, use some of the auditing tools.
(My suggestion...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:3A0D1A1D-529B-427B-9B68-5B30CA56E0EF@.microsoft.com...[vbcol=seagreen]
> Thanks, Uri. But I don't see how that full or bulk-logged model related to
> my question. The database is using FULL model, by the way. What I was
> looking for was who accessed which databases when and performed what
> operations. I don't see much of that in SQL server logs. Maybe I have to
> use some 3rd party tools, e.g. Log Explorer?
> Bing
> "Uri Dimant" wrote:
|||Check out Log Explorer from Lumigent, there are probably other similar tools
but nothing that comes with SQL Server. You might be able to use Profiler to
determine some of what you want, but it will require some setup and will
only help if it's running when the events you're tracking happen.
Mike Kruchten
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
> changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing
|||Depending on what you need to captuer, you could also create triggers that
are fired under certain circumstances - e.g. when data in a field are edited
or deleted.
Regards
Steen
bing wrote:[vbcol=seagreen]
> Thanks, Uri. But I don't see how that full or bulk-logged model
> related to my question. The database is using FULL model, by the
> way. What I was looking for was who accessed which databases when
> and performed what operations. I don't see much of that in SQL
> server logs. Maybe I have to use some 3rd party tools, e.g. Log
> Explorer?
> Bing
> "Uri Dimant" wrote:

can server logs catch who performs critical actions?

By critical actions, I mean some operations like dumping database, changing
data etc.
Can SQL server logs catch those actions or do I need to turn on some
switches to be able to do so?
Thanks in advance for any help.
Bingbing
I have to set recovery mode to FULL or Bulk-Logged.
For more info please refer to the BOL
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing|||Thanks, Uri. But I don't see how that full or bulk-logged model related to
my question. The database is using FULL model, by the way. What I was
looking for was who accessed which databases when and performed what
operations. I don't see much of that in SQL server logs. Maybe I have to
use some 3rd party tools, e.g. Log Explorer?
Bing
"Uri Dimant" wrote:
> bing
> I have to set recovery mode to FULL or Bulk-Logged.
> For more info please refer to the BOL
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> > By critical actions, I mean some operations like dumping database,
> changing
> > data etc.
> >
> > Can SQL server logs catch those actions or do I need to turn on some
> > switches to be able to do so?
> >
> > Thanks in advance for any help.
> >
> > Bing
>
>|||Look at Profiler to see if it does what you want. If you need more, use some of the auditing tools.
(My suggestion...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:3A0D1A1D-529B-427B-9B68-5B30CA56E0EF@.microsoft.com...
> Thanks, Uri. But I don't see how that full or bulk-logged model related to
> my question. The database is using FULL model, by the way. What I was
> looking for was who accessed which databases when and performed what
> operations. I don't see much of that in SQL server logs. Maybe I have to
> use some 3rd party tools, e.g. Log Explorer?
> Bing
> "Uri Dimant" wrote:
>> bing
>> I have to set recovery mode to FULL or Bulk-Logged.
>> For more info please refer to the BOL
>> "bing" <bing@.discussions.microsoft.com> wrote in message
>> news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
>> > By critical actions, I mean some operations like dumping database,
>> changing
>> > data etc.
>> >
>> > Can SQL server logs catch those actions or do I need to turn on some
>> > switches to be able to do so?
>> >
>> > Thanks in advance for any help.
>> >
>> > Bing
>>|||Check out Log Explorer from Lumigent, there are probably other similar tools
but nothing that comes with SQL Server. You might be able to use Profiler to
determine some of what you want, but it will require some setup and will
only help if it's running when the events you're tracking happen.
Mike Kruchten
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
> changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing|||Depending on what you need to captuer, you could also create triggers that
are fired under certain circumstances - e.g. when data in a field are edited
or deleted.
Regards
Steen
bing wrote:
> Thanks, Uri. But I don't see how that full or bulk-logged model
> related to my question. The database is using FULL model, by the
> way. What I was looking for was who accessed which databases when
> and performed what operations. I don't see much of that in SQL
> server logs. Maybe I have to use some 3rd party tools, e.g. Log
> Explorer?
> Bing
> "Uri Dimant" wrote:
>> bing
>> I have to set recovery mode to FULL or Bulk-Logged.
>> For more info please refer to the BOL
>> "bing" <bing@.discussions.microsoft.com> wrote in message
>> news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
>> By critical actions, I mean some operations like dumping database,
>> changing data etc.
>> Can SQL server logs catch those actions or do I need to turn on some
>> switches to be able to do so?
>> Thanks in advance for any help.
>> Bing

can server logs catch who performs critical actions?

By critical actions, I mean some operations like dumping database, changing
data etc.
Can SQL server logs catch those actions or do I need to turn on some
switches to be able to do so?
Thanks in advance for any help.
Bingbing
I have to set recovery mode to FULL or Bulk-Logged.
For more info please refer to the BOL
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing|||Thanks, Uri. But I don't see how that full or bulk-logged model related to
my question. The database is using FULL model, by the way. What I was
looking for was who accessed which databases when and performed what
operations. I don't see much of that in SQL server logs. Maybe I have to
use some 3rd party tools, e.g. Log Explorer?
Bing
"Uri Dimant" wrote:

> bing
> I have to set recovery mode to FULL or Bulk-Logged.
> For more info please refer to the BOL
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> changing
>
>|||Look at Profiler to see if it does what you want. If you need more, use some
of the auditing tools.
(My suggestion...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:3A0D1A1D-529B-427B-9B68-5B30CA56E0EF@.microsoft.com...[vbcol=seagreen]
> Thanks, Uri. But I don't see how that full or bulk-logged model related t
o
> my question. The database is using FULL model, by the way. What I was
> looking for was who accessed which databases when and performed what
> operations. I don't see much of that in SQL server logs. Maybe I have t
o
> use some 3rd party tools, e.g. Log Explorer?
> Bing
> "Uri Dimant" wrote:
>|||Check out Log Explorer from Lumigent, there are probably other similar tools
but nothing that comes with SQL Server. You might be able to use Profiler to
determine some of what you want, but it will require some setup and will
only help if it's running when the events you're tracking happen.
Mike Kruchten
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
> changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing|||Depending on what you need to captuer, you could also create triggers that
are fired under certain circumstances - e.g. when data in a field are edited
or deleted.
Regards
Steen
bing wrote:[vbcol=seagreen]
> Thanks, Uri. But I don't see how that full or bulk-logged model
> related to my question. The database is using FULL model, by the
> way. What I was looking for was who accessed which databases when
> and performed what operations. I don't see much of that in SQL
> server logs. Maybe I have to use some 3rd party tools, e.g. Log
> Explorer?
> Bing
> "Uri Dimant" wrote:
>