Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    11

    Unanswered: Identity_insert not happening

    I am setting insert_identity to on for a table in t-sql.
    The table name is passed as a parameter in the tsql procedure.

    When i write the following code.
    set @setStr = 'set IDENTITY_INSERT ' + @toTableName +' ON'
    execute (@setStr)

    and then set the insert query and execute it as follows:

    set @insQuery = 'insert into ' + @toTableName + ' ( ' + @colString + ') select ' + @colString + ' from ' + @fromTableName
    execute(@insQuery)

    when i execute the procedure it doesnt insert values into the table and gives the following error though i am setting the identity to on.

    Error: cannot insert explicit value for identity column in table 'emp' when IDENTITY_INSERT is set to OFF.

    i cant make out why it is not applying identity_insert to the table.
    Can anybody help me out.

    Thank You

  2. #2
    Join Date
    Jan 2003
    Location
    Leamington Spa, UK
    Posts
    17
    Don't use EXECUTE as it will run in a different thread/process to the rest of your code - so the code which follows the call, doesn't know anything about the fact you have set IDENTITY_INSERT to ON.

    Try using sp_executesql instead. (Books Online has more information on how to use this system stored proc)

    macka.

  3. #3
    Join Date
    Apr 2003
    Posts
    11
    it doesnt work...
    beacuse i've to use exec to execute the sp_executesql proc.
    so it gives the same result..

    so i can try to make one string by putting a newline character between the following 2 strings... and then just run one string... i guess it might be possible..

    'set IDENTITY_INSERT ' + @toTableName +' ON'

    and

    'insert into ' + @toTableName + ' ( ' + @colString + ') select ' + @colString + ' from ' + @fromTableName

    but the problem is that i dont know how to append a newline character in the string \r \n \\r \\n dont work... can somebody suggest something on this...


    Originally posted by macka
    Don't use EXECUTE as it will run in a different thread/process to the rest of your code - so the code which follows the call, doesn't know anything about the fact you have set IDENTITY_INSERT to ON.

    Try using sp_executesql instead. (Books Online has more information on how to use this system stored proc)

    macka.

  4. #4
    Join Date
    Jan 2003
    Location
    Leamington Spa, UK
    Posts
    17
    Why not just build it as a single string with space between the statements ? I've just tested that and it works fine.

    macka.

  5. #5
    Join Date
    Apr 2003
    Posts
    11
    Thanks for this.. i really appriciate ur help...
    space works and actually newline character is char(10).. it works with this too...


    Originally posted by macka
    Why not just build it as a single string with space between the statements ? I've just tested that and it works fine.

    macka.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •