how to get sqlcmd return values

I'm having a hard time getting a non-zero return value from sqlcmd when a
error in the sql script it is executing occurs.
I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
is relevant) but regardless ERRORLEVEL is always 0
Here is a sample of the sqlcmd and the os syntax I'm running from within a
sql agent job step:
sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
input file.sql"
echo %ERRORLEVEL%
or
set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
"C:\Admin\bcp table space input file.sql"
also, I've tried different iterations with and without spaces between -V and
10
any help or suggestions is greatly appreciated.What error occurs on the script you run? I just tried with RAISERROR and it
work find. Here's the
bat file:
sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
ECHO %ERRORLEVEL%
pause
And here's the contents of a.sql:
RAISERROR('Ouch', 15, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
> I'm having a hard time getting a non-zero return value from sqlcmd when a
> error in the sql script it is executing occurs.
> I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
> is relevant) but regardless ERRORLEVEL is always 0
> Here is a sample of the sqlcmd and the os syntax I'm running from within a
> sql agent job step:
> sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
> input file.sql"
> echo %ERRORLEVEL%
> or
> set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> also, I've tried different iterations with and without spaces between -V a
nd
> 10
>
> any help or suggestions is greatly appreciated.
>|||Here is the error message:
Msg 945, Level 14, State 2, Server servername, Line 1
Database 'databasename' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
Basically I iterate through different databases and select from some sys
tables. The problem here is one particular db is either in a loading state
or in a suspect state and therefore causes the error. Nonetheless, resolvin
g
that issue is not hard and not as important as just catching the error itsel
f.
Thanks
"Tibor Karaszi" wrote:

> What error occurs on the script you run? I just tried with RAISERROR and i
t work find. Here's the
> bat file:
> sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> ECHO %ERRORLEVEL%
> pause
> And here's the contents of a.sql:
> RAISERROR('Ouch', 15, 1)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
>|||Try the "-b" command line option.
"paul" <paul@.discussions.microsoft.com> wrote in message
news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
> I'm having a hard time getting a non-zero return value from sqlcmd when a
> error in the sql script it is executing occurs.
> I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
> is relevant) but regardless ERRORLEVEL is always 0
> Here is a sample of the sqlcmd and the os syntax I'm running from within a
> sql agent job step:
> sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
> input file.sql"
> echo %ERRORLEVEL%
> or
> set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> also, I've tried different iterations with and without spaces between -V
> and
> 10
>
> any help or suggestions is greatly appreciated.
>|||Tried this but didn't work. Is my dos look right?
!!sqlcmd -U username -P password -S servername -b -V 10 -h-1 -i
"C:\Admin\bcp table space input file.sql"
!!echo %ERRORLEVEL%
with or without -V doesn't work?
"Mike C#" wrote:

> Try the "-b" command line option.
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
>
>|||It work just fine for me with such an error as well. Here's what I did:
CREATE DATABASE x
ALTER DATABASE x SET OFFLINE
And here's what I have in the bat file:
sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
ECHO %ERRORLEVEL%
pause
And what is in a.sql:
SELECT 'Hello'
SELECT * FROM x..sysobjects
SELECT 'Hello again'
And below is output from executing the bat file:
C:\>sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
Msg 942, Level 14, State 4, Server TIBWORK\RTM, Line 2
Database 'x' cannot be opened because it is offline.
C:\>ECHO 14
14
C:\>pause
Press any key to continue . . .
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:7B3754E7-C857-4BE3-9DFD-14BED25FD33B@.microsoft.com...
> Here is the error message:
> Msg 945, Level 14, State 2, Server servername, Line 1
> Database 'databasename' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for detail
s.
>
> Basically I iterate through different databases and select from some sys
> tables. The problem here is one particular db is either in a loading stat
e
> or in a suspect state and therefore causes the error. Nonetheless, resolv
ing
> that issue is not hard and not as important as just catching the error its
elf.
> Thanks
>
> "Tibor Karaszi" wrote:
>|||Tibor,
Thanks for following through. Can you try one more thing that I'm wondering
may be having an effect.
Change you select sql to be a dynamically executed string.
Like this:
declare @.sql varchar(255)
set @.sql = 'select * from x..sysobjects'
select 'Hello'
exec(@.sql)
select 'Hello again'
If that works on your machine, I would say it is something local to mine,
else I'm wondering if exec() behaves the way I'm expecting it to.
Thanks again.
"Tibor Karaszi" wrote:

> It work just fine for me with such an error as well. Here's what I did:
> CREATE DATABASE x
> ALTER DATABASE x SET OFFLINE
> And here's what I have in the bat file:
> sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> ECHO %ERRORLEVEL%
> pause
> And what is in a.sql:
> SELECT 'Hello'
> SELECT * FROM x..sysobjects
> SELECT 'Hello again'
>
> And below is output from executing the bat file:
> C:\>sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> Msg 942, Level 14, State 4, Server TIBWORK\RTM, Line 2
> Database 'x' cannot be opened because it is offline.
> C:\>ECHO 14
> 14
> C:\>pause
> Press any key to continue . . .
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:7B3754E7-C857-4BE3-9DFD-14BED25FD33B@.microsoft.com...
>|||Same result. I still managed to catch the error with errorlevel.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:1AB3EA52-EAB2-4C11-82A2-E0A9A97A0B19@.microsoft.com...
> Tibor,
> Thanks for following through. Can you try one more thing that I'm wonderi
ng
> may be having an effect.
> Change you select sql to be a dynamically executed string.
> Like this:
> declare @.sql varchar(255)
> set @.sql = 'select * from x..sysobjects'
> select 'Hello'
> exec(@.sql)
> select 'Hello again'
>
> If that works on your machine, I would say it is something local to mine,
> else I'm wondering if exec() behaves the way I'm expecting it to.
> Thanks again.
>
> "Tibor Karaszi" wrote:
>|||I've been able to run it ok and get the return error value as well... I'm
running SQL 2K5 SP 1 if that makes a difference.
"paul" <paul@.discussions.microsoft.com> wrote in message
news:6897329C-EEFC-4756-A1DA-46E366EAF3EA@.microsoft.com...
> Tried this but didn't work. Is my dos look right?
> !!sqlcmd -U username -P password -S servername -b -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> !!echo %ERRORLEVEL%
> with or without -V doesn't work?
>
>
> "Mike C#" wrote:
>|||Tibor / Mike,
Thanks for all your help, I believe I have solved the riddle. As contorted
as my logic might seem, I am actually calling numerous sqlcmd from inside th
e
same transact sql bactch.
To do this I am prefixing it of course with '!!'. So there in lies the
problem, each !!sqlcmd must be spawning its own dos session so if I do the
following:
!!sqlcmd ....
!!echo %errorlevel%
error level will always be 0 in the second dos session.
thanks for helping with that guys
"Tibor Karaszi" wrote:

> Same result. I still managed to catch the error with errorlevel.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:1AB3EA52-EAB2-4C11-82A2-E0A9A97A0B19@.microsoft.com...
>