sqlcmd using -Q query can only be 128 characters

Question

Post reply

sqlcmd using -Q query can only be 128 characters

SW_Lindsay

SSCommitted

Points: 1983
More actions
March 26, 2008 at 7:48 am

#122900

I am getting a message that says my query can only be 128 characters. It is 169. Is there a way to extend it? I am using -Q command line command. It works fine on SQL Server 2000 but not on 2005.
Steve

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gordon-265412 SSCrazy Points: 2898 More actions · Answer 1

Is using a file to hold the query to execute, and then use -i instead of -Q an option for you?

Instead of doing a cmdline query, you can save the query to file and then use the file as input.

Your friendly High-Tech Janitor... 🙂

SW_Lindsay SSCommitted Points: 1983 More actions · Answer 2

No, it's dymanic SQL. I am getting the database file information from a given database.

SELECT FI.[filename] FROM master..sysaltfiles FI Inner Join Master..sysdatabases DB On FI.dbid = DB.dbid Where DB.[Name] = ' '

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

You could use OSQL to run a script, where the script could be as big as you want ie:-:-

EXEC master..xp_cmdshell 'osql -E -S Servername-d master -l 100 -i "D:\script.sql" -o "D:\script.log"'

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

SW_Lindsay SSCommitted Points: 1983 More actions · Answer 4

I don't think i pass the parameter, the database name, to a file.

Steve

Gordon-265412 SSCrazy Points: 2898 More actions · Answer 5

Well, there are tricks to echo the dynamic sql to file, but then you have filesystem maintenance to include in your process(es) as well....

Other than the file trick, I think you've run out of options with sqlcmd...

It is odd that the -Q option is so 'short'.

Your friendly High-Tech Janitor... 🙂

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

SW_Lindsay (3/26/2008)
No, it's dymanic SQL. I am getting the database file information from a given database.
SELECT FI.[filename] FROM master..sysaltfiles FI Inner Join Master..sysdatabases DB On FI.dbid = DB.dbid Where DB.[Name] = ' '

Write a stored procedure to construct and execute the dynamic SQL from parameters, and then just exec the stored procedure from the SQLCMD passing in the appropiate parameters.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Balmukund SSCertifiable Points: 6648 More actions · Answer 7

i tried below query having length of more than 200 characters.

C:\Windows\system32>sqlcmd -Q"select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa11

111111111111111111111111111111111111111111111111111111'"

--------------------------------------------------------------------------------

----------------

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1111111111111111111111111111111111111111

1111111111111111

(1 rows affected)

Did i miss somthing??

can you post acutal query and error message please.