?
Solved

T-SQL adding control characters to NTEXT field

Posted on 2011-02-15
3
Medium Priority
?
735 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

764 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