"Internal SQL Server error" -- where to start

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 Administration (2000)

"Internal SQL Server error" -- where to start

Author

Topic

uberman
Posting Yak Master

159 Posts

Posted - 2006-01-30 : 11:32:12

HELP!

I am getting a reproduceable, but as yet not isolatable error, but only on my production machine... and I need to know what to do next!

All machines are running SQL Server 2000/SP4

When a stored proc. is run it generates an "Internal SQL Server error" message.

I have isolated the line of code that is causing the error


DELETE
	dbo.ANSWERS
FROM
	dbo.ANSWERS A
INNER JOIN
	dbo.FORM_AREAS FA
ON
	A.uidformarea = FA.uidformarea
AND
	FA.uidarea = 8105
AND
	FA.hide = 0
INNER JOIN
	dbo.PURCHASEORDERLINES POL
ON
	POL.uidpurchaseorderline = A.uid
AND
	ISNULL(POL.quantity,0) = 0
AND
	POL.uidpurchaseorder = @poid

commenting out the line in question causes the problem to go away!

If I restore the "problem" database to any of my other systems (my .upgrade, .test and .dev systems) then the code works fine with no need for alteration and certainly no sql server errors!

I have NOT been able to create a DDL and data set that reproduces the issue; also, it is only on the production system that the error occurs, but I can cause the error every time (so repeatable but not isolatable :-(

The code style ISNULL(POL.quantity,0) = 0 is used in lots of places ... for the time being I have created a @temp table, turned the delete into a select into the temp table, and then deleted by joining to the @temp table ... doing this across the whole app is going to be hell and there must be something else wrong!

I have run DBCC CHECKDB and there are no reported problems

So, what do I do next...?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-30 : 11:43:49

If you change that line to:


AND (POL.quantity = 0 OR POL.quantity IS NULL)

does it work?

uberman
Posting Yak Master

159 Posts

Posted - 2006-01-30 : 11:47:50

...! knew I should have put that bit in !...

In short : NO!

That was the first thing I tried and it still caused the error, hence the messing around with @temp tables...

Also, as this is a common code style used in lots of places I would like to try and find out what is happening and fix the underlying issue, esp. as the same database, restored to dev. does not exhibit the problem!

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-01-30 : 11:57:13

What happens if you move the offending statement from the join to the where clause?

uberman
Posting Yak Master

159 Posts

Posted - 2006-01-30 : 12:01:23

Moving to where clause also has no effect, the error still occurs!

uberman
Posting Yak Master

159 Posts

Posted - 2006-01-31 : 03:49:30

Just to be clear about what I would like to achieve...

I already have a work around for the section of the code that is resulting in the server error.

I would like to look further into what, exactly, is causing this error to happen, and why it is occuring on my production box and not on any of my other machines, even when an identical backup is restored and tested...because this has occured more than once and in different places, but using the same ISNULL structure.

Any / All suggestion re: how to proceed eagerly awaited!

mr_mist
Grunnio

1870 Posts

Posted - 2006-01-31 : 05:26:01

Have you applied the latest hotfix?

Are you running with multiple processors? If so, what happens if you set maximum processors to 1 for the query?

-------
Moo. :)

uberman
Posting Yak Master

159 Posts

Posted - 2006-01-31 : 06:27:39

Latest hotfix? = No
All systems tested on are running SP4 only, only my production box is exhibiting the issue

Multiple Processors = Yes (2)

Adding option (maxdop 1) to the delete still results in internal server error

mr_mist
Grunnio

1870 Posts

Posted - 2006-01-31 : 07:19:20

Raise a support call...

-------
Moo. :)

Kristen
Test

22859 Posts

Posted - 2006-01-31 : 10:27:53

What datatype is POL.quantity?

If you explicitly cast the rest of that line does that fix it?

e.g.

ISNULL(POL.quantity, CONVERT(int, 0)) = CONVERT(int, 0)

or

ISNULL([red]CONVERT(int, POL.quantity), 0) = 0

My thinking is based on the fact that some queries have slowed down with SP4 when the datatypes are mismatched.

Second thought: Have you done a DBCC CHECKDB and the like to be sure that the DB is sound? Maybe restore a backup to a "fresh" database, on the same server, and try the query - assuming that RESTORE will only pull back "used" pages. A corruption in TEMPDB would probably muck things up too - but presumably a stop/start of SQL Service would fix that as TEMPDB will be recreated.

Kristen

tamartin
Starting Member

4 Posts

Posted - 2008-12-08 : 12:12:34

How was this resolved? We are seeing similar issues with a stored proc that has been running fine on SQL2000 SP4 for years.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources