Sql-server – sqlcmd returning NUL instead of NULL for tinyint column

replicationsql-server-2008sqlcmd

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.

Best Answer

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.

Related Question