How to capture an error message in T-SQL?

I need to capture an actual error message and place this record into a
log table.
I know that I can get error # from ERROR variable.
Where can I get the text, that consist information aboout objects that
caused problem?
Example
create procedure MyProc as
DECLARE Err int;
DECLARE ErrMsg varchar(256)
DELETE FROM ParentTable where ID=@.P_ID;
set @.Err=ERROR;
if @.Err!=0
begin
set @.ErrMsg=''?
insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
end;
go;
I expect to see in MyLog something like `Foreign key violation, child
record found, constraint "FK_ChildTable"`,
or "No permission to delete for table "ParentTable"`
or whatever I would see if I execute this TSQL in SQL Analyzer manually.
BTW, are all of such errors being written into some standard MSSQL log?
Is it SysLog or where?
Thanks,
MarkMark
Unfortunatly you cannot capture such kind of errors in current version.I
know with Yukon will introduce error handle like try--catch but cannot give
more details at this time.
On other hand look at my example helps you to solve the problem (Modify it
for your needs)
create table parent
(
col int not null primary key
)
go
insert into parent values (1)
go
create table child
(
col int not null primary key,
col1 int not null --references parent (col)
)
go
insert into child(col,col1) values (1,1)
insert into child(col,col1) values (2,1)
go
create trigger my_tr on parent for delete
as
if exists ( select * from child c join deleted d on c.col1=d.col)
raiserror ('Cannot delete',16,1)
go
delete parent where col=1
go
drop table child
drop table parent
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>|||> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot
give
> more details at this time.
See http://msdn.microsoft.com/msdnmag/i...on/default.aspx
for new Yukon enhancements in error handling with the TRY/CATCH construct
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
************************************
SQL Source Control 2003 - for
SQL Server Source Safe integration
and custom databases documentation|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Sebastian, thanks,
It is good that Yukon can handle exceptions by TRY/CATCH. However I have
not found any Error Message Text capturing in examples. The error
messages are coded into the procedure, and are not actual messages from
Server. That unfortunate because in many cases the sole error code is
not enough, the object names associated with exception would be very
helpfull for further troubleshooting.
Mark
Sebastian K. Zaklada wrote:
> give
>
>
> See http://msdn.microsoft.com/msdnmag/i...ledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ************************************
> SQL Source Control 2003 - for
> SQL Server Source Safe integration
> and custom databases documentation
>|||Mark,

> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information about objects that
> caused problem?
You can't at the TSQL level.

> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
It depends whether the error is configured to. Check out the manage errors
dialog in EM and you'll see that checkbox. "Soft" errors are not written to
logs by default.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>