CODEWITHSUNDEEP
c#sql-serverado.net
VansFannel
VansFannel

Reputation: 45921

Timeout inserting 1,000,000 of rows in database (sometimes)

I'm developing a C# library with .NET Framework 4.0, Entity Framework 6.1.3 and SQL Server 2012.

I have this stored procedure that I call with Ado.net:

CREATE PROCEDURE [dbo].[InsertCodes]
      @codes as dbo.SerialAndValueList READONLY -- Codes
    , @username nvarchar(50)            
    , @source nvarchar(50)              
    , @column2 tinyint                  
AS
    Declare @codesCount int;

    set nocount on;

-- =========== CODE =============   
    INSERT INTO Code (Serial, Column1, Column2, Column3, UserName, Source)
        SELECT Serial, Column1, @column2, @column3, @username, @source FROM @codes

    IF (@@ERROR != 0)
        RETURN -1 -- Database error

RETURN 0

And SerialAndValueList:

CREATE TYPE [dbo].[SerialAndValueList]
AS TABLE
(
    Serial nvarchar(20),
    Column1 tinyint
)

I'm using it to store 1,000,000 of Serials (@codes has 1,000,000 of rows).

I'm testing it with the same amount of codes and sometimes I get a time out exception, and other times not.

I have found the article Configure the remote query timeout Server Configuration Option suggesting:

EXEC sp_configure 'remote query timeout', 0 ;  
GO  
RECONFIGURE ;  
GO

But if I do this, I want to disable it only in this stored procedure (at the beginning), and re-enable it before leave it.

Another question is that the timeout occurs only 60 seconds after I run the stored procedure but reading here, remote query timeout Option, I see the default value is 600 seconds.

How can I get the current value of remote query timeout Option on my database?

Or, how can I fix this problem?

Upvotes: 3

Views: 801

Answers (1)

Alex K.
Alex K.

Reputation: 175796

Remote XXX timeout values relate to timeouts for outgoing remote queries run on the server - e.g. an outgoing query to a linked server, the settings have no effect on incoming queries that come from a client.

If you want a higher timeout set .CommandTimeout which is available on ObjectContext & SqlCommand.

Upvotes: 3

Related Questions