Strange anomaly with Generate Scripts Feature in SQL Server 2016

Igor 21 Reputation points
2022-01-21T22:59:59.753+00:00

Hello,

I ran the Generate Scripts task on a table that has around 25k rows in SQL Server 2016. The result was a script file that contains the schema and data, was about 16MB. At first I tried to use the sqlcmd command to to run the script against another database; even after using the max value for packet size, it would still cut off after about 11K records. At first, I tried to open the .sql file in SMSS and run it, but it would error out due to a syntax error but everything on that line of the file looked fine. I then opened the .sql file in VS Code and noticed a strange NUL character with a red background in that spot that was failing. I replaced the NUL character with a space in all occurrences and everything was working fine after that. I have attached a screenshot.

Has anyone seen this issue or know why it happens? It's not that large of a table and I have ran scripts against much larger tables that worked fine.

Thanks, Igor
167372-null.jpg

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,754 questions
0 comments
Accepted answer
  1. Erland Sommarskog 101.2K Reputation points MVP
    2022-01-21T23:22:59.617+00:00

    I guess this happens because there is a NUL character in the data. And yes, SSMS thinks that NUL (a.k.a \0) is the end of string, and then things go downhill from there.

    How the NUL got into your data, I don't know.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Igor 21 Reputation points
    2022-01-22T00:08:05.99+00:00

    Some of this data was generated in the 70s and migrated from a mainframe so that might explain why.