Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Must pass parameter number 4 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be... Expand / Collapse
Author
Message
Posted Friday, June 27, 2014 1:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:56 AM
Points: 50, Visits: 212
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
Post #1586735
Posted Friday, June 27, 2014 2:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1586740
Posted Friday, June 27, 2014 2:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:56 AM
Points: 50, Visits: 212
Thank you Gail!!
Post #1586749
Posted Friday, June 27, 2014 3:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:56 AM
Points: 50, Visits: 212
Hi Gail,

One more question if I want multiple users to receive the email.how would I go about scripting it?
Post #1586752
Posted Friday, June 27, 2014 3:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:17 AM
Points: 2,434, Visits: 7,513
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 ;




Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1586756
Posted Friday, June 27, 2014 4:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1586778
Posted Friday, June 27, 2014 6:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:56 AM
Points: 50, Visits: 212
Thanks it worked
Post #1586793
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse