Red Gate forums :: View topic - Error using UPDATETEXT involving CRLF split
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

Error using UPDATETEXT involving CRLF split

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
aSaMattJ



Joined: 01 Sep 2011
Posts: 2

PostPosted: Thu Sep 01, 2011 8:03 pm    Post subject: Error using UPDATETEXT involving CRLF split Reply with quote

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:

Quote:
"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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6578

PostPosted: Mon Sep 05, 2011 11:44 am    Post subject: Reply with quote

Are you creating the script and then packaging the script rather than packaging the execution block object?
Back to top
View user's profile Send private message
aSaMattJ



Joined: 01 Sep 2011
Posts: 2

PostPosted: Mon Sep 05, 2011 3:35 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6578

PostPosted: Mon Sep 05, 2011 4:00 pm    Post subject: Reply with quote

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:
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)
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6578

PostPosted: Thu Sep 08, 2011 12:37 pm    Post subject: Reply with quote

Did packaging the execution block instead help you get around the CR/LF issue?
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group