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

Question

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

GOODS

Ten Centuries

Points: 1360
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

GOODS Ten Centuries Points: 1360 More actions · Answer 2

GOODS

Ten Centuries

Points: 1360

June 27, 2014 at 2:47 am

#1725353

Thank you Gail!!:-D

GOODS Ten Centuries Points: 1360 More actions · Answer 3

Hi Gail,

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

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/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

GOODS Ten Centuries Points: 1360 More actions · Answer 6

GOODS

Ten Centuries

Points: 1360

June 27, 2014 at 6:11 am

#1725390

Thanks it worked