Sql-server – How to send an email with embedded values

database-mailsql serversql-server-2008-r2stored-procedures

I need to do a stored procedure that sends an email when a value reaches 100 with this wording in the body: "100 daily file downloaded from the user:USERID" where USERID is the user that reached 100.

I tried this:

    EXEC msdb.dbo.sp_send_dbmail
        @recipients='MYMAIL',
        @body='100 Daily documents downloaded', 
        @subject ='100 Daily documents downloaded from user',
        @query = 'SELECT USERID FROM MYTABLE WHERE MYCOUNTER > ''100'''

I receive this error:

Messaggio 22050, livello 16, stato 1, riga 0 Error formatting query, probably invalid parameters Messaggio 14661, livello 16, stato 1,
procedura sp_send_dbmail, riga 504 Esecuzione della query non
riuscita: Msg 208, Level 16, State 1, Server MYSERVER, Line 1 Invalid
object name 'MYTABLE'.

Best Answer

You have to use dbname.schema.objectName e.g. dbname.dbo.MYTABLE in your tsql

EXEC msdb.dbo.sp_send_dbmail
        @recipients='MYMAIL',
        @body='100 Daily documents downloaded', 
        @subject ='100 Daily documents downloaded from user',
        @query = 'SELECT USERID FROM dbName.schema.MYTABLE WHERE MYCOUNTER > ''100'''

EDIT:

You only want to send email when there are 100 Daily documents downloaded from user, so you can add

if exists (
        select 1
        from dbName.schema.MYTABLE
        where MYCOUNTER > '100'
        )
begin
    exec msdb.dbo.sp_send_dbmail @recipients = 'MYMAIL'
        ,@body = '100 Daily documents downloaded'
        ,@subject = '100 Daily documents downloaded from user'
        ,@query = 'SELECT USERID FROM dbName.schema.MYTABLE WHERE MYCOUNTER > ''100'''
end
else
    print ' the user has not downloaded 100 docs yet'

Note: Why are you storing integers as chars ? e.g. 100 is stored as '100' ?

Related Question