CODEWITHSUNDEEP
sqlsql-servercsv
jbalter
jbalter

Reputation: 59

Send results as csv file

I am trying to get SQL Server Agent to send this as an email

All I get is errors. It works without the @query parameter but I don't see the issue.

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'e-automate mail',
     @recipients = '[email protected]',
     @subject = 'on hold customers',
     @body='Customers on Hold in E-automate',
     @query = 'SELECT CC.CustomerID AS 'E-auto Number',
                      CC.TextVal AS 'Autotask Number',
                      ARC.CustomerName
                  FROM [CoCopeco].[dbo].[ARCustomerCustomProperties] CC
                  INNER JOIN [CoCopeco].[dbo].[ARCustomers] ARC  ON ARC.CustomerID = CC.CustomerID
                  WHERE ShAttributeID = '2043' 
                    AND ARC.OnHoldCodeID IS NOT NULL
                    AND DATALENGTH (CC.TextVal) > 0 ' ,
    @attach_query_result_as_file=1,
    @query_attachment_filename = 'Results.csv',
    @query_result_separator = ','

Upvotes: 1

Views: 71

Answers (1)

VKarthik
VKarthik

Reputation: 1429

You would need to replace single quotes with additional single quotes within the @Query parameter tab i.e. replacing ' with ''.

Put the @Query value as shown below - 

@query = 'SELECT CC.CustomerID AS ''E-auto Number'',
              CC.TextVal AS ''Autotask Number'',
              ARC.CustomerName
          FROM [CoCopeco].[dbo].[ARCustomerCustomProperties] CC
          INNER JOIN [CoCopeco].[dbo].[ARCustomers] ARC  ON ARC.CustomerID = CC.CustomerID
          WHERE ShAttributeID = ''2043'' 
            AND ARC.OnHoldCodeID IS NOT NULL
            AND DATALENGTH (CC.TextVal) > 0 '

Upvotes: 1

Related Questions