Post reply

Must pass parameter number 4 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed

  • June 27, 2014 at 1:59 am

    #287397

    Hi Guys I got the following SP that runs perfect in SSMS but when I try to include the sp in a query to send out mail.Get the following error:

    Must pass parameter number 4 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed

    My Sp has no parameters

    My SP

    CREATE PROCEDURE Sp_Compare_Acc_Comment

    AS

    SELECT

    MAX(TableName) as TableName,

    aco_code,acc_code,

    ctr_id,cte_id,usr_code,

    -- convert(datetime,convert(varchar,current_timestamp,112),112)

    Cast(DATEPART(hour,current_timestamp) as varchar(3))+':'+ CAST(DATEPART(minute,current_timestamp) as varchar(3))as [Time]

    INTO #AB

    FROM

    (

    SELECT 'TM_ACO_Account_Comment'as TableName,

    a.aco_code,

    a.acc_code,

    a.ctr_id,

    a.cte_id,

    a.usr_code

    FROM

    TM_ACO_Account_Comment a with(NOLOCK)

    UNION ALL

    SELECT 'TM_ACO_Account_Comment'as TableName,

    b.aco_code,

    b.acc_code,

    b.ctr_id,

    b.cte_id,

    b.usr_code

    FROM

    [172.17.14.77].[IS_ND_BLAKE].[dbo].[TM_ACO_Account_Comment] b with(NOLOCK)

    )zzz

    GROUP BY aco_code,

    acc_code,

    ctr_id,

    cte_id,

    usr_code

    HAVING COUNT(*) = 1

    ORDER BY

    aco_code

    SELECT *

    FROM

    #AB

    DROP TABLE #AB

    The Query to send mail

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SystemAlerts',

    @from_address = 'DBAlerts@blake.co.za',

    @recipients = 'Dlozi.Nzuke@interactivesa.co.za','nivashan.govender@interactivesa.co.za',

    @query = 'exec SP, Sp_Compare_Acc_Comment' ,

    @execute_query_database = 'IS_ND_BLAKE',

    @subject = 'Compare Table',

    @attach_query_result_as_file = 1 ;

    Please assist...thank you

  • June 27, 2014 at 2:19 am

    #1725347

    The procedure does have parameters, because the error is referring to sp_send_dbmail. The bolded line is the problem

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SystemAlerts',

    @from_address = 'DBAlerts@blake.co.za',

    @recipients = 'Dlozi.Nzuke@interactivesa.co.za', 'nivashan.govender@interactivesa.co.za',

    @query = 'exec SP, Sp_Compare_Acc_Comment' ,

    @execute_query_database = 'IS_ND_BLAKE',

    @subject = 'Compare Table',

    @attach_query_result_as_file = 1 ;

    You have

    @recipients = 'Dlozi.Nzuke@interactivesa.co.za',

    'nivashan.govender@interactivesa.co.za',

    Not one value being passed, but two separate strings. One is picked up as a parameter for @recipients (the first one), the second is considered another parameter, but with no idea where it belongs, it's causing the error.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 27, 2014 at 2:47 am

    #1725353

    Thank you Gail!!:-D

  • June 27, 2014 at 3:18 am

    #1725354

    Hi Gail,

    One more question if I want multiple users to receive the email.how would I go about scripting it?

  • June 27, 2014 at 3:24 am

    #1725357

    GOODS (6/27/2014)

    Hi Gail,

    One more question if I want multiple users to receive the email.how would I go about scripting it?

    Isn't recipients a semi-colon deliminated list? Try: -

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SystemAlerts',

    @from_address = 'DBAlerts@blake.co.za',

    @recipients = 'Dlozi.Nzuke@interactivesa.co.za;nivashan.govender@interactivesa.co.za',

    @query = 'exec SP, Sp_Compare_Acc_Comment' ,

    @execute_query_database = 'IS_ND_BLAKE',

    @subject = 'Compare Table',

    @attach_query_result_as_file = 1 ;

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • June 27, 2014 at 4:35 am

    #1725377

    GOODS (6/27/2014)

    Hi Gail,

    One more question if I want multiple users to receive the email.how would I go about scripting it?

    Straight from Books Online:

    [ @recipients= ] 'recipients'

    Is a semicolon-delimited list of e-mail addresses to send the message to. The recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 27, 2014 at 6:11 am

    #1725390

    Thanks it worked

Viewing 7 posts - 1 through 6 (of 6 total)

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