# ETIMEOUT error - MSSQL PLUS

**URL:** <https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036>\
**Category:** General\
**Created:** [30 January 2021 09:28 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036 "2021-01-30T09:28:40Z")\
**Posts on this page:** 15\
**Page:** 2

<div class="post-metadata">

**Author:** ![sathishkumarc14](https://avatars.discourse-cdn.com/v4/letter/s/e480ec/32.png) [@sathishkumarc14](https://discourse.nodered.org/u/sathishkumarc14)\
**Post date:** [3 February 2021 13:00 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/21 "2021-02-03T13:00:15Z")

</div>

Thanks for the input. I tried the same as you mentioned.

Is there any difference, using "EXEC" command in query mode? Executing stored procedure in query mode.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [3 February 2021 13:02 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/22 "2021-02-03T13:02:47Z")

</div>

> [@sathishkumarc14](#):
>
> Is there any difference, using "EXEC" command in query mode? Executing stored procedure in query mode.

Yes. Under the hood, the driver uses 2 different types of operation.

Query mode is just that - dynamic (unprepared) query.

For stored procs, use "execute procedure" mode for best performance and security.

---

<div class="post-metadata">

**Author:** ![sathishkumarc14](https://avatars.discourse-cdn.com/v4/letter/s/e480ec/32.png) [@sathishkumarc14](https://discourse.nodered.org/u/sathishkumarc14)\
**Post date:** [4 February 2021 11:14 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/23 "2021-02-04T11:14:40Z")

</div>

We are using query mode to execute stored procedure.  
And it has been called 4 times in a second.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [4 February 2021 12:37 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/24 "2021-02-04T12:37:35Z")

</div>

Did you try execute procedure mode? Did it work?  
Why on earth are you polling the database every 250ms? That suggests your whole design is not ideal. For example, if you are wanting close to real-time values, then you should probably be advertising these via something like MQTT instead of polling a database table.

---

<div class="post-metadata">

**Author:** ![sathishkumarc14](https://avatars.discourse-cdn.com/v4/letter/s/e480ec/32.png) [@sathishkumarc14](https://discourse.nodered.org/u/sathishkumarc14)\
**Post date:** [4 February 2021 12:49 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/25 "2021-02-04T12:49:48Z")

</div>

We are having "Asset Status" in database. Whenever user scans the his ID card (RFID), Node RED will get the "Asset Status" from database and proceed to our business logic.

We are not getting this much frequency (4 scans in a second) every time. But sometimes we are getting these much. It mays leads to this "ETIMEOUT" error. Is there any possibility like this?

If so, what to keep

1. Connect Timeout 2. Request Timeout 3 Cancel Timeout 4. Max Pool Size in MSSQL database configuration node?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [4 February 2021 13:49 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/26 "2021-02-04T13:49:43Z")

</div>

I have asked / suggested at least 2 times now...

> [@Steve-Mcl](#):
>
> Did you try execute procedure mode? Did it work?

---

<div class="post-metadata">

**Author:** ![sathishkumarc14](https://avatars.discourse-cdn.com/v4/letter/s/e480ec/32.png) [@sathishkumarc14](https://discourse.nodered.org/u/sathishkumarc14)\
**Post date:** [5 February 2021 02:27 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/27 "2021-02-05T02:27:49Z")

</div>

Yes. Execute Procedure mode is working. I am afraid ETIMEOUT error will happen again. Anyway will try that way.

---

<div class="post-metadata">

**Author:** ![sathishkumarc14](https://avatars.discourse-cdn.com/v4/letter/s/e480ec/32.png) [@sathishkumarc14](https://discourse.nodered.org/u/sathishkumarc14)\
**Post date:** [15 February 2021 04:31 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/28 "2021-02-15T04:31:18Z")

</div>

Hi @Steve-Mcl

We are facing again the same issue while using "Execute Procedure" mode as below,

[Name : RequestError]

[Code : ETIMEOUT]  
[Message : Failed to cancel request in 5000ms]

We kept our configuration as 5000 ms in Cancel Timeout.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [15 February 2021 07:18 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/29 "2021-02-15T07:18:56Z")

</div>

Can you add a rate limit just before the SQL node? This will evenly space out calls to database.

Add a delay node set to rate limit. Enter 5 per 1 second. Then when a burst of database activity occurs, it will space them out by 200ms. If there is no burst this will not cause a delay.

Also, there still may be a coding error and I need info to assess..

> [@Steve-Mcl](#):
>
> share your flow? (perhaps something odd is occurring)
> 
> also, can you capture the full `msg` object when error occurs ?
> 
> if you can access console log, paste that too?
> 
> PS: when pasting logs / code press the `</>` button first then paste

---

<div class="post-metadata">

**Author:** ![sathishkumarc14](https://avatars.discourse-cdn.com/v4/letter/s/e480ec/32.png) [@sathishkumarc14](https://discourse.nodered.org/u/sathishkumarc14)\
**Post date:** [15 February 2021 13:09 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/30 "2021-02-15T13:09:38Z")

</div>

We applied this delay node. Will wait for the results.

Before we applying delay node, we faced the following issue for the first time.

[Name : ConnectionError] [Code : ESOCKET]  
[Message : Failed to connect to localhost:1433 - Could not connect (sequence)]

My flow is kind of following,

```auto
[{"id":"c8825fbe.6e5c8","type":"MSSQL","z":"9adb9ed8.a2f7e","mssqlCN":"1a363e26.a71eb2","name":"SP13","outField":"payload","returnType":0,"throwErrors":1,"query":"[WBS].[dbo].[GetStatus]","modeOpt":"","modeOptType":"execute","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","params":[{"output":false,"name":"WBID","type":"VarChar(100)","valueType":"msg","value":"WBID"}],"x":1010,"y":140,"wires":[["85d24c33.522aa"]]},{"id":"1b0a6037.a1f0c","type":"delay","z":"9adb9ed8.a2f7e","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":915,"y":140,"wires":[["c8825fbe.6e5c8"]],"l":false},{"id":"61b5a979.125f08","type":"function","z":"9adb9ed8.a2f7e","name":"SP13-WBStatus","func":"msg.WBID = global.get(\"sWBID\");\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":855,"y":140,"wires":[["1b0a6037.a1f0c"]],"l":false},{"id":"38e5835c.04ef3c","type":"function","z":"9adb9ed8.a2f7e","name":"SP13-WBStatus","func":"msg.WBID = global.get(\"sWBID\");\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":855,"y":220,"wires":[["cbc96dd7.c5aba"]],"l":false},{"id":"cbc96dd7.c5aba","type":"delay","z":"9adb9ed8.a2f7e","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":915,"y":220,"wires":[["8454a929.973458"]],"l":false},{"id":"8454a929.973458","type":"MSSQL","z":"9adb9ed8.a2f7e","mssqlCN":"1a363e26.a71eb2","name":"SP13","outField":"payload","returnType":0,"throwErrors":1,"query":"[WBS].[dbo].[GetStatus]","modeOpt":"","modeOptType":"execute","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","params":[{"output":false,"name":"WBID","type":"VarChar(100)","valueType":"msg","value":"WBID"}],"x":1010,"y":220,"wires":[["85d24c33.522aa"]]},{"id":"6d34f38e.96cbbc","type":"inject","z":"9adb9ed8.a2f7e","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"1","crontab":"","once":false,"onceDelay":0.1,"topic":"Source1","x":730,"y":140,"wires":[["61b5a979.125f08"]]},{"id":"ef0af1db.d2f2f","type":"inject","z":"9adb9ed8.a2f7e","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"1","crontab":"","once":false,"onceDelay":0.1,"topic":"Source2","x":730,"y":220,"wires":[["38e5835c.04ef3c"]]},{"id":"85d24c33.522aa","type":"debug","z":"9adb9ed8.a2f7e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1190,"y":180,"wires":[]},{"id":"1a363e26.a71eb2","type":"MSSQL-CN","tdsVersion":"7_4","name":"UWBS","server":"localhost","port":"1433","encyption":false,"database":"","useUTC":false,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"1000","parseJSON":false,"enableArithAbort":true}]

```

I am getting these errors in very random. and every time I am facing with same stored procedure.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [15 February 2021 13:20 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/31 "2021-02-15T13:20:27Z")

</div>

> [@sathishkumarc14](#):
>
> We applied this delay node. Will wait for the results.

ok, let me know.

> [@sathishkumarc14](#):
>
> Before we applying delay node, we faced the following issue for the first time.
> 
> [Name : ConnectionError] [Code : ESOCKET]  
> [Message : Failed to connect to localhost:1433 - Could not connect (sequence)]

Can you reduce your pool from `1000` to something sensible like `10` or `20`

* * *

Regarding your flow, i see you grab a value from global context...

```auto
msg.WBID = global.get("sWBID");

```

you have NO checks to see if this WBID value is valid (using context can land you with concurrency issues - you should _REALLY_ be passing the WBID value in from your RFID flow sequence directly) You should add some code to check that `msg.WBID` is a sensible value & if not, then call `node.error(`WBID value '${msg.WBID}' is not valid`,msg);` the `return` out of the function to avoid the msg being passed to the SQL node?

---

<div class="post-metadata">

**Author:** ![sathishkumarc14](https://avatars.discourse-cdn.com/v4/letter/s/e480ec/32.png) [@sathishkumarc14](https://discourse.nodered.org/u/sathishkumarc14)\
**Post date:** [16 February 2021 04:04 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/32 "2021-02-16T04:04:29Z")

</div>

Already the pool size is 10 only.  
And msg.WBID whatever value it have like NULL or ''(empty string), stored procedure will return results.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [16 February 2021 07:01 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/33 "2021-02-16T07:01:33Z")

</div>

When I imported the flow you posted, pool was set to 1000.

What happens if WBID is `undefined` or too long?

---

<div class="post-metadata">

**Author:** ![sathishkumarc14](https://avatars.discourse-cdn.com/v4/letter/s/e480ec/32.png) [@sathishkumarc14](https://discourse.nodered.org/u/sathishkumarc14)\
**Post date:** [16 February 2021 10:08 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/34 "2021-02-16T10:08:21Z")

</div>

If WBID is having undefined value also giving response within a milliseconds.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [17 April 2021 10:08 UTC](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036/35 "2021-04-17T10:08:30Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.

[Previous page](https://discourse.nodered.org/t/etimeout-error-mssql-plus/40036.md?page=1)