Retrieving all error messages generated by a command

Question

Retrieving all error messages generated by a command

Colin Daley

SSChasing Mays

Points: 624
More actions
July 17, 2014 at 6:14 am

#287920

Hello All,
Some T-SQL commands can fail with multiple errors. For example, if you try to disable a primary key constraint using
ALTER TABLE t1 NOCHECK CONSTRAINT PK, you will get messages like:
Msg 11415, Level 16, State 1, Line 341
Object 'PK' cannot be disabled or enabled. This action applies only to foreign key and check constraints.
Msg 4916, Level 16, State 0, Line 341
Could not enable or disable the constraint. See previous errors.
However, in the code below, only the last message is printed. How can I print all the error messages?
USE tempdb;
CREATE TABLE #t1(c1 INT CONSTRAINT PK PRIMARY KEY);
BEGIN TRY
ALTER TABLE #t1 NOCHECK CONSTRAINT PK;
PRINT 'Primary key disabled'
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

The only way to show all the errors is to use THROW and let the client get the error. There is a CONNECT item about it found here.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Colin Daley SSChasing Mays Points: 624 More actions · Answer 2

Thanks, Jack. I marked it as the solution. It does not solve my problem as the client is SSMS, but the question has been answered (the feature is lacking, and there are no specific plans to add it). THROWing defeats my purpose as it causes the script to stop executing at that point. In some cases, I can forget about TRY/CATCH and just "GO" to start a new batch, but I can't do that when I am using variables, or I'll get:

Must declare the scalar variable @x1.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Yeah, it's a bummer. It would be nice if it was an errors collection like .NET has or a virtual errors table like inserted/deleted.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Gotta love their honesty 😛

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato