CODEWITHSUNDEEP
sqlparametersout
user2408122
user2408122

Reputation: 1

Must pass parameter number 4 and subsequent parameter as '@name =value'

I've got the following stored procedure that runs perfectly in SSMS. 

Create Store Proc MyTT

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

But when I try to include the procedure in a query to send out mail, I get the following error:

Must pass parameter number 4

My Mail Query looks like this.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMail',
    @recipients = '[email protected]',
    @query = 'exec SP,`MYTT`
    @execute_query_database = 'MYDB',
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

Upvotes: 0

Views: 8763

Answers (1)

Andriy M
Andriy M

Reputation: 77717

In your EXEC msdb.dbo.sp_send_dbmail command, you seem to have not closed the string literal in this line properly:

@query = 'exec SP,`MYTT`

It is missing a closing ', as well as a comma to separate this argument from the next one:

@query = 'exec SP,`MYTT`',

However, the passed query itself looks a bit strange. Your stored procedure is called MyTT and if you meant it to be called in the query you are passing to msdb.dbo.sp_send_dbmail, you specified the exec statement incorrectly. It should probably be simply like this:

@query = 'exec MyTT',

Although it might be a good idea to specify the database and the schema too:

@query = 'exec YourDBName.YourSchema.MyTT',

I have slightly corrected the name proper to match the one in your definition exactly. If your server's default collation is case-insensitive (which is likely), that may be unnecessary, but it would not harm to be consistent in such matters.

Upvotes: 0

Related Questions