Post reply

Funtion returning different results depending on where it's called from

  • March 18, 2011 at 4:26 am

    #236516

    Hello,

    I've got a head scratcher here and it's causing me stress.

    I have two SQL 2005 dbs on one server (Windows 2003 R2). Each of the databases are/(should be) identical since one is a production db and the other is a 'test' db (Test is to allow users to try transactions before running them in the live system). In both of the databases I have a view and a function. The view calls the function.

    If I am in the test DB and I call the view I get a set of results returned, of which one record is of interest to me as it has a date. The date is in american format (mm/dd/yy). If I use the values from this view and call the function directly I also get the date in mm/dd/yy format. So far so good.

    However, if I call the view on the live db I get the date in dd/mm/yy format, yet the function still (correctly) returns mm/dd/yy.

    What? How?

    Any ideas?

    Mike

  • March 18, 2011 at 4:30 am

    #1300396

    More info...

    If I copy the SELECT statement from the live query into a new SSMS window (on the same PC) and run it I get the date returned as mm/dd/yy.

    So this means that if I run the view statment from a window created by a right click script object method I get dd/mm/yy and if I copy that statment to a new window in the same SSMS and run it I get mm/dd/yy.

    Is there anything that sets regional settings per SSMS window?

    Is there anything else that can affect how regional settings are applied to databases on a server?

    Mike

  • March 18, 2011 at 5:22 am

    #1300412

    Have a look at the properties of the login you are using to connect to the two servers... I suspect the default language for the login on one server will be "English" and the other will be "British English".

    You need to make sure the test server is the same as the production server.

  • March 18, 2011 at 6:06 am

    #1300431

    Ian,

    Same server i'm afraid! It's two databases on the same server.

    I think I've (maybe) identified what is going on. The live server is using a different context for the language when it returns results. This is configured in the applciation that uses the database (Infor SyteLine) and is database specific. I'm going to investigate this further.

    Mike

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply