xp_readmail

Question

Post reply

xp_readmail

vsjayashri

Right there with Babe

Points: 797
More actions
February 11, 2010 at 12:18 am

#216632

Hi All
I have configured sql mail on my server, xp_sendmail is working fine and sends email to receipient daily after report generation.
Now I need to use xp_readmail also,
I have googled and got the code which gets the message id from findtextmsg and also used xp_readmail.
Here is the code
SET nocount ON
USE master
DECLARE @originator varchar(255), @subj varchar(255)
DECLARE @msg varchar(255), @sql nvarchar(2000)
DECLARE @status int, @msgid varchar(255)
SET @status = 0
SET @msgid = ''
SET @msg=''
EXEC @status = master.dbo.xp_findnextmsg @msg_id = @msgid OUTPUT
IF @msgid IS NULL RETURN
EXEC @status = master.dbo.xp_readmail @msg_id = @msgid,
@originator = @originator OUTPUT,
@subject = @subj OUTPUT,
@message = @msg OUTPUT,
@peek = 'FALSE',
@suppress_attach = 'TRUE'
PRINT @msg
When I ran this query I get the error as follows -
Msg 17985, Level 16, State 1, Line 0
xp_findnextmsg: Procedure expects parameter @user, which was not supplied.
Msg 17985, Level 16, State 1, Line 0
xp_readmail: Procedure expects parameter @user, which was not supplied.
Can you please solve the above issue? Thanks in advance.
Jayasri

Viewing 14 posts - 1 through 13 (of 13 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I believe that you hve to enable those Extended SP's via code or the Surface area configuration'

Msg 15281, Level 16, State 1, Procedure xp_findnextmsg, Line 1

SQL Server blocked access to procedure 'sys.xp_findnextmsg' of component 'SQL Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

Msg 15281, Level 16, State 1, Procedure xp_readmail, Line 1

SQL Server blocked access to procedure 'sys.xp_readmail' of component 'SQL Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

Check this link written by the MAK, he is good:

http://www.databasejournal.com/features/mssql/article.php/3626056/Database-Mail-in-SQL-Server-2005.htm

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

While you can use SQL Mail in SQL 2005 and 2008 I REALLY must advise against it. The major issues with SQL Mail is the requirement for it to use MAPI. You should really be using Database Mail. You do lose the ability to use xp_readmail but there are ways around that, however, with that said, I've used that sproc and I would question what you are truly trying to accomplish and whether in SQL is the best place to do this.

From my perspective I have had nothing but pain with SQL Mail, it has caused me to have many server reboots. I just hate it.

CEWII

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I'm not a fan of SQL Mail either and I never worked anywhere where we were allowed to use it.

SMTP was the standard and I used ActiveX Scripts to accomplish this.

The introduction of the Database Mail Task is a great feature.

Those extended Stored Procedures are depreciated and I would not recommend using them since they will become obsolete.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Welsh Corgi (2/17/2010)
I'm not a fan of SQL Mail either and I never worked anywhere where we were allowed to use it.
SMTP was the standard and I used ActiveX Scripts to accomplish this.
The introduction of the Database Mail Task is a great feature.
Those extended Stored Procedures are depreciated and I would not recommend using them since they will become obsolete.

I have never been allowed (heart breaking I know) to use it on a SQL 2005/2008 box, only 2000 and only because there was no choice..

I have done it with SQLCLR and in script in SSIS.

I met the guy who wrote it and shook his hand.. Love it! It even uses service broker..

As I said, I can't stress enough not using those extended sprocs.. ESPECIALLY in 2008, you are just heading for heartbreak..

CEWII

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I found the Database Mail Task to be limited and the SQLCLR and Script task to be more robust.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Agreed. But I will say that the Database mail task in SSIS is EASY to use and requires basically no knowledge other than the SMTP server address..

CEWII

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Excuse me but I believe that I said the same thing?

You are very limited with this task.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Robert Davis One Orange Chip Points: 28027 More actions · Answer 8

The problem is clearly defined in the error message. You're not passing in the @user parameter which is a required parameter.

xp_findnextmsg: Procedure expects parameter @user, which was not supplied.

xp_readmail: Procedure expects parameter @user, which was not supplied.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

Welsh Corgi (2/18/2010)
Excuse me but I believe that I said the same thing?
You are very limited with this task.

I wasn't debating it, I was only expounding on your point.

CEWII

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

----------------------------------------------------------------------------------

Elliott W

I have done it with SQLCLR and in script in SSIS.

I met the guy who wrote it and shook his hand.. Love it! It even uses service broker..:cool:

----------------------------------------------------------------------------------

Elliot, would you mind sharing your version of the code?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 11

Code to send mail for SSIS:

Dim _oSMTPClient As New SmtpClient("smtpmailhost.yourcompany.com")

Dim _oMailMsg As New MailMessage()

_oMailMsg.From = New MailAddress("someemailaddress@yourcompany.com")

_oMailMsg.To.Add("someotheremailaddress@yourcompany.com")

_oMailMsg.Subject = "The Subject"

_oMailMsg.Body = "Message Body"

_oSMTPClient.Send(_oMailMsg)

Don't forget:

Imports System.Net.Mail

Can't find the SQLCLR code..

CEWII

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Thank you Sir. If you stumble upon the SQLCLR Code someday I would appreciate if you could post it.

Thanks again.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 13

I can't remember why we did that with SQLCLR, there was something we couldn't do, or do easily with Database Mail. I'm still not sure if it was the right answer to do it that way.

I'm not sure if it used System.Net.Mail or I used something from codeplex/gotdotnet.. The issue is always using assemblies that can be referenced in SQLCLR..

CEWII