CODEWITHSUNDEEP
sql-server
gray
gray

Reputation: 133

SQL Server Execution Timeout Expired

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

Upvotes: 1

Views: 14252

Answers (1)

sepupic
sepupic

Reputation: 8697

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)

Upvotes: 2

Related Questions