Re: SET IDENTITY_INSERT tablename ON problem
From: Val Mazur (group51a_at_hotmail.com)
Date: 04/28/04
- Next message: Val Mazur: "Re: Sending CLOBs to Oracle using VB/ADO"
- Previous message: Suzette: "Re: DataGrid & Invoicing App."
- In reply to: Dave: "SET IDENTITY_INSERT tablename ON problem"
- Messages sorted by: [ date ] [ thread ]
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. > >
- Next message: Val Mazur: "Re: Sending CLOBs to Oracle using VB/ADO"
- Previous message: Suzette: "Re: DataGrid & Invoicing App."
- In reply to: Dave: "SET IDENTITY_INSERT tablename ON problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
