T-SQL Transaction with many (1000+) INSERTS gets disconnected

Question

Is there a limit on the number of INSERT statements you can put inside a transaction? I am getting a transport error and the connection disconnects in the middle of running the statement.

Should I just populate a temp table and then run a single INSERT statement?

Error Message in the middle of a 1000+ insert inside a transaction...

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. 
(provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

I run the script inside SQL Server 2008 R2 10.50.4000.0 against a database on my local Windows 8 machine (Toshiba Satellite S955, x64 8GB RAM, Intel (R) Core(TM) i5-3317U CPU @ 1.70GHz 1.70 GHz)

declare @currentDatabase nvarchar(255);
select @currentDatabase = DB_NAME();

if CHARINDEX('canada', @currentDatabase) = 0
begin
    print 'Please run this script on the Canada server'
    return
end

begin try
begin transaction
SET IDENTITY_INSERT company_name ON
insert into company_name (company_name_id, company_name, display_index, approved_by_administrator, requestor_id, modify_timestamp, active, create_timestamp) values (10000601010001, '6 Day Dental', 5868, 1, NULL, '2013-04-08 00:00:00.000', 1, '2013-04-08 00:00:00.000');
insert into company_name (company_name_id, company_name, display_index, approved_by_administrator, requestor_id, modify_timestamp, active, create_timestamp) values (10000601010002, '7-Eleven', 5869, 1, NULL, '2013-04-08 00:00:00.000', 1, '2013-04-08 00:00:00.000');
insert into company_name (company_name_id, company_name, display_index, approved_by_administrator, requestor_id, modify_timestamp, active, create_timestamp) values (10000601010003, 'AC Properties', 5870, 1, NULL, '2013-04-08 00:00:00.000', 1, '2013-04-08 00:00:00.000');
-- 1287 more INSERT statements...
SET IDENTITY_INSERT company_name OFF

commit
end try
begin catch
    rollback
    declare @Msg nvarchar(max)
    select @Msg=Error_Message();
    raiserror('Error Occured: %s', 20, 101,@Msg) with log
end catch

How are you executing the script? This error has [already been asked about](http://stackoverflow.com/questions/6534175/a-transport-level-error-has-occurred) but it seems that there's no one answer for every situation. To answer your question about the number of statements, there is a [maximum batch size](http://msdn.microsoft.com/en-us/library/ms143432.aspx) in SQL Server, but it's 256MB by default so that seems unlikely to be an issue here. — Pondlife, May 01 '13 at 16:24
I just run the script inside SQL Server Management Studio (SQL Server 2008 R2 [10.50.400]) against my local database. — Abe, May 01 '13 at 17:04

score 2 · Accepted Answer · edited May 23 '17 at 12:06

Problem

I believe your problem lies in the following line:

raiserror('Error Occured: %s', 20, 101,@Msg) with log

To start, let's take a look at the signature of RAISERROR:

RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

As emphasized, the piece I want to focus on is the severity argument. Within the same MSDN page, you will find the following (regarding the severity argument):

[severity] Is the user-defined severity level associated with this message

Also:

Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.

So far, so good. From your code snippet, we can see that an error message with a severity level of 20 is being generated, via RAISERROR. As such, the WITH LOG option is utilized.

However, the MSDN page also notes:

Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.

So, in effect, your call to RAISERROR is terminating your connection.

Solution

I assume that your intention for the CATCH block is to "rethrow" the original error that caused the CATCH block to run. If so, take a look at the following (taken from Is there an equivalent in T-SQL to C#'s "throw;" to re-throw exceptions?):

...
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return 
    -- error information about the original error that 
    -- caused execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

This uses ERROR_MESSAGE(), ERROR_SEVERITY(), and ERROR_STATE() to gather information about the error that caused the CATCH block to run. It then uses RAISERROR to generate a new error message with said information.

score 0 · Answer 2 · answered May 01 '13 at 17:29

0

You do know you can insert more than one set of values per insert.

values (), (), ()

There in an optimal. The total number of values should be under 1024.
Looks like you have 8 values so 128 () per insert.

answered May 01 '13 at 17:29

paparazzo

44,497
23
105
176

Yes, I am aware of that option. I also tried pre-populating a temptable and calling insert (...) select * from #company_name. – Abe May 01 '13 at 17:48
Then I would believe it is a transport error. Try a different transport. You may be on named pipes (for speed). If so try TCP. And try not with log on the raiseerror. It may be as simple as the error is a constraint violation but the size of the error message is too big for the transport. – paparazzo May 01 '13 at 18:07
I'd be interested in where you have these numbers from (max. 1,024 values per insert/transaction?). Could you please cite a source for this? – stakx - no longer contributing Jun 29 '15 at 18:37
@stakx Optimal is not the same as max – paparazzo Jun 29 '15 at 22:06
@Blam: OK, "optimal" then. I'm still interested in learning about this recommendation. Can you cite a source, or is this simply from personal experience? – stakx - no longer contributing Jun 30 '15 at 06:37

score 0 · Answer 3 · answered May 01 '13 at 18:29

0

You can also try an alternate syntax that is more set-based.

INSERT INTO dbo.Employee ( LastName, FirstName ) 
          Select 'Smith', 'John'
UNION ALL Select 'Jones', 'Mary'
UNION ALL Select 'Packer', 'Penny'
UNION ALL Select 'Stokes', 'Daryl'

answered May 01 '13 at 18:29

granadaCoder

26,328
10
113
146

Der U · Answer 4 · 2013-06-18T13:44:58.233

0

the UNION looks like one statement instead of 1290, therefore better

(1290 separate statements with hardcoded content really scares me)

edited Jun 18 '13 at 13:44

answered May 01 '13 at 20:32

Der U

3,192
1
12
13

T-SQL Transaction with many (1000+) INSERTS gets disconnected

4 Answers4

Problem

Solution