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

sp_helpdb fails to run succesfully

When I ran sp_helpdb on a SQL Server 2000 box, I got the following error:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column ‘owner’, table ‘tempdb.dbo.#spdbdesc___ … __000100004AA1’; column does not allow nulls. INSERT fails.The statement has been terminated.

I narrowed this down to the statement [select name, suser_sname(sid), convert(nvarchar(11), crdate), dbid, cmptlevel from master.dbo.sysdatabases] within the sp_helpdb stored procedure. The database owner sid was not a valid user on the server, but a valid user on a different server from which the database had been restored

sp_changedbowner ‘insert_valid_user_here’ quickly corrected the problem.

SQL Server Agent will not start

SQL Server (2000) Agent failed to start today and the message in the Event Viewer was
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but ‘(Unknown)’ is not a member of the SysAdmin role).

We looked at permissions and policies on the Win2K server and also tried using other administrator ids without success.

Finally, when all else failed, I tried profiler and discovered…

This is caused by setting the msdb compatibility to 7 (sp_dbcmptlevel ‘database_name’, 70). Specifically, the use of COLLATE fails in a temporary table creation within the sp_sqlagent_has_server_access stored procedure which is called as part of service startup

Just run sp_dbcmptlevel ‘database_name’, 80 to correct.

Unfortunately, SQL Server 2000 allows you to change the compatibility level on the msdb and, therefore, cause this problem.

SQL Server Ireland User Group – July 4th

The next SQL Server Ireland User Group is on July 4th, 2006 in the Microsft’s Atrium offices. I will be presenting on Indexing in SQL Server 2000 & 2005.

Though this was not the most popular of themes, I believe it to be an important stepping stone towards the more popular performance troubleshooting requested by members of the user group.

If you are interested in coming along, then contact me on ndflanagan at 02.ie

%d bloggers like this: