Error using UPDATETEXT involving CRLF split
aSaMattJ
Posts: 3
I have run into a problem with columns of the DataType TEXT.
I used the RedGate SDK to script out the data for a table containing a TEXT column. The application takes the resulting script and writes it to a .sql file that can be run in Query Analyzer or Management Studio. This part works great -- no problems.
The problem is when I try to use RedGate Packager to package this script. The package throws this error when executing:
I have identified what is happening, but to my knowledge there is nothing I can do to control it -- it's a problem with Packager. When the original script is written to file, some of the data in the TEXT field is split between a Carriage Return and Line Feed. If you view the script in an editor capable of showing EOL characters you will see one of the lines ends in a Carriage Return and the next line ends with a Line Feed.
When RedGate packages this script, it must be normalizing line endings and adding a LF to the first line and a CR to the second line so that both lines end in the expected CRLF. First of all, that's wrong because now instead of 1 line break in the data, there will be 2. The bigger problem at hand is what is causing the error -- The extra characters are apparently pushing the UPDATETEXT call over its capacity.
Please note this isn't all I am with the SDK and Packager -- this is just a small part that is causing the entire thing to fail.
I have a very simple script you can use for testing. Please contact be with an e-mail address if you would like for me to send it.
I am using RedGate Packager v6.3.1.122
I used the RedGate SDK to script out the data for a table containing a TEXT column. The application takes the resulting script and writes it to a .sql file that can be run in Query Analyzer or Management Studio. This part works great -- no problems.
The problem is when I try to use RedGate Packager to package this script. The package throws this error when executing:
"Cannot convert data type nvarchar(max) to text.
The statement has been terminated."
I have identified what is happening, but to my knowledge there is nothing I can do to control it -- it's a problem with Packager. When the original script is written to file, some of the data in the TEXT field is split between a Carriage Return and Line Feed. If you view the script in an editor capable of showing EOL characters you will see one of the lines ends in a Carriage Return and the next line ends with a Line Feed.
When RedGate packages this script, it must be normalizing line endings and adding a LF to the first line and a CR to the second line so that both lines end in the expected CRLF. First of all, that's wrong because now instead of 1 line break in the data, there will be 2. The bigger problem at hand is what is causing the error -- The extra characters are apparently pushing the UPDATETEXT call over its capacity.
Please note this isn't all I am with the SDK and Packager -- this is just a small part that is causing the entire thing to fail.
I have a very simple script you can use for testing. Please contact be with an e-mail address if you would like for me to send it.
I am using RedGate Packager v6.3.1.122
Comments