Common problems calling procedures in MS SQL Server via DBD::ODBC

Recently I've had a spate of issues reported in dbi-users mailing list and direct to me wrt calling procedures in MS SQL Server. I thought it might be worth covering some of the more common ones here.

MS SQL Server does not batch up results in procedures - so what does this mean? It means each insert, update, delete, select is treated separately and you need to move through those results one at a time even though as far as you are concerned you have called a single procedure. For example assume the procedure my_proc does this:

insert statement
insert statement
select statement
select statement

Normally (via the ODBC API) you'd call SQLPrepare (DBI prepare) to prepare the call to the procedure then SQLExecute (DBI execute) to execute the procedure. The problem here is that MS SQL Server does not batch the results up so to move from one to the next you have to call (via ODBC) SQLMoreResults and each call moves you through the result for the first insert (SQLRowCount returns N and SQLNumResultCols returns 0), the second insert (SQLRowCount returns N and SQLNumResultCols returns 0), then the first select (SQLRowCount returns 0 and SQLNumResultCols returns N) and lastly the second select (SQLRowCount returns 0 and SQLNumResultCols returns N). So from ODBC you have to do:

SQLPrepare("{call myproc()}");
while (SQL_SUCCEEDED(SQLMoreResults)) {
  /* do something with the statement */
  /* this may be call SQLRowCount to find the affected rows */
  /* this could be call SQLNumResultCols to find if there is a result-set */

In DBD::ODBC, users found having to call SQLMoreResults was a pain for the insert/update/delete statements since all they could find out was the rows affected (via $sth->{Rows}) and if the insert failed, the execute failed anyway. As a result, DBD::ODBC automatically calls SQLMoreResults and if SQLNumResultCols returns a non-zero value it knows there is a result-set and can set it up whereas if SQLNumResultCols returns 0 it is an insert/update/delete and it is ignored and SQLMoreResultCols is called again - effectively skipping the non-result-set generating statements. Note: DBI's do method in DBD::ODBC does not call SQLMoreResults since the do method is supposed to be used for non-result generating SQL. Also note that it is much more efficient with MS SQL Server to use "SET NOCOUNT ON" as this will skip the results from most insert/update/delete statements automatically in SQL Server and avoid DBD::ODBC from having to call SQLMoreResults itself.

So what this means that if you are calling procedures you should avoid DBI's do method and use prepare/execute instead (since these call SQLMoreResults internally in DBD::ODBC) - even if the procedure does not generate result-sets. Also, due to vagaries in the MS ODBC Driver you mostly need to call the ODBC API SQLExecDirect for procedures if they perform multiple actions (I cannot say exactly why right now but experience has told me this and it is probably due to done_in_proc messages in the TDS protocol). A similar situation exists when using temporary tables in MS SQL Server where if you use prepare/execute the temporary tables disappear but if you use SQLExecDirect they are available until you disconnect. To force DBD::ODBC to use SQLExecDirect instead of SQLPrepare/SQLExecute you pass "{odbc_exec_direct => 1}" as the second argument to DBI's prepare method. This will delay calling SQLPrepare on DBI's prepare and instead call SQLExecDirect when you call DBI's execute method. If you don't do this then the procedure may not complete fully but seem to succeed.

DBD::ODBC's odbc_more_results does not call SQLMoreResults in the ODBC API. DBD::ODBC calls SQLMoreResults automatically when it may be necessary (e.g., when SQLFetch returns SQL_NO_DATA and SQLNumResultCols returns 0 columns). When you call odbc_more_results in DBD::ODBC all you are doing is finding out if DBD::ODBC has already detected another result-set and moving on to that result-set (in Perl terms) if it has.

Procedures occasionally output informational messages or issue Print statements. The former of these result from an ODBC API returning SQL_SUCCESS_WITH_INFO and the latter from calling Print in the procedure. To capture most of these you set up an odbc_err_handler or DBI's HandleSetErr handler (although be aware that until post DBD::ODBC 1.23_2 SQLMoreResults does not always report informational messages to DBI's error handler). Basically every ODBC API called looks for SQL_SUCCESS_WITH_INFO returned and if it is informational messages are available via SQLError or SQLGetDiagRec and these are pushed to DBI's set_err which may be captured via the HandleSetErr. As a result you can capture and monitor print statements in procedures or diagnostic information output during the procedure. e.g., when calling restore database foo from disk=`c:\foo.bck' the procedure needs SQLMoreResults calling 3 times and each time it returns SQL_SUCCESS_WITH_INFO and each time DBD::ODBC will call DBI's set_err with the diagnostic showing the progress of the restore.

Lastly, you should be aware that output bound parameters are not available until the procedure has finished. The only way of making sure the procedure is complete is to continually call odbc_more_results until it returns false.

You can find examples of odbc_more_results etc in DBD::ODBC's t subdirectory in the 20SqlServer.t file and at Connecting Perl on UNIX or Linux to Microsoft SQL Server - Perl DBI/DBD::ODBC Tutorial Part 3.

Thanks to Jenda Krynicky for comments on this document on dbi-users. As a result I amended my use of "statements" in batching and added comment on SET NOCOUNT ON.