Stored procedure to execute automatically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored procedure to execute automatically

Author

Topic

kvt.aravind
Starting Member

24 Posts

Posted - 2010-09-15 : 01:37:10

Stored procedure For Deleting older records

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[ArchiveData]
(
@CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
--Declare @rcnt int

IF @CutOffDate IS NULL
BEGIN
SET @CutOffDate = DATEADD(dd, -90, getdate())

END
ELSE
BEGIN
IF @CutOffDate > DATEADD(dd, -90, getdate())
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN

-- Inserting Travel Open Status Data into Archive database
INSERT [EEMS_ARCHIVE].[dbo].[TATA_TRAVEL_TicketInfo]
SELECT TINFO.[Template_No],TINFO.[created_by],tata_travel_bookticketnew.[Created_on],TINFO.[Prefix_Activity_Id],TINFO.[Temp_Activity_No],TINFO.[Status_Id],TINFO.[Button_Id],TINFO.[WF_ID],TINFO.[Travel_Id],TINFO.[Book_Id],TINFO.[TVB_Date],TINFO.[Travel_Type],TINFO.[Req_For],TINFO.[Pass_Name],TINFO.[Pass_Contact_Name],TINFO.[Designation],TINFO.[Department],TINFO.[DepartmentCode],TINFO.[Book_Type],TINFO.[Book_Confirm],TINFO.[Travel_Agent],TINFO.[Gl_Code],TINFO.[Approver],TINFO.[Approver_Contact_Name],TINFO.[Purpose],TINFO.[Sex],TINFO.[Age],TINFO.[Branch],TINFO.[Cost_Center],TINFO.[App_EmailId],TINFO.[Mobile_No],TINFO.[Travel_Plan],TINFO.[Board_Plan],TINFO.[Train_Details],TINFO.[Flight_Details],TINFO.[Hotel_Details],TINFO.[Cab_Details],TINFO.[ETicket1],TINFO.[ETicket2],TINFO.[ETicket3],TINFO.[ETicket4],TINFO.[ETicket5],TINFO.[Status],TINFO.[User_EmailId],TINFO.[Booking_Date],TINFO.[Incidental_Cost]
FROM [TATA_TRAVEL_TicketInfo] TINFO inner join tata_travel_bookticketnew
ON tata_travel_bookticketnew.book_id = TINFO.book_id and TINFO.Status in ('open') and tata_travel_bookticketnew.created_on < @CutOffDate

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying Open status data to Archive.dbo.tata_travel_ticketinfo', 16, 1)
RETURN -1
END

--Inserting Travel Delete Status Data into Archive Database
INSERT [EEMS_ARCHIVE].[dbo].[TATA_TRAVEL_TicketInfo]
SELECT TINFO.[Template_No],TINFO.[created_by],tinfo.[Created_on],TINFO.[Prefix_Activity_Id],TINFO.[Temp_Activity_No],TINFO.[Status_Id],TINFO.[Button_Id],TINFO.[WF_ID],TINFO.[Travel_Id],TINFO.[Book_Id],TINFO.[TVB_Date],TINFO.[Travel_Type],TINFO.[Req_For],TINFO.[Pass_Name],TINFO.[Pass_Contact_Name],TINFO.[Designation],TINFO.[Department],TINFO.[DepartmentCode],TINFO.[Book_Type],TINFO.[Book_Confirm],TINFO.[Travel_Agent],TINFO.[Gl_Code],TINFO.[Approver],TINFO.[Approver_Contact_Name],TINFO.[Purpose],TINFO.[Sex],TINFO.[Age],TINFO.[Branch],TINFO.[Cost_Center],TINFO.[App_EmailId],TINFO.[Mobile_No],TINFO.[Travel_Plan],TINFO.[Board_Plan],TINFO.[Train_Details],TINFO.[Flight_Details],TINFO.[Hotel_Details],TINFO.[Cab_Details],TINFO.[ETicket1],TINFO.[ETicket2],TINFO.[ETicket3],TINFO.[ETicket4],TINFO.[ETicket5],TINFO.[Status],TINFO.[User_EmailId],TINFO.[Booking_Date],TINFO.[Incidental_Cost]
FROM [TATA_TRAVEL_TicketInfo] TINFO where TINFO.Status in ('deleted')

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying Delete Status data to Archive.dbo.tata_travel_ticketinfo', 16, 1)
RETURN -1
END

--Inserting Travel Open Status Data into New table in EEMS Databse
INSERT into [EEMSPROD].[dbo].[TATA_TRAVEL_Opendeletestatus] (Created_By,Created_on,Book_id,TVB_Date,Pass_Name,Status)
SELECT TINFO.[created_by],tata_travel_bookticketnew.[Created_on],TINFO.[Book_Id],TINFO.[TVB_Date],TINFO.[Pass_Name],TINFO.[Status]
FROM [TATA_TRAVEL_TicketInfo] TINFO inner join tata_travel_bookticketnew
ON tata_travel_bookticketnew.book_id = TINFO.book_id and TINFO.Status in ('open') and tata_travel_bookticketnew.created_on < @CutOffDate

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying Delete Status data to Archive.dbo.tata_travel_ticketinfo', 16, 1)
RETURN -1
END

--Inserting Travel Open Status Data into New table in EEMS Databse
INSERT into [EEMSPROD].[dbo].[TATA_TRAVEL_Opendeletestatus] (Created_By,Created_on,Book_id,TVB_Date,Pass_Name,Status)
SELECT TINFO.[created_by],tinfo.[Created_on],TINFO.[Book_Id],TINFO.[TVB_Date],TINFO.[Pass_Name],TINFO.[Status]
FROM [TATA_TRAVEL_TicketInfo] TINFO where TINFO.Status in ('deleted')

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying Delete Status data to Archive.dbo.tata_travel_ticketinfo', 16, 1)
RETURN -1
END

--Inserting Expense Open and Delete Status into Archive Databse
INSERT INTO [EEMS_ARCHIVE].[dbo].[TATA_TRAVEL_ExpenseinfoNew]
SELECT [Template_No],[created_by],[Created_on],[Prefix_Activity_Id],[Temp_Activity_No],[Status_Id],[Button_Id],[WF_ID],[Travel_Id],[Book_Id],[Travel_Type],[Exp_Id],[Exp_Date],[Exp_UName],[Exp_emailID],[Dept_Name],[Designation],[Branch],[Mobile_No],[Exp_From_Date],[Exp_To_Date],[Card_Number],[Cost_Center],[Approver],[App_Emailid],[GL_Code],[GL_Desc],[Travel_Exp_Detail],[Expense_GL],[Expense_Narration],[Exp_Amt],[Card_Amt],[Train_Fare],[Air_Fare],[Hotel_Fare],[Cab_Fare],[Incidental_Cost],[Est_Cost],[Narration],[Travel_Payable_CCard],[Mobile_Exp_Detail],[Mobile_GL],[Mobile_Narration],[Mobile_Exp_Amt],[Mobile_CCard],[Mob_payable],[Mob_Payable_CCard],[Convey_Exp_Detail],[Convey_GL],[Convey_Narration],[Convey_Tot_Mileage],[Convey_Tot_Amt],[Convey_CCard],[Con_Payable],[Convey_Payable_CCard],[Entertain_Exp_Detail],[Ent_GL],[Ent_Narration],[Entertain_Exp_Total],[Ent_CCard],[Ent_Payable],[Ent_Payable_CCard],[Late_Exp_Detail],[Late_GL],[Late_Narration],[Late_Exp_Total],[Late_CCard],[Late_payable],[Late_Payable_CCard],[Other_Expense],[Others_GL],[Others_Narration],[Others_Exp_Tot],[Others_CCard],[Others_Payable],[Others_Payable_CCard],[Support_document],[Transfer_Form],[Relocation_Expense],[Status],[Approver_contact_Name],[User_Contact_Name],[Total_Amt],[Total_Corp],[Feedback],[Remarks]
FROM [TATA_TRAVEL_EXPENSEINFONEW]
WHERE [TATA_TRAVEL_EXPENSEINFONEW].EXP_DATE < @CutOffDate AND [TATA_TRAVEL_EXPENSEINFONEW].STATUS IN ('Open','Deleted')

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.tata_travel_ticketinfo', 16, 1)
RETURN -1
END

--Inserting Expense Open and Delete Status into new Tables in EEMS Production
INSERT INTO TATA_TRAVEL_EXPENSEOPENDELETESTATUS ([created_by],[Created_on],[Exp_Id],[Exp_Date],[Exp_UName],[Status])
SELECT [Created_by],[Created_on],[Exp_Id],[Exp_Date],[Exp_UName],[Status]
FROM [TATA_TRAVEL_EXPENSEINFONEW]
WHERE [TATA_TRAVEL_EXPENSEINFONEW].EXP_DATE < @CutOffDate AND [TATA_TRAVEL_EXPENSEINFONEW].STATUS IN ('Open','Deleted')

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.tata_travel_expenseinfonew', 16, 1)
RETURN -1
END

-- Deletes Book id's in Open Beyond 90 Days
DELETE FROM tata_travel_ticketinfo
WHERE tata_travel_ticketinfo.book_id in
(SELECT tata_travel_ticketinfo.book_id FROM tata_travel_ticketinfo inner join tata_travel_bookticketnew ON tata_travel_bookticketnew.book_id = tata_travel_ticketinfo.book_id and tata_travel_ticketinfo.Status in ('open') and tata_travel_bookticketnew.created_on < @CutOffDate)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.tata_travel_expenseinfonew', 16, 1)
RETURN -1
END

-- Deletes Book id's in Delete Beyond 90 Days
DELETE FROM tata_travel_ticketinfo
WHERE STATUS in ('Deleted')

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END

-- Deletes Expense id's in Open and Delete status Beyond 90 Days
DELETE FROM tata_travel_expenseinfonew
WHERE [TATA_TRAVEL_EXPENSEINFONEW].EXP_DATE < @CutOffDate AND [TATA_TRAVEL_EXPENSEINFONEW].STATUS IN ('Open','Deleted')

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END

IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END

END

STORED PROCEDURE For sending mail to persons with appropriate reords deleted.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ReportDeleteMail]
AS
declare @table nvarchar(max)
--declare @TABLE1 nvarchar(max)

