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'