2nd update after update memo field corrupts row 
Author Message
 2nd update after update memo field corrupts row

I've got a 6.5 sp2 database with a table containing many normal fields
and 3 textfields.

If I modify the normal fields using an UPDATE query, all goes well.

If I modify one of the text fields, the next time I run the stored
procedure I use to lock the record with my custom lock field (which
doesn't modify any memo fields), the row becomes corrupted.  The
timestamp field becomes NULL and some keys end up with trash
characters.

This behavior only began after I added some text fields.  I am at a
loss.

The code that I use to update the record (bill address is a text
field) looks like:

update tSite SET billAddress='xxxxafafadfadfadfafadf' ,revDate =
'3/19/97' ,lockUser = NULL,lockWs = NULL,lockTime = NULL where siteCtr
= 5 and tsequal(siteTs, convert(binary(8),
convert(money,429499.5888)))

The stored procedure is as follows:

CREATE PROCEDURE prLockSite

)
as





        from tSite



begin
        raiserror (55000, 1,1)
        return 1
end


        begin
                raiserror(55001, 1, 1)
                return 1
        end

UPDATE tSite





begin

        from tSite

end

-- if there is an error in select, will return that or a
-- 0 for success

GO

How can I make my database work with these text fields?  I don't
understand how SQL Server can corrupt a row.  This sounds like a bug
to me.  Are there some workarounds so that I can get text fields to
work?
--



Mon, 06 Sep 1999 03:00:00 GMT
 2nd update after update memo field corrupts row

CALL MICROSOFT.  There are bugs with sp2 and they should :) give you a
hotfix for your problem.
I had this and other problems with text until I got the hotfix.

--
Louis Davidson

SQL Server Consultant



Quote:
> I've got a 6.5 sp2 database with a table containing many normal fields
> and 3 textfields.

> If I modify the normal fields using an UPDATE query, all goes well.

> If I modify one of the text fields, the next time I run the stored
> procedure I use to lock the record with my custom lock field (which
> doesn't modify any memo fields), the row becomes corrupted.  The
> timestamp field becomes NULL and some keys end up with trash
> characters.

> This behavior only began after I added some text fields.  I am at a
> loss.

> The code that I use to update the record (bill address is a text
> field) looks like:

> update tSite SET billAddress='xxxxafafadfadfadfafadf' ,revDate =
> '3/19/97' ,lockUser = NULL,lockWs = NULL,lockTime = NULL where siteCtr
> = 5 and tsequal(siteTs, convert(binary(8),
> convert(money,429499.5888)))

> The stored procedure is as follows:

> CREATE PROCEDURE prLockSite
> (





> )
> as





>    from tSite



> begin
>    raiserror (55000, 1,1)
>    return 1
> end


>    begin
>            raiserror(55001, 1, 1)
>            return 1
>    end

> UPDATE tSite





> begin

>    from tSite

> end

> -- if there is an error in select, will return that or a
> -- 0 for success

> GO

> How can I make my database work with these text fields?  I don't
> understand how SQL Server can corrupt a row.  This sounds like a bug
> to me.  Are there some workarounds so that I can get text fields to
> work?
> --




Sat, 11 Sep 1999 03:00:00 GMT
 2nd update after update memo field corrupts row

Sounds like we have similar problems.  We have also have a table with 3
text fields and after a certain amount of updates, data in the updated rows
gets trashed.  Ours shows up as -0 or huge negative numbers in our number
fields when we didn't put them there.  All updates to the row after that
produce junk.  We're not sure if we have to update the text columns to make
it happen, but changing them to varchar seems to make the problem go away.
I've only been on this a day and am not sure where to start.  We've thought
about wrapping every update to text fields in an explicit trans.  Are your
corrupted fields immediately following the text fields?

Sorry I don't have any answers.  I'm kind of hoping you may have had some
luck so far.

We're running on NT4.0




Sat, 11 Sep 1999 03:00:00 GMT
 2nd update after update memo field corrupts row


you run?

        Christer Romson

Quote:

> Sounds like we have similar problems.  We have also have a table with 3
> text fields and after a certain amount of updates, data in the updated rows
> gets trashed.  Ours shows up as -0 or huge negative numbers in our number
> fields when we didn't put them there.  All updates to the row after that
> produce junk.  We're not sure if we have to update the text columns to make
> it happen, but changing them to varchar seems to make the problem go away.
> I've only been on this a day and am not sure where to start.  We've thought
> about wrapping every update to text fields in an explicit trans.  Are your
> corrupted fields immediately following the text fields?

> Sorry I don't have any answers.  I'm kind of hoping you may have had some
> luck so far.

> We're running on NT4.0





Sun, 12 Sep 1999 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. 2nd update after update memo field corrupts row

2. 2nd SQL Server update after update text field corrupts row - using RDO

3. Updating data to a memo field using SQL UPDATE

4. Update field on updated row with trigger??

5. Using a trigger to update a time field in a table when a row is updated

6. SQL Server update error: row cannot be update for updating

7. Updating a text col with NULL corrupts row...

8. Update a field with time stamp when other field are updated

9. Update Memo-Column with SQL UPDATE Statement

10. ODBC Update of Text field corrupting table...

11. Updating SQL memo fields - Invalidates log

12. updating Memo/Text Field


 
Powered by phpBB® Forum Software