Problem Description
I have a problem with iBO.COM inserts in SQL 2005 on an Australian server.
We have a live and a test site, with respective databases.
The live site is showing this in the SQL trace when iBODataServer user connects. Inserts work.
- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language British
set dateformat dmy
set datefirst 1
set transaction isolation level read committed
The test site has this in the setup:
set language US
set dateformat mdy
The test database is a restore of live. The test site is a copy of live.
Why is iBO using a different date format? The insert into Invoice is failing in test, because the date in the insert is 14/04/2008. If I test the insert, and change it to 04/14/2008 it works.
It was suggested to change the language of the test db to British, but I am not familiar with how to do this, and I don't know why a restored database would have a different language setting.
Any suggestions? How to change the db language?
Database Language and Date Format in iBO.COM -Solution
Thanks to Adam Oakman of ASI Consulting I found the solution.
iBO.COM (don't know about iBO.NET) gets the locale from Windows on the server on which it resides. In this case iBO correctly formatted the dates as dd/mm/yyyy.
The iBODataServer login was set to ENGLISH, which in SQL is actually US-english.
I changed the iBODataServer login to British and SQL accepted the Australian date format and the transaction completed.
So when implementing iBO.COM internationally, change the language of the iBODataserver user. However, it is recommended that no new iBO.COM implementations occur.