BUG: Sp_processmail Returns Datatype Error Message

• Microsoft SQL Server version 7.0

SYMPTOMS

When you attempt to run sp_processmail to process queries sent through e-mail, you receive the following error message:

Server: Msg 18007, Level 16, State 1, Line 0
Supplied datatype for @set_user is not allowed, expecting 'varchar'

CAUSE

Sp_processmail declares both @set_user and @dbuse using the sysname datatypes. This stored procedure then calls sp_sendmail, which declares both of those variables as varchars.

WORKAROUND

Change sp_process mail to declare @set_user and @dbuse as varchar(128). To do this, follow these steps:

1. Start Query Analyzer, verify master is the current database and execute the following command to view the text of the stored procedure:

   
   sp_helptext sp_processmail 




2. Copy the text of the stored procedure to Notepad or another text editor.



3. Add the following two lines above the line "create procedure sp_processmail":

   
   drop procedure sp_processmail
   go 




4. Modify the following two parameter declarations from:

   
   @set_user sysname='guest',
   @dbuse sysname='master' 

   to:

   
   @set_user varchar(128)='guest',
   @dbuse varchar(128)='master' 




5. Now, copy the new text back to the Query Analyzer and run it.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words:

Keywords : kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug