Detecting SQL Errors

[jplahitko]

I recently started working for a small company and inherited a problem with SQL2000 server and a related application. We have no source code for the application and the OEM is out of business. It is a legacy application for this company and they do not want to change.

Basically, the legacy application reads a data file from one folder on a windows 2000 server, processes info and then writes another file to another folder. After reading the file, during the processing, it writes records to a table in the SQL server database.

This system ran for about two years until September 2006 when it started throwing an error. The error dialog only indicates 'overflow'. There have been no programming or system changes. The file format is the same as it has always been. It seems to be an application error and not an SQL error. However, the OEM may only be showing its interpretation of the error. I suspect it might be trouble with the database.

If I could see more detail about the error I might have a chance at fixing it. If it is an SQL error, is it possible to detect it at the database before or during the time it is reported to the connected application?

 

[mrdenny]

Fire up SQL Profiler and set it to show you all errors then rerun the process. If it is a database error it will show up in the SQL Profiler. It it's an application error it will not.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jplahitko]

Thank you for the exceedingly fast response. I have never used the Profiler. I did as you said and now I think I know what the problem is. SQL did not error, but I was able to see the last query before the error occurs in the application. SELECT * FROM Parts

The Parts table has 32770 records in it. Interesting that it is two more than an integer can handle. It seems the application developers did not plan for this many records.

Thank you for your very timely response and help.

 

[SQLDenis]

>>32770 records in it. Interesting that it is two more than an integer can handle

you mean smallint right? because an int can handle 2 billion + change

declare @int int
select @int = 2000000000

select @int


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[jplahitko]

Thr problem is not the number of records SQL Server can handle or the size of the integer in SQL Server.

I believe the problem to be in the application programming. I suspect the input buffer is only capable of handling 32768 records or some other variable, possibly a count variable, is allocated as an integer.

I am not an expert on this, but one possible scenerio is the developer allocated a variable of type 16 bit signed integer that has a range of -32,768 to 32767. This would allow for referencing 0-32,767 (or 32768 records) in a zero based array for instance. The error usually associated with trying to store a number higher than 32767 is 'overflow'.

Thanks again.

 

[mrdenny]

That sounds like a very possible explination.

Try taking a backup of the database, then updating the key for that table (you will also need to update anything that references that table as well) and subtract say 30,000 from the number. That way your highest number is now ~2,700 and you should have room to grow. If that doesn't fix it or screws something else up you can restore the database back to it's origional state.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com