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 = 'test@test.co.uk',
  @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.

Have you enable the use of `sp_send_dbmail`? It's disabled by default and you have to enable it. — jradich1234, Jun 23 '15 at 15:58
It should be, we are using it for another job on another DB which seems to be working ok. I should probably run a default test on just the send mail part with no query to check though. Thanks. — David T, Jun 23 '15 at 16:16

score 1 · Answer 1 · answered Jun 23 '15 at 15:58

1

Replace this:

TestcoRef <> ''

With this:

TestcoRef <> ''''

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

answered Jun 23 '15 at 15:58

Tab Alleman

31,483
7
36
52

Didn't help I'm afraid, but I'll check over the rest of it just to be sure. – David T Jun 23 '15 at 16:18

score 0 · Answer 2 · answered Jun 29 '15 at 09:00

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.

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

2 Answers2