Need example -- SQLCMD -y

Hi,

Please can any one let me know jhow to use -y option with SQLCMD.

The -y option affects the number of characters wide that variable width fields like varchar(max) or nvarchar(max) display as in the output.

The the following two examples...

Command:
sqlcmd -Q"SELECT CONVERT(nvarchar(max),100) AS Test"

Output:
Test

--
--
--
-
100

versus...

Command:
sqlcmd -y 10 -Q"SELECT CONVERT(nvarchar(max),100) AS Test"

Output:
Test
-
100

Notice that the output column in the first one is so wide that it's underline wraps multiple times on the screen. However, in the second command the width of the column is restricted to 10 characters.

Does that help?

|||Nice use of font and background coloring :)|||Thanks. The features are there. Might as well use them eh?|||

Hi Bret Stateham,

Here what I want to run, when i run in sqlcmd it did not show me the completecode.This -y will help me.
SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');

*************************************************************************************
Please can you let me know how I can use here

sqlcmd -y 10 -Q"SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"

definition
-
(0 rows affected)

I am doing it wrong, Can you help to write this statement.

|||

I am not sure why you are still attempting to use the -y option after the problems I indicated in using SQLCMD to script out objects. Your query may or may not work depending on the default database for the login from which you are trying the SQLCMD command. You could use the command-line below:

sqlcmd -y 8000 -Q"SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"

And add "-h-1" to remove headers. But there will be additional spaces before and after the object definition. You need to adjust -y based on the maximum length of definition or use -y 0 which will be slower to use. There are also other problems like informational messages interleaved in the output if you have a USE <dbname> in the query for example. And you have to do that in your query because the default database may not be the user database so object_id & query will be evaluated in a different database. You could use the redirect option in SQLCMD to output messages to a different file for example. See SQLCMD topic in Books Online for each option and how to use it.

|||

Hi,

It is giving me an error

C:\>sqlcmd
1> use lfin

2> sqlcmd -y 8000 -Q"SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"
3> go

Msg 102, Level 15, State 1, Server test, Line 2
Incorrect syntax near 'sqlcmd'.

Thanks in advance

|||

This is because SQLCMD is not a SQL command, it is a DOS command. Once in sqlcmd, just execute the query, or set the db in the statement and run from dos.

C:\>sqlcmd -y 8000 -Q"SELECT definition FROM lfin.sys.sql_modules WHERE object_id = OBJECT_ID('PLocation');"

|||

HI Louis,

Thank you for the detail. It run fine but
it did not provide me the code of PLocation procedure.
It only show me - all over.

at then end say 0 row effected

Thank you for your help.

thanks

|||

Hi :

If I don't want to output in console, I want to set this value to a variable, How can I do?

Any help would be much appreciated!