如何使用sql server 2005中的存储过程从gmail帐户邮寄 [英] How to mail from gmail account using stored procedure in sql server 2005

问题描述

嗨...

我尝试从SQL Server 2005中的存储过程发送邮件。我创建了ManageStaff_SP存储过程来插入/更新/删除/获取人员详细信息和另一个存储过程 SendMail_SP发送邮件。我在ManageStaff_SP中调用SendMail_SP。一旦员工详细信息插入到DB表中，我必须将他们的LOGIN ID和PASSWORD发送给员工emailid。



SendMail_SP工作正常，我将发送邮件给要发送电子邮件ID。

问题是我无法在ManageStaff_SP中调用SendMail_Sp。

我得到错误 -

Hi...
Im trying send mail from Stored procedure in SQL server 2005. I have created "ManageStaff_SP" stored procedure to insert/update/delete/fetch staff details and another stored procedure "SendMail_SP" to send mail. Im invoking "SendMail_SP" within "ManageStaff_SP" . Once staff details get inserted into DB table , i have to send their LOGIN ID and PASSWORD to staff emailid.

"SendMail_SP" working fine, i will send mail to "To" email ID.
The problem is im not getting how to invoke "SendMail_Sp" within "ManageStaff_SP".
Im getting error-

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'SendMail'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

这是我的代码： -

This my code:-

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SendMail_SP]
(
	@From_Email nvarchar(128),
	@To_Email nvarchar(1024),
	@Subject_Email nvarchar(256),
	@Bodytext_Email nvarchar(512),
	@Password_Email nvarchar(128)
)
as
begin
/* Gmail SMTP server address */
DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.gmail.com'

/* Set your Gmail email address */
DECLARE @From nvarchar(128)
Set @From = @From_Email;

DECLARE @To nvarchar(1024)
/*You can input multiple recipients and use comma (,) to separate multiple addresses */
Set @To = @To_Email;

DECLARE @Subject nvarchar(256)
Set @Subject = @Subject_Email;

DECLARE @Bodytext nvarchar(512)
Set @BodyText = @Bodytext_Email;


/* Gmail user authentication should use your 
 Gmail email address as the user name. 
For example: your email is "[e-mail]", then the user should be "[e-mail]" */
DECLARE @User nvarchar(128)
Set @User = @From_Email;

DECLARE @Password nvarchar(128)
Set @Password = @Password_Email;

/* Enable SSL/TLS */
DECLARE @SSL int
Set @SSL = 1

/* If you want to use TLS, please set it to 25 or 587 */
DECLARE @Port int
Set @Port = 465

PRINT 'start to send email ...'

exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port


end
-----------------------------------------------------

ALTER procedure [dbo].[ManageStaff_SP]
(
	@StaffId [int]=0,
	@FName varchar(50)=null,
	@LName varchar(50)=null,
	@Password varchar(50)=null,
	@EmailId varchar(50)=null,
	@MobileNo varchar(50)=null,
	@Photo varchar(50)=null,
	@Type varchar(20)
)
as
begin
if(@Type='Stf_Add')
begin
	DECLARE @Random int;
	DECLARE @Upper int;
	DECLARE @Lower int;

	set @Lower=1000
	set @Upper=10000
	select @Random=ROUND(((@Upper-@Lower-1)*RAND()+@Lower),0)
	select @Random
	
	select CAST(@Random as varchar(50))

	INSERT INTO tblStaffs (FName,LName,Password,EmailId,MobileNo,Photo) values
	      (@FName,@LName,@Random,@EmailId,@MobileNo,@Photo)

	declare @sub nvarchar(500);
	set @sub='Login ID and Password';

	declare @body nvarchar(1024);
	set @body='Dear '+@FName+' '+@LName+' , Your LoginID : '+@EmailId+' and Password : '+@Random+'. Using this login id and password, you can login to RTO-Timer website.';
	exec SendMail '[e-mail]@gmail.com',@EmailId,@sub,@body,'password';

end

end

任何帮助表示赞赏......！谢谢...！

Any help appreciated...! Thank you...!

推荐答案

嗨...

我得到了解决方案。我在调用SendMail_SP商店程序时犯了愚蠢的错误。



这是正确的代码......！



ALTER程序[dbo]。[ManageStaff_SP]

（

@StaffId [int] = 0，

@FName varchar（50） = null，

@LName varchar（50）= null，

@Password varchar（50）= null，

@EmailId varchar（50 ）= null，

@MobileNo varchar（50）= null，

@Photo varchar（50）= null，

@Type varchar（ 20）

）

as

开始

if（@ Type ='Stf_Add'）

开始

DECLARE @Random int;

DECLARE @Upper int;

DECLARE @Lower int;



set @ Lower = 1000

set @ Upper = 10000

select @ Random = ROUND（（@ @ Upper- @ Lower- 1）* RAND（）+ @ Lower），0）

选择@Random



选择CAST（@Random as varchar（50） ）



INSERT INTO tblStaffs（FName，LName，Password，EmailId，MobileNo，Photo）值

（@FName，@ LName，@ Random，@ EmailId，@ MobileNo，@ Photo）



声明@sub nvarchar（500）;

set @ sub ='登录ID和密码';



声明@body nvarchar（1024）;

set @ body ='亲爱的'+ @FName +''+ @ LName +'，你的LoginID：'+ @ EmailId +'和密码：'+ cast（@Random as varchar（50））+'。使用此登录ID和密码，您可以登录您的网站。';

exec SendMail_SP'[e-mail]'，@ EmailId，@ sub，@ body，'password';



结束



结束





:-) :-) :-)

谢谢..！



问候，

Rashmi

Hi...
I got solution. I did silly mistake while invoking "SendMail_SP" store procedure.

This is correct code...!

ALTER procedure [dbo].[ManageStaff_SP]
(
@StaffId [int]=0,
@FName varchar(50)=null,
@LName varchar(50)=null,
@Password varchar(50)=null,
@EmailId varchar(50)=null,
@MobileNo varchar(50)=null,
@Photo varchar(50)=null,
@Type varchar(20)
)
as
begin
if(@Type='Stf_Add')
begin
DECLARE @Random int;
DECLARE @Upper int;
DECLARE @Lower int;

set @Lower=1000
set @Upper=10000
select @Random=ROUND(((@Upper-@Lower-1)*RAND()+@Lower),0)
select @Random

select CAST(@Random as varchar(50))

INSERT INTO tblStaffs (FName,LName,Password,EmailId,MobileNo,Photo) values
(@FName,@LName,@Random,@EmailId,@MobileNo,@Photo)

declare @sub nvarchar(500);
set @sub='Login ID and Password';

declare @body nvarchar(1024);
set @body='Dear '+@FName+' '+@LName+' , Your LoginID : '+@EmailId+' and Password : '+ cast( @Random as varchar(50))+'. Using this login id and password, you can login to your website.';
exec SendMail_SP '[e-mail]',@EmailId,@sub,@body,'password';

end

end


:-):-):-)
Thank you..!

Regards,
Rashmi

这篇关于如何使用sql server 2005中的存储过程从gmail帐户邮寄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！