.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

create stored procedure to remove records older than 90 days.

Posted By:      Posted Date: October 22, 2010    Points: 0   Category :Sql Server

Hi All, i want to create two new stored procedures to remove records older than 90 days from tables DirStaging.Trr.FullEnrollmentImport and DirStaging.Trr.WeeklyMonthlyImport. The same stored procedure will remove all records from Dir.dbo.ErrorTable where there is not a record in Dir.Trr.FullEnrollment and Dir.Trr.WeeklyMonthly.    Both the procedures will accept an input variable called @CleanupDays. This will be passed from the calling system. there is a field called EtlId in DirStaging.Trr.FullEnrollmentImport which i would have to use to get recent load of information. i mean, if i am loading thousand records then they would have same EtlId. so i guess i have to have @EtlId and @CleanupDays as input variables.

I tried something like moving all the data from the DirStaging.Trr.FullEnrollmentImport to temp table, truncate DirStaging.Trr.FullEnrollmentImport and apply condition on temp table to get recent

View Complete Post

More Related Resource Links

Create stored procedure from asp.net



we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.



Need to create a stored procedure on a db if that db exists..

IF   EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'test') BEGIN use test Create   Procedure test @empno int, @empname varchar (50), @loc varchar (50) as print   'it is a msg' end   getting the error msg Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'Procedure'. I need to deploy this sp  only if that db exists to around 400 db servers in a batch... I heard within begin.. end .. we should not include DDL's .. but i need to find a work around...  thanks   

want to create common Stored procedure

Hello, I have more than 20 tables, for this i want to create a common Stored procedure which fetches data by id Column So I tried like this,but it doesnt work ALTER PROCEDURE [dbo].[ERS_SP_GetAllTableDataByID] -- Add the parameters for the stored procedure here @TableName nvarchar(100), @ColumnName nvarchar(100), @ColumnVal nvarchar(100) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @ExecQuery nvarchar(100) SET @ExecQuery =@TableName -- Insert statements for procedure here select @ExecQuery = 'SELECT * FROM [' + @TableName + '] WHERE ' + '[' +@ColumnName +']=' + @ColumnVal exec (@ExecQuery) END please help me out Regards,

How to create a second independent transaction inside a CLR stored procedure?

I use the context connection for the "normal" work in the CLR procedure.But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.What is the best way to do this inside a CLR procedure?Thanx a lot

Error when trying to create a stored procedure

Hello allI am using c# to create a new stored procedure and i keep getting a stupid failed operation exception. This is the code i use static private void CreateProcedure(string connectionString, string userName, string password, string DTName, List<DataColumnsObj> parametersIn, List<DataColumnsObj> parametersOut) { SqlConnection connection = new SqlConnection(connectionString); ServerConnection serConnection = new ServerConnection(connection.DataSource); serConnection.LoginSecure = false; serConnection.Login = userName; serConnection.Password = password; Server server = new Server(serConnection); string storedProcedureName = _procedureName; StoredProcedure sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); sp.Refresh(); try { sp.Drop(); sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); } catch (Exception ex) { } sp.TextMode = false; sp.AnsiNullsStatus = false; sp.QuotedIdentifierStatus = false; AddParametersStoredProcedure(sp, parametersIn, false); if (parametersOut != null) { AddParamet


How do you limit the execution of stored procedure / or update of items only to happen on specific days.   i need this only to execute if today is day 3 through day 7 (tue - sat) UPDATE itm SET AVG_WHSL_PRC = AWP.CURR_AWP_AMT ,AWP_EFF_DT = @Pricing_Date ,UPDT_DTS = GETDATE() ,UPDT_OPR_ID = RIGHT(SUSER_SNAME(), 7) FROM dbo.T_ITEM_PRC_CHG itm JOIN #TEMP_AWP awp ON itm.EM_ITEM_NUM = awp.EM_ITEM_NUM WHERE itm.PRC_CURR_CD IN ('C','F') AND @Pricing_Date BETWEEN itm.PRC_EFF_DT AND itm.PRC_END_DT AND itm.HOME_ACQ_COST > 0 AND ISNULL(itm.AVG_WHSL_PRC, 0) <> awp.CURR_AWP_AMT

count records from the same stored procedure SP (SQL2000)

i wish to create a task if there is any record from that SP, and then run The SP return list of records. My question is how to count records on SP? i find this solution (http://www.sqldev.org/transactsql/how-to-return-count-from-same-stored-procedure-which-is-used-for-pagination-73308.shtml) but my SQL is SQL2000 which does not support WITH clause. Thans a lot.

can I create a job to call a stored procedure?




Can I create a job to call a stored procedure everyday? If yes, how to do it?



how to create stored procedure in sql server ???


here i have 3 attributes i want to write a sp it should take 1input value by tht i get remaining 2out put values

 table name: Employee

attributes: id,name,sal;

help me in this....

if this exected i should get succecss msg also and boolen is true...


flase and not exectuted msg...


Create a stored procedure that will take different files like .csv,.txt and create a table in my dat


Hi All,

I am new in SQL Server.Currently I am using SQL Server 2008.

I have to create a stored procedure that will take different files like .csv,.txt and create a table in my database.

At a single time i am using only one file like .csv,.txt.

Here I am generating a XML file which contains script to create table and how to map data into table.

Here we are using XML as a middle man which gives us idea about file structure and how to map data into table.

Can any one guide me how can i do this thing?

Thanks & Regards

Gaurav Jain

Create a stored procedure that will take these different files like .csv,.txt and create a table in


Hi All,

I am new in SQL Server.Currently I am using SQL Server 2008.

I have a lot of different files like .csv,.txt files.

I have to create a stored procedure that will take these different files and create a table in my database.

At a single time i am using only one type of file like it may be .csv or may be .txt.

I am creating a XML file which contains script of Create Table and how to map table in sql server?

Here XML is working as middle man.

Can any one guide me how can i do this things?

Thanks & Regards,

Gaurav Jain

how to create stored procedure in sql server 2005 and how to use that stored procedure in asp.net


Hi friends ,

       I am very newbie

I have created stored procedure and student database and also asp.net application for asp.net page

but it could not found stored procedure what is the mistake actually I don't no

Please help me

Warm Regards


How to create a batch file using a stored procedure



Please let me know how to create a batch file with some content in it, using a stored procedure in SQL 2000.

Thanks in advance.



A Stored Procedure to Create a Set of Stored Procedures


Is it possible to use a stored procedure to create a bunch of other stored procedures?  When the user creates a new database, I need to create a bunch of stored procedures and I would like to just run a stored procedure on the new database that creates all the needed stored procedures.  I tried just putting the CREATE PROCEDURE syntax inside a stored procedure and got some errors.  I imagine this can be done using dynamic sql and the EXECUTE function but I was hoping there was a better way.



Nick's Programming Tips

Need To create Stored Procedure for a Search Criteria


Hi all,

I need to create a stored procedure for a search criteria same as the linkedin.com is having with it under job search module. but i don't understand from where should i start. i am not getting any clue for that. please help.....

i am working on SQL Server 2005.

Can we create Stored Procedure from SSAS MDX Query


HI All,

Is this possible to create STORED PROCEDURE TO FROM MDX QUERY,if yes please tell me how and also can we use that STORE PROCESDURE in SSRS report.



How can navigate the records using stored procedure in front end?


Hi all i have a task like i had windows form with studentid, sname, marks and buttons for first, next ,prev, last. Now i have to get the data form table student and display and navigate the records by using stored procedures only with put datareader or dataset..

Any body can me help me out?

thanks in advance..

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend