seriate RequestError: SqlContext Error. Failed on step "GetData" with: "Timeout: Request failed to complete in 15000ms

Question

I am trying to run a stored procedure on my SQL database from the node server using seriate. however i get the follwoing error and i am not sure why. I'd really appreciate your help on it.

Error: { [RequestError: SqlContext Error. Failed on step "GetData" with: "Timeout: Request failed to complete i 15000ms"] name: 'RequestError', message: 'SqlContext Error. Failed on step "GetData" with: "Timeout Request failed to complete in 15000ms"', code: 'ETIMEOUT', number: 'ETIMEOUT', lineNumber: undefined, state: undefined, class: undefined, serverName: undefined, procName: undefined, precedingErrors: [], step: 'GetData' }

This is my code:

var sql = require( "seriate" );

var connection = {
    name: "example-1",
    user: "user",
    password: "pass",
    host: "host_ip",
    database: "Test"
};    

exports.getDataSql = function(req, res) {
    var results = {};

    sql.execute( connection, {
       procedure: "GetData",
        params: {
            Name: {
                type: sql.NVARCHAR(50),
                val: "user2"
            },
            LName: {
                type: sql.NVARCHAR(50),
                val: "user1"
            },
            finalName: {
                type: sql.NVARCHAR(50),
                val: "user3"
            }
        }
    }).then( function( results ) {
        console.log("getting data: ");
        res.json(results[0][0]);

    }, function( err ) {
        console.log( "Error", err );
        res.status(500).send({ error: 'Something failed!' });
    } );

};

Ohaus

Answer 1

You probably need to bump up your request timeout as follows:

var connection = {
    name: "example-1",
    user: "user",
    password: "pass",
    host: "host_ip",
    database: "Test",
    requestTimeout: 300000
};    

There is also a connectionTimeout property that could be affecting you as well. But this issue is probably the requestTimeout

Counterbalance

Thank you for your reply! I added the request timeout but I get the same error still. do you know what else it might be? – Ohaus

You could try setting connectionTimeout as well but the error message seems to indicate the request timeout is the issue. Does the error still indicate that "Request failed to complete in 15000ms" as opposed to the new timeout value of 300000? And how long does your stored proc take to run when executed in SSMS? – Counterbalance

I added both and still get the same error, yes it still says "Request failed to complete in 15000ms" as if the line we added did not bump up the timeout value. it runs in 1 minute in SSMS. – Ohaus

Hmmm...strange. My next troubleshooting steps to see what's going on would be 1) Add a new console.log to ensure you are running the latest file. (Sorry, sometimes it pays to check for the silly stuff) 2) Change your proc or point it temporarily to a new proc that you know runs very fast (or at least faster than the default timeout of 15 secs) and see if you can successfully run that. – Counterbalance

Yes very strange... you are right my first thought was that it is running the old code but I verified it is running the changed code cause I added alerts, also I can run fast stored procedure using the same code with no problem. Do you think it might be the version of seriate I am using maybe? – Ohaus

My version of seriate is 0.7.0. I ran into the same issue as you yesterday (same error message and code) and adding requestTimeout worked for me. The only difference is that I was calling a script file and not a stored proc. Only thing left would be to post an issue if you think it's reproducible and you could perhaps try using the node mssql package directly. – Counterbalance

I see, thank you so much for your help. I will investigate this to figure out why mine is not woking then. good to know it works. I have already raised an issue but have not received any answer yet. thanks again! – Ohaus

Answer 2

In my case the error is due to an uncommited transaction. Closing SQL Management Studio free the tables, since I had an error commiting a previous transaction from the editor.

Savadove

Answer 3

I use mssql which this library implements. mssql uses tedious to connect to the remote database. In my case I was using it inside of an Azure function and did not call

pool.close()

in the end of each execution, so it ran out of connections and then started to time out. tedious should probably provide a better error description.

Affected