Issue with using STUFF

• RCRock

  Right there with Babe

  Points: 718

  More actions

  May 13, 2018 at 9:12 am

  #330814

  HI,
  I am executing below query in query window then it works fine and get the result for Dept field into single row in between comma

  SELECT lname as LastName, FirstName, convert(varchar(100), email) as email,
  convert(varchar, MAX(LoginDate)) as LoginDate,
  Dept=STUFF 
  ( 
    ( 
    SELECT DISTINCT '', ''+ CAST(TD.DeptName AS VARCHAR(MAX)) 
    FROM TBLEMP UR inner join TBLDEPT TD 
    oN UR.ROLE_ID=TD.ID and UR.USER_ID=max(u.id)
    FOR XMl PATH('') 
    ),1,1,'' 
  )
  from tblP p inner join
  tblU u ON p.user_id = u.id
  where p.status = 1
  and p.user_id = u.id
  Group by lastname, firstname,email
  order by lastname, firstname

  But If I am using below query I am getting below error while executing sp_send_dbmail

  Error formatting query, probably invalid parameters
  Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 117
  Query execution failed: Msg 156, Level 15, State 1
  Incorrect syntax near the keyword 'from'.

  --declare @ sqll nvarchar(max)    
  declare @tab char(1) = char(9),
              @recipient varchar(255) = 'xyz@example.com',            
              @sql nvarchar(4000),
              @sdate1 char(11) = format( getdate(), 'dd-MMM-yyyy'),
              @sdate2 char(8) = format( getdate(), 'yyyyMMdd'),
              @sub varchar(50),
              @file varchar(50)

      
      
      set @sql = 'SELECT lname as LastName, FirstName, convert(varchar(100), email) as email,
  convert(varchar, MAX(LoginDate)) as LoginDate,
  Dept=STUFF 
  ( 
    ( 
    SELECT DISTINCT '', ''+ CAST(TD.DeptName AS VARCHAR(MAX)) 
    FROM TBLEMP UR inner join TBLDEPT TD 
    oN UR.ROLE_ID=TD.ID and UR.USER_ID=max(u.id)
    FOR XMl PATH('') 
    ),1,1,'' 
  )
  from tblP p inner join
  tblU u ON p.user_id = u.id
  where p.status = 1
  and p.user_id = u.id
  Group by lastname, firstname,email
  order by lastname, firstname '

      set @sub = 'LIST1 - ' + @sdate1
      set @file = 'EX_L1_' + @sdate2 + '.txt'
      exec msdb..sp_send_dbmail @recipients = @recipient,
                          @subject = @sub,
                          @body = '',
                          @query = @sql,
                          @attach_query_result_as_file = 1,
                          @query_attachment_filename = @file,
                          @query_result_separator = @tab,
                           @query_result_width = 500

                         

          
  GO

  Can someone please help here? Issue with seems to be whie using DISTINCT '', ''+ CAST(TD.DeptName AS VARCHAR(MAX))

• Thom A

  SSC Guru

  Points: 99126

  More actions

  May 13, 2018 at 9:44 am

  #1990287

  You need to escape all of your single quotes. For example FOR XML PATH('') should be FOR XML PATH('''').

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk