Re: SET IDENTITY_INSERT tablename ON problem

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

Date: Tue, 27 Apr 2004 22:28:45 -0400

Hi Dave,

It should work. Try to do next - run SQL profiler and see if ADO Command
does not open another connection to the database when it executes this
statement. If it does (which is possible), then setting will not be applied
against temp table, since it will not be visible. But you should get error
message in this case.
Another way is to use Execute method of ADO connection. Since connection is
opened against the temp table, it should work 

-- 
Val Mazur
Microsoft MVP
"Dave" <dakotav8@earthlink.net> wrote in message 
news:u$xAsFHLEHA.2716@tk2msftngp13.phx.gbl...
> I'm updating an existing SQL 2000 database. I'm using VB 6 and ADO 2.7 on 
> a
> XP Pro computer. A couple of my tables use identity columns. To update the
> database I add a new tmp table with the new structure. I set the identity
> column in the tmp table like this: "LogID int NOT NULL IDENTITY(1, 1), " & 
> _
> as part of the CREATE TABLE string. That works fine, the table is created
> properly. Now I want to insert the data from the existing table so I use 
> an
> INSERT INTO string to copy data from the old to the new. In order for this
> to work I need to set IDENTITY_INSERT ON for the tmp table. I use the ADO
> command object for this:
>
>    command.Execute "SET IDENTITY_INSERT (tmp_table_name) ON"
>
> The next command is to insert the data but I get an error that says I 
> can't
> insert a value in the identity column when IDENTITY_INSERT is set to OFF
> even though the previous command seems to have worked and didn't cause an
> error. I have tried setting the cursorlocation to adUseServer with no 
> help.
> I tried creating the table without setting up the int column as an 
> identity
> column and everything worked fine. All the data was inserted into the tmp
> table.
>
> Is it possible to do this using VB code? I don't want the user to run a 
> sql
> script or have to use Enterprise Manager, or Query Analyzer to update the
> database. In many cases they are using MSDE databases and don't have these
> tools available.
>
> Thanks for any help.
>
>

Relevant Pages

  • Re: Please Urgent - Update Data Source Problem ???
    ... then there's nothing to submit to the database. ... values that are only assigned by the DB once the update command is executed. ... > the connection. ... > schedule, and they can update the existing schedule, add new schedule ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Reading with ADO from Access database over network
    ... I suspect the *problem* isn't the failure to open the folder ... check the active Connection ... Perhaps you could follow the above instructions and post the *ADO* ... > exclusively the database. ...
    (microsoft.public.excel.programming)
  • Re: Reading with ADO from Access database over network
    ... Is your ADO code trying to open the Connection with exclusive locks ... If so, instead consider locking the ... workgroups security so the database is only locked when someone with ...
    (microsoft.public.excel.programming)
  • Re: trying to copy the autonumber from one table to the next
    ... Ok I think I understand...and yes this is an access 2000 database ... OleDbCommand command = new OleDbCommand (sqlString, Connection); ... >support multi statement batch commands (which is different from SQL Server). ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: ADOCommand and grave accent problem
    ... problem - Paolo has spotted the solution. ... a parameterised command I do not know. ... >> when using an ADO Command, and not when using an ADO Connection object. ...
    (borland.public.delphi.database.ado)