I have been trying to test to use Microsoft SQL Server 2008 R1 as a back
end for an web2py application. I have encountered the usual cascading
deletes issue and have solved it by setting the On Delete action to no
action.
However I still do not get the test app running as there seems to be an
issue with the conversion of datetime fields such as the standard
created_on fields to the MSSQL format. I believe it is because
datetime.now() has a higher microsecond precision (6 digits) than MSSQL (3
digits). The tables in MSSQL were all created by web2py, so there cannot be
a migration issue. I cannot even create a new user as the registration
process is using a datetime field in one of the auth tables as well.
In searching the internet I found a similar issue way back in 2009, but
nothing since then. I know the answer was that it is a MSSQL configuration
issue, but I can not change the configuration. Has anybody any idea how I
could maybe truncate the timestamp before the insert statement?
Error Message (in German)
<class 'pyodbc.DataError'> ('22007', '[22007] [Microsoft][ODBC SQL Server
Driver][SQL Server]Bei der Konvertierung eines varchar-Datentyps in einen
datetime-Datentyp liegt der Wert au\xdferhalb des g\xfcltigen Bereichs.
(242) (SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Die Anweisung wurde beendet. (3621)')
Database schema besides the usual authentication tables from :
########################################
db.define_table('t_currency',
Field('f_name', type='string',
label=T('Name')),
auth.signature,
format='%(f_name)s',
migrate=settings.migrate)
db.define_table('t_currency_archive',db.t_currency,Field('current_record','reference
t_currency',readable=False,writable=False))
########################################
db.define_table('t_test',
Field('f_name', type='string',
label=T('Name')),
Field('f_value', type='double',
label=T('Value')),
Field('f_curr', type='reference t_currency',
label=T('Curr')),
auth.signature,
format='%(f_name)s',
migrate=settings.migrate)
db.define_table('t_test_archive',db.t_test,Field('current_record','reference
t_test',readable=False,writable=False))
--
