database mail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

database mail

Author

Topic

newdba
Starting Member

30 Posts

Posted - 2011-06-16 : 09:50:11

I am running a stored procedure that sends out an email. I have run the script before however now im getting this error...the only diffrence is this is on a diffrent server

Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid

here is the script

USE [EZPay]
GO

/****** Object: StoredProcedure [dbo].[Send_Double Charge_Error_Report] Script Date: 06/16/2011 08:58:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[Send_Double Charge_Error_Report1] as

EXEC msdb.dbo.sp_send_dbmail
@profile_name='SQLAlerts',
@recipients = 'dtrivedi@musicarts.com',
@blind_copy_recipients = 'dtrivedi@musicarts.com',
@body='Test Email',
@subject = 'POS Double charge errors'
DECLARE @query nvarchar(1000)
set @query='SELECT [LOCATION],[TRANS_DATE],[TRANS_TIME],
[TRANS_AMOUNT],[AUTH_CODE],[ACCT_NUM],[INTRN_SEQ_NUM],[FIX_COMMAND]
FROM [EZPay].[dbo].[COMBINED_ERRORS]'

DECLARE @body1 varchar(1000)
DECLARE @RecCount int

EXECUTE [EZPay].[dbo].[sp_Import_TBLAUTH10]

IF @RecCount>0
BEGIN
set @body1 = 'Test Email'
EXEC msdb.dbo.sp_send_dbmail @recipients='dtrivedi@musicarts.com',
@subject = 'POS Double Charge Errors',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT [LOCATION],[TRANS_DATE],[TRANS_TIME],[TRANS_AMOUNT],[AUTH_CODE],[ACCT_NUM],[INTRN_SEQ_NUM],[FIX_COMMAND] FROM [EZPay].[dbo].[COMBINED_ERRORS]'
END

GO

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-16 : 09:52:29

On this server there is no profile named 'SQLAlerts' but on the other server it exists.
Ask the one who has set up the profile to help you doing this on the new server too.

No, you're never too old to Yak'n'Roll if you're too young to die.

newdba
Starting Member

30 Posts

Posted - 2011-06-16 : 09:56:28

is there a way taht i can set it up my self?

newdba
Starting Member

30 Posts

Posted - 2011-06-16 : 09:57:59

actually i did this and it added it i'm going to try running the query again

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQL Alerts',
@description = 'Profile used for administrative mail.' ;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-16 : 10:04:46

quote:
Originally posted by newdba

is there a way taht i can set it up my self?

Sorry but I never did it by myself so I have no clue.

No, you're never too old to Yak'n'Roll if you're too young to die.

newdba
Starting Member

30 Posts

Posted - 2011-06-16 : 10:16:10

oh ok
i did it. it added the profile however i'm still getting this error ...does SQL Server need to be restarted? or sql server AGENT?

Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid

newdba
Starting Member

30 Posts

Posted - 2011-06-16 : 13:24:21

so i got it work but it's not sending out an email....do i have it wrong somewhere?

USE [EZPay]
GO

/****** Object: StoredProcedure [dbo].[Send_Double Charge_Error_Report1] Script Date: 06/16/2011 13:01:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Send_Double Charge_Error_Report1] as

DECLARE @RecCount int
EXECUTE [EZPay].[dbo].[sp_Import_TBLAUTH10]
IF @RecCount>0
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLAlert',
@recipients = 'dtrivedi@musicarts.com',
@query = 'SELECT [LOCATION],[TRANS_DATE],[TRANS_TIME],
[TRANS_AMOUNT],[AUTH_CODE],[ACCT_NUM],[INTRN_SEQ_NUM],[FIX_COMMAND]
FROM [EZPay].[dbo].[COMBINED_ERRORS]',
@subject = 'POS Double charge errors',
@attach_query_result_as_file = 1 ;

END

GO

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-06-17 : 01:40:14

did u check the error logs?what do you see in database mail?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources