locked
"EXECUTE msdb.dbo.sp_send_dbmail" runs via SSMS 2008R2 via Query but fails as stored procedure job. RRS feed

  • Question

  • Environment: SQL Server 2008R2.

    Problem: "EXECUTE msdb.dbo.sp_send_dbmail" runs via SSMS 2008R2 via Query but fails as stored procedure job.

    The following query:

    -- Execute usp_MyStoredProcedure stored procedure in Master database.
     Declare
     @profilename sysname,
     @RecipientsProxy VARCHAR(MAX),
     @SendEmailOnly BIT,
     @Debug BIT
     
     SET @ProfileName = 'My Profile Name'
     SET @SendEmailOnly = 0
     SET @RecipientsProxy = 'me@mydomain.com'
     SET @Debug = 0
     
     EXECUTE Master.[dbo].[usp_MyStoredProcedure ] 
     @ProfileName, 
     @RecipientsProxy, 
     @SendEmailOnly, 
     @Debug

    runs just fine in SQL Server Management Studio when run as Query but when run as a SSQL Server Agenct Job it failed with the following error:

    Error formatting query, probably invalid parameters (SQLSTATE 42000][Error 22050] DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000][Error 2528]

    I did not include the entire stored procedure as it appears to run okay via SSMS query. I can include it if someone needs to see it. I did not write the stored procedure.

    I know that the stored procedure fails with "EXECUTE msdb.dbo.sp_send_dbmail". When this part of the stored procedure is commented out the SQL Server Agent job run just fine.

    I have created other jobs via the Maintenance Plan Wizard and have received emails when the job completes. DBMAIL appears to work in all cases with the exception of running a stored procedure as a SQL Server Agent Job with "EXECUTE msdb.dbo.sp_send_dbmail".

    Any help on resolving this issue will be greatly appreciated.

    Thanks,

    Howard

    Monday, December 26, 2011 10:09 PM

Answers

  • Hi Howard,

    The issue could be caused by:
    1. Incorrect parameter was passed in the stored procedure.
    2. The account for the agent service don’t have the sufficient privilege.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Tuesday, January 17, 2012 8:42 AM
    Thursday, December 29, 2011 2:48 AM

