sql - Must pass parameter number 4 and subsequent parameter as '@name =value' -
i've got following stored procedure runs in ssms.
create store proc mytt select max(tablename) tablename, aco_code,acc_code, ctr_id,cte_id,usr_code, -- convert(datetime,convert(varchar,current_timestamp,112),112) cast(datepart(hour,current_timestamp) varchar(3))+':'+ cast(datepart(minute,current_timestamp) varchar(3))as [time] #ab ( select 'tm_aco_account_comment'as tablename, a.aco_code, a.acc_code, a.ctr_id, a.cte_id, a.usr_code tm_aco_account_comment with(nolock) union select 'tm_aco_account_comment'as tablename, b.aco_code, b.acc_code, b.ctr_id, b.cte_id, b.usr_code [172.17.14.77].[is_nd_blake].[dbo].[tm_aco_account_comment] b with(nolock) )zzz group aco_code, acc_code, ctr_id, cte_id, usr_code having count(*) = 1 order aco_code select * #ab drop table #ab but when try include procedure in query send out mail, following error:
must pass parameter number 4
my mail query looks this.
exec msdb.dbo.sp_send_dbmail @profile_name = 'dbmail', @recipients = 'mitch@domain.com', @query = 'exec sp,`mytt` @execute_query_database = 'mydb', @subject = 'work order count', @attach_query_result_as_file = 1 ;
in exec msdb.dbo.sp_send_dbmail command, seem have not closed string literal in line properly:
@query = 'exec sp,`mytt` it missing closing ', comma separate argument next one:
@query = 'exec sp,`mytt`', however, passed query looks bit strange. stored procedure called mytt , if meant called in query passing msdb.dbo.sp_send_dbmail, specified exec statement incorrectly. should this:
@query = 'exec mytt', although might idea specify database , schema too:
@query = 'exec yourdbname.yourschema.mytt', i have corrected name proper match 1 in definition exactly. if server's default collation case-insensitive (which likely), may unnecessary, not harm consistent in such matters.
Comments
Post a Comment