Error using UPDATETEXT involving CRLF split

aSaMattJaSaMattJ Posts: 3
edited September 8, 2011 7:37AM in SQL Packager Previous Versions
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:
"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

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Are you creating the script and then packaging the script rather than packaging the execution block object?
  • Yes, I am writing the script to disk and then packaging it at a later time. Is there an example of packaging an execution block? This may be useful.
  • The examples are installed as part of the SDK distribution. There is a SQL Packager Code Snippets example installed on your hard disk. The key bit is to use the appropriate engine constructor.
    PackagerEngine engine=new PackagerEngine(
    						   TemplateFolder,					//Get Folder to the Templates
    						   TargetPackageFolder,				//Gets the TargetPackageFolder
    						   PackageName,						//The name of the package to produce
    						   m_SchemaBlock,					//The exection block regarding schema modifications
    						   m_DataBlock,						//The exection block regarding data modifications
    						   OutputType.Executable)
    
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Did packaging the execution block instead help you get around the CR/LF issue?
Sign In or Register to comment.