Re: IDENTITY_INSERT error

From: Val Mazur (group51a_at_hotmail.com)
Date: 10/08/04 

Date: Thu, 7 Oct 2004 22:45:40 -0400

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!
>
>

Relevant Pages

  • Re: APPN Connection z/os - AHHC / LLC2.
    ... implied that you session stopped for a while and then continued. ... the ANNC link is a type 2.1 connection. ... I would suggest trying to limit the request unit size used over the type 2.1 ... MAXDATA operand when defining a PU statement for an adjacent link station ...
    (bit.listserv.ibm-main)
  • Re: Remote desktop deadlock on XP SP2
    ... the TS connection is frozen, even if I specify a wait timeout. ... TermSrv.dll creates a new session for the purpose of displaying the logon ... lives on a DPC routine for the network miniport ... the network packets are encapsulated and decapsulated in UDP. ...
    (microsoft.public.win32.programmer.kernel)
  • Re: JES-APPL
    ... Then there has been a session initiation request - which can only happen over an available session path - this isn't any of your flaky IP, ... Unless the NJE application on the AS/400 has consistently been caught in the middle of being activated in some way - highly unlikely I would expect - this is not a timing problem. ... Since the connection is necessarily in place, there is no problem with the connection - just in case a modem is involved - which, given a "switched" definition is used, is not impossible, but unlikely. ...
    (bit.listserv.ibm-main)
  • Re: os users, sessions, and connections.
    ... From what I've read a session is a logged connection. ... do the following (paddr is the address of the process ... select sid, serial#, paddr, username from v$session ...
    (comp.databases.oracle.server)
  • Re: Some outstanding ASP .NET questions
    ... Typically, we store the connection in the ... Session and use a Singleton pattern to retrieve it back later: ... Is it appropriate to store table key information in the session? ... I was referring to primary keys in database tables. ...
    (microsoft.public.dotnet.languages.csharp)
Loading