Wednesday, April 13, 2011

osql & sqlcmd

Osql has long been deprecated, but it still exists on those pesky sql 2000 servers...


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: