ODBC error message

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

Can anyone please explain the following error message? My usual coding strategies do not overcome this error. I have to delete the SQL statement prior to the SQL statement that comes up in this error statement. I think that it is a weakness of MS SQL Server or Perl DBI rather than my code.


DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Conne
+ction is b
usy with results for another hstmt (SQL-HY000)(DBD: st_execute/SQLExec
+ute err=-1
) at table_ZK.pl line 846.
Couldn't execute query: [Microsoft][ODBC SQL Server Driver]Connection 
+is busy wi
th results for another hstmt (SQL-HY000)(DBD: st_execute/SQLExecute er
+r=-1) at table_ZK.pl line 846.
[download]

20040127 Edit by BazB: Changed title from 'Error message'

Comment on ODBC error message Download Code

Replies are listed 'Best First'.
Re: ODBC error message by derby (Abbot) on Jan 27, 2004 at 14:15 UTC
No. It's pretty much your code. You have done a query (A) but not fully read the results. Hence the next query (B) cannot be done because: Connection is busy with results from another hstmt. The error message is pretty self-explantory. -derby	[reply]
Re: ODBC error message by arden (Curate) on Jan 27, 2004 at 14:49 UTC
From the error, I'll wager it is actually your code. Unfortunately, you didn't provide any of it for us to look at. I see a couple solutions, (A) Flush the remaining results from the previous query or (B) open another connection to the database.	[reply]
Re: ODBC error message by monktim (Friar) on Jan 27, 2004 at 13:55 UTC
I don't think this is a DBI problem. If you post a larger chunk of the script I'll be able to tell you more. It looks like there might be a limit on the number of connections the Server allows or the Server is overloaded. If you're not on a production system, you can try killing some processes before you run your code again. In MS SQL you can run the sp_who stored procedure to see the current activities in the dB. You can use the kill command to stop them.	[reply]
Re: ODBC error message by iburrell (Chaplain) on Jan 27, 2004 at 20:56 UTC
It is a limitation of SQL Server that it can only have a single active statement on a connection. Many other databases will support multiple active statements on the same connection. You will need to rewrite your code to get around the limitation. I have usually seen this limitation when looping over the results of one query and performing another query on each row. One solution is to pull all the data into memory instead of looping over each element of the results. Another is to use a second connection for the second statements. DBI will automatically finish the statement when all the results have been read. If you aren't reading all the results from a query, then you will need to call `$sth->finish()` manually. There are convienence function like `selectrow_hashref`.	[reply] [d/l] [select]
Re: ODBC error message by stvn (Monsignor) on Jan 27, 2004 at 17:30 UTC
Doubt its MS SQL server or DBI, both are pretty reliable. Have you tried "finishing" the DBI statement? `$statement->finish();` [download] This will tell DBI at least, that you dont care about the rest of your results, and maybe release the connection. Without seeing your code though, not much we all can do but guess. -stvn	[reply] [d/l]