SQLCMD vs OSQL: Missing Script errors

Question

Post reply

SQLCMD vs OSQL: Missing Script errors

Michael Lato

Ten Centuries

Points: 1278
More actions
October 18, 2006 at 7:11 am

#367658

Hello all,
I use command files in order to perform batch updates to my databases, most frequently during development to refresh all stored procedures in a single pass. In SQL2000, I used the following lines in my "deploy.cmd" file:
osql -E -S MYSERVER -d MYDB -n -i pUserAdd.sql >> Deploy.txt
osql -E -S MYSERVER -d MYDB -n -i pUserUpdate.sql >> Deploy.txt
If a script file was missing, I would get a message in the "Deploy.txt" file. However, the SQCMD utility prints the error to the screen, rather than the file, when using the following lines:
sqlcmd -S MYSERVER -d MYDB -E -i pUserAdd.sql >> Deploy.txt
sqlcmd -S MYSERVER -d MYDB -E -i pUserUpdate.sql >> Deploy.txt
Has anyone else noticed / resolved this? I typically run ~500 scripts during a development refresh and it will really help to know if I have deleted or renamed a script while working.
Regards,
Michael Lato

Viewing 7 posts - 1 through 6 (of 6 total)

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Michael

It seems you forgot to give the necessary output parameter in the statement.

Go through this link and you will find examples:

http://msdn2.microsoft.com/en-us/library/ms180944.aspx

Hope this helps.

Thanks

Lucky

Michael Lato Ten Centuries Points: 1278 More actions · Answer 2

Egads, I knew I should have put more in my post! Here is a full script:

@if exist __Test.txt del __Test.txt

sqlcmd -S MYSERVER -d MYDB -E -i pPoodle.sql >> __Test.txt

sqlcmd -S MYSERVER -d MYDB -E -i pPoodle.sql -o __Test.txt

@notepad __Test.txt

This always returns a blank file. Also, one drawback of the "-o" parameter is that it clears the output file on every script.

Regards,
Michael Lato

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 3

I tried this with the same outcome. Fortunately, I can validate the input files.

This problem is actually an old DOS issue with not being able to redirect the stdout. Google STDERR redirection (may want to include DOS in the search string or you'll a plethora of UNIX answers also).

hope this helps

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Michael Lato Ten Centuries Points: 1278 More actions · Answer 4

SOLVED: thanks to lucky, and special thanks to MG.

My guess is that "file missing" errors are directed to STDOUT in osql, but to STDERR in sqlcmd. The following script will append all returned information to the log file:

@if exist Test.txt del Test.txt
sqlcmd -S 3it-mlato2\SQL2K5 -d SC00 -E -i pPoodle.sql >> Test.txt 2>&1 sqlcmd -S 3it-mlato2\SQL2K5 -d SC00 -E -i pPoodle.sql >> Test.txt 2>&1
@notepad Test.txt

The "2>&1" entry redirects STDERR output to STDOUT, which is gathered into the Test.txt file by ">>".

Regards,
Michael Lato

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

ahh yes ... good old DOS command line stuff that was 'cloned' from *nix ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

vivek4075 SSC Rookie Points: 39 More actions · Answer 6

Hi All,

I am new to this .I am posting this Q. on same as a reply but please answer the Q:

How to redirect only the stderr of sqlcmd into a file not all stdoutput.

When the error has come they only it will redirect to a file ?

Thanks in advance