FW: MS SQL 7 and identity columns insert problem

Sorry, a possibly significant typo in my original....autocommit is left on
the default (ON)- not off.

Thanks,

Steve Howard

Has anyone been successful in doing explicit inserts into columns with
identity properties from PERL?  I would appreciate any help, or any
experience anyone has had with this.

I am using dbi with dbd::odbc, and MS SQL 7.0. autocommit is left on the
default (off). I have thus far tried unsuccessfully two different ways to do
this.
The first way is as follows:

        $sth = $dbh->prepare(qq{SET identity_insert $target..$table ON});
        $sth->execute() || print "$target..$table does not have the identity
property \n";

        my $insert=qq{INSERT INTO $target..$table ($columnlist) SELECT
$columnlist FROM $source..$table};
        my $sth = $dbh->prepare($insert);
        $sth->execute || die "Can't execute ($insert): $dbh->errstr";



This prints the message I specified for all tables that do not have identity
property, and the insert works perfectly on them. When it hits a table with
an identity column, even though I have set identity insert on for that
table - and that is session-specific normally - I still get the error
telling me:

        Cannot insert explicit value for identity column in table
<tablename> when IDENTITY_INSERT is set to off.



The second way I have tried (Doesn't really make sense that a table property
can be set here, but if it can, I don't have it right) is to connect as so:

    my $dbh = DBI->connect($dsn, $db_user, $db_password,
{IDENTITY_INSERT=>1});

This does not produce an error - it just doesn't work.

In our case a DTS package to do this migration will be much less flexible,
and much more work for the number of databases, and possible situations than
what we have almost worked out. I would appreciate any help as this is too
close to perfection to be hung on this detail at this point in the project.



Thanks,



Steve Howard

• FW: MS SQL 7 and identity columns insert problem by Steve Howard