Changing a MSSQL table definition with migrate=True in the models
produces this error ('escalated' is the original field name in my
table):
ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC SQL Server
Driver][SQL Server]Invalid column name 'escalated__tmp'. (207)
(SQLExecDirectW)")
I traced down the error to sql.py, class SQLTable, function _migrate
where the following batch of statements is built:
ALTER TABLE calls ADD escalated__tmp DATETIME;
UPDATE calls SET escalated__tmp=escalated;
ALTER TABLE calls DROP COLUMN escalated;
ALTER TABLE calls ADD escalated DATETIME;
UPDATE calls SET escalated=escalated__tmp;
ALTER TABLE calls DROP COLUMN escalated__tmp;
The problem is that batches in MSSQL (2005) have these rules and the
batch above breaks rule number 2:
1) CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE,
CREATE TRIGGER, and CREATE VIEW statements cannot be combined with
other statements in a batch. The CREATE statement must start the
batch. All other statements that follow in that batch will be
interpreted as part of the definition of the first CREATE statement.
2) A table cannot be changed and then the new columns referenced in
the same batch.
3) If an EXECUTE statement is the first statement in a batch, the
EXECUTE keyword is not required. The EXECUTE keyword is required if
the EXECUTE statement is not the first statement in the batch.
The correct batch should be (note the GO on the second line, it has to
sit on a line by itself):
ALTER TABLE calls ADD escalated__tmp DATETIME;
GO
UPDATE calls SET escalated__tmp=escalated;
ALTER TABLE calls DROP COLUMN escalated;
ALTER TABLE calls ADD escalated DATETIME;
UPDATE calls SET escalated=escalated__tmp;
ALTER TABLE calls DROP COLUMN escalated__tmp;
The fix is apparently easy but I am not sure about the implications of
the other rules so I will leave it to Massimo.
Denes.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
