Solved

T-SQL adding control characters to NTEXT field

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now