[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Internal SQL Server error on SQL Statement

Catch_22

3/26/2007 4:38:00 AM

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.
3 Answers

Razvan Socol

3/26/2007 5:01:00 AM

0

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

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://support.microsoft.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

Razvan Socol

3/26/2007 5:28:00 AM

0

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

Catch_22

3/26/2007 5:30:00 AM

0

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.

On Mar 26, 3:00 pm, "Razvan Socol" <rso...@gmail.com> wrote:
> The "Internalservererror" messages are usually caused by a bug in
> the query processor. For example, see:http://www.google.com/search?q=site%3Asupport.microsoft.com......
>
> What version ofSQLServer2000 do you have ? If you have less than
> SP4, install SP4. If you installed it already, install hotfix 2187
> from:http://support.microsoft.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