I'm connecting to SQL Server using ADO, and I'm selecting records from a
table, getting some additional info, and updating the same table. So
for example, if I do:
$sql = "select id from table where criteria='asdf'";
my $gr = $dbh->prepare($sql)
$gr->execute;
while(my $id = $gr->fetchrow_array){
# Find out some stuff regarding $id
$sql = "update table set criteria2='qwer' where id=$id";
$dbh->do($sql);
}
$gr->finish;
I was doing this using DBI ADO on a machine that had sql-server and IIS
installed, and it worked just fine. But now I'm running the script on a
different box than the sql-server, and I get the error: "Cannot create
new connection because in manual or distributed transaction mode."
Now, if I prepare the statement like this:
my $gr = $dbh->prepare($sql, {CursorType => 'adOpenStatic'});
I don't get an error, and the first time through the loop, the record is
updated, but it doesn't proceed to the next iteration.
I could store the ids in an array, and loop through the array, but that
would involve lots of code rewriting. Is there something I'm missing?
