Post reply

Output sqlcmd result in a .txt file (only the value, not more info)

  • July 24, 2015 at 8:04 am

    #299111

    Hi guys,

    I am using the following sqlcmd to export the value of the query into a .txt file:

    sqlcmd -S brdsqldev01 -d brframedevelopment -Q "SELECT name FROM sys.databases AS d WHERE CHARINDEX('Snapshot', name) > 0 AND create_date = (SELECT MAX(create_date) FROM sys.databases AS d WHERE CHARINDEX('Snapshot', name) > 0)" -b -s "," -o "U:\testResults.txt"

    This is what I get as a result in the .txt file:

    name

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

    BRFrameDevelopmentSnapshot20150602

    (1 rows affected)

    The value is the one I was looking for. However, I need to read this value back into another system using the following command line:

    set /p SNAPSHOT=< "U:\testResults.txt"

    The value “NAME” is then assign to the variable!!

    Is there a way to export only the value in the .txt file?

  • July 24, 2015 at 8:16 am

    #1814783

    try this:

    sqlcmd -S brdsqldev01 -d brframedevelopment -Q "SET NOCOUNT ON; SELECT name FROM sys.databases AS d WHERE CHARINDEX('Snapshot', name) > 0 AND create_date = (SELECT MAX(create_date) FROM sys.databases AS d WHERE CHARINDEX('Snapshot', name) > 0)" -b -s "," -o "U:\testResults.txt" -h -1

    -- Gianluca Sartori

  • July 24, 2015 at 9:31 am

    #1814816

    It's working ! Thanks !

  • July 27, 2015 at 2:11 am

    #1815047

    Glad I could help. The trick here is SET NOCOUNT on to suppress the "x rows affected" messages and -h -1 to suppress column headers.

    -- Gianluca Sartori

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

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