2 
sqlcmd -E -d MyDatabase -Q "sp_helpsubscription" -s "," -o file.csv -W -u

Column 23 which is suppose to be a tinyint (subscriber_type) is output as NUL instead of NULL when the value is NULL.

I cannot figure out why it's doing this or a way to fix it. When I run sp_helpsubcription in SSMS the column contains the expected full NULL text.

In order to replication this you need a replicated database with a publication in at least one subscription to it. This particular issue is with a transactional publication.

It does this even if I output the results to the screen instead of to a file.

Edit: The reason I'm doing this is because I can't issue an INSERT #TEMP EXEC sp_helpsubcription because of a NESTED EXEC INSERT inside sp_helppublication.

Improve this question
6
  • 1
    There is a (max_column_width = 3) setting or similar hidden somewhere that cuts the output that is longer than 3 chars?
    – ypercubeᵀᴹ
    Commented Jul 25, 2014 at 16:19
  • I ended up using a Powershell script to get rid of the NULL and NUL values since I was going to BULK INSERT the results back into a table. Ex: (get-content $FILE) | foreach-object {$_ -replace ",NUL[L]*", ","} | set-content $FILE
    – Brad
    Commented Jul 25, 2014 at 19:15
  • ypercube, I don't believe so since every other column that has NULL values is displayed correctly as NULL. It's only this one column.
    – Brad
    Commented Jul 25, 2014 at 19:16
  • 1
    Right but only this column is tinyint, which can hold values from 0 up to 255. i.e. numbers with up to 3 digits only.
    – ypercubeᵀᴹ
    Commented Jul 28, 2014 at 12:31
  • 1
    Column name < 4 chars? Enlarge the length of the name
    – user53313
    Commented Dec 3, 2014 at 5:30

1 Answer 1

Reset to default
1

sqlcmd only displays as many characters in a column as the width of the column data or the width of the column name, whichever is longer. You can expect the same behavior with the following data types:

  • bit
  • tinyint
  • decimal with precision < 2
  • varchar with length < 4

To work around the issue you can either cast the column to a longer data type or rename the column to a longer name.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.