Migrating from IIS5 W2000 -> IIS6 W2003 Problems

Hi Everyone,

We have just got a new web server and I need to migrate all our existing
sites to it. Our old server was a windows 2000 server running IIS5 and the
new server is a 2003 server running IIS6. Almost everything worked fine after
the migration.

We do however have an issue with a few asp pages and converting date time to
use with SQL queries.

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "VALID_CONNECTION_STRING"

page = "test.asp"
ip = Request.ServerVariables("REMOTE_HOST")

query = "INSERT INTO tblTracking (track_page,track_ip, track_datetime)
VALUES ('"& page &"', '"& ip &"', '"& Now & "');"
conn.Execute query, rowsAffected, adCmdText Or adExecuteNoRecords
%>

When the code above gets run I get the following error.

Microsoft OLE DB Provider for SQL Server error '80040e07'
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

Looking around on the net the obvious solution is regional settings, but the
regional settings on this computer are correct and are the same as they were
on the old web server.  To test that the region settings are correct I
created a new page that displayed the value of NOW, this was formatted as
required.

The other logical fix is to convert this query from SQL to a stored
procedure, when I do this I get a very similar error when I add NOW to the
stored procedures parameters collection.

One possible workaround would be to build an asp function that converts the
current date to a format that I know SQL supports, but we have a lot of pages
so this is not really practical.

Any ideas as to why the server migration has broken this basic asp
functionality?

Any advice anyone could offer would be much appreciated.

Regards,

Tom Sedlacek
Network Software Analyst
Dmg Radio Australia

Migrating from IIS5 W2000 -> IIS6 W2003 Problems

Tom wrote  on Mon, 19 Mar 2007 14:29:11 -0700:


All that does is confirm the regional setting for the account IIS is running
under. You also need to check that SQL Server is running under an account
with the same regional setting, and if you have any users who will be
connecting via Integrated Authentication you will also need to ensure that
all of their user accounts have the same regional setting.


Changing to a stored procedure will change nothing of the date being passed
in does not appear to be a valid one due to regional setting differences in
IIS and SQL Server.


Go back and check the regional settings for every account involved. Also
ensure that the default system region is selected to match too.

Dan