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.

6
  • 1
    There is a (max_column_width = 3) setting or similar hidden somewhere that cuts the output that is longer than 3 chars? Jul 25 '14 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
    Jul 25 '14 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
    Jul 25 '14 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. Jul 28 '14 at 12:31
  • 1
    Column name < 4 chars? Enlarge the length of the name
    – user53313
    Dec 3 '14 at 5:30
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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