Calling sp_procedure_params_rowset fails

Last weekend, we moved an important application from SQL Server 7 sp4 up to SQL Server 2000 sp4. However, at one point, the code repeatedly failed on a sql call to the simplest of transact-sql stored procedures - containing a very basic insert statement.

By running profiler on the session, we identied a call - exec sp_procedure_params_rowset proc_name - as the last good call to the database. This call was not made by our code, but appears to be called when parameterized SQL Server stored procedures use a data access technology such as ActiveX Data Objects (ADO).

This system stored procedure returns parameter information for the specified stored procedure. The first row (ordinal position of 0)is the result value from the stored procedure.

However, in SQL Server 7 with MDACs below 2.5, this is named RETURN_VALUE. While, in both SQL Server 2000 and MDACs 2.6 & above, this was changed to @RETURN_VALUE.

The only recommended solution is to correct calling code - switching from RETURN_VALUE to @RETURN_VALUE.

Finding information on this one was much easier once we realised what the problem was. These details can be found at http://support.microsoft.com/kb/285295/en-us