Strange Nul issue in SQL Server script data

Question

I have created a sql server script of a large database table (more than 2 GB). while running the script I got this error: "Unclosed quotation mark after the character string." While trying to debug the issue I have found that there is a strange "NUL" value between text. This "NUL" is only visible in Notepad++.

Anyone knows how to make script of database table without this "NUL". {If I remove this manually script works fine. But I am not able to remove it manually as some tables are more than 10 GB}

Thanks

I guess your data contains `NUL` literal: http://stackoverflow.com/questions/2828333/what-is-the-null-character-literal-in-tsql. Check for one column with it `SELECT col_name, LEN(col_name) , DATA_LENGTH(col_name) FROM table WHERE id = ?` — Lukasz Szozda, Nov 16 '15 at 11:35
Probably you will need to clean your data. Or use replace in Notepad++ `NUL` to empty — Lukasz Szozda, Nov 16 '15 at 11:37
be careful that your script does not include or introduce unexpected characters. look through your script for unexpected whitespace at the end of lines — Paul Maxwell, Nov 16 '15 at 12:02

score 0 · Accepted Answer · answered Nov 18 '15 at 10:59

Thanks all I have solved my issue.

I was reading data from NON MS Sql Database and inserting into MS SQL server using asp .net ADO way. During testing of the cause I found that there is invalid data "NUL" in the existing database. So I modified my insertion code and just replaced "NUL" with "Blank" using REGEX.

Regex.Replace(MyDataColumnName, "\x00", "", RegexOptions.IgnoreCase);

Now there is no "NUL".

Strange Nul issue in SQL Server script data

1 Answers1