activerecord datetime field - MSSQL via ODBC

[incognito]

Hi All,

Running into a few problems getting the ActiveRecord side of things
inserting records into SQL Server. Was wondering if anyone has had
success.

Can run the rake migrate in order to build the tables, but when ECHI-
Conv processes a CHR file and tries to insert the records into the DB,
I get the following error:

<i>Mon Jul 13 13:54:16 +1000 2009 (2124) EchiRecord Create (0.0ms)
DBI::DatabaseError: 22008 (242) [Microsoft][SQL Server Native Client
10.0][SQL Server]The conversion of a varchar data type to a datetime
data type resulted in an out-of-range value.: INSERT INTO
[echi_records] ([segment], [vdn9], [events0], [asaiuui], [cwcdigits],
[cwcs0], [queuetime], [events1], [split1], [malicious],
[origholdtime], [cwcs1], [firstvdn], [holdabn], [events2], [split2],
[dispsklevel], [cwcs2], [lastobserver], [audiodifficulty], [events3],
[split3], [observingcall], [cwcs3], [uuilen], [events4], [cwcs4],
[segstart], [eqloc], [events5], [conference], [talktime], [duration],
[disptime], [onholdtime], [acdnum], [agentreleased], [events6],
[dispivector], [collectdigits], [firstivector], [origreason],
[calldisp], [callingII], [ansreason], [dispsplit], [events7],
[obslocid], [daqueued], [events8], [consulttime], [ucid],
[trunkgroup], [dialednumber], [dispvdn], [vdn2], [vdn3], [segstop],
[assist], [origlocid], [callid], [vdn4], [transferred], [tklocid],
[vdn5], [ringtime], [callingparty], [acwtime], [answerlocid], [vdn6],
[holds], [disppriority], [vdn7], [origlogid], [anslogid], [netintime],
[vdn8]) VALUES(1, '', 0, '', '', '', 0, 0, 414, 'N', 0, '', '42000',
'N', 0, -1, 1, '', '', 'N', 0, -1, 'N', '', 0, 0, '', '2007-07-20
16:55:10.000', '004A12012', 0, 'N', 183, 261, 78, 0, 1, 'Y', 0, 421,
'2', 400, 0, 2, '', 0, 414, 0, 0, 'N', 0, 0, '00001143721184914510',
41, '42000', '41021', '41007', '41021', '2007-07-20 17:00:02.000',
'N', 0, 9557402, '', 'N', 3, '', 11, '0839411671', 31, 3, '', 0, 4,
'', '', '43031', 0, '')

Mon Jul 13 13:54:16 +1000 2009 (2124) EXECUTE (0.0ms) ROLLBACK
TRANSACTION</i>

I have a feeling this is ODBC related (either using windows default
SQL server ODBC client or the native SQL client).

Troubleshooting performed:
* When I run the query from SQL server itself, it executes perfectly.
* When I run the query from another application using the same ODBC
DSN, it throws the same error.
* When I run the query through the above application, but remove the
hyphens from the datetime fields, it executes perfectly.

My initial thought would be to modify activerecord some how so that it
leaves the hyphens out of those fields.

Does anyone know which rb files this would be done in, or does anyone
have a better suggestion?

Cheers,
- Ryan

[incognito]

I should also mention I have no problems getting this working on a
MySQL setup, but would prefer to have it inserting directly into the
MSSQL database.

Cheers.

[incognito]

All sorted.

Instead of using a DB user, I changed ODBC to use NT permissions and
it seemed to fix it.

Wondering if the NT user side of things has anything to do with
regional (EN-UK) settings, but alas it's fixed and I don't really care
any more :)

- Ryan

[JasonGoecke]

Glad it got sorted. I am traveling in Japan right now, so did not have
many cycles to think about it.