SqlCMD and dynamic SQL statements

Question

SqlCMD and dynamic SQL statements

Per Bylund-388363

SSC-Addicted

Points: 428
More actions
June 26, 2007 at 2:16 am

#91206

Hi there,
I'm building a SQL statement dynamically, which includes commands such as Space() in order to make the result of fixed width. The result of the dynamic query is supposed to be exported as a fixed width text file using the xp_cmdshell and the sqlcmd command. This is where I get stuck.
declare
@t varchar(8000)
set
@t = ' sqlcmd -h-1 -E -S(Local) -d"my database" -q"SELECT StreetAddress + Space(35 - Len(StreetAddress)), ZipCode + Space(5 - Len(ZipCode)), City + Space(25 - Len(City)), FirstName + Space(30 - Len(FirstName)), LastName + Space(30 - Len(LastName)) FROM dTable
WHERE (Upper(dTable.FirstField) = ''x'' OR Upper(dTable.SecondField) = ''y')
AND NOT ((dTable.SomeId IS NULL) OR (dTable.SomeId = 0))) ORDER BY FieldThree" -o''D:\export\testing_file_ref1.txt'' -s'''' -W '
EXECUTE
master..xp_cmdshell @t
It seems the sqlcmd command only accepts 128 characters, which is way too little for my purposes. I did find a solution to this; by using variables of greater length and not using double quotation marks one should be able to send the parameter to xp_cmdshell instead.
However, it seems I need to use double quotation marks since my database name includes spaces (there's nothing I can do about that...). I tried using brackets ([]) instead, but that simply resulted in a login error (because the database name with brackets doesn't exist, it seems).
I guess my question is simply "How do I pass a parameter longer than 128 characters to SQLCMD?". Anybody with sqlcmd experience? Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

I hope sqlcmd can take a input file as a parameter. put your query into the file adn call the file containing the sql code in the sqlcmd statement.

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

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

Did you try to include the xp_cmdshell in the variable and then execute (@t) instead?

Per Bylund-388363 SSC-Addicted Points: 428 More actions · Answer 3

Thanks guys.

I tried putting the query in a text file. It works fine, and I think I can live with the overhead.

Thanks, Anders, for the suggestion to execute a string which includes xp_cmdshell. I'll check it out.