Row failed to retrieve on last operation

Hello,

I am getting the following errors when "inserting" data into my tables using Miscrosoft SQL Server Management Studio with a SQL Server 2005 database...

"This row was successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after the commit. Because of this, the displayed data in this row is read-only. To fix this problem, please re-run the query."

In the status bar:

"row failed to retrieve on last operation"

I am hoping I just have some database properties set wrong but have not been able to figure it out.

close cursor on commit is set to False, set it to True, retested, no affect on the error.

other info:

The database is used to supply data to a Microsoft Access ADP application

Are you getting any error number with this?

Anything in the SQL logs?

Does this happen on all inserts or just certain ones?

|||

There is no error number that comes with this error.

There is nothing significant in the logs...

This error occurs when inserting a new record to any table in the database. The record does insert but is not visible until I have followed the instructions of the error msg.

Error Log:

2007-07-04 19:00:42.00 Server (c) 2005 Microsoft Corporation.
2007-07-04 19:00:42.00 Server All rights reserved.
2007-07-04 19:00:42.00 Server Server process ID is xxxx.
2007-07-04 19:00:42.00 Server Authentication mode is xxxxxx.
2007-07-04 19:00:42.00 Server Logging SQL Server messages in file xxxxx.
2007-07-04 19:00:42.00 Server This instance of SQL Server last reported using a process ID of xxxx at 7/4/2007 7:00:00 PM (local) 7/5/2007 2:00:00 AM (UTC). This is an informational message only; no user action is required.
2007-07-04 19:00:42.00 Server Registry startup parameters:
2007-07-04 19:00:42.00 Server xxxxx

2007-07-04 19:00:42.00 Server xxxxxx

2007-07-04 19:00:42.00 Server xxxxxx

2007-07-04 19:00:42.01 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-07-04 19:00:42.01 Server Detected X CPUs. This is an informational message; no user action is required.
2007-07-04 19:00:42.51 Server Using dynamic lock allocation. Initial allocation of xxxx Lock blocks and xxxxx Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-07-04 19:00:42.57 Server Database mirroring has been enabled on this instance of SQL Server.
2007-07-04 19:00:42.57 spidxs Starting up database 'master'.
2007-07-04 19:00:42.95 spidxs Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2007-07-04 19:00:43.01 spidxs SQL Trace ID 1 was started by login "xxxxxx".
2007-07-04 19:00:43.04 spidxs Starting up database 'xxxxxxxxxxx'.
2007-07-04 19:00:43.06 spidxs The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-07-04 19:00:43.20 spidxs Starting up database 'model'.
2007-07-04 19:00:43.21 spidxs Server name is 'xxxxx\MYDB'. This is an informational message only. No user action is required.
2007-07-04 19:00:43.43 spidxs Clearing tempdb database.
2007-07-04 19:00:43.71 spidxs Starting up database 'tempdb'.
2007-07-04 19:00:43.75 spidxs The Service Broker protocol transport is disabled or not configured.
2007-07-04 19:00:43.75 spidxs The Database Mirroring protocol transport is disabled or not configured.
2007-07-04 19:00:43.76 spidxs Service Broker manager has started.
2007-07-04 19:00:44.23 Server A self-generated certificate was successfully loaded for encryption.
2007-07-04 19:00:44.25 Server Server is listening on [ xxxxxx].
2007-07-04 19:00:44.25 Server Server local connection provider is ready to accept connection on [ xxx].
2007-07-04 19:00:44.25 Server Server named pipe provider is ready to accept connection on [xxxx].
2007-07-04 19:00:44.25 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2007-07-04 19:00:44.28 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2007-07-04 19:00:44.43 spidxs Starting up database 'msdb'.

|||

Can you send the DML of the table? And do you have a trigger on the table? I assume you are doing this in the "Open table" tool.

It seems to me that you possibly are changing the key value in a trigger or something and it cannot find the row with the key you edited. I get that error when I edit the following table:

create table fred

(

fredId int primary key,

value varchar(10)

)

go

create trigger fred$insertTrigger

on fred

after insert

as

update fred

set fredId = fredId * 10000

where fredId in (select fredId from inserted)

go

|||There is a constraint for a default in the table and for some reason, when using the open table tool, the default is not being applied automatically and that is what I believe makes the error msg appear.|||That sounds plausible. Can you send the DML for the table? I will test it too.