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

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.
LVL 1
ZekeLAAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ZekeLAConnect With a Mentor Author Commented:
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
 
Brendt HessSenior DBACommented:
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
 
ZekeLAAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.