SQL Job (Send Mail) - error formatting query probably invalid parameters

Question

I know this has come up a lot in the past but none of the fixes I've Googled or found on here has worked for me in this instance.

I'm running a fairly standard SQL Server Agent Job as a Transact-SQL script with the follow details: (replaced some stuff as *** for my boss's sanity)

-- Start T-SQL

USE msdb
EXEC sp_send_dbmail
  @profile_name = 'MainProfile',
  @recipients = '[email protected]',
  @subject = 'T-SQL Query Result',
  @execute_query_database = 'test30',
  @query = 'SELECT ReferralReceivedDate,testRef,testcoMetadata.testcoRef,testcoMetadata.TimeSpentWithtester    
FROM TestCasesTable, TestcoMetadata 
WHERE testcasestable.CaseID = TestcoMetadata.CaseID AND AgencyName = [Test Injury] AND TestcoMetadata.TestcoRef IS NOT NULL AND TestcoRef <> '' 
order by ReferralReceivedDate desc',
@attach_query_result_as_file=1,
@query_attachment_filename = 'Results.csv',
@query_result_separator = ','

-- End T-SQL --

The query itself runs fine as a normal query with no issues. The owner of this job has been used on other jobs again with no problems. In the step properties the Database selected is the same one as that mentioned in the @execute line.

I have a feeling this is either falling over the way it's trying to create the csv or something to do with permissions with dbmail part. I'm only a part time DBA so this has now lost me and I need help.

Answer 1

Replace this:

TestcoRef <> '' 

With this:

TestcoRef <> ''''

You are creating dynamic sql, so you need to escape the single quotes.

Answer 2

So I never did get this working, but it turns out my boss already had something cooked up.

He had a stored procedure set up to run a batch file that was using an emailer exe to send the mail out, as its apparently better/more powerful than SQL mail. I simply copied his S.P and amended it to include my query.