none
SQL Server 2012 sqlcmd: Unexplained behaviour returning output containing square brackets [] RRS feed

  • Question

  • From a SQL Server 2012 installation, executing either:

    sqlcmd /E -S <ServerName> -Q "PRINT '[A], [B], [C]'" or

    sqlcmd /E -S <ServerName> -Q "PRINT CHAR(91) + 'A], [B], [C]'"

    gives:-

    , [B], [C]

    wheras for 2005, 2008 or 2008R2, gives the expected result of:-

    [A], [B], [C]

    I haven't been able to find an explanation or way to force the correct result from 2012 sqlcmd.  It appears to me that 2012 is interpreting a lefthand square bracket as the first character on a line as some sort of token delimiter or similar.

    Can anyone shed some light on this or is it an issue for Connect?  This is just an example of the problem, I am not actually trying to print [A], [B], [C]! :) -  we are experiencing data loss/corruption on outputs from stored procedures where the leftmost character is [.

    Note that 2012 sqlcmd expects a closing ] on the line, otherwise the output gets returned like this for:-

    sqlcmd /E -S <ServerName> -Q "PRINT '[A, [B], [C'"

    [Microsoft][SQL Server Native Client 11.0][SQL Server][A, [B], [C

    Thanks


    • Edited by swh1 Monday, December 23, 2013 12:51 PM Typo
    Friday, December 20, 2013 12:16 PM

Answers

  • I also see the issue when using Version 11.0.2100.60 NT x64.  I can also duplicate it interactively.

    1> PRINT '[A],[B],[C]';
    2> PRINT '[A,[B],[C';
    3> go
    ,[B],[C]
    [Microsoft][SQL Server Native Client 11.0][SQL Server][A,[B],[C
    1>

    It must be tokenizing the [A] for some reason.

    I would suggest reporting it on https://connect.microsoft.com/

    • Marked as answer by swh1 Monday, December 23, 2013 7:29 AM
    Friday, December 20, 2013 1:53 PM

All replies

  • Hello,

    I can't confirm this issue, whatever I try I get correct output:


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, December 20, 2013 1:08 PM
  • Hello,

    I can't confirm this issue, whatever I try I get correct output:


    Olaf,

    Are you are using a SQL Server 2012 of SQLCMD?  I can repro swh1 reported with the following with different versions of the client tools installed. 

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd" /E -S. -Q "PRINT '[A], [B], [C]';"
    "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" /E -S. -Q "PRINT '[A], [B], [C]';"

    "SQLCMD /?" for my SQL 2012 version returns:

    Microsoft (R) SQL Server Command Line Tool
    Version 11.0.2100.60 NT x64
    Copyright (c) 2012 Microsoft. All rights reserved.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, December 20, 2013 1:44 PM
  • I also see the issue when using Version 11.0.2100.60 NT x64.  I can also duplicate it interactively.

    1> PRINT '[A],[B],[C]';
    2> PRINT '[A,[B],[C';
    3> go
    ,[B],[C]
    [Microsoft][SQL Server Native Client 11.0][SQL Server][A,[B],[C
    1>

    It must be tokenizing the [A] for some reason.

    I would suggest reporting it on https://connect.microsoft.com/

    • Marked as answer by swh1 Monday, December 23, 2013 7:29 AM
    Friday, December 20, 2013 1:53 PM
  • Same version (11.0.2100.60 NT x64), same result here. '[a]' is not printed.
    Friday, December 20, 2013 2:04 PM
  • Thanks for all your responses.

    I didn't mention but my experiences are with SQL Server 2012 patched to sp1 (11.0.3000.0); and sqlcmd is reporting: Version 11.0.2100.60 NT x64.

    I will raise on Connect - thanks again.


    swh1

    Friday, December 20, 2013 2:20 PM
  • Olaf,

    Are you are using a SQL Server 2012 of SQLCMD? 


    Dan and swh1, you are right, with SqlCmd 2012 (version 11.0.2100.60) I can also reproduce the issue; it an issue of this SqlCmd version, not of the SQL Server version you are connected to.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, December 20, 2013 4:10 PM
  • It must be tokenizing the [A] for some reason.

    So, its not a [A] which is a problem its "[" as the first character of the output.

    I agree that it's an issue with SQLCMD in SQL 2012. If you capture profiler, command is passed correctly to SQL engine.  It seems like when output stream comes out, SQLCMD looks for "[" as first character and chops off content till next "]"

    If there is not matching closing "]" it shows the text with additional message.

    Workaround - Just add additional space prior to square bracket and it works fine.



    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, December 21, 2013 12:00 PM
    Moderator
  • Thanks for the info and workaround guys.

    I used regex find and replace to fix these and add a leading - char

    find: ((raiserror\s*\(\')|(print\s*\'))(\[)

    replace: $1-$4

    This works in visual studio 2013 (and probably 2012 also) but I think previous versions used a different regex engine

    I don't guarantee this to be bullet proof but it worked for our scripts

    Wednesday, October 1, 2014 6:09 PM