CODEWITHSUNDEEP
sqlsql-server
lonious
lonious

Reputation: 696

SQL Transaction Basics

I've been hopping around SQL related forums quite a bit the last few days (I'm new) and I've been making some headway! However, I can't seem to crack this one. Here's my query: 

USE EpiworksTest;

BEGIN TRANSACTION [tran3]
BEGIN TRY
    INSERT [Catalog].[TaskDefinition] ([ID], [WIPDesc], [SpecTable], [ResultsTable], [SpecFilePath], [SpecFileName], [SetupFilePath], [SetupFileName], [EWProduct], [Process], [Active], [CalcDur], [UnTimedEst], [ChamberUse], [DateCreated]) 
    VALUES (1, N'wp', N'spec', N'0', NULL, NULL, NULL, NULL, N'ew', NULL, NULL, NULL, NULL, NULL, NULL);
    SET IDENTITY_INSERT [Catalog].[TaskDefinition] OFF
    INSERT [Catalog].[TaskStatus] ([ID], [Name], [Description]) 
    VALUES (1, N'Static', N'wat');
    COMMIT TRANSACTION [tran3]
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION [tran3]
END CATCH

I've checked and rechecked my table columns -- they match up with both of the Inserts here. Both of my tables are currently empty. This query says it completes successfully but no rows are updated even though I have two Inserts! Any ideas? I've tried recreating my schema. Will the SQL "compiler?" tell me if it has errors that I catch or tell me if it rolls any changes back? I have a feeling there's something funky going on with the try/catch or the transaction since that's what I've been getting to know today. Thanks for any responses.

Upvotes: 1

Views: 121

Answers (2)

M.Ali
M.Ali

Reputation: 69494

By default the IDENTITY_INSERT option is OFF so you do not need to set it to off if you havent set it to ON. I think this is something what you are trying to do.

USE EpiworksTest;


BEGIN TRY
   BEGIN TRANSACTION [tran3]; 

    SET IDENTITY_INSERT [Catalog].[TaskDefinition] ON;
    INSERT [Catalog].[TaskDefinition] ([ID], [WIPDesc], [SpecTable], [ResultsTable], [SpecFilePath], [SpecFileName], [SetupFilePath], [SetupFileName], [EWProduct], [Process], [Active], [CalcDur], [UnTimedEst], [ChamberUse], [DateCreated]) 
    VALUES (1, N'wp', N'spec', N'0', NULL, NULL, NULL, NULL, N'ew', NULL, NULL, NULL, NULL, NULL, NULL);
    SET IDENTITY_INSERT [Catalog].[TaskDefinition] OFF;

    INSERT [Catalog].[TaskStatus] ([ID], [Name], [Description]) 
    VALUES (1, N'Static', N'wat');

    COMMIT TRANSACTION [tran3];
END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
   BEGIN
    ROLLBACK TRANSACTION [tran3];
   END

    SELECT  ERROR_MESSAGE()  AS [Error_Message]
           ,ERROR_LINE()     AS [Error_Line]
           ,ERROR_NUMBER()   AS [Error_Number]
           ,ERROR_SEVERITY() AS [Error_Severity]

END CATCH

Upvotes: 1

usr
usr

Reputation: 171178

You are suppressing all errors so you never see them. This is a very severe anti-pattern and now you know why. Un-suppress errors. I suggest you add

THROW

to the catch block which rethrows the current error. Errors usually should bubble up to a place that can meaningfully handle and log them.

Upvotes: 1

Related Questions