Solved

T-SQL adding control characters to NTEXT field

Posted on 2011-02-15
3
732 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 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore 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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

707 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