xp_sendmail sometime work sometime don't in stored prcedure

xp_sendmail sometime work sometime don't in stored prcedure

Post by YW5kcmV » Thu, 23 Dec 2004 11:43:01


I've coded master.dbo.xp_sendmail in my stored procedure. however, it
sometime failed but when run again it pass? Do anyone have this problem too?
How can i resolve this?
 
 
 

xp_sendmail sometime work sometime don't in stored prcedure

Post by Mary Bra » Thu, 23 Dec 2004 15:34:36

Could be a permission issue :
Execute permissions for xp_sendmail default to members of the db_owner fixed
database role in the master database and members of the sysadmin fixed
server role, but can be granted to other users

[Books-On-Line]
Is it failing when someone outside these fixed roles runs it?

--

Mary Bray [SQL Server MVP]
Please reply only to newsgroups

 
 
 

xp_sendmail sometime work sometime don't in stored prcedure

Post by YW5kcmV » Thu, 23 Dec 2004 15:47:02

my xp_sendmail is granted execute rights to public, so there isn't any
permission problem. The stored procedure is run by the same group of users.
eg. he execute the procedure now, it failed. Then he execute it immediate
again and it'll pass. It happens almost everyday. it seems like everday the
first execute will fail, but subsequent execute will pass.
 
 
 

xp_sendmail sometime work sometime don't in stored prcedure

Post by Mary Bra » Fri, 24 Dec 2004 07:48:44

I'd be inclinded to start a trace with Profiler and capture the error and
appropriate stored proc events to see if you can see what is happening. Is
the proc being called from a front end application?

--

Mary Bray [SQL Server MVP]
Please reply only to newsgroups
 
 
 

xp_sendmail sometime work sometime don't in stored prcedure

Post by YW5kcmV » Thu, 30 Dec 2004 14:15:03

Hi Mary,

The stored procedure is being executed using dts job scheduler. Infact we
try execute directly using query analyzer also encounter this problem.
Everyday, first time execute will fail, subsequent execute will pass. It
happens for all my servers that have mssql installed.