Internal SQL Server error. [SQLSTATE 42000] (Error 8624). The step failed.

[Devron Blatchford]

Hi there,

Internal SQL Server error. [SQLSTATE 42000] (Error 8624). The step failed.

We have an SQL procedure that fails with the above error running on W2K SP4,
SQL 2000 SP2. I have restored our database to another test server with the
same OS, SQL and SP's but the error does not occur.

Can anyone give me ideas on what may cause this. I looked in TechNet and
found a few articles but none of them seemd to be relevent.

Thanks
Devron

[Dejan Sarka]

Here are few possibilities:
http://support.microsoft.com/default.aspx?scid=kb;en-us;290817
http://support.microsoft.com/default.aspx?scid=kb;en-us;280468
http://support.microsoft.com/default.aspx?scid=kb;en-us;317527

You should give more info what's going on.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Devron Blatchford" <dev...@auspine.com.au> wrote in message
news:#wThv1qc...@tk2msftngp13.phx.gbl...

[Devron Blatchford]

Here is the exact error:

Server: Msg 8624, Level 16, State 1, Line 376
Internal SQL Server error.

Notice the state is one.

This is the SQL Statement it is failing on: I can't see anything here that
relates to the articles you posted.

Does this help?

Thanks
Devron

INSERT INTO dbo.tStockHistory
(
StockID,
StockCode,
ProductID,
TransactionID,
OwnerLocationID,
StockLocationID,
DestinationLocationID,
SourceDocumentID,
AllocatedToID,
DateIn,
DateOut,
FinishedGoods,
Damaged,
StockStatusID,
ReservedPartnerID,
ReservedOrderItemID,
ShippingUnitID,
BaseQuantityPieces,
BaseQuantityLength,
BaseQuantityArea,
BaseQuantityVolume,
QuantityStockUomID,
QuantityStock,
Price,
ExclusiveAmount,
ShiftID,
CrewID,
JobID,
PreviousStockHistoryID,
QuantityPricingUomID,
QuantityPricing,
RecordStatus,
RecordLastModified,
RecordUserID
)
SELECT
InvoiceSH.StockID,
InvoiceSH.StockCode,
ReturnSH.ProductID,
CreditTran.TransactionID AS TransactionID,
ReturnSH.OwnerLocationID,
ReturnSH.StockLocationID,
ReturnSH.DestinationLocationID,
ReturnSH.SourceDocumentID,
ReturnSH.AllocatedToID,
ReturnSH.DateIn,
ReturnSH.DateOut,
ReturnSH.FinishedGoods,
ReturnSH.Damaged,
InvoiceSH.StockStatusID,
ReturnSH.ReservedPartnerID,
ReturnSH.ReservedOrderItemID,
ReturnSH.ShippingUnitID,
ReturnSH.BaseQuantityPieces,
ReturnSH.BaseQuantityLength,
ReturnSH.BaseQuantityArea,
ReturnSH.BaseQuantityVolume,
ReturnSH.QuantityStockUomID,
ReturnSH.QuantityStock,
ReturnSH.Price,
ReturnSH.ExclusiveAmount,
ReturnSH.ShiftID,
ReturnSH.CrewID,
@CreditJobID AS JobID,
InvoiceSH.StockHistoryID, -- was ReturnSH.StockHistoryID
ReturnSH.QuantityPricingUomID,
ReturnSH.QuantityPricing,
ReturnSH.RecordStatus,
ReturnSH.RecordLastModified,
ReturnSH.RecordUserID
FROM
dbo.tTransaction ReturnTran INNER JOIN
dbo.tStockHistory ReturnSH ON ReturnTran.TransactionID =
ReturnSH.TransactionID INNER JOIN
dbo.tStockHistory InvoiceSH INNER JOIN
dbo.tTransaction InvoiceTran ON InvoiceSH.TransactionID =
InvoiceTran.TransactionID ON
ReturnSH.PreviousStockHistoryID = InvoiceSH.StockHistoryID INNER JOIN
dbo.tTransaction CreditTran
ON ReturnTran.TransactionID = CreditTran.ParentTransactionID
AND
InvoiceTran.AccountID = CreditTran.AccountID
WHERE
(InvoiceTran.TransactionProfileID = @TransactionProfileInvoiceID) AND
(ReturnTran.TransactionProfileID = @TransactionProfileReturnID) AND
(CreditTran.TransactionProfileID = @TransactionProfileCreditID) AND
(ReturnSH.TransactionID = @TransactionID)

"Dejan Sarka" <dejan_please_reply_...@avtenta.si> wrote in
message news:eRMv09qc...@TK2MSFTNGP12.phx.gbl...

[Dejan Sarka]

This article: http://support.microsoft.com/default.aspx?scid=kb;en-us;259141
is for SQL 7.0, but the error is very similar. Check if this is useful. Can
you do the Insert manually, with Values clause? And you can try to install
SP3a - not directly connected to your problem, but it is security-oriented,
so you should have it anyway. And it might solve the problem :-)

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Devron Blatchford" <dev...@auspine.com.au> wrote in message

news:eia5DKrc...@TK2MSFTNGP11.phx.gbl...

[Devron Blatchford]

Thanks will try, for your info I have found by changing the folowing line in the below statement the problem goes away.

 

Changed from:

ReturnSH.PreviousStockHistoryID = InvoiceSH.StockHistoryID

INNER JOIN
dbo.tTransaction CreditTran
ON  ReturnTran.TransactionID = CreditTran.ParentTransactionID
AND
InvoiceTran.AccountID = CreditTran.AccountID

Changed To:

ReturnSH.PreviousStockHistoryID = InvoiceSH.StockHistoryID

LEFT JOIN

dbo.tTransaction CreditTran
ON  ReturnTran.TransactionID = CreditTran.ParentTransactionID
AND
InvoiceTran.AccountID = CreditTran.AccountID

 

"Dejan Sarka" <dejan_please_reply_...@avtenta.si> wrote in message news:ubEdhZsc...@tk2msftngp13.phx.gbl...

[oj]

inner join and left join are not the same. they would produce different
result.

e.g.
create table t1 (i int)
insert t1 select 1 union all select 2 union all select 3
create table t2(i int)
insert t2 select 1 union all select 3

select *
from t1 inner join t2 on t1.i=t2.i

select *
from t1 left join t2 on t1.i=t2.i

drop table t1,t2

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"Devron Blatchford" <dev...@auspine.com.au> wrote in message

news:uE$7B0zcD...@TK2MSFTNGP09.phx.gbl...

[Devron Blatchford]

Yes I relalise that but in the case that t1.i and t2.i always exist in both
tables the result will be the same which is the case in this instance.

Thanks
Devron

"oj" <nospam...@home.com> wrote in message
news:uLELZ6zc...@TK2MSFTNGP09.phx.gbl...

[oj]

If you have the latest service pack installed and have to resort to left
join to get it to work then it's a *bug*.

Although, it works for you this time due to your current data. Leaving left
join in your production code would eventually give you bad result as data
would change. It's your data so you do whatever you want. However, I think
you're taking a great risk.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"Devron Blatchford" <dev...@auspine.com.au> wrote in message

news:eNU1fL0c...@TK2MSFTNGP10.phx.gbl...