March 5, 2009 at 1:42 pm
I have narrowed this issue down to a single field that is causing the problem. In both databases the field if of FLOAT data type. Does anyone know of any issues relating to updating this data type between the two databases?
I have added an extra step to an existing overnight job. The job extracts data from a SQL database and updates an Oracle view through a linked server. When I run the job it returns an error on the new step.
Message
(21 row(s) affected)
[OLE/DB provider returned message: Row cannot be located for updating. Some values may have been changed since it was last read.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::SetData returned 0x80040e38: The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.].
Msg 7343, Level 16, State 4, Line 17
OLE DB provider 'MSDAORA' could not UPDATE table '[SPATIAL]..[SDC_EFEAT].[V_SEWER_MISC]'. The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
SQL Script for Step
--Sewer Miscellaneous
UPDATE SPATIAL..SDC_EFEAT.V_SEWER_MISC
SET
S_TYPE = 'Maintenance Shaft',
ASSET_ID= null,
ASSET_TYPE=null,
ABBREV_ASSET_ID = null,
S_DEPTH = null,
YEAR_CONSTRUCTED = null,
SERVICE_STATUS = null,
NO_COMPLETE_REACT_WO = null,
NO_OPEN_WO = null
WHERE
COMPKEY is null
UPDATE SPATIAL..SDC_EFEAT.V_SEWER_MISC
SET
S_TYPE =
CASE
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'CLARFY' THEN 'Clarifier'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'CMPST' THEN 'Composting Toilet'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'CULOUT' THEN 'Culvert Outlet'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'CULVET' THEN 'Culvert'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'CULVIN' THEN 'Culvert Inlet'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'DUMP' THEN 'Campervan Dump Station'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'FHTANK' THEN 'Fire Fighting Tank'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'HDWALL' THEN 'Headwall'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'HOLD' THEN 'Holding Tank'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'LGDRP' THEN 'Long Drop Toilet'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'NIGHT' THEN 'Night Soil (Toilet)'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'OUTLET' THEN 'Storm Outlet'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'RESCON' THEN 'Resource Consent'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'RESTR' THEN 'Restrictor No Tank'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'RETIC' THEN 'Reticulation (Toilet)'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'RWSTOR' THEN 'Rural Water Storage Tank'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'RWTANK' THEN 'Tank Used for Rural Water'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'SEPTIC' THEN 'Septic Tank'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'SH' THEN 'Soak Hole (Type Unknown)'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'SHAFT' THEN 'Maintenance Shaft'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'SHL' THEN 'Soak Hole'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'SHUL' THEN 'Soak Hole (Unlined)'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'SVENT' THEN 'Sewer Air Vent'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'URBAN' THEN 'Tank in Urban Area'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'WSHOUT' THEN 'Washout Point'
WHEN (IMSV7.COMPSMS.UNITTYPE) = 'WTRRT' THEN 'Water Right'
ELSE 'Error Unit Type'
END,
ASSET_ID= RTRIM(IMSV7.COMPSMS.UNITID),
ABBREV_ASSET_ID= substring(IMSV7.COMPSMS.UNITID,6,len(IMSV7.COMPSMS.UNITID)),
S_DEPTH = IMSV7.COMPSMS.DPTH,
YEAR_CONSTRUCTED = YEAR(IMSV7.COMPSMS.INSTDATE),
SERVICE_STATUS = IMSV7.TBL249.DESCRIPT
FROMIMSV7.COMPSMS
INNER JOIN SPATIAL..SDC_EFEAT.V_SEWER_MISC SM
ON IMSV7.COMPSMS.COMPKEY = SM.COMPKEY
LEFT OUTER JOIN IMSV7.TBL249
ON IMSV7.COMPSMS.SERVSTAT = IMSV7.TBL249.CODE
--WORKORDERS
UPDATE SPATIAL..SDC_EFEAT.V_SEWER_MISC
SET
NO_COMPLETE_REACT_WO = sdc1.no_completed_reactive_workorders(compkey),
NO_OPEN_WO = sdc1.No_Open_Workorders(compkey)
WHERE
COMPKEY is not null
February 10, 2011 at 5:57 am
Any replies for this..? I am also facing same problm... If somebody helps grt......... 🙂
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply