sql - 将查询变量传递给MSDB.dbo.sp_Send_DBMail

Question

我已经写了一个SQL查询。但这给了我一个错误。

DECLARE @Delimiter Char(1)
SET @Delimiter = CHAR(9)
EXEC MSDB.dbo.sp_Send_DBMail
@profile_name = 'K2MailSetup',
@Recipients='test@xyz.com',
@Subject='Extraction Report',
@Body='Hi',
@Query='set nocount on;
Select Coalesce(replace(replace(A.[type], char(10), ''''), char(13), ''''),'''') as Type,
 try_convert(xml, col).value(''(/collection/object/fields/field/value)[1]'', ''varchar(3)'') 
from (select col= Coalesce(replace(replace(A.[business_line], char(10), ''''), char(13), ''''),'''')
    FROM [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS] A'

错误状态

Msg 102, Level 15, State 1, Line
Incorrect syntax near '/'.

Answer 1

我在这里发现了2个问题。

1. your subquery is not properly closed
2. your quotes are not property terminated

Declare @Query as nvarchar(1000) 
Set @Query='set nocount on; 
            set QUOTED_IDENTIFIER on;
            Select try_convert(xml, Coalesce(replace(replace(A.[business_line], char(10), '''''''')
                        , char(13), ''''''''),''''''''))
                        .value(''(/collection/object/fields/field/value)[1]'', ''varchar(3)'') 
                , Coalesce(replace(replace(A.[type], char(10), ''''''''), char(13), ''''''''),'''''''') as [Type]
                from [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS] A;'