Post reply

bcp exporting to txt and wish to keep nulls

  • June 24, 2019 at 6:30 am

    #3656802

    How to get end result where the word 'NULL' appears if value was NULL in source table?

    Getting WARNING: "Error = [Microsoft][ODBC Driver 11 for SQL Server]Warning: BCP import with a format file will convert empty strings in delimited columns to NULL."  , yet wherever there were NULLS in source table there are blanks in txt file.

    I'm using the -k switch

    SET @Cmd = 'bcp "Admin.dbo.[AccountList7919$]" out "' + @ServerShare + '\' + @DataFileName + @DataFileExtension + '" -S' + @@SERVERNAME + ' -t, -T -c -k';

    EXECUTE xp_cmdshell @Cmd;

     

    • This topic was modified 5 years, 5 months ago by  polkadot.

    --Quote me

  • June 24, 2019 at 8:06 am

    #3656835

    In a text file NULL is provided by the absence of a value. If you have the string "NULL", it no longer has no value, it has a value. If you were to then reimport that, you would import the string 'NULL', which if your column isn't a string datatype, will cause the import to fail.

    Also careful with that statement, it's open to injection and as you're using xp_cmdshell. You really need to ensure you are properly quoting your injected values.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 2 posts - 1 through 1 (of 1 total)

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