Hello, all (and how many is that?)

I have a nasty problem in my automation batch scripts that
invoke `sqlcmd`[1]: whatever I try, I cannot cause it to
terminate with an error level in case of a timeout. The
parameters `-V1 -b' do not help a wee whit. The article on
error severities[2] says:

For compatibility reasons, the Database Engine converts
severity 10 to severity 0 before returning the error
information to the calling application.

This alone sounds crazy, but the apparent inabilty to
override this illogical behavior makes `sqlcmd' incurably
broken. Please, tell me I am wrong.

____________________
1. <https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility>
2. <https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities>

--
() ascii ribbon campaign -- against html e-mail
/\ www.asciiribbon.org -- against proprietary attachments

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

I wrote:

I have a nasty problem in my automation batch scripts that
invoke `sqlcmd`: whatever I try, I cannot cause it to
terminate with an error level in case of a timeout.

The only solution I have found is to detect the words
"Timeout expired" in the standard output. Here is a sample
batch script:

@ECHO OFF
SET OUT=out.txt
SET ERR=err.txt

sqlcmd -S <your_server> -V 11 -r -b -t 1 -i test.sql >%OUT% 2>%ERR%

:: Handle a normally reported error:
IF ERRORLEVEL 1 GOTO Error

:: Handle a timeout, by examining the first line of stdout:
SET /P OUT_FST=<%OUT%
IF "%OUT_FST%"=="Timeout expired" (
ECHO equ!
COPY %OUT% %ERR%
GOTO :Error
)

:: No errors detected, exiting:
ECHO All went well.
EXIT /B

:Error
echo Error encountered:
type %ERR%

I have published this on StackOverflow: <https://stackoverflow.com/a/78472213/2862241>

--
() ascii ribbon campaign -- against html e-mail
/\ www.asciiribbon.org -- against proprietary attachments

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:

I have a nasty problem in my automation batch scripts that
invoke `sqlcmd`[1]: whatever I try, I cannot cause it to
terminate with an error level in case of a timeout. The
parameters `-V1 -b' do not help a wee whit. The article on
error severities[2] says:

For compatibility reasons, the Database Engine converts
severity 10 to severity 0 before returning the error
information to the calling application.

This alone sounds crazy, but the apparent inabilty to
override this illogical behavior makes `sqlcmd' incurably
broken. Please, tell me I am wrong.

What sort of timeout are we talking about? I guess this is something
that involves a linked server - or how would you else get a timeout
with SQLCMD?

There are situations when query timeouts on calls to stored procedures
on linked server only results in an informational message. I've
written about it here: https://www.sommarskog.se/error_handling/Appendix1.html#querytimeout

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Erland Sommarskog:

What sort of timeout are we talking about? I guess this is
something that involves a linked server -- or how would
you else get a timeout with SQLCMD?

Nothing of the sort. It is the usual timeout that occurs
when the command or script executed via `sqlcmd' is taking
longer than the number of seconds speciffied in the -t
command-line parameter. In my test, I was executing:

echo WAITFOR DELAY '00:00:10' | sqlcmd -S mytesty -V 11 -r -b -t 8

--
() ascii ribbon campaign -- against html e-mail
/\ www.asciiribbon.org -- against proprietary attachments

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Anton Shepelev ([email protected]) writes:

Erland Sommarskog:

What sort of timeout are we talking about? I guess this is
something that involves a linked server -- or how would
you else get a timeout with SQLCMD?

Nothing of the sort. It is the usual timeout that occurs
when the command or script executed via `sqlcmd' is taking
longer than the number of seconds speciffied in the -t
command-line parameter. In my test, I was executing:

echo WAITFOR DELAY '00:00:10' | sqlcmd -S mytesty -V 11 -r -b -t 8

I see. They have a new SQLCMD, written in go. It behaves the same way,
but you could file an issue on https://github.com/microsoft/go-sqlcmd.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)