Solved

T-SQL adding control characters to NTEXT field

Posted on 2011-02-15
3
726 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
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
In this article I will describe the Detach & Attach 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.
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…

820 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