Error formatting query, probably invalid parameters

• Vikz

  SSC Enthusiast

  Points: 125

  More actions

  November 10, 2015 at 10:58 pm

  #150417

  I am getting below error

  Msg 22050, Level 16, State 1, Line 23

  Error formatting query, probably invalid parameters

  Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 518

  Query execution failed: Msg 102, Level 15, State 1, Server servername, Line 1

  Incorrect syntax near ','.

  while executing the procedure which is compiling without any error

  alter procedure checkemail as

  DECLARE @body NVARCHAR(MAX)

  DECLARE @xml NVARCHAR(MAX)

  DECLARE @strTempQuery nvarchar(max)

  set @strTempQuery = 'select * from LongRunningProcessHistory';

  SET @xml = 'CAST(( SELECT EXECUTIONTIME AS ''td'','',HOST_NAME AS ''td'','',

  Job_Name AS ''td'','', Job_Name AS ''td'','',

  Owner AS ''td'','',

  Command AS ''td'','',

  Description AS ''td'','',

  Last_Start AS ''td'','',

  Next_Start AS ''td'','',

  Running_Time AS ''td'',''

  FROM LongRunningProcessHistory

  FOR XML PATH(''tr''), ELEMENTS ) AS NVARCHAR(MAX))'

  set @body = @xml + @strTempQuery

  EXEC msdb.dbo.sp_send_dbmail

  @recipients = 'myemail@hotmail.com',

  @subject = 'subject_test',

  @query = @body

  Any suggestions, what I am doing wrong.