Click here to Skip to main content
15,888,454 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:-
SQL
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 "gmailid@gmail.com", then the user should be "gmailid@gmail.com" */
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 'abc@gmail.com@gmail.com',@EmailId,@sub,@body,'password';

end

end

Any help appreciated...! Thank you...!
Posted
Updated 17-Nov-15 21:02pm
v2

1 solution

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 'abc@gmail.com',@EmailId,@sub,@body,'password';

end

end


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

Regards,
Rashmi
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900