OSQL return codes

Question

Post reply

OSQL return codes

kromo

SSC-Addicted

Points: 459
More actions
December 8, 2003 at 2:39 pm

#83676

I am using a batch file to run osql commands that create a database specified by the user and and then populates the database using an sql script, but how do I know if the osql command succeeds to go on?

here is a sample

@Echo off

osql -S %1 -d master -E -Q "create database %2"

osql -S %1 -d %2 -E -n -i c:\MSPEO.sql

I am launching this batch file from C# and I don't know how to tell if the the batch worked.

Please help.

Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

If there is a dos errorlevel of 0, then the query succeeds. The query in other words returns a 0.

Steve Jones

sjones@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/

http://www.dkranch.net

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

gljjr SSCrazy Eights Points: 9970 More actions · Answer 2

Visual Studio will actually create the batch files for you if you create a database project and have a folder you want to script the files for. I use this functionality a lot 🙂 To create the command file you simply right click the folder in Solution Explorer and click on Create Command File. I've modified it to not echo the line numbers. The command for osql and the resulting check would look like the following...



@echo off

REM: Command File Created by Microsoft Visual Database Tools

REM: Date Generated: 12/2/2003

REM: Authentication type: Windows NT

REM: Usage: CommandFilename [Server] [Database]



if '%1' == '' goto usage

if '%2' == '' goto usage



if '%1' == '/?' goto usage

if '%1' == '-?' goto usage

if '%1' == '?' goto usage

if '%1' == '/help' goto usage

osql -S %1 -d %2 -E -b -n -i "SqlScript.SQL"

if %ERRORLEVEL% NEQ 0 goto errors



REM: How to use screen

:usage

echo.

echo Usage: MyScript Server Database

echo Server: the name of the target SQL Server

echo Database: the name of the target database

echo.

echo Example: MyScript.cmd MainServer MainDatabase

echo.

echo.

goto done



REM: error handler

:errors

echo.

echo WARNING! Error(s) were detected!

echo --------------------------------

echo Please evaluate the situation and, if needed,

echo restart this command file. You may need to

echo supply command parameters when executing

echo this command file.

echo.

pause

goto done



REM: finished execution

:finish

echo.

echo Script execution is complete!

:done

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.