[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

T-SQL adding control characters to NTEXT field

Posted on 2011-02-15
3
Medium Priority
?
741 Views
Last Modified: 2012-06-21
Hi, I'm modifying the reportserver catalog table parameter field which is NTEXT using a stored procedure.  I'm reading in the original ntext parameter field and converting it's  value from ntext to XML to manipulate specific elements and change their values.  That is working fine.  I'm writing the changed parameter value back to catalog.parameter converting it back to NTEXT.  I noticed because I converted it to XML it loses all of the control characters (tabs, carriage returns ? which were stored originally in the NTEXT value.  I think when I then try to run Report Manager with my new parameter value, it's failing because I need the control characters.  I'm attaching a file to show the parameter field before and after.  Can someone tell me if there is an easy way to fix this?  THank You!
20110215ParameterFieldWithandWit.doc
0
Comment
Question by:athea123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 34903986
don't use NTEXT, but NVARCHAR(MAX) instead as data type (TEXT and NTEXT are depreciated in sql 2005+), or directly XML as data type...

listening about the "loosing characters" ...
0
 

Assisted Solution

by:athea123
athea123 earned 0 total points
ID: 34908164
Thanks for the response.  I did read that NTEXT will be depreciated but we are on SQL Server 2008 and in the ReportServer database, Microsoft still uses NTEXT - in this case I was dealing with the catalog table, parameter field where they use the datatype NTEXT.  I'm closing this ticket because I was able to solve my problem last night with this command:
SELECT @genericparameter = REPLACE(cast(@nd as nvarchar(max)),'><','>'+CHAR(13)+CHAR(10)+'<')
Once I put the control characters in I stored the parameter string back to the catalog.parameter field and when I ran Report Manager, everything worked perfectly (with the changes I wanted to see from changing the parameter field in the back-end).  Thanks again!
0
 

Author Closing Comment

by:athea123
ID: 34941324
Solved on my own.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question