0

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

  • 1
    Can you edit your post, please, and add what code you've tried so far and what specific error messages you receive. Also add a tag to show which DBMS you use. (You have sql, did you mean SQL Server?) – Michael Green Aug 18 '15 at 10:43
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.