Can any help me desperately - 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)

Can any help me desperately

Author

Topic

senpoly
Starting Member

19 Posts

Posted - 2007-08-07 : 07:19:16

i am running the following sp frequently in production

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,

regards
senthil

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-08-07 : 10:17:35

Not sure why you are using permanent tables as temporary tables.

Instead of inserting and then deleting, try inserting ONLY the data you want.

INSERT INTO GimDomContainers
( EDISNO, ContainerNo, ActivityDate , MovementCode, Documentrefno, PortCode
, CrDate )

select * from
(
SELECT
m.edisno, m.containerno, m.activitydate, m.movementcode, m.documentrefno , m.portcode, m.crdate
FROM
cntrmovementdetail_mirror m (nolock)
LEFT JOIN
lrpdemurrageedinos a
ON
m.edisno = a.edisno
WHERE
( m.crdate >= @FROMDATE and m.crdate < @TODATE1 )
and
m.movementcode in ( 'GIM', 'DOM' )
and
a.edisno IS NULL

UNION ALL

SELECT
f.edisno, f.containerno, f.activitydate, f.movementcode, f.documentrefno , f.portcode, f.crdate
FROM
gimdomcontainers2 f
LEFT JOIN
lrpdemurrageedinos b
ON
f.edisno = b.edisno
WHERE
( f.crdate >= @FROMDATE and f.crdate < @TODATE1 )
and
f.movementcode in ( 'GIM', 'DOM' )
and
b.edisno IS NULL
)

also, this bit of code

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

looks like it can return more than one value in your select, therefore VC.ContainerNo cannot = more than one value. It also looks like you are emptying a table, inserting into it, reading from another table, then inserting that into the first table. You may just want to start over and lay out what is going on. It is pretty cofusing....

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

senpoly
Starting Member

19 Posts

Posted - 2007-08-08 : 05:09:52

actually i am using the permanent table as temporary table cause, this has to be continued for another 8 stored procedures continuously, so rather than using temp tables i am using a physical table, and for this below mentioned (SELECT DISTINCT ContainerNo FROM GimDomContainers D
WHERE D.ContainerNo = vc.ContainerNo AND VC.ActivityDate <= D.ActivityDate )

i have been using this query for last 1 and half years no problem occured, as it can fetch result for all containerno,

so please suggest me further

regards

senthil

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-08 : 05:23:20

A couple of suggestions here - it may not solve the problem, but it should speed it up a bit.
1.) if the following tables don't have identity columns on them - then trunctae them at the beginning instead of deleting them.
GimDomContainers
DemurrageContainerMovement
DemurrageList
DemurrageListFinal

ie istead of delete from DemurrageListFinal - use truncate table DemurrageListFinal

2.) Like donatwork suggested - why do you insert and then delete - it is really, really not practical, rather insert in one go.

perhaps if the code was more slick - you wont experience these problems.

Duane.

senpoly
Starting Member

19 Posts

Posted - 2007-08-14 : 02:50:50

ditch thks for ur reply,
actually i am facing this problem, when i am inserting in to that table
i have mentioned it clearly, i have checked without deleting also, i face the same problem, i have one more doubt 10 days before my dba set replication of that table(cntrmovementdetail_mirror) will it be cause

suggest me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources