I do not see a problem with this but is GO a mssql only keyword?
On Apr 3, 3:29 pm, DenesL <denes1...@yahoo.ca> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
