Error using UPDATETEXT involving CRLF split

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

Error using UPDATETEXT involving CRLF split

Postby aSaMattJ » Thu Sep 01, 2011 8:03 pm

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
aSaMattJ
 
Posts: 2
Joined: Thu Sep 01, 2011 4:56 pm

Postby Brian Donahue » Mon Sep 05, 2011 11:44 am

Are you creating the script and then packaging the script rather than packaging the execution block object?
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby aSaMattJ » Mon Sep 05, 2011 3:35 pm

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.
aSaMattJ
 
Posts: 2
Joined: Thu Sep 01, 2011 4:56 pm

Postby Brian Donahue » Mon Sep 05, 2011 4:00 pm

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.
Code: Select all
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 Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Thu Sep 08, 2011 12:37 pm

Did packaging the execution block instead help you get around the CR/LF issue?
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 0 guests