set @table=
N'<h3>Deleted TravelIds Report on ' + Convert(varchar(10),getdate(),105) + '</h3>'+
N'<table border="3">' +
N'<tr><align="left" border="2"><th>Book Id</th><th align="left">Created Date</th><th align="left">Employee ID</th>' +
N'<th align="left">Status</th><th align="left">Description</th>'+
N'</tr>' +

cast ((select td=Book_id, '' ,
td=Created_On, '' ,
td=Pass_name, '' ,
td=status, '' ,
td=Description, ''
From TATA_TRAVEL_Opendeletestatus where convert(varchar(10),Archived_on,105)>= convert(varchar(10),getdate(),105)
Order By Book_id ASC
For XML PATH('tr'), TYPE
) AS NVARCHAR(MAX))+
N'</table>' ;

Set @table=
N'<h3>Deleted Expense Ids Report on ' + convert(varchar(10),getdate(),105) + '</h3>' +
N'<table Border="3">'+
N'<tr align="Left" Border="2"><th align="left">Expense Id</th><th align="Left">Expense Date</th><th align="left">Employee Id</th>' +
N'<th align="left">Status</th><th align="left">Description</th>'+
N'</tr>' +

cast((Select td=Exp_id, '' ,
td=Exp_date, '' ,
td=Exp_uname, '' ,
td=status, '' ,
td=Description, ''
From TATA_TRAVEL_EXPENSEOPENDELETESTATUS where convert(varchar(10),Archived_on,105) >= convert(varchar(10),getdate(),105)
Order By Exp_id ASC
For XML PATH('tr'),TYPE
) AS NVARCHAR(MAX)) +
N'</table>';

EXEC msdb.dbo.sp_send_dbmail @profile_name='Xtenza',@blind_copy_recipients='vinu@xtenzasolutions.com',@recipients='arvind@xtenzasolutions.com',@subject = 'Deleted Records List',@body = @table,@body_format = 'HTML';

Now i execute thie two sp automatically

First exec archivedate
second exec reportdeletemail

once reportdeletemail excuted mail should be sent i tried sql agent but bit collapased as it is giving error with user log in

Aravind.T

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-15 : 04:30:08

Right, what exactly do you mean by "i tried sql agent but bit collapased as it is giving error with user log in"?

Can you get into SQL Agent and see jobs? Do you have sufficient rights? Is there an admin who could either give you the rights or do it for you?

You need to give more information on this side of things rather than your code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-16 : 12:43:18

have you also set up mail profile correctly?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources