During a large, long running store procedure that creates a large number of constraints and temp tables, it is difficult to determine exactly where errors are occurring. Below is a small example that illustrates the problems I am encountering.
First, assume the following temp tables have been created already (when my procedure runs, they are supposed to be nonexistent):
CREATE TABLE #temp1 ( temp_col_1 INT NOT NULL,
temp_col_2 NVARCHAR(10) NULL,
CONSTRAINT pk_temp1_temp_col_1 PRIMARY KEY CLUSTERED ( temp_col_1 ) )
CREATE TABLE #temp2 ( temp2_col_1 INT NOT NULL,
temp2_col_2 INT NULL,
CONSTRAINT pk_temp2_temp2_col_2 PRIMARY KEY CLUSTERED ( temp2_col_1 )
Then we have the following stored procedure:
ALTER PROCEDURE [proc].[temp_proc]
AS
BEGIN TRY
CREATE TABLE #temp1 ( temp_col_1 INT NOT NULL,
temp_col_2 NVARCHAR(10) NULL,
CONSTRAINT pk_temp1_temp_col_1 PRIMARY KEY CLUSTERED ( temp_col_1 ) )
CREATE TABLE #temp2 ( temp2_col_1 INT NOT NULL,
temp2_col_2 INT NULL,
CONSTRAINT pk_temp2_temp2_col_2 PRIMARY KEY CLUSTERED ( temp2_col_1 ) )
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT;
END CATCH
When the procedure is run without the tables or constrains existing, it creates the temp tables and constraints no problem, returning Command(s) completed successfully.
When the tables already exist, the following error message is returned:
Msg 50000, Level 16, State 0, Line 18
Could not create constraint. See previous errors.
Which is less than helpful, seeing as how the actual error I am looking for is one up the call stack. Trying to recreate the constraints and tables without a try-catch and error handling gets the following trace where I can immediately identify the problem:
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'pk_temp1_temp_col_1' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
How do I get SQL Server to return the full trace, or multiple error entries?
Aucun commentaire:
Enregistrer un commentaire