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.

Rounding problems performing math with Decimal data types

We're seeing some rounding errors in a financial application. We had chosen
to use Decimal(38, 10) fields for both dollar amounts and allocation
percentages. However, when we multiply the numbers together we loose
significant precision, as in the following example:
DECLARE @.TNA_PERCENT AS DECIMAL(38, 10)
DECLARE @.ALLOC_PERCENT AS DECIMAL(38, 10)
DECLARE @.Result AS DECIMAL(38, 10)
SET @.TNA_PERCENT = .7363612218
SET @.ALLOC_PERCENT = .01
SET @.Result = @.TNA_PERCENT * @.ALLOC_PERCENT
SELECT @.TNA_PERCENT, @.ALLOC_PERCENT, @.Result
Returns:
.7363612218 .0100000000 .0073640000
Granted, the Decimal(38, 10) data type is overkill for the value .01, but it
was one example that came up. Why do we lose the precision during this
multiplication operation? Should we revise our data types, and if so, to
what? We will be dealing with large dollar amounts that need to be multiplie
d
accurately by calculated allocation percentages and stored.
Thanks,
Craig RandallCraig,
If you multiply a decimal 38 with another decimal 38, then the result
would be a decimal 76 (38+38). Since your result variable is restricted
to 38 digits, you loose precision.
If you want an exact result in a decimal(38,10), then the two
multiplication variables should have a combined total of at most 38.
Same for the precision. So if you multiply a decimal(19,5) with a
decimal(19,5), then this will fit in a decimal(38,10).
You will see that if you change the definition of @.TNA_PERCENT and
@.ALLOC_PERCENT to Decimal(19,10) that the result will be:
.7363612218 .0100000000 .0073636122
In this case, to get an exact result, you would need a precision of (at
least) 12 digits.
Gert-Jan
Crandaddy wrote:
> We're seeing some rounding errors in a financial application. We had chose
n
> to use Decimal(38, 10) fields for both dollar amounts and allocation
> percentages. However, when we multiply the numbers together we loose
> significant precision, as in the following example:
> DECLARE @.TNA_PERCENT AS DECIMAL(38, 10)
> DECLARE @.ALLOC_PERCENT AS DECIMAL(38, 10)
> DECLARE @.Result AS DECIMAL(38, 10)
> SET @.TNA_PERCENT = .7363612218
> SET @.ALLOC_PERCENT = .01
> SET @.Result = @.TNA_PERCENT * @.ALLOC_PERCENT
> SELECT @.TNA_PERCENT, @.ALLOC_PERCENT, @.Result
> Returns:
> .7363612218 .0100000000 .0073640000
> Granted, the Decimal(38, 10) data type is overkill for the value .01, but
it
> was one example that came up. Why do we lose the precision during this
> multiplication operation? Should we revise our data types, and if so, to
> what? We will be dealing with large dollar amounts that need to be multipl
ied
> accurately by calculated allocation percentages and stored.
> Thanks,
> Craig Randall