Unable to detect error in sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unable to detect error in sp

Author

Topic

senpoly
Starting Member

19 Posts

Posted - 2007-08-06 : 09:54:30

i am running the following sp

alter PROCEDURE [dbo].[USP_LRPCONTAINERDETENTION_MAINEXE]
@ChargeRunName VARCHAR( 25 ),
@FROMDATE DATETIME,
@TODATE DATETIME
AS
BEGIN
SET NOCOUNT ON
DELETE FROM GimDomContainers
DELETE FROM DemurrageContainerMovement
DELETE FROM DemurrageList
DELETE FROM DemurrageListFinal

SET NOCOUNT ON
INSERT INTO DemurrageStatistics ( ChargeRun, ProcNo, StartTime ) VALUES ( @ChargeRunName, '1' , getDate() )

DECLARE @TODATE1 DATETIME
SET @TODATE1 = DATEADD( DAY, 1 , @TODATE )
INSERT INTO GimDomContainers( EDISNO, ContainerNo, ActivityDate , MovementCode, Documentrefno, PortCode, CrDate )
SELECT edisno, containerno, activitydate, movementcode, documentrefno , portcode, crdate
FROM cntrmovementdetail_mirror (nolock)
WHERE ( crdate >= @FROMDATE and crdate < @TODATE1 ) and movementcode in ( 'GIM', 'DOM' )-- and

delete from gimdomcontainers where edisno in ( SELECT EdisNo FROM lrpdemurrageedinos )
-----------------------------------------------------

INSERT INTO GimDomContainers( EDISNO, ContainerNo, ActivityDate , MovementCode, Documentrefno, PortCode, CrDate )
SELECT edisno, containerno, activitydate, movementcode, documentrefno , portcode, crdate
FROM gimdomcontainers2
delete from gimdomcontainers where edisno in ( SELECT EdisNo FROM lrpdemurrageedinos )

-----------------------------------------------------

INSERT INTO DemurrageContainerMovement( EdisNo, ContainerNo, MovementCode, ActivityDate, Documentrefno, VslCode, VslName,
VoyCode, ServiceCode, Bound, PortCode, Depot, CarrierCode, Remarks, CrDate )
SELECT vc.edisno, vc.containerno, vc.movementcode, vc.activitydate, vc.documentrefno, vc.vslcode, vc.vslname,
vc.voycode, vc.servicecode, vc.bound, vc.portcode, vc.depot, vc.carriercode, vc.remarks, vc.crdate
FROM cntrmovementdetail_mirror vc (nolock)
WHERE VC.ContainerNo = ( SELECT DISTINCT ContainerNo FROM GimDomContainers D
WHERE D.ContainerNo = vc.ContainerNo AND VC.ActivityDate <= D.ActivityDate ) AND
VC.EdisNo NOT IN ( SELECT EdisNo FROM lrpdemurrageedinos )
-----------------------------------------------------

UPDATE DemurrageStatistics SET EndTime = GetDate() WHERE ChargeRun = @ChargeRunName AND ProcNo = 1

END

sometimes it runs fine

but many times it shows the following error

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

on scrutinising it i found the actual error occurs when the following query runs inside the sp
INSERT INTO DemurrageContainerMovement( EdisNo, ContainerNo, MovementCode, ActivityDate, Documentrefno, VslCode, VslName,
VoyCode, ServiceCode, Bound, PortCode, Depot, CarrierCode, Remarks, CrDate )
SELECT vc.edisno, vc.containerno, vc.movementcode, vc.activitydate, vc.documentrefno, vc.vslcode, vc.vslname,
vc.voycode, vc.servicecode, vc.bound, vc.portcode, vc.depot, vc.carriercode, vc.remarks, vc.crdate
FROM cntrmovementdetail_mirror vc (nolock)
WHERE VC.ContainerNo = ( SELECT DISTINCT ContainerNo FROM GimDomContainers D
WHERE D.ContainerNo = vc.ContainerNo AND VC.ActivityDate <= D.ActivityDate ) AND
VC.EdisNo NOT IN ( SELECT EdisNo FROM lrpdemurrageedinos )

can any one suggest me, how can i solve this problem,

please treat it as urgent, if it is not solved in a day, i have to look for a new job

regards
senthil

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-06 : 11:56:31

(nolock)
is a likely cause - get rid of it.

WHERE VC.ContainerNo = ( SELECT DISTINCT ContainerNo
will only work if there is a single ContainerNo erturned so (select top 1 ContainerNo would be better.
In fact it's = vc.ContainerNo so this an exists filter (actually I suspect it should be checking the date rather than the containerno to get the last record).

WHERE VC.ActivityDate = ( SELECT min(D.ActivityDate) FROM GimDomContainers D
WHERE D.ContainerNo = vc.ContainerNo AND VC.ActivityDate <= D.ActivityDate )

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-06 : 11:57:28

check this part of the code:


....
WHERE VC.ContainerNo = ( SELECT DISTINCT ContainerNo FROM GimDomContainers D 
						WHERE D.ContainerNo = vc.ContainerNo AND VC.ActivityDate <= D.ActivityDate ) 
...

will the subquery return more than one value under any sicrumstance or will t always be one record?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

senpoly
Starting Member

19 Posts

Posted - 2007-08-07 : 06:12:20

it returns more than a thousand records dinakar, but the same problem persists if i use the inner join
and my question is y it runs sometimes perfectly and many times shows the error

alse the table cntrmovementdetail_mirror is replicated will it cause problem

regards
senthil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources