Re: IDENTITY_INSERT error

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

Date: Sun, 10 Oct 2004 22:59:25 -0400

Hi,

Yes, it is always good to restore previous settings 

-- 
Val Mazur
Microsoft MVP
"Ted W9999" <TedW9999@discussions.microsoft.com> wrote in message 
news:0A7A85E8-56C2-43F4-956B-51DB8937EE36@microsoft.com...
> 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!
>> >
>> >
>>
>>
>>

Relevant Pages

  • Re: Is it possible to create a custom SQL session function/variable
    ... Take a look at SET CONTEXT_INFO in the Books Online. ... That allows you to set a value for the connection that is accessible anywhere in the session with the CONTEXT_INFO function. ... SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: Session state lost after accessing database
    ... updates values in the session state and executes a SQL statement while the ... >>I have an ASP.NET application that connects to an Access database. ... >>The second button creates a connection to the database, executes a SQL ... Connection is a OledbConnection connecting via OLEDB JET. ...
    (microsoft.public.dotnet.framework.aspnet)
  • 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)
  • [Info-ingres] dbmsinfo(connection_name) vs inquire_sql
    ... Each session is identified by a connection name. ... The SQL has no error, but it also returns nothing - ie.just like ... return the default connection name whih is the database name. ...
    (comp.databases.ingres)
  • Re: ADO Error ( think)
    ... > Server database, once I have created the connection to the database I say ... > "Val Mazur" wrote in message ... >>> Response.Write filepath ...
    (microsoft.public.data.ado)