All replies

  • Hi Howard,

    Could you please post the whole stored procedure and the detailed version of your Windows server?


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, December 27, 2011 8:22 AM
  • Peja:

    The code was put up on the internet for anyone to use. It was stated that it ran on SQL Server 2005 but had not been run on SQL server 2008 which is my environment.

    I am running Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) .

    I belive that my problem may be related to:

    Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

    I am not certain what the proper procedure is to create the proper permissions.

    Here is the complete stored procedure.

    USE Master

    GO

    IF OBJECT_ID('dbo.usp_MyStoredProcoedure', 'P') IS NOT NULL DROP PROCEDURE dbo.usp_MyStoredProcoedure

    GO

    CREATE PROCEDURE dbo.usp_MyStoredProcoedure

    @ProfileName SYSNAME = NULL, -- Valid DB Mail Profile

    @RecipientsProxy VARCHAR(MAX) = NULL, -- semicolon-delimited list of e-mail addresses

    @SendEmailOnly BIT = 0, -- So we can send email of existing records in

    -- the [Master].[dbo].[DBCCCheckDBOutput] table

    @Debug BIT = 0 -- Show or hide verbose output

    AS


    SET NOCOUNT ON;

    DECLARE

    @BatchDate DATETIME,

    @DBName SYSNAME,

    @MachineName SYSNAME,

    @InstanceName SYSNAME,

    @PK TINYINT,

    @SQL VARCHAR(8000),

    @SQLServerInstanceName SYSNAME,

    @YES BIT,

    -- Error message vars

    @ErrorMessage NVARCHAR(4000),

    @ErrorNumber INT,

    @ErrorSeverity INT,

    @ErrorState INT,

    @ErrorLine INT,

    @ErrorProcedure NVARCHAR(200),

    -- DB Mail Variables

    @AttachQueryResultAsFileProxy BIT,

    @Body NVARCHAR(MAX),

    @BodyProxy NVARCHAR(MAX),

    @NO BIT,

    @Query VARCHAR(MAX),

    @QueryProxy VARCHAR(MAX),

    @Recipients VARCHAR(MAX),

    @Subject NVARCHAR(255),

    @SubjectProxy NVARCHAR(255)


    -- Ephemeral Work tables

    DECLARE @t TABLE

    (

    PK TINYINT IDENTITY,

    DBName SYSNAME

    )

    -- Defint Constants

    SET @YES = 1

    SET @NO = 0

    -- Set DBMail variables

    SET @AttachQueryResultAsFileProxy = @YES


    -- Variables

    SET @BatchDate = GETDATE()

    SET @InstanceName = CAST(SERVERPROPERTY('InstanceName') AS SYSNAME)

    SET @MachineName = CAST(SERVERPROPERTY('MachineName') AS SYSNAME)

    IF @InstanceName IS NULL

    SET @SQLServerInstanceName = @MachineName

    ELSE

    SET @SQLServerInstanceName = @MachineName + '\' + @InstanceName

    SET @SubjectProxy = @SQLServerInstanceName + ': DBCC CheckDB Results'

    SET @BodyProxy = 'Here are the summary DBCC CHECKDB output lines.'

    SET @QueryProxy = 'SET NOCOUNT ON;SELECT CAST(DBName AS VARCHAR(25)) AS DBName, DTStamp, LEFT(MessageText, 200) MessageText FROM Membership.dbo.DBCCCheckDBOutput WHERE IsSummaryLine = 1 AND DTSTamp = ( SELECT MAX(DTStamp) FROM Master.dbo.DBCCCheckDBOutput ) ORDER BY DBName;'


    -- 1) Parameter validation

    -- Parameter existence check:

    IF @ProfileName IS NULL

    BEGIN

    RAISERROR( 'Please provide a DB Mail Profile Name', 16, 1 )

    RETURN

    END

    IF @RecipientsProxy IS NULL

    BEGIN

    RAISERROR( 'Please provide a receipent email address', 16, 1 )

    RETURN

    END

    -- Profile name validity check

    BEGIN TRY

    EXEC msdb.dbo.sysmail_help_profile_sp @profile_name = @ProfileName

    END TRY

    BEGIN CATCH

    SELECT

    @ErrorNumber = ERROR_NUMBER(),

    @ErrorSeverity = ERROR_SEVERITY(),

    @ErrorState = ERROR_STATE(),

    @ErrorLine = ERROR_LINE(),

    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    RAISERROR

    (

    @ErrorMessage,

    @ErrorSeverity,

    1,

    @ErrorNumber, -- parameter: original error number.

    @ErrorSeverity, -- parameter: original error severity.

    @ErrorState, -- parameter: original error state.

    @ErrorProcedure, -- parameter: original error procedure name.

    @ErrorLine -- parameter: original error line number.

    );

    RETURN


    END CATCH


    IF @SendEmailOnly = @NO

    BEGIN

    -- 2) Create or maintain work table

    IF OBJECT_ID(N'Master.dbo.DBCCCheckDBOutput', N'U') IS NOT NULL

    TRUNCATE TABLE Master.dbo.DBCCCheckDBOutput

    ELSE

    BEGIN


    CREATE TABLE dbo.DBCCCheckDBOutput

    (

    PK INT IDENTITY CONSTRAINT PK_DBCCCheckDBOutput PRIMARY KEY,

    ServerName SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    DBName SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    Error INT NULL,

    [Level] INT NULL,

    [State] INT NULL,

    MessageText VARCHAR(7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    RepairLevel INT NULL,

    [Status] INT NULL,

    [DbID] INT NULL,

    ID INT NULL,

    IndID INT NULL,

    PartitionID INT NULL,

    AllocUnitID INT NULL,

    [File] INT NULL,

    Page INT NULL,

    Slot INT NULL,

    RefFile INT NULL,

    RefPage INT NULL,

    RefSlot INT NULL,

    Allocation INT NULL,

    DTStamp DATETIME NULL,

    IsSummaryLine BIT CONSTRAINT DF_DBCCCheckDBOutput_IsSummaryLine DEFAULT (0)

    )

    END

    -- 3) Create a loop to perform DBCC CHECKDB against all databases other than TempDB

    INSERT @t( DBName )

    SELECT name

    FROM master.sys.databases

    WHERE database_id <> 2

    WHILE EXISTS ( SELECT TOP 1 1 FROM @t )

    BEGIN


    SELECT TOP 1 @PK = PK, @DBName = DBName FROM @t

    SET @SQL = 'SET NOCOUNT ON;DBCC CHECKDB(' + @DBName + ') WITH TABLERESULTS, ALL_ERRORMSGS;'

    IF @Debug = @YES SELECT @SQL;

    INSERT INTO [Master].[dbo].[DBCCCheckDBOutput]

    (

    [Error]

    ,[Level]

    ,[State]

    ,[MessageText]

    ,[RepairLevel]

    ,[Status]

    ,[DbID]

    ,[ID]

    ,[IndID]

    ,[PartitionID]

    ,[AllocUnitID]

    ,[File]

    ,[Page]

    ,[Slot]

    ,[RefFile]

    ,[RefPage]

    ,[RefSlot]

    ,[Allocation]

    )

    EXEC (@SQL)


    -- Update BatchDate, ServerName, and DBName


    UPDATE Master.dbo.DBCCCheckDBOutput

    SET

    DTStamp = @BatchDate,

    ServerName = @SQLServerInstanceName,

    DBName = @DBName

    WHERE ServerName IS NULL AND DBName IS NULL

    -- Mark summary line for given database for email since we can't use local variables for the email session

    UPDATE Master.dbo.DBCCCheckDBOutput

    SET IsSummaryLine = @YES

    WHERE PK = ( SELECT MAX(PK) FROM Master.dbo.DBCCCheckDBOutput WHERE DBName = @DBName )

    -- Remove record from work table

    DELETE @t WHERE PK = @PK;


    END

    END

    -- 4) Send email having summary lines of DBCC CHECKDB

    BEGIN TRY

    IF @Debug = @YES SELECT @QueryProxy

    EXECUTE msdb.dbo.sp_send_dbmail

    @profile_name = @ProfileName,

    @Recipients = @RecipientsProxy,

    @Subject = @SubjectProxy,

    @Body = @BodyProxy,

    @body_format = 'TEXT',

    @query_result_header = @YES,

    @Query = @QueryProxy,

    @attach_query_result_as_file = @AttachQueryResultAsFileProxy,

    @query_result_width = 1000

    END TRY

    BEGIN CATCH

    SELECT

    @ErrorNumber = ERROR_NUMBER(),

    @ErrorSeverity = ERROR_SEVERITY(),

    @ErrorState = ERROR_STATE(),

    @ErrorLine = ERROR_LINE(),

    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    RAISERROR

    (

    @ErrorMessage,

    @ErrorSeverity,

    1,

    @ErrorNumber, -- parameter: original error number.

    @ErrorSeverity, -- parameter: original error severity.

    @ErrorState, -- parameter: original error state.

    @ErrorProcedure, -- parameter: original error procedure name.

    @ErrorLine -- parameter: original error line number.

    );


    END CATCH

    Tuesday, December 27, 2011 5:27 PM
  • @hmbdtx

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.  

    Thank you for your understanding and support


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, December 28, 2011 3:34 AM
  • Hi Howard,

    The issue could be caused by:
    1. Incorrect parameter was passed in the stored procedure.
    2. The account for the agent service don’t have the sufficient privilege.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Tuesday, January 17, 2012 8:42 AM
    Thursday, December 29, 2011 2:48 AM
  • Peja:

    Thank you for your response.

    1. Incorrect parameter was passed in the stored procedure.

    I assume that the parameters passed are correct as thay are in the stored procoedure and the stored procedure works correctly when run from a query in SSMS.


    2. The account for the agent service don’t have the sufficient privilege

    SQL Server Agent does send email notifications on maintenance jobs.

    if the problem is due to insufficient privileges I cannot determine how to correct this.

    Thanks

    Howard

    Tuesday, January 17, 2012 1:47 PM