In short: Is it possible to work your way through the error stack in a CATCH block?
In long: A single statement can generate more than one error message. Unhandled (outside of a TRY...CATCH) we see all the messages. Within a CATCH block we can see only the last one (which often says something like "could not perform operation - see previous messages"). Any way we can see these previous messages in the CATCH block?
Yah - it's the ERROR_MESSAGE() function family I am looking at.
This is illustrative - I don't want any crap about "use the correct names in the first place you monkey".
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.foo'))
DROP TABLE dbo.foo
CREATE TABLE dbo.foo
bar INT IDENTITY(1, 1) NOT NULL
, CONSTRAINT pugh PRIMARY KEY CLUSTERED (bar)
ALTER TABLE dbo.foo
DROP CONSTRAINT puhg
There is a prior error message to one that is returned by the function:
Hmmm....An interesting problem. I think this can be done, if the drop constraint statement is issued by a vbscript or .NET program, because there is an error stack for those. It rather looks like the try-catch implemented for T-SQL is intended to help you recover from DML problems, rather than DDL ones.
Yeah, building a .NET harness for testing deployment script in the event there was a previous error is gilding the lilly a bit. Thanks fort he idea - I'll stick with verbose success logging to go with my error traps