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

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 = '[e-mail]'
 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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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