sqlcmd: Using double quotes within query(-q)

Question

Post reply

sqlcmd: Using double quotes within query(-q)

sqld-_-ba

SSCarpal Tunnel

Points: 4365
More actions
February 21, 2008 at 10:37 am

#179319

How to use double quotes to identify column name within a sqlcmd query using -q
Ex:
sqlcmd -S srvr -q "select "first name" from pubs..Names"
This gives an error
'Sqlcmd: 'first name" from pubs..Names"': Unexpected argument.
Is there an escape sequence for the ' " ' character. Using \"first name\" does not work.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

socko2 SSC Rookie Points: 40 More actions · Answer 1

Not real sure with sqlcmd but if first name is a variable in vb you would enter

sqlcmd -S srvr -q "select '" & first_name & "' from pubs..Names"

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 2

Not using VB..

am running the command directly from windows(cmd)

C:\> sqlcmd -S srvr -q "select "first name" from pubs..Names"

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 3

DECLARE @SQL VARCHAR (100)

SET @SQL = 'sqlcmd -S server -q "select first_name from pubs..name"'

EXEC master..xp_cmdshell @SQL

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 4

Please note.. the column is "first name", not first_name (which does not need double quotes)

Besides.. I'm running this from local cmd prompt.. not on the 'server' (where xp_cmdshell doesn't work)

Is there an escape sequence for the " character? ( '\' doesn't work)

C:\> sqlcmd -S srvr -q "select \"first name\" from pubs..Names" -- ERROR

Thanks

Martin Skilton Mr or Mrs. 500 Points: 576 More actions · Answer 5

Martin Skilton

Mr or Mrs. 500

Points: 576

February 27, 2008 at 7:19 am

#782500

use square brackets instead of "

hope this helps

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 6

Thanks.. But

'[]' works for column names, but another statement i run from cmd is :

sqlcmd -S srvr -Q "exec sp_oamethod @oSrv, 'Databases("pubs").Tables("T_Names").Script(2,"c:ames.sql")"

for which double quotes are a must in the syntax!

There is a workaround for sqlcmd: Instead of -Q "t-sql", manually put the t-sql query in a file (input.sql) and then use -i "input.sql"

However, it kind of defeats the goal of total automation..

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 7

Use it as [first name] instead of using as "first name"

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 8

Thanks.. Sugesh. I tried that and it works.. but please see the comment:

Thanks.. But
'[]' works for column names, but another statement i run from cmd is :
sqlcmd -S srvr -Q "exec sp_oamethod @oSrv, 'Databases("pubs").Tables("T_Names").Script(2,"c:ames.sql")"
for which double quotes are a must in the syntax!
There is a workaround for sqlcmd: Instead of -Q "t-sql", manually put the t-sql query in a file (input.sql) and then use -i "input.sql"
However, it kind of defeats the goal of total automation..

abair34 Ten Centuries Points: 1076 More actions · Answer 9

Hi,

I just encountered the same issue and I believe the way to escape the quote within the -q or -Q is to double up the quotes.

So you would want to REPLACE(<yourstring>, '"', '""')

For example

C:\Documents and Settings\ay9805>sqlcmd -S .\abair -Q"SELECT '"error"' as text"

Sqlcmd: 'error"' as text"': Unexpected argument. Enter '-?' for help.

C:\Documents and Settings\ay9805>sqlcmd -S .\abair -Q"SELECT '""error""' as text

"

text

-------

"error"

(1 rows affected)

Hope this helps,

-Michael Abair

casilvis SSC Enthusiast Points: 145 More actions · Answer 10

Thanks for that, the double double quotes worked for me!