Prev: very useful info
Next: SQL Help
From: Guru on 21 Jan 2010 03:10
Hi All,

For the following code, I am getting two difference errors. I need to
capture the first error message. Please advise how to capture the
first error message. I need to log this info in Error_Log table.

CODE
=====

drop table authors
go
CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO

ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
GO

print @@error

================

Thanks in Advance,
Guru
From: Uri Dimant on 21 Jan 2010 04:03
Hi
Can you use BEGIN ..TRY ..BEGIN CATCH ... to capture the errors?

"Guru" <guruprasathb(a)gmail.com> wrote in message
news:d0729226-30bd-499a-86e5-76f1793e6b23(a)f12g2000yqn.googlegroups.com...
> Hi All,
>
> For the following code, I am getting two difference errors. I need to
> capture the first error message. Please advise how to capture the
> first error message. I need to log this info in Error_Log table.
>
> CODE
> =====
>
> drop table authors
> go
> CREATE TABLE Authors (
> AuthorID INT NOT NULL PRIMARY KEY,
> Name VARCHAR(100) NOT NULL
> )
> GO
>
> ALTER TABLE Authors
> ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
> GO
>
> print @@error
>
> ================
>
> Thanks in Advance,
> Guru


From: Erland Sommarskog on 21 Jan 2010 06:26
Guru (guruprasathb(a)gmail.com) writes:
> For the following code, I am getting two difference errors. I need to
> capture the first error message. Please advise how to capture the
> first error message. I need to log this info in Error_Log table.
>
> CODE
>=====
>
> drop table authors
> go
> CREATE TABLE Authors (
> AuthorID INT NOT NULL PRIMARY KEY,
> Name VARCHAR(100) NOT NULL
> )
> GO
>
> ALTER TABLE Authors
> ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
> GO
>
> print @@error

BEGIN TRY
ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
END TRY
BEGIN CATCH
SELECT error_message(), error_severity(), error_number()
END CATCH

This requires SQL 2005. If you are on SQL 2000, you have two options: 1)
have a client that caputers the error. 2) Upgrade to SQL 2005/2008.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Plamen Ratchev on 21 Jan 2010 10:01
I think TRY CATCH gets only the second error as well. The only solution I have used is using client side code. Plus this
error you cannot catch at the same level of execution, it has to be at a higher level, like this:

BEGIN TRY
EXEC('ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)');
END TRY
BEGIN CATCH
SELECT error_message(), error_severity(), error_number();
END CATCH

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: very useful info
Next: SQL Help