Sunday, March 11, 2012

Datetime function glitch -- same settings, different results

hi guys,

i am encountering a pretty weird problem on our SQL database stored procs, particularly those concerning datetime functions. we have two setups, one is on london, and another one is here in singapore. both servers have the same regional settings, the same database tables, stored procs and functions, almost the same in every aspect. the only difference we know is that one is a direct database cut (sql 7.0), while the other used database migration in win2k.

here goes: all stored proc functions work just fine on the singapore setup. the london setup however, is not working at all! it does all the functions like change flags and status, but it doesn't update dates nor search for data by date. i have also checked on the collision names, and they are the same. where else could i start searching for discrepancies between the two?

hope to hear something from you guys, i'm getting desperate. thanks in advance!

What do you mean by "but it doesn't update dates nor search for data by date". The date representation is a session setting which can be different depending on the user which is using the SQL Server. YOu have to be aware that an application which was not prepared to handle that might encounter problem during data retrieval or manipulation.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Here's an example: I have a datetime column that contains the date when info on a particular row gets altered. Ideally, everytime the user changes something from the front end, this corresponding date info gets updated as well. The weird thing here is that my setup on the Singapore database works just fine, the date gets updated. However, the London setup doesn't do this. I have checked the language and other regional settings, and they are the same across these two databases. I also checked the user profiles, and they have the same settings as well.

My stored procs use the datetime variable, and so does the application that calls the stored procs.

What have I overlooked?

|||

Can you provide some example of the method used that's supposed to execute this date-update operation?

Exactly what is the 'error'? Is it that nothing happens, or is it that the expected rows aren't found?
There are many subtle differences between 'not working' and 'not working' =;o)

/Kenneth

|||I would suggest you use Profiler to trace the code that gets generated and then you can isolate whether it is the code generated or "user error"...

Check out my SQL Server 2005 Video Tutorials: http://www.learnsqlserver.com/

No comments:

Post a Comment