locked
Errors: M2SS0165: RRS feed

  • Question

  • Hello:

    I'm new to Sql Server and T-SQL, but have experience with MySql.  I am attempting to use SSMA to migrate a mysql database to sql server.  The tables in the MySql database that contain triggers appear to be generating errors in the conversion process. The error is:  M2SS0165: The column referenced in VALUES claues must be previously assigned in the same INSERT STATEMENT.

    An example of a MySql Trigger sql is:
    CREATE DEFINER= `lenny`@`localhost` TRIGGER addresses_change_delete AFTER DELETE ON addresses FOR EACH ROW INSERT INTO `addresses_cr` (ADDRESS_ID, STREET, CITY, STATE, ZIP, CHANGED_USERID, changed_time, changed_action) VALUES (ADDRESS_ID, STREET, CITY, STATE, ZIP, CHANGED_USERID, UTC_TIMESTAMP(), 'DELETE')

    An example of the T-Sql response that appears to be generating the error is:
    /*
    *   SSMA informational messages:
    *   M2SS0003: The following SQL clause was ignored during conversion:
    *   DEFINER = `lenny`@`localhost`.
    */
    CREATE TRIGGER dbo.addresses_change_delete_AfterDelete
       ON dbo.addresses
        AFTER DELETE
          AS
             BEGIN
                SET  NOCOUNT  ON
                SET  XACT_ABORT  ON
                DECLARE
                    ForEachDeletedRowTriggerCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
                      SELECT NULL
                      FROM deleted
                OPEN ForEachDeletedRowTriggerCursor
                FETCH ForEachDeletedRowTriggerCursor
                WHILE @@fetch_status = 0        
                   BEGIN
                      /* trigger implementation: begin*/
                      BEGIN
                         /*
                         *   SSMA error messages:
                         *   M2SS0165: The column referenced in VALUES clause must be previously assigned in the same INSERT statement.
                         INSERT dbo.addresses_cr(
                            dbo.addresses_cr.ADDRESS_ID,
                            dbo.addresses_cr.STREET,
                            dbo.addresses_cr.CITY,
                            dbo.addresses_cr.STATE,
                            dbo.addresses_cr.ZIP,
                            dbo.addresses_cr.CHANGED_USERID,
                            dbo.addresses_cr.changed_time,
                            dbo.addresses_cr.changed_action)
                            VALUES (
                               dbo.addresses_cr.ADDRESS_ID,
                               dbo.addresses_cr.STREET,
                               dbo.addresses_cr.CITY,
                               dbo.addresses_cr.STATE,
                               dbo.addresses_cr.ZIP,
                               dbo.addresses_cr.CHANGED_USERID,
                               getutcdate(),
                               N'DELETE')
                         */
                         DECLARE
                            @db_null_statement int
                      END
                      /* trigger implementation: end*/
                      FETCH ForEachDeletedRowTriggerCursor
                   END
                CLOSE ForEachDeletedRowTriggerCursor
                DEALLOCATE ForEachDeletedRowTriggerCursor
             END
    GO

    I suspect that the problem is the line:
     dbo.addresses_cr.changed_time,
    and the line:
     getutcdate(),
     
    but have no idea how to correct it.  Can someone offer suggestions if that is the problem? of help identify what is causing this error?  Although there is a call to getutcdate(), . . . I haven't created that function, . . . is it built into Sql Server?

    Any suggestions/help would be greatly appreciated.

    Thank you.


    Robotuner
    Friday, November 11, 2011 5:36 PM

Answers

  • that the problem is the line:
     dbo.addresses_cr.changed_time,
    and the line:
     getutcdate(),
     
    but have no idea how to correct it.  Can someone offer suggestions if that is the problem? of help identify what is causing this error?  Although there is a call to getutcdate(), . . . I haven't created that function, . . . is it built into Sql Server?

    Any suggestions/help would be greatly appreciated.

    Thank you.


    Robotuner


    Hi Robotuner,

    Function getutcdate() is a SQL Server system function which is aim to get  the OS System UTC time. So that is not defined by yourself. Could you please check the data types of table dbo.addresses_cr to see whether they match or not?


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Edited by Peja Tao Monday, November 14, 2011 8:48 AM
    • Marked as answer by Peja Tao Thursday, November 17, 2011 5:16 AM
    Monday, November 14, 2011 8:47 AM