I answered your later post where it sounds like you are working now. I did not take your reponse as meaning you were blaming anything, I was just simply pointing out there were more parameters than you had considered.

I'm interested in 2 things in particular (both of which could help me).

1. Why are you using so old a DBI and DBD::ODBC?
2. Why do you think you got to the point where you were calling a procedure with the do method and never got to odbc_more_results? Did you read any of DBD::ODBC and if so, how could I make it clearer so the next person who hits this doesn't have to go through your pain.

1. I'm relying on the install of an existing server to which I am not an admin. However we will be upgrading. 2. The do method was only an attempt at trying something new at the time that I posted my code. I was actually using prepare/execute because I knew I was expecting a resultset. I usually work with .net and if I run any SQL using any of the methods, it executes 100% fully before continuing.

I've come across odbc_more_results in previous research but DBD::ODBC only says "SQL Server supports this feature" so I assumed I didnt have to use this unless I was expecting to see multiple results from using sql such as select * from table1; select * from table2;

You've been a great help. Thank you.

my $sthTestProc1 = $dbhTestInsert->prepare(qq/exec spTestSelectAndUpda
+te/);
$sthTestProc1->execute();

do {
   my @row;
   while (@row = $sthTestProc1->fetchrow_array()) {
      # do stuff here
   }
} while ($sthTestProc1->{odbc_more_results});
[download]

The new code you show should be your template for calling ANY procedure via DBD::ODBC. I can explain why but it would get into a load of discussion of the ODBC API. The main reasons are in my previous post.

Now put your other logic with raise_error and prints back in and see where you get to. But most importantly, remember, NO procedure in SQL Server has completed UNTIL odbc_more_results (aka SQLMoreResults) returns false. This is particularly important if you have any output parameters as they are not available until then.

Also keep in mind that using RAISERROR in your SQL code to return a message can potentially cause the DBI/DBD code to think that an error has occurred, and cause the fetch loop to terminate.

Michael

That is true which is why I also tested with only select and update. Originally it was doing:

Select * from tempTable;
Update tempTable set done=1 where id=1;
Update tempTable set done=1 where id=2;
--connection gets broken
[download]

now with more results it executes fully:

Select * from tempTable;
Update tempTable set done=1 where id=1;
Update tempTable set done=1 where id=2;
Update tempTable set done=1 where id=3;
Update tempTable set done=1 where id=4;
Update tempTable set done=1 where id=5;
[download]

In the stored procedure the updates are within a while loop that generates the id so in this case no additional print or raiserror was being used.