ACT7 settings

[jimdurkin]

I am trying to create a non-us install of ACT! without re-installing ACT!. Does anybody know how you can change the ACT7 SQL occurrence to another region. I want to change the US to a non-US for some testing without having to load a VMWare or re-install ACT!.

The ultimate would be able to switch back and forth between US and non-US with these three steps:

1) Change my OS regional settings

2) Change the ACT7 SQL regional settings

3) Open an existing non-US database.

Once the SQL server is in another region and my OS regional setting are changed shouldn't I be able to create a non-US database?

Life would be a joy if that is possible.

Thanks

-- Jim Durkin

[mwood]

I'm pretty sure you can open an Act database from any region regardless of what locale you have installed. The regional settings are controled by the DBCONFIG table, which houses the language and currency, and this shouldn't be affected by opening the DB in your installation.

I haven't done this myself, but DB services frequently gets non-US databases and doesn't use a VM or reinstall act to troubleshoot them.

[GLComputing]

What parts of the database or app are you trying to change?

The currency is the only regional specific setting in the database ASAIK ... other regional settings are part of the application itself.

There were a few versions from Sage that didn't permit sharing a database with different regional versions of ACT!, but this was changed a couple of versions ago

[jimdurkin]

I am trying to change the culture of the ACTDatabase/ACT7 SQL server instance. I think the ACT7 SQL server is returning the culture based on the original installation not the current database opened.

The 'where' clause creating the error is set to en-AU format because of the current thread /ACT UI:
"WHERE ([TIMEBILLED].[CREATE_DATE] BETWEEN '29/05/2011 12:00:00 AM' AND '4/06/2011 11:59:59 PM' )"

The error is:

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

The ACT UI is en-AU using the current thread:
System.Threading.Thread.CurrentThread.CurrentCulture.ToString()  ' returns "en-AU"

The ACTDatabase it is still set to en-US:
ACTFramework.ActDatabase.Culture.ToString() ' return "en-US"

Since I build many SQL strings to query the DB using the ACTOLEDB.2 I am trying to format the date strings for the ACT7 SQL server not the ACT UI.

I was thinking I need to set  the culture of the ACT7SQL server to en-AU without re-installing ACT!.

Does this make sense?

-- Jim Durkin

[mwood]

The ACT7 instance returning the culture from the install is expected, as it isn't required for the culture of the instance to match the database. The cutlure of the current database can be found in the DBCONFIG table (along with language and currency), but the method through the SDK is going to return that of the instance.

I'm going to restate the goal to make sure I understand what is desired here: We'd like to be able to assertain the culture of the current database so we can format our queries to use the appropriate date/time format? If this is the case, would it be possible to simply convert all date/times to UTC prior to querying?

[jimdurkin]

I could try using UTC but that would require a lot of coding and testing against a released product that works except for when a user sends me a database in another culture. Maybe the problem is deeper then that. Maybe other users with the same type of set-up SQL "en-US" and database "en-AU" are having the same issues with my add-ons?

My original point was to change the SQL7ACT instance to the same culture as the database. From your descriptions that is not required.

The question is why does ACT throw this error when the date is set to "en-AU" on an "en-AU" database?

"WHERE ([TIMEBILLED].[CREATE_DATE] BETWEEN '29/05/2011 12:00:00 AM' AND '4/06/2011 11:59:59 PM' )"
"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

I though the OLEDB.2 driver was regionalized. I remember the OLEDB.1 driver was all about UTC and that was a pain.

Since we re-engineered to work with only the .2 driver I do not use UTC in any of our SQL strings anymore.

-- Jim

[mwood]

Sorry for beating around the bush, the reason my answers probably seem evasive is because I don't believe the culture of the instance can be changed at any time except during install. The OLEDB 2.0 driver is regionalized, but it appears to be using the culture from the SQL install and not the culture of the current DB. 

I'll look into this some more and reply here when I have some more information.

[wellmet]

Jim,

I think it has a problem with the '0' that 12:00 AM produces in the range.  Go back another thousandth of a second and it will probably work.

Stan

[jimdurkin]

Stan,

I am not sure I understand. To get the date range from the ACT UI I use ACT's internal CurrentWeek function

StartDate =Date.Today

EndDate =Date.Today

Me.ACTApplication.UIActivityManager.CurrentWeek(StartDate, EndDate)

I also use .NET functions since I present the users with more 'date range' options then native ACT!

StartDate = Date.Today

EndDate = Date.Today.AddDays(8).Subtract(New TimeSpan(1))

How do you 'go back another thousands of a second' on the StartDate or EndDate?

Thanks in advance.

-- Jim Durkin

[wellmet]

JIm,

I thought you were submitting the SQL statement yourself.  If you're using the SDK you could try using a DateTime variable and subtracting ".001" from it.  I haven't tried it but I had the same issue pop up when I was using a SQL statement in SQL Management Studio yesterday when I was trying to look up histories in a date range so I just changed the start date back to the day before and made the time 11:59:59 and that SQ: statement worked so I assumed you were running into the same issue.

Stan