osql | sqlcmd |
osql usage : osql –e –q”SELECT column FROM table” osql -Uusername -Ppassword -Sservername -iC:\script.sql -oc:\output.txt | sqlcmd usage : sqlcmd –e –q”SELECT column FROM table” sqlcmd -Uusername -Ppassword -Sservername -iC:\script.sql -oc:\output.txt |
parameters -e Use Trusted Connection (windows Authentication) -u User name -p Password -s Server name -i Script name -q Query -o Output file -n remove numbering | parameters as osql parameters , with new additions including - -b batch abort -f Codepage -u Unicode output -v Pass Variables |
To Get values out from osql , use the EXIT command like this - C:\>osql -e -n -q "EXIT (SELECT 47)" 47 Or in a batch file like this... osql -e -n -q "EXIT (SELECT COUNT(*) FROM sysobjects)" SET SqlObjectCount=%ErrorLevel% ECHO %SqlObjectCount% | To return values out from sqlcmd , use the EXIT command like this - C:\>sqlcmd -Usa -Ppassword -q"EXIT(SELECT 101)" 101 Or in a batch file like this... sqlcmd -Usa -Ppassword -q "EXIT (SELECT COUNT(*) FROM sysobjects)" SET SqlObjectCount=%ErrorLevel% ECHO %SqlObjectCount% |
To Pass parameters in to OSQL, build a sql script file on the fly, and load it ... SET currentobject=table1 ECHO EXIT(SET NOCOUNT ON;SELECT COUNT(*) from sysobjects WHERE [name] = '%currentobject%') >%currentobject%_exists.sql OSQL -Usa -P%password% -S %server% -d %db% -f 65001 -i %currentobject%_exists.sql | sqlcmd supports variables via the –v switch pass.sql SET NOCOUNT ON; EXIT(SELECT 'filename passed is $(file)') pass.bat @echo off for %%f in (*.bat) do ( sqlcmd -v file="%%f" -i "pass.sql" ) ) Results - B:\test2>pass ---------------------------- filename passed is check.bat -------------------------------- filename passed is check_old.bat --------------------------- filename passed is pass.bat ------------------------------ filename passed is rollout.bat |
MSDN : osql
MSDN : sqlcmd
Using the SQLCMD command line utility for SQL SERVER
Mohamad Shehadeh's Blog : SQLCMD VS OSQL
No comments:
Post a Comment