Re: SET IDENTITY_INSERT tablename ON problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Dmitriy Antonov (dantonov_at_netsynergy.com.NOT_FOR_SPAM)
Date: 04/27/04 

Date: Tue, 27 Apr 2004 15:23:31 -0400

Try to combine all statements into one string and then execute them in one
Execute method. As far as I know this is run time (per-session) setting. So
after each execute it is reset to OFF.

Dmitriy.

"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: Creation of DSN
    ... Build a text string from the input data. ... rather execute the string that was just built. ... script file has been processed. ... > I'm using directly a Command, ...
    (microsoft.public.vb.database.ado)
  • Re: Executing "unknown" command line in C#
    ... > fresh copy of the XML file. ... I have a string that needs to be ... And it works fine if you execute it from a DOS box, ... > my problem - to do this, I have to divide my command line into two ...
    (microsoft.public.dotnet.csharp.general)
  • Re: Scheduled tasks in WP
    ... The log rolls around so the end of the file is probably not where the logging stopped last. ... Have you tried running the string you entered in the Command field for the event? ... Is the RunAs for the event the same account under which you say the scheduled task will execute okay when started manually? ...
    (microsoft.public.windowsxp.general)
  • Re: Use of System function
    ... DOS command but every time I use it it returns -1 which meansthe ... on Unix) to execute the specified command. ... If that second one doesn't work check up on escape sequences WRT the ... according to C's rules for string literals. ...
    (comp.lang.c)
  • Re: Uninstall software from Advanced Client using SMS 2003
    ... One trick I have used in the past is to put the command string in a batch ... file and then execute the batch with SMS. ...
    (microsoft.public.sms.swdist)