Solved

What's a good way to edit varchar field containing CRLFs in SQL 2000?

Posted on 2010-09-20
3
283 Views
Last Modified: 2012-05-10
I have an email template table in SQL 2000 which stores the body of an email. I initialized the data using SQL scripts that contained char(13) and char(10).  My client needs to make changes to this data but doesn't have access / knowledge of SMS or T-SQL.

In trying to use Excel (using Alt-Enter for line breaks) and Access (via a linked table), the data gets pasted but replaces each line break with a bunch of spaces.

Are there any end-user friendly ways to edit the text and keep the CRLFs? Or should I generate the email in html format and just teach my client how to incldue "<br />" tags?

Thanks in advance.
0
Comment
Question by:ZekeLA
[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 32

Expert Comment

by:bhess1
ID: 33720680
You can still use Excel / Access, but you need to think differently.  Here's what I would do with those tools for editing the data.

First, write a procedure to split the text body into multiple rows (there are a lot of SPLIT type functions out there that can handle this already).

Create a table for staging the data that will include the ID of the email template and one line of email template data per row.  So, if your email template had ten line breaks, the resulting table would have eleven rows.

Export the data to that table as multiple rows.

Have Excel read in the rows, one per cell.

Have the user edit the text, one line per cell (don't use the ALT-ENTER line breaks).

When done, the user clicks something that calls a section of code that will update the staging table with all of the new data and trigger another procedure.

The final procedure merges the rows into a CR/LF delimited piece of text and updates the original table.

Voila.

It may sound kludgy, but it actually can work quite well, and gets rid of other kludges (such as the Alt-Enter thing).
0
 
LVL 1

Author Comment

by:ZekeLA
ID: 33720748
I appreciate the idea but since I already have a table with a single template body field, I think it would be easier to just convert it to html and let the user edit it with a simple html editor. A problem with each line as a record is that I would really need a parent child relationship since the email subject is not per line. There's also the issue of what to do for future maintenance if lines get moved around. But your solution would resolve the CR/LF conversion issue.
0
 
LVL 1

Accepted Solution

by:
ZekeLA earned 0 total points
ID: 33799832
Since I already had admin pages that used textarea fields to edit text data, I built a new admin page to edit this data. The textarea preserves the CRLFs.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

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 article describes a serious pitfall that can happen when deleting shapes using VBA.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

710 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