CODEWITHSUNDEEP
sqlsql-server
GihanDB
GihanDB

Reputation: 631

Sending Multiple DB mail using curser

I have a Table which have ID, Subject, Result and Email columns. I want to send Subject and result to user. I try to use bellow code to do this 

Declare @ID INT

Declare Agent Cursor for
SELECT ID FROM myTable
GROUP BY ID


OPEN Agent 
FETCH NEXT FROM Agent INTO @ID

While (@@Fetch_Status = 0)
Begin

DECLARE @email NVARCHAR(MAX)
SET @email = (SELECT email FROM myTable
             WHERE ID = @ID)

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT ID, Subject, Result FROM myTable  WHERE ID = ''@ID'''
--print EXEC @query
EXEC msdb.dbo.sp_send_dbmail 
    @profile_name='Reports',
    @recipients='[email protected]',
    @subject = 'Results',
    @body = '  ',
    @body_format = 'HTML',
    @query = @query,
    @query_result_header = 0,
    @exclude_query_output = 1,
    @append_query_error = 1,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'qry.txt',
    @query_result_no_padding = 1


FETCH NEXT FROM Agent INTO @ID
end
CLOSE Agent
DEALLOCATE Agent

However when I execute this i do not get any error. Only get a message telling

Command(s) completed successfully

I couldn't get the

Mail (Id: 16) queued.

message which should normally come with this kind of executions.

Where is the bug in this script?

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Presumably, you want the query to contain the id. I think you want:

SET @query = REPLACE('SELECT ID, Subject, Result FROM myTable  WHERE ID = @ID', '@ID', @ID);

As explained in the documentation:

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.

Upvotes: 0

Dave Brown
Dave Brown

Reputation: 500

You're treating @ID as both numeric and a string (I have no idea what it actually IS in "myTable") - and there's a possibility of a data conversion error on the first execution of sp_send_dbmail.

SET @email = (SELECT email FROM myTable
             WHERE ID = @ID)

SET @query = 'SELECT ID, Subject, Result FROM myTable  WHERE ID = ''@ID'''

Try changing the above to treat @ID as a numeric.

SET @query = 'SELECT ID, Subject, Result FROM myTable  WHERE ID = @ID'

Upvotes: 1

Related Questions