"Out of Present Range" Error in ASP Database Application, after migrating from Windows 2000 -> Windows 2003

Hi All,

We have an ASP application which uses ADODB to connection to a data source (SQL Server 2000).  The developers machines are running Windows XP Professional with MDAC version 2.81.  We have recently upgraded our main development webserver to Windows 2003, which is running MDAC version 2.82.

Ever since we have done this, we receive the following error on one of the SQL statements we execute:

2147352566:Out of present range.

For the following SQL:

UPDATE FUEL_ISSUES_FW SET DRIVER_RECORD_NUMBER_FW = (SELECT TOP 1 RECORD_NUMBER_FW FROM DRIVERS_FW WHERE DRIVERS_FW.DRIVER_NAME_FW=FUEL_ISSUES_FW.DRIVER_NAME_FW) WHERE DRIVER_RECORD_NUMBER_FW is null

Searching the web, I saw numerous references to the above error message, but in relation to people putting numbers that were too big in to datatypes that were too small.  With regard to the database, the fuel issues table, driver record number field is an INT, and the Driver table, record number is an INT IDENTITY.  The script above is a "fixup" script.

As I said before, running from IIS on XP Pro, the above works fine (and that IS running against the same database).  Running the same code on a different web server, generates the error.  Our previous web server was Windows 2000 Server, and we didn't have the problem on that.


Thanks in advance!


LVL 1
Moo-JuiceAsked:
Who is Participating?
 
solution46Commented:
Ben,

if the statment runs fine in QA, have you checked that the correct statement is being passed from MDAC? As I said, use Profiler (same location as QA and EM in your Start folder); this will log (almost) all calls to SQL Server and will immediately highlight if there is an issue in generating the SQL statement.

If the query being passed is correct, you need to make sure that your ASP app is handling the returned value(s) correctly; if not, you need to follow the process of generating the statement and see where it gets broken.

FWIW, my money is now on an 'undocumented design change'! :)

s46.
0
 
solution46Commented:
Moo-Juice,

can you confirm this is a SQL Server error message and not something IIS is chucking up?

If it is SQL Server, I would suggest running Profiler against the database on the new system to check what is being sent to SQL Server when the relevant command is run. If that doesn't shed any light on the issue, try running Profiler against the old setup and see if there are any differences.

Assuming that the passed SQL looks OK, copy it out of Profiler and run it directly in QA. If SQL Server has a problem with it, you will get more information about the error from here.

My best guess is that this is down to either a coincidental change in the system (it is a dvelopment server...) or a difference in the way the command is being handled my MDAC. In either case, this should highlight the problem.

Regards,

s46.
0
 
Moo-JuiceAuthor Commented:
Hi solution46,

   The statement works fine in QA, sorry I didn't mention it previously.  And as I mentioned before, all webservers (development/release candidate) are using the same SQL Server.  Interestingly enough it doesn't happen in all applications (we have several instances of the same app running on the webserver).  I'm in the process of tracking down what is "different" between those apps.

   I am leaning toward your opinion about an IIS/MDAC difference, although I'm not sure what could be so different in such a simplistic SQL statement, but then, that's what it's all about :)



Ben
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
solution46Commented:
Another idea...

Do a search in your ASP code for the term 'Out of Present Range'. I've just been trawling through the MS knowledge base and can't find it so it's possible it's an application error message...

s46.
0
 
Moo-JuiceAuthor Commented:
Hi Solution46,

   I have managed to resolve it, simply by restructuring the SQL.  Instead of the original statement, I modified it to:

UPDATE FUEL_ISSUES_FW SET DRIVER_RECORD_NUMBER_FW = other.RECORD_NUMBER_FW
FROM DRIVERS_FW other
WHERE FUEL_ISSUES_FW.DRIVER_RECORD_NUMBER_FW IS NULL

This now works without problem.  Funny old world.

Thanks for your comments, and I accepted your answer for your contributions.  Have a good day!
0
 
solution46Commented:
OK, that is wierd.

Anyway, cheers for the points and glad you got it sorted.

Regards,

s46.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.