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
DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.gmail.com'
DECLARE @From nvarchar(128)
Set @From = @From_Email;
DECLARE @To nvarchar(1024)
Set @To = @To_Email;
DECLARE @Subject nvarchar(256)
Set @Subject = @Subject_Email;
DECLARE @Bodytext nvarchar(512)
Set @BodyText = @Bodytext_Email;
DECLARE @User nvarchar(128)
Set @User = @From_Email;
DECLARE @Password nvarchar(128)
Set @Password = @Password_Email;
DECLARE @SSL int
Set @SSL = 1
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...!