Internal SQL Server error on SQL Statement

Internal SQL Server error on SQL Statement

Post by Catch_2 » Tue, 27 Mar 2007 13:38:19


Hi,

I'm have a stored procedure running on an SQL Server 2000 database.

One particular SQL statement is failing (listed below) :

DELETE t_PaymentLN
FROM t_Payment inner join t_PaymentLN
ON t_Payment.PaymentID = t_PaymentLN.PaymentID
inner join t_StgSASummary
on t_StgSASummary.CustomerNumber = t_Payment.CustomerNumber
and t_StgSASummary.OrderNumber = t_Payment.OrderNumber
and t_StgSASummary.DetailNumber = t_Payment.DetailNumber
inner join t_AASummaries AAS1
on t_StgSASummary.BatchNumber = AAS1.BatchNumber
inner join t_AASummaries AAS2
on t_Payment.AuditAllocSummaryID = AAS2.AuditAllocSummaryID
where t_StgSASummary.responseaction = @RespAction_Payment
and t_StgSASummary.BatchNumber = @BatchNumber
and IsDate(t_Payment.DateResponse) = 0
and t_StgSASummary.[ReasonRejected] = 0
and AAS2.SNumber = AAS1.SNumber

This statement is currently causing the stored procedure to crash with
the following error message :

Msg 8624, Level 16, State 1, Procedure proc_LoadPreAllocEntries, Line
1201
Internal SQL Server error.

If I remove the last condition in the statement "and AAS2.SNumber =
AAS1.SNumber" the procedure will run without error.

Can anyone help with what might be causing this error, is it the
structure of the statement ?

I've run a DBCC CHECKTABLE on the tables as well as on the database
and they both come back OK.
 
 
 

Internal SQL Server error on SQL Statement

Post by Razvan Soc » Tue, 27 Mar 2007 14:00:50

The "Internal server error" messages are usually caused by a bug in
the query processor. For example, see:
http://www.yqcomputer.com/ %3Asupport.microsoft.com+%22Internal+SQL+Server+error%22+%22Msg+8624%2C+Level+16%2C+State+1%22

What version of SQL Server 2000 do you have ? If you have less than
SP4, install SP4. If you installed it already, install hotfix 2187
from:
http://www.yqcomputer.com/
If the problem still appears, you can contact Microsoft Support (for a
charge, but if they see that the problem is due to a bug, they may
cancel the fee).

Razvan

 
 
 

Internal SQL Server error on SQL Statement

Post by Razvan Soc » Tue, 27 Mar 2007 14:28:10

You can also try one of these queries:

DELETE t_PaymentLN
FROM t_Payment inner join t_PaymentLN
ON t_Payment.PaymentID = t_PaymentLN.PaymentID
inner join t_StgSASummary
on t_StgSASummary.CustomerNumber = t_Payment.CustomerNumber
and t_StgSASummary.OrderNumber = t_Payment.OrderNumber
and t_StgSASummary.DetailNumber = t_Payment.DetailNumber
inner join t_AASummaries AAS1
on t_StgSASummary.BatchNumber = AAS1.BatchNumber
inner join t_AASummaries AAS2
on t_Payment.AuditAllocSummaryID = AAS2.AuditAllocSummaryID
and AAS2.SNumber = AAS1.SNumber
where t_StgSASummary.responseaction = @RespAction_Payment
and t_StgSASummary.BatchNumber = @BatchNumber
and IsDate(t_Payment.DateResponse) = 0
and t_StgSASummary.[ReasonRejected] = 0

or:

DELETE t_PaymentLN
FROM t_Payment inner join t_PaymentLN
ON t_Payment.PaymentID = t_PaymentLN.PaymentID
inner join t_StgSASummary
on t_StgSASummary.CustomerNumber = t_Payment.CustomerNumber
and t_StgSASummary.OrderNumber = t_Payment.OrderNumber
and t_StgSASummary.DetailNumber = t_Payment.DetailNumber
inner join t_AASummaries AAS1
on t_StgSASummary.BatchNumber = AAS1.BatchNumber
inner join t_AASummaries AAS2
on t_Payment.AuditAllocSummaryID = AAS2.AuditAllocSummaryID
and AAS2.SNumber = AAS1.SNumber
where t_StgSASummary.responseaction = @RespAction_Payment
and t_StgSASummary.BatchNumber = @BatchNumber
and IsDate(t_Payment.DateResponse) = 0
and t_StgSASummary.[ReasonRejected] = 0

DELETE t_PaymentLN WHERE PaymentID IN (
SELECT t_Payment.PaymentID
FROM t_Payment inner join t_PaymentLN
ON t_Payment.PaymentID = t_PaymentLN.PaymentID
inner join t_StgSASummary
on t_StgSASummary.CustomerNumber = t_Payment.CustomerNumber
and t_StgSASummary.OrderNumber = t_Payment.OrderNumber
and t_StgSASummary.DetailNumber = t_Payment.DetailNumber
inner join t_AASummaries AAS1
on t_StgSASummary.BatchNumber = AAS1.BatchNumber
inner join t_AASummaries AAS2
on t_Payment.AuditAllocSummaryID = AAS2.AuditAllocSummaryID
and AAS2.SNumber = AAS1.SNumber
where t_StgSASummary.responseaction = @RespAction_Payment
and t_StgSASummary.BatchNumber = @BatchNumber
and IsDate(t_Payment.DateResponse) = 0
and t_StgSASummary.[ReasonRejected] = 0
)

...there is a slim chance that one of them may avoid the bug.

Razvan
 
 
 

Internal SQL Server error on SQL Statement

Post by Catch_2 » Tue, 27 Mar 2007 14:29:57

Hi,

Thanks for your quick reply.

We're running SQL Server 2000 - SP4 (8.00.2039)

So perhaps this is the issue - we only have 2039 - so we should try
installing 2187 ?

Regards,
Ian.