SQL作业（发送邮件） - 错误格式化查询可能是无效参数

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.

我知道这在过去已经出现了很多,但是我在这里用Google搜索或找到的修复程序都不适用于我。

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)

我正在运行一个相当标准的SQL Server代理作业作为Transact-SQL脚本,其中包含以下详细信息:(替换了一些东西作为我的老板理智的***)

-- Start T-SQL

- 启动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 --

- 结束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.

查询本身作为普通查询运行正常,没有任何问题。这份工作的所有者再次被用于其他工作,没有任何问题。在步骤属性中,所选数据库与@execute行中提到的数据库相同。

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.

我有一种感觉,这要么是试图创建csv的方式,要么是与dbmail部分的权限有关。我只是兼职DBA所以现在已经失去了我,我需要帮助。

2 个解决方案

#1

Replace this:

TestcoRef <> ''

With this:

TestcoRef <> ''''

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

您正在创建动态SQL,因此您需要转义单引号。

#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.

他设置了一个存储过程来运行批处理文件,该文件使用emailer exe发送邮件,因为它显然比SQL邮件更好/更强大。我只是复制了他的S.P并修改它以包含我的查询。

#1