Re: IDENTITY_INSERT error

From: Ted W9999 (TedW9999_at_discussions.microsoft.com)
Date: 10/08/04

  • Next message: Brian_Harrell: "Re: ADO Runtimes to distribute" 
    Date: Fri, 8 Oct 2004 09:31:04 -0700

    Forgot to ask in my last post:

    I should probably go ahead and append ";SET IDENTITY_INSERT Surveys OFF" to
    the statement as well? These insert statements are in a paragraph that gets
    executed over and over, possibly within a fraction of a second. Will SQL
    Server possibly get confused with the fast switching between setting the
    IDENTITY_INSERT on and off so quickly? Maybe I should just omit setting the
    IDENTITY_INSERT off?

    "Val Mazur" wrote:

    > Hi,
    >
    > Session means same opened connection. If you do not close connection
    > somewhere in between the calls, then it should work. But it is possible that
    > your command opens another connection and you could check it using SQL
    > Profiler. What you could do is (if you are using OLEDB Provider for SQL
    > Server) is to execute both statement in one batch separating them by
    > semicolon (;)
    >
    > cmd.CommandText = "SET IDENTITY_INSERT Surveys ON; INSERT INTO SURVEYS
    > (SurveyID,InsuredRef,Location,Sequence) VALUES (?,?,?,?)"
    > cmd.parameters(0) = 123
    > cmd.parameters(1) = 'abc'
    > ......
    >
    > --
    > Val Mazur
    > Microsoft MVP
    >
    >
    > "Ted W9999" <Ted W9999@discussions.microsoft.com> wrote in message
    > news:3CABCFDD-82BC-4BAE-89DA-B2E1BAA51609@microsoft.com...
    > > I've had an application in production for years, and after making minor
    > > changes it is getting errors at my client site and now here. The error is
    > > "Cannot insert explicit value for identity column in table 'Surveys' when
    > > IDENTITY_INSERT is set to OFF."
    > >
    > > Here is part of the table:
    > > CREATE TABLE [Surveys] (
    > > [SurveyID] [int] IDENTITY (1, 1) NOT NULL ,
    > > [InsuredRef] [varchar] (8) NULL ,
    > > [Location] [smallint] NULL ,
    > > [Sequence] [smallint] NULL
    > > ) ON [PRIMARY]
    > >
    > > I am working in VB6 using ADO. I need to insert a record overriding the
    > > identity column. Here is the essential paraphrased bits of the code:
    > >
    > > '--------------------- set identity_insert on
    > > Set cmd = New ADODB.Command
    > > Set cmd.ActiveConnection = adoCon
    > > cmd.CommandText = "SET IDENTITY_INSERT Surveys ON"
    > > On Error Resume Next
    > > cmd.Execute
    > > If Err.Number <> 0 Then
    > > MsgBox "Error on line 'SET IDENTITY_INSERT Surveys ON'. Please
    > > contact Technical Support." & vbCrLf & Err.Number & " " & Err.Description,
    > > vbOKOnly Or vbCritical, "Error!"
    > > End If
    > > On Error GoTo ErrHandler
    > > Set cmd = Nothing
    > > DoEvents 'give it a chance to sink in
    > >
    > > That part works okay. Here is where I get the error:
    > >
    > > Set cmd = New ADODB.Command
    > > Set cmd.ActiveConnection = adoCon
    > > cmd.CommandText = "INSERT INTO SURVEYS
    > > (SurveyID,InsuredRef,Location,Sequence) VALUES (?,?,?,?)
    > > cmd.parameters(0) = 123
    > > cmd.parameters(1) = 'abc'
    > > cmd.parameters(2) = 1
    > > cmd.parameters(3) = 2
    > > cmd.Execute
    > > Set cmd = Nothing
    > >
    > > Now, I've read that you have to have the IDENTITY_INSERT statment in the
    > > same "session" as the insert statement. I don't know what the definition
    > > of
    > > "session" is, but we are using the same connection on sequential
    > > statements.
    > > I tried combining the statements separated by a space or vbCrLf as I've
    > > seen
    > > on other sites, but get an error due to the parameters (no way to get
    > > around
    > > those). This is the only IDENTITY_INSERT column anywhere in my tables and
    > > the
    > > only spot in the code where the IDENTITY_INSERT is changed. No other apps
    > > are
    > > messing with it either.
    > >
    > > How can this be done in ADO?
    > > Has something recently changed in MSDE that broke this (as I said it's
    > > been
    > > working until a few days ago)?
    > > Does IDENTITY_INSERT need to be set OFF or is it done implicitly?
    > >
    > > Thanks in advance!
    > >
    > >
    >
    >
    >

  • Next message: Brian_Harrell: "Re: ADO Runtimes to distribute"

    Relevant Pages

    • SQL Server 2000 (Application) Role accessed through ADO.NET
      ... users from connecting to SQL Server through other MSOffice ... The database role claims to answer my ... I establish the connection to the database ... I execute sp_setapprole stored procedure (providing the ...
      (microsoft.public.sqlserver.security)
    • Re: ADODB.Recordset: Operation is not allowed when the object is closed
      ... > ' Create the ADO Connection and Recordset objects. ... > ' Set the connection string, open the connection and execute the ... If your sql string is an insert/update/delete statement, ...
      (microsoft.public.scripting.vbscript)
    • Re: VBA - Testing Links to tables
      ... You can execute a stored procedure by creating a pass-through query in ... VBA code by creating a DAO QueryDef object and setting its connection ... Microsoft Access Developer's Guide to SQL Server ...
      (microsoft.public.sqlserver.programming)
    • Re: VBA - test link to tables
      ... Only way to test connection is to execute some sort of *light* SQL statement ...
      (microsoft.public.vb.database.ado)
    • Re: IDENTITY_INSERT error
      ... the same connection, just a different ADODB.Command. ... > Session means same opened connection. ... What you could do is (if you are using OLEDB Provider for SQL ... > Server) is to execute both statement in one batch separating them by ...
      (microsoft.public.data.ado)