T-SQL : remote stored procedure result is sometimes empty

Question

I have a strange behaviour when calling a remote stored procedure: normally it works as expected, but sometimes I don't get a result from the stored procedure.

In detail: I have two stored procedures. The first one is calling the second one, triggered by a third, which is triggered by SQL Server Agent, but I'll focus on #1 and #2.

Here is stored procedure #1 (a little bit reduced):

CREATE PROCEDURE [dbo].[spCalculateForecast]
AS
BEGIN
    DECLARE @ForecastResult TABLE(
        [ProductId] INT NOT NULL,
        [Forecast30] DECIMAL(13,3) NOT NULL,
        [Forecast60] DECIMAL(13,3) NOT NULL,
        [Forecast90] DECIMAL(13,3) NOT NULL,
        [Forecast120] DECIMAL(13,3) NOT NULL
    )

    INSERT INTO @ForecastResult
        EXECUTE sp_execute_external_script
                @language = N'R',
                @script = N'

    DO SOME MAGIC

    OutputDataSet <- forecast.result
    ',
    @input_data_1 = N'
        SELECT *
        FROM Forecast_Data
        ORDER BY ProductId, Date',
    @input_data_1_name = N'forecast.data';

    IF NOT EXISTS (SELECT TOP 1 1 FROM @ForecastResult)
    BEGIN
        RAISERROR ('No forecast results generated.', 18, 1);
        RETURN
    END

    SELECT * FROM @ForecastResult
END

As you can see, I've added a check to validate the result of stored procedure #1 is not empty.

In stored procedure #2 I call #1:

CREATE PROCEDURE [dbo].[spExecuteForecast]
AS
BEGIN
    BEGIN TRY
        DECLARE @ForecastResult TABLE(
            [ProductId] INT NOT NULL,
            [Forecast30] DECIMAL(13,3) NOT NULL,
            [Forecast60] DECIMAL(13,3) NOT NULL,
            [Forecast90] DECIMAL(13,3) NOT NULL,
            [Forecast120] DECIMAL(13,3) NOT NULL
        )

        INSERT INTO @ForecastResult
            EXEC [RemoteServer].[RemoteDB].[dbo].[spCalculateForecast]

        IF NOT EXISTS (SELECT TOP 1 1 FROM @ForecastResult)
        BEGIN
            RAISERROR ('Forecast result empty', 18, 1)
        END

        -- DO SOMETHING WITH THE RESULT
    BEGIN CATCH
        -- log error
    END CATCH
END

In stored procedure #2, I've added also a check to validate the result of stored procedure #1 is not empty.

As mentioned, both stored procedures work as expected, except if an error occurs. Then it's everytime error 'Forecast result empty' from stored procedure #2.

For me, it looks like stored procedure #1 creates a valid result but it could not been fetched by stored procedure #2. I thought it could be a connection problem but then I would expect a error message indicating this or if it's a problem at creating the forecast result, I would expect a error message indicating this or at minimum the validation error of stored procedure #1.

Does anybody have an idea what's going on here?

Thanks in advance.

Answer 1

After searching a long time and trying everything I can thought of, I've run a monitoring with SQL Server Profiler (as @Amirhossein advised, thanks for that). The monitoring shows an error, which pointed me in the wrong direction. But the monitoring contained also the start and end time of the RPC call which was exactly 20 minutes. The only possible answer for me was: Timeout. I had a new search direction and found this answer: https://stackoverflow.com/a/54531530/12997628

A short SQL statement shows me, that the RPC Timeout is set to 20 Minutes.

SELECT * FROM sys.configurations WHERE name = 'remote query timeout (s)'

So, in short: I got no data and no error, because TRY CATCH doesn't catch timeouts

I've increased the timeout and now everything is fine.