Using a variable in my code crashes my sp_send_dbmail code

Question

So here's my code that doesn't work:

SET QUOTED_IDENTIFIER ON

DECLARE 
    @tab char(1) = CHAR(9),
    @arg VARCHAR(MAX) = 'N2'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Company Profile',
    @recipients = 'randomemail.gmail.com',
    @query = 'SET NOCOUNT ON
              SELECT
                  e.EmplName,
                  FORMAT(SUM(t.ManHrs), @arg) AS [Hrs Logged to Jobs]
              FROM EmplCode e JOIN TimeTicketDet t ON e.EmplCode = t.EmplCode
              WHERE CAST(t.TicketDate AS DATE) = CAST(GETDATE() AS DATE)
              AND t.WorkCntr <> 50
              GROUP BY e.EmplName, t.WorkCntr
              HAVING SUM(t.ManHrs) < 6
              ORDER BY SUM(t.ManHrs)',
    @subject = 'Hello',
    @query_result_separator = @tab,
    @execute_query_database = 'Company DB';

I get the following error when I run it:

Failed to initialize sqlcmd library with error number -2147467259.. If I remove the @arg variable inside @query and instead use

CAST(SUM(t.ManHrs) AS FLOAT)

It works fine, even leaving the declared variable, so I suppose I could do that, but I'm just wondering what the error message even means. Any time I have quotes inside my @query argument, I'm going to have to use variables right? So what's going on? And how can I prevent this in the future? Thanks

What if you try ...FORMAT(SUM(t.ManHrs),' + @arg +')... ?

BJones
– BJones

2018-04-06 20:12:03 +00:00
Commented Apr 6, 2018 at 20:12 — BJones
– BJones, Commented Apr 6, 2018 at 20:12
Can't use ' ' inside @query

user1452574
– user1452574

2018-04-06 20:13:45 +00:00
Commented Apr 6, 2018 at 20:13 — user1452574
– user1452574, Commented Apr 6, 2018 at 20:13

ASP · Accepted Answer · 2018-04-07 14:25:53Z

1

--Build your query outside and then send mail as below

SET QUOTED_IDENTIFIER ON

CREATE TABLE Table1 (id int, EmplName varchar(10), ManHrs int)
insert into table1 values (1, 'xxxxx', 8)
insert into table1 values (2, 'xxxxx', 4)
insert into table1 values (3, 'xxxxx', 6)

insert into table1 values (4, 'YYYYY', 8)
insert into table1 values (5, 'YYYYY', 4)
insert into table1 values (6, 'YYYYY', 6)

DECLARE 
    @tab char(1) = CHAR(9)
    ,@arg VARCHAR(MAX) = 'N2'
    ,@SQLquery NVARCHAR(1000)

SET @SQLquery = 'SET NOCOUNT ON
SELECT
    e.EmplName,
    FORMAT(SUM(e.ManHrs),''' + @arg + ''') AS [Hrs Logged to Jobs]
FROM table1 e 
GROUP BY e.EmplName
HAVING SUM(e.ManHrs) > 6
ORDER BY SUM(e.ManHrs)'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Profile Name',
    @recipients = 'randomemail.gmail.com',
    @query = @SQLquery,
    @subject = 'Hello',
    @query_result_separator = @tab,
    @execute_query_database = 'DB Name';

answered Apr 7, 2018 at 14:25

ASP

6664 silver badges9 bronze badges

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

1 Comment

user1452574 Over a year ago

Yeah, building the query outside seems like the best thing to do, thanks

Lukasz Szozda · Accepted Answer · 2018-04-06 20:13:51Z

0

From sp_send_dbmail:

[ @query= ] 'query'

Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

FORMAT(SUM(t.ManHrs), @arg)  -- @arg variable is a problem

So your option is to hardcode value or concatenate query string.

answered Apr 6, 2018 at 20:13

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

Using a variable in my code crashes my sp_send_dbmail code

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related