Post reply

Retrieving a parameter using sysmail_help_configure_sp

  • December 23, 2009 at 9:23 am

    #228757

    I am writing a stored procedure that uses Database Mail to send e-mail sometimes with attachments, and before I send the e-mail, I am checking if the attachments are greater than what Database Mail will allow me to send. I am trying to get the MaxFileSize from the following stored procedure so I do not have to hard-code this value:

    EXEC msdb.dbo.sysmail_help_configure_sp

    @parameter_name = 'MaxFileSize'

    According to BOL...

    Syntax

    sysmail_help_configure_sp [ [ @parameter_name = ] 'parameter_name' ]

    Arguments

    [@parameter_name = ] 'parameter_name'

    The name of the configuration setting to retrieve. When specified, the value of the configuration setting is returned in the @parameter_value OUTPUT parameter. When no @parameter_name is specified, this stored procedure returns a result set containing all of the Database Mail configuration settings in the instance.

    Above it mentions a @parameter_value OUTPUT parameter which would be nice to use because then I can retrieve the information that way. However, when running the following SQL...

    DECLARE @parameter_value NVARCHAR( 256 )

    EXEC msdb.dbo.sysmail_help_configure_sp

    @parameter_name = 'MaxFileSize'

    , @parameter_value = @parameter_value OUTPUT

    I get the following Error:

    Msg 8144, Level 16, State 2, Procedure sysmail_help_configure_sp, Line 0

    Procedure or function sysmail_help_configure_sp has too many arguments specified.

    I know I can just use a temp table and get this information to be used in the stored procedure, but it seems like overkill. Am I missing something here or did BOL make a mistake?

    EDIT to add the code that works with the temp table (and which is horribly inefficient compared to just being able to retrieve the value straight to the variable):

    DECLARE @parameter_value NVARCHAR( 256 )

    DECLARE @TempTable TABLE

    ( paramname NVARCHAR( 256 )

    , paramvalue NVARCHAR( 256 )

    , description NVARCHAR( 256 )

    )

    INSERT INTO @TempTable

    EXEC msdb.dbo.sysmail_help_configure_sp

    @parameter_name = 'MaxFileSize'

    SELECT @parameter_value = paramvalue

    FROM @TempTable

    SELECT @parameter_value

  • December 23, 2009 at 10:31 am

    #1096036

    Looking at the proc itself, it only takes one parameter. It wouldn't be the first time BOL was wrong.

    CREATE PROCEDURE dbo.sysmail_help_configure_sp

    @parameter_name nvarchar(256) = NULL

    AS

    SET NOCOUNT ON

    SELECT paramname, paramvalue, description

    FROM msdb.dbo.sysmail_configuration

    WHERE paramname = ISNULL(@parameter_name, paramname)

    RETURN(0)

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply