dotnetco.de

The problem with editing large texts in SQL Backend is that the text is broken up into separate lines, so the “carriage return” character at the end of the first line is interpreted by anything SQL as the “I’ve finished typing, now go do that” command.
The quickest way around it is simply to update the table using standard SQL commands (i.e. update tablename set field=value where….). Now usually, you would simply put the value bit in single quotes and run the SQL, but you have the added complication that the text contains single quotes itself, and the way to specify single quotes as literals is putting a single quote before it (i.e. ” – which is obviously not the same as the ” double quote even if it looks the same on screen).
Therefore, I would suggest doing the following (name of the textfield is ‘comment’ and ‘logid’ is the unique identifier):

1. Open up a new query window in SQL Server Management Studio, pointing to the database.
2. Paste in the following code:

   set quoted_identifier ON
   declare @comment varchar(8000), @logid int
   set @logid =
   set @comment = ''
   update actionlog
   set comment = @comment
   where logid = @logid

3. Paste the text you want to change into Notepad, change it and then do an Edit Replace, replacing single quote ‘ with double quote ” and replace all instances.
4. Do a Select All, and copy the text.
5. Paste what you have on the clipboard into the SSMS query window between the two single quotes next to @Comment, so the text you copied gets assigned to the @comment variable.
6. Specify the logid number to identify the record to be updated.
7. Run the code.

It looks like a lot of steps, but once you’ve gone through it once, you’ll see it’s not much work at all, and certainly much quicker than scrolling through the field.

An alternative way could be to use the REPLACE function as follows:

update Actionlog
set comment = replace(convert(varchar(8000), comment), 'put what you want replaced here within the single quotes', 'put the new text you want here within the single quotes')
where logid = 'replace this string including single quotes with the logid number of the record you want changed'

e.g.

update Actionlog
set comment = replace(convert(varchar(8000), comment), 'Test result from yesterday, 'Test result from today')
where logid = 132

That way, you don’t need to copy large amounts of text out and in again, since you’re just changing small bits of it.