SQL Server Transaction Commits but results are missing

Question

I am not sure how to describe the problem, so I will do the best I can and answer any questions.

My project is a Database Project for SQL Server 2008 (client dependent). Within that project I have a series of Seed Scripts that are compiled into a single sql script. The bulk of those seed scripts deal with inserting a large number of VARBINARY records (200MB+ worth of inserts). Since Visual Studio (and most other text editors) have trouble dealing with large files, I have the inserts logically split out into different files. Each file is wrapped in a transaction. The inserts themselves are constructor inserts.

The problem I have is each transaction commits without error. However I am getting the incorrect number of records in the finished database. Or the other effect I am seeing is zero records in the resulting table. While the scripts run, I see records inserted but once the script completes all the records are gone! So ... very confused. Any insight as to why I might be seeing this behavior would be great!

Each is laid out like e.g.

BEGIN TRANSACTION AdBannerBlackEagle
PRINT N'BEGIN TRANSACTION AdBannerBlackEagle'

PRINT N'AdBanner BlackEagle English'

INSERT INTO [dbo].[ContentImageTemplateDetails]
    ([ContentImageTemplateId]
    ,[ContentImageTemplateDetailsCodeKey]
    ,[Data]
    ,[Checksum]
    ,[ContentType]
    ,[RegionID]
    ,[LanguageCode]
    ,[DisplayOrder]
    ,[CreatedDate]
    ,[ModifiedDate]
    )
VALUES
    (
        1,
        'AdBanner'
        ,0xFFD8FFE10018457869660000<reduced for brevity>
        ,'5F44C068DEE6507FDB01F52C9D66C291E06896DAACB2A6D687BDCE0EE3A5CE47'
        ,'jpg'
        ,5
        ,'en-DE'
        ,1
        ,SYSDATETIMEOFFSET()
        ,SYSDATETIMEOFFSET()
    ),
    (
        1,
        'AdBanner'
        ,0xFFD8FFE10018457869660000<reduced for brevity>
        ,'059FF9C3FCAF65EEDC01719D791AE88791C5C1A647BBC7F229A5F3FB0862D54D'
        ,'jpg'
        ,5
        ,'de-DE'
        ,2
        ,SYSDATETIMEOFFSET()
        ,SYSDATETIMEOFFSET()
    )
GO

COMMIT TRANSACTION AdBannerBlackEagle
PRINT N'COMMIT TRANSACTION AdBannerBlackEagle'

Answer 1

You state that:

each transaction commits without error

I believe there is a nuance here that is being overlooked. The DML statement can cause an error and the COMMIT still happens (hence "nothing" is Committed) because this is not being done in a TRY...CATCH construct (and I assume that you are not setting XACT_ABORT ON at the beginning of the scripts). I believe that the DML statements are getting an error (possibly a truncation one?) but the Commit then runs and succeeds.

AND / OR:

Another possibility is that you have an unmatched BEGIN TRAN in one of the scripts (meaning, no matching COMMIT). Or how exactly are you running these scripts? If there is an open Transaction that is not committed by the time the scripts end, then the Transaction will be automatically rolled-back.

Individual DML / DLL statements, when executed outside of an explicit BEGIN TRAN...COMMIT/ROLLBACK are already their own Transaction and are automatically committed. Hence, you do not need BEGIN TRAN...COMMIT for single statements, unless you have some additional logic after the DML / DDL statement that conditionally decides on COMMIT or ROLLBACK. Also, unless you need the ability to discard portions of work that covers many statements within a single Transaction without rolling back the ALL statements, then you don't need to name them. It looks like you should start by removing the BEGIN TRAN / COMMIT / ROLLBACK statements, but still keep the TRY...CATCH statements that you are adding.