Torque
  1. Torque
  2. TORQUE-86

MSSQL data inserts with with IDENTITY key require IDENTITY_INSERT property to be on

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 3.2
    • Fix Version/s: 3.3-RC3
    • Component/s: Generator
    • Labels:
      None

      Description

      For Microsoft SQL Server, if a primary key is of type IDENTITY (autoincrement), then you will not be able to insert a record containing a primary key.

      For example, the following line will give an error if field WEB_APP_ID is a primary key.

      INSERT INTO WEB_APP (WEB_APP_ID,WEB_APP_NAME,ROOT_PATH,BASE_URL,IS_PRO,ALLOW_NATIVE_VENSIM)
      VALUES (1,'Broadcast','c:/www/sims','http://localhost/sims/',1,1);

      The global property IDENTITY_INSERT can prevent this error. However, if your dataset has a mix of tables, some with autoincrement and some without then you will not be able to set this globally.

      The solution is to check to see if the primary key is has the auto_increment property, and to wrap the insert with an IDENTITY_INSERT setting if so. Example:

      SET IDENTITY_INSERT WEB_APP ON;
      INSERT INTO WEB_APP (WEB_APP_ID,WEB_APP_NAME,ROOT_PATH,BASE_URL,IS_PRO,ALLOW_NATIVE_VENSIM)
      VALUES (1,'Broadcast','c:/www/sims','http://localhost/sims/',1,1);
      SET IDENTITY_INSERT WEB_APP OFF;

      I've attached a patch to the templates which implements this.

      1. mssql_row.patch
        0.9 kB
        Will Glass-Husain

        Activity

          People

          • Assignee:
            CG Monroe
            Reporter:
            Will Glass-Husain
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development