xp_sendmail and quoted identifiers errors

[abuchan]

I am having the sqlagent run this job daily however the last query I am asking it to run is returning errors.

EXEC xp_sendmail @recipients ='abuchan'
@query = 'use Ticoon_Dump select max(price_date)[OFFBOOK PRICE DATE],(select count (*) from tc_ndi_pos_stk
where price_date = (select max(price_date)from tc_ndi_pos_stk)) [UPDATED] from tc_ndi_pos_stk
use Ticoon_Dump select max(price_date)[ONBOOK PRICE DATE],(select Count(*) from tc_security
where price_date=(select max(price_date) from tc_security))[UPDATED] from tc_security
use Ticoon SET QUOTED_IDENTIFIER OFF select max(UPDATED_DT)as [DATE],STATUS_CODE from ticoon.PROCESS
where STATUS_CODE="Complete" group by STATUS_CODE SET QUOTED_IDENTIFIER ON',
@subject = 'DAILY PRICES'



errors are

Executed as user: (user name). SQL Mail session started. [SQLSTATE 01000] (Message 17954) ODBC error 170 (42000) Line 5: Incorrect syntax near '20040206'. [SQLSTATE 01000] (Message 18014). The step succeeded.

 

[ClaireHCM]

Try to troubleshoot by the following ways

1.Remove "Use Ticoon_Dump", use full qualified name for selecting table

select Ticoon_Dump.dbo.max(price_date) as [OFFBOOK PRICE DATE],(select count (*) from Ticoon_Dump.dbo.tc_ndi_pos_stk where price_date = (select max(price_date)from Ticoon_Dump.dbo.tc_ndi_pos_stk))

2.Remove "Set quoted identifier off", replace each double quote with two single quote.

 

[abuchan]

Thank-you for your response Claire

However, the select commands run on their own through Query Analyzer. It is when the xp_sendmail is used


this works
EXEC xp_sendmail @recipients ='abuchan'
@query = 'use Ticoon_Dump select max(price_date)[OFFBOOK PRICE DATE],(select count (*) from tc_ndi_pos_stk
where price_date = (select max(price_date)from tc_ndi_pos_stk)) [UPDATED] from tc_ndi_pos_stk
use Ticoon_Dump select max(price_date)[ONBOOK PRICE DATE],(select Count(*) from tc_security
where price_date=(select max(price_date) from tc_security))[UPDATED] from tc_security',
@subject = 'DAILY PRICES'

this does not

EXEC xp_sendmail @recipients ='abuchan'
@query = 'use Ticoon SET QUOTED_IDENTIFIER OFF select max(UPDATED_DT)as [DATE],STATUS_CODE from ticoon.PROCESS
where STATUS_CODE="Complete" group by STATUS_CODE SET QUOTED_IDENTIFIER ON',
@subject = 'DAILY PRICES'

both statements on their own work in QA