June 5, 2013 at 6:34 am
Hello Everyone
How is your day going so far?
I am working with some SQL Command scripts. I was asked by an Oracle DBA, and front-end developer if the SQL Server SQLCMD line can contain input parameters. I cannot find anything in the SQL BOL that indicates this. I am thinking that it cannot.
Does anyone have experience with the SQLCMD line Utility that has used it in a similar way?
Thank you in advance for your comments, suggestions and assistance
Andrew
SQLDBA
June 5, 2013 at 7:20 am
sure, just run sqlcmd /? to see the defaults, but BOL for details:
sqlcmd /?
Microsoft Windows [Version 6.1.7601]Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\lizaguirre>sqlcmd /?
Microsoft (R) SQL Server Command Line Tool
Version 10.50.1600.1 NT x64
Copyright (c) Microsoft Corporation. All rights reserved.
usage: Sqlcmd [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-N Encrypt Connection][-C Trust Server Certificate]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend] [-L[c] list servers[clean output]]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]
[-u unicode output] [-r[0|1] msgs to stderr]
[-i inputfile] [-o outputfile] [-z new password]
[-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-b On error batch abort]
[-v var = "value"...] [-A dedicated admin connection]
[-X[1] disable commands, startup script, enviroment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]
for an example, here's something i've used to do the same work a bcp command would do:
declare @cmd varchar(4000)
--sqlcmd -q "select col1,col2,col3 from table" -oc:\myfile.csv -h-1 -s","'
SELECT @cmd = 'sqlcmd.exe' --the executable...if not in the PATH variable, you might need something like 'C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\bcp.exe '
+ ' ' --space required for parameterizing
+ '-q' -- -q = sqlcmd parameter
+ '"SELECT EMAILTEMPLATESID, EMAILBODY FROM BFONRA.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID"'
--<-- the query itself: no CrLf allowed.
+ ' ' -- space required for parameterizing
+ '-o ' -- -o = sqlcmd parameter
+ 'c:\Data\bcpExample3.txt' -- destination file name:
+ ' ' -- space required for parameterizing
+ '-h-1' -- -h = headers -1 = no
+ ' ' -- space required for parameterizing
+ '-s","' -- comma delimited -s"[||]" = field terminator
EXECUTE master.dbo.xp_cmdshell @cmd
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy