CODEWITHSUNDEEP
sql-servert-sqltransactionssql-insert
Abe
Abe

Reputation: 6514

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

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

Upvotes: 1

Views: 932

Answers (4)

Der U
Der U

Reputation: 3335

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

(1290 separate statements with hardcoded content really scares me)

Upvotes: 0

TMcManemy
TMcManemy

Reputation: 824

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.

Upvotes: 2

granadaCoder
granadaCoder

Reputation: 27904

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'

Upvotes: 0

paparazzo
paparazzo

Reputation: 45106

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.

Upvotes: 0

Related Questions