SQLCMD Utility and Input Parameters

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  June 5, 2013 at 6:34 am

  #275908

  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

• Lowell

  SSC Guru

  Points: 323522

  More actions

  June 5, 2013 at 7:20 am

  #1621344

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!