Solved

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

Posted on 2010-09-20
3
284 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:Brendt Hess
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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