Post reply

Why msdb.dbo.sp_send_dbmail is not working for me? Thanks.

  • January 12, 2012 at 10:11 am

    #254875

    This is very confusing! why I get error by running this:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SQL 2005 Email',

    @recipients = 'abc@def.com',

    @query = 'SELECT COUNT(*) FROM ISCSGSWEBUP01.dbo.ISC_OrgUnitManagers' ,

    @subject = 'Work Order Count',

    @attach_query_result_as_file = 1 ;

    Error:

    Msg 22050, Level 16, State 1, Line 0

    Error formatting query, probably invalid parameters

    Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495

    Query execution failed: Msg 208, Level 16, State 1, Server ISCSGSWEBUP01, Line 1

    Invalid object name 'ISCSGSWEBUP01.dbo.ISC_OrgUnitManagers'.

  • January 12, 2012 at 10:15 am

    #1432740

    so you have a linked server for ISCSGSWEBUP01, right?

    did you specify which credentials should be used when accessing it?

    could it be that YOU, as a sysadmin, have matching credentials on the link, but the user calling the mail does not?

    you might need to add a remote login, perhaps?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 12, 2012 at 10:19 am

    #1432744

    It's on the same server.

  • January 12, 2012 at 10:20 am

    #1432745

    removing the server name and dbo is no use

  • January 12, 2012 at 10:21 am

    #1432750

    halifaxdal (1/12/2012)

    It's on the same server.

    it might still be a permissions issue, though.

    if im a user and only have access to production.dbo.Tables, and no access to the ISCSGSWEBUP01 database (which is required due to the dynamic sql) i cannot call the code or a procedure which calls the code.

    which user gets the error calling the mail?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 12, 2012 at 10:23 am

    #1432753

    I am the admin of the server/db

  • January 12, 2012 at 10:28 am

    #1432757

    halifaxdal (1/12/2012)

    I am the admin of the server/db

    ok, but where is the code running from? SSMS directly, or part of a job? wouldn't a job might be using a different login/user than yourself?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 12, 2012 at 10:41 am

    #1432764

    As the first step, i'm running this as a query n still fail

  • January 12, 2012 at 11:37 am

    #1432817

    Cause identified, my bad. The query is not correct.

    I found this by running the query alone, and immediately realized the object part should be: server.database.dbo.table, I missed database there, when I tested it by removing the prefix, it also failed because the send mail command is executed in msdb database.

    Thank you Lowell for your time anyway, it is really appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply