Pass query variable to a MSDB.dbo.sp_Send_DBMail

Question

I have written a SQL query. But its giving me an error.

DECLARE @Delimiter Char(1)
SET @Delimiter = CHAR(9)
EXEC MSDB.dbo.sp_Send_DBMail
@profile_name = 'K2MailSetup',
@Recipients='[email protected]',
@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'

Error states

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

I am bad in these single quotes. Can you help me with complete query @DaleBurrell — Nikhil
– Nikhil, Commented Oct 9, 2019 at 6:39
I did so as told by you.. But it still gives me an error in my original query — Nikhil
– Nikhil, Commented Oct 9, 2019 at 6:55
Msg 22050, Level 16, State 1, Line 0 Error formatting query, probably invalid parameters Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517 Query execution failed: Msg 1033, Level 15, State 1, Server MYKULK2DB01Q\MSSQLSTG, Line 13 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. — Nikhil
– Nikhil, Commented Oct 9, 2019 at 7:01
@DaleBurrell See my originial query in the stackoverflow.com/q/58298194/12118671 — Nikhil
– Nikhil, Commented Oct 9, 2019 at 7:01

Ed Bangga · Accepted Answer · 2019-10-09 07:58:55Z

1

I found 2 issues here.

your subquery is not properly closed

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;'

edited Oct 9, 2019 at 7:58

answered Oct 9, 2019 at 6:34

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2019-10-09 08:02:44Z

The trick to dynamic SQL is to make sure the SQL works before trying to make it dynamic.

Your query needs multiple tweaks before it works as a basic SQL 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([business_line], char(10), ''''), char(13), ''''),''''), Type
    FROM [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS]
) A;

Then and only then, convert to dynamic SQL by doubling all single quotes:

declare @Query as nvarchar(1000) = '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([business_line], char(10), ''''), char(13), ''''),''''), Type
    FROM [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS]
) A;'

exec(@Query);

Collectives™ on Stack Overflow

Pass query variable to a MSDB.dbo.sp_Send_DBMail

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related