Internal SQL Server error. - 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 2000 Forums

SQL Server Development (2000)

Internal SQL Server error.

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-05-07 : 13:47:40

My SP Returns Internal SQL Server error.
Server: Msg 8624, Level 16, State 7, Line 4

And yet the select alone returns good data

I checked MSKB and it said something about a service pack but my system is SQL 2000 with all servce packs installed.

Declare @ReleaseID Int
Select @ReleaseID = 107

INSERT INTO dbo.TempARTRansDetail
(TransID, PartId, [Desc], TaxClass, GLAcctSales, GLAcctCOGS, GLAcctInv, QtyOrdSell, UnitsSell,
UnitsBase, QtyShipBase, QtyShipSell, QtyBackordSell, UnitPriceSell, UnitPriceSellFgn, PriceExt, PriceExtFgn)
SELECT DISTINCT
dbo.ShipRelease.ReleaseID, MasterPCS.dbo.SpecOrderdetail.SpecorderID, MasterPCS.dbo.SpecOrderdetail.Discription,
MasterPCS.dbo.SpecOrderdetail.Taxable, 1,1,1, MasterPCS.dbo.SpecOrderdetail.Order_qty AS QtyOrdSell,
'1' AS UnitsBase, '1' AS QtyShipBase, '1' AS QtyShipSell, '0' AS QtyBackordSell,
MasterPCS.dbo.SpecOrderdetail.UnitPrice AS unitPricesell, MasterPCS.dbo.SpecOrderdetail.UnitPrice AS unitPricesellFgn,
MasterPCS.dbo.SpecOrderdetail.UnitPrice AS PriceExt, MasterPCS.dbo.SpecOrderdetail.UnitPrice
FROM CCC.dbo.tblArCust INNER JOIN
MasterPCS.dbo.SpecOrder INNER JOIN
MasterPCS.dbo.SpecOrderdetail ON MasterPCS.dbo.SpecOrder.SpecOrderID = MasterPCS.dbo.SpecOrderdetail.SpecorderID INNER JOIN
dbo.ShipRelease ON MasterPCS.dbo.SpecOrder.SpecOrderID = dbo.ShipRelease.SpecOrderID ON
CCC.dbo.tblArCust.CustId = MasterPCS.dbo.SpecOrder.CustID
WHERE (MasterPCS.dbo.SpecOrderdetail.ChargeItemSent = 0)
GROUP BY MasterPCS.dbo.SpecOrderdetail.Discription, MasterPCS.dbo.SpecOrderdetail.Order_qty, MasterPCS.dbo.SpecOrderdetail.UnitsBase,
dbo.ShipRelease.ReleaseID, dbo.ShipRelease.ShipmentDate, CCC.dbo.tblArCust.CustName, MasterPCS.dbo.SpecOrderdetail.SpecorderID,
ISNULL(MasterPCS.dbo.SpecOrderdetail.UnitPrice, 0), MasterPCS.dbo.SpecOrderdetail.UnitPrice, MasterPCS.dbo.SpecOrderdetail.Taxable
HAVING (MasterPCS.dbo.SpecOrderdetail.Order_qty = 1) AND (dbo.ShipRelease.ReleaseID = @ReleaseID)

Does anyone see anything that would not be in line.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-07 : 13:59:54

Did you build the SELECT statement from Enterprise Manager? The ON conditions are not in the proper places, not sure if that is what is causing your error though.

Should be like this:

SELECT...
FROM Table1 t1
INNER JOIN Table2 t2
ON...
INNER JOIN Table3 t3
ON
...

You've got:

INNER JOIN Table2
INNER JOIN Table3
ON...
ON...

I've only seen EM do this.

And what's with the ISNULL in the GROUP BY?

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-05-07 : 14:21:13

The Is null is a leftover from deveopment where I used a Sum to validate the trasactions to our current manual entry.

I just stumbled accross the problem, The insert and select do not contain the same number of entries.

Funny I have seen QA pick up on that with other SP's but not this one, maybee the relationships are to complex for it to find.

It Works now. (another 3 hours of my Life I wont get back because I did something stupid)

Thanks Tara

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources