sp_send_dbmail error when executed in loop

Question

Let's say I have the table TEST that looks like this:

    ID  |  Value |  FK_ForeignKey
    ---------------------------
    1   |  Foo   |  3
    2   |  Bar   |  4

and another table FK:

    ID  |  Email
    -------------
    3   |  Foo@bar.com
    4   |  Bar@foo.com

I have the following code:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'test'
, @recipients = @email
, @subject = 'test'
, @body= 'test'
, @query = @query
, @query_result_separator = ' '
, @query_result_no_padding = 1;

I need to send some values based on a FK to certain e-mail addresses.

If I give

@query = 
  'SELECT Value FROM TEST WHERE FK_ForeignKey = 3'

and run it, it correctly sends the e-mail with the results.

If I put it in a while loop and give

@query = 
  'SELECT Value FROM TEST WHERE FK_ForeignKey = @id'

with @id going from MIN(ID) to MAX(ID) of table FK, I have an error.

The error message is:

[SQLSTATE 42000] (Error 22050)  Failed to initialize sqlcmd library with error number -2147467259.

I have tried both a WHILE loop and a CURSOR and I have the same issue.

I have also tried hardcoding @email to receive multiple e-mails on a single address based on how many IDs there are in FK.

I have browsed online and seems like everyone points to security permissions but I am not running this from SQLAgent, but from the SSMS Query window.

TLDR: Sending e-mail from SQL Server works only if not in a loop.

Found the issue shortly after re-reading what I posted. I guess it paid off to write my issue. I'm embarassed how long it took me to notice this novice mistake.

@query = 
  'SELECT Value FROM TEST WHERE FK_ForeignKey = @id'

should've been

@query = 
  'SELECT Value FROM TEST WHERE FK_ForeignKey =' + CONVERT(varchar(1), @id)

can you share the whole code ? and can you please try sending mail with `@append_query_error = 1` — Derviş Kayımbaşıoğlu, Jan 23 '19 at 10:29
Possible duplicate of [sp\_send\_dbmail executed from job fails with query result attached as file](https://stackoverflow.com/questions/15112849/sp-send-dbmail-executed-from-job-fails-with-query-result-attached-as-file) — Derviş Kayımbaşıoğlu, Jan 23 '19 at 10:32
Seems like re-reading what I posted gave me the solution to my question. Will update with solution. — AlexC, Jan 23 '19 at 10:34

JMabee · Accepted Answer · 2019-01-23T10:48:15.777

0

I would imagine the query has a problem with @id given that you are sending a string.

You should try:

@query = 
  'SELECT Value FROM TEST WHERE FK_ForeignKey = ' + CAST(@id as varchar(5))

edited Jan 23 '19 at 10:48

answered Jan 23 '19 at 10:45

JMabee

2,230
2
9
13

This was exactly it. Noticed it shortly after posting.Thank you. – AlexC Jan 23 '19 at 10:46

sp_send_dbmail error when executed in loop

1 Answers1