SQL Server Execution Timeout Expired

Question

Is there something wrong with my code? I get an exception:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I'm trying to update a dynamic table with dynamic columns depending on the searched facility of user

ALTER PROCEDURE SP_UPDATE_FACILITY
    @featid int,
    @facilityid int,
    @updatetbl as TABLE_UPDATE_FACILITYDETAILS READONLY 
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @tblname AS NVARCHAR(255);
    SET @tblname = (SELECT dbo.FNC_Search_GetSearchTable(@facilityid));

    DECLARE @key varchar(255);
    DECLARE @value varchar(255);

    DECLARE @MyCursor CURSOR

    SET @MyCursor = CURSOR FOR
        SELECT col_key, col_value FROM @updatetbl

    OPEN @MyCursor 

    FETCH NEXT FROM @MyCursor INTO @key , @value

    WHILE @@FETCH_STATUS = 0 
    BEGIN
        IF(@key != '' and @value != '')
        BEGIN
            BEGIN TRY  
                SET @value = (CAST ( @value AS varchar(255)));
            END TRY  
            BEGIN CATCH
                SET @value = (CAST ( @value AS float));
            END CATCH;

            DECLARE @sSQL NVARCHAR(500);
            SET @sSQL = 'UPDATE ' + QUOTENAME(@tblname) + ' SET ' + QUOTENAME(@key) + ' =  @value WHERE FEATID = @featid'

            EXEC sp_executesql @sSQL , N'@value VARCHAR(255), @featid INT', @key, @featid

            FETCH NEXT FROM @MyCursor INTO @key, @value
        END
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END
GO

Is there a way to loop through rows of table then get the values per rows of table

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Jul 31, 2017 at 9:12
@Value is already varchar(255), why would you cast it to varchar(255)? You could update all columns at once instead of having an update statement per column. — Peter
– Peter, Commented Jul 31, 2017 at 9:14

sepupic · Accepted Answer · 2017-07-31 09:44:18Z

2

SQL Server does not have any "execution timeout", while the clients can set it.

If you use C#, the default Execution timeout is 30 seconds that is too small. Of course you should investigate what your query is waiting for using sys.dm_os_waiting_tasks, but if 30 seconds are not enough to your code to complete, just change this Execution timeout value to smth else (0 = infinite)

answered Jul 31, 2017 at 9:44

sepupic

8,7371 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gray Over a year ago

thanks but i already optimized my query so it doesn't exceed 30 seconds

sepupic Over a year ago

Your update can be blocked by any SELECT, and you can see who blocks it in sys.dm_os_waiting_tasks

Collectives™ on Stack Overflow

SQL Server Execution Timeout Expired

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related