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

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

Drop tables by date it were created

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server


I have a large database that were used to archive tables before implementing structural change on it.

We had this database for years, I want to create a dynamic script to check today's date and drop any table in this database that were created 3 years or older.


View Complete Post

More Related Resource Links

Dynamically Drop and Create tables, overcoming 4000 charachter limit

We have a Navision SQL-server database for 7 companies. Each table starts with the name of the company. Except for the CompanyName-part, the tables names are equal. I made a Foreach loop that dynamically transfers tables/data from the the Navision-database to a staging-database. I also want to Drop and re-Create the tables dynamically. However, if I try to do that via an expression in a SQL Task, SSIS complains about the 4000 character limit. What is a better way to do this? I want to execute SQL-commands and have SSIS replace part of the tablename with the contents of a variable (being the name of one the 7 companies). I have no access to the Management Studio or the DB so I need it to be done within the package. What is the best way to do that?


Sorry my terrible english,   I will try to explain my problem, I made some data changes at the publisher and I had to throw away the PK and FK cascading multiple tables. The problem I have is that these changes were trying to apply to the subscribers and can not do because it does not in order which I did step by step through the Publisher. subscribers now show me this error and does not synchronize anything, and takes 0.5 sec and gives the error infinitely I need help, which infinitely grateful Error messages: The schema script ' if exists (select * from sysconstraints where constid = object_id(N'[FinancieroAbonos_PK]')) if object_id(N'[dbo].[FinancieroAbonos]') is not null exec('ALTER TABLE [dbo].[FinancieroAbonos] DROP CONSTRAINT [FinancieroAbonos_PK] ')' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001 The constraint 'FinancieroAbonos_PK' is being referenced by table 'FinancieroAbonosContabilizacion', foreign key constraint 'FinancieroAbonos_FinancieroAbonosContabilizacion_FK1'. (Source: MSSQLServer, Error number: 3725) Get help: http://help/3725 Could not drop constraint. See previous errors. (Source: MSSQLServer, Error number: 3727)

Dynamic list item title based on date created & regional settings?

I have a list of things that doesn't *need* a title.  However, because sharepoint uses Title for links, RSS etc  I tried generating a title in an event handler.   This would work great if I wasn't basing it on a date field: public override void ItemAdding(SPItemEventProperties properties) { DateTime dt = Convert.ToDateTime(properties.AfterProperties["TestDate"]); properties.AfterProperties["Title"] = dt.ToShortDateString(); base.ItemAdding(properties); } In testing, this sets the title to "8/9/2010" when I selected 8/10/2010 in the date picker.  I figure this is because of the regional settings of my test user.  I can probably correct the title value for 'test user' but that won't fix 'test user in mongolia' who would still see two different values. So What I would like is to have the Title always display the value MyDate in the uesr's locale.  So the field needs to be dynamic not only at edit/update time, but at display time. Does anybody know if this can be accomplished, and more specifically how?

Drop extended property "MS_Description" of ALL tables and ALL columns


Is there an easy way (a sql script) to drop the "MS_Description" of all tables and all columns in my database?


how do it i created a Grid view that contain onecoloum Expiredate a i require the date compare to


i created a Grid view that contain onecoloum Expiredate a  i require the date compare to today date if it is

  Todaydate<=Expiredate (difference 30 days then color is red
  Todaydate<=Expiredate (difference 15 days then color is blue
  Todaydate<=Expiredate (difference 5 days then color is yellow
    id Expire date  Filename
    1  22/08/10      a(Red )   
    2  22/09/10     b(yellow )
    3  8/09/10      c(green  )
    how do it

Get Date Created info for file before uploading?


I have a FileUpload control on my page. I would like to get hold of the "date created" information of the file before it is uploaded. (Obviously, when you upload the file, the uploaded/copied version has a new "date created", set to the time of when you actually uploaded the file. This is not what I want!)

Does anyone please have any suggestions?

Drop tables where table name is a parameter


I'm trying to drop some tables and I created a curser to do so.

The table name is a parameter.

I get an error message when I run this code in the curser.

Is there any way to run this code in a different way.

I'm trying to drop databases as well where database name is a paramerter, can I do so?





@DatabaseName varchar(30)




Does a store procedure perform better when all temporary tables are created at first lines


I heard something which I could not understand why and how (and if true at all).

I heard that if you create all temporary tables at the beginning (initial lines of the stored proc), the store proc performs better.

I actually thought that it would be better to create the temporary table when (and if) it is used:

Create table #tmp1 (


insert into #tmp1 .....

if not exists (select * from #tmp1)


--(this code is conditional, so #tmp2 is created only if needed - so why isn't this better coding and better performance?)

create table #tmp2 (....



Now what I just heard (and can't understand), this performs better:

Create table #tmp1 (


create table #tmp2 (....


insert into #tmp1 .....

if not exists (select * from #tmp1)

 return --#tmp2 was create for no reason - so how can this solution perform better?

insert into #tmp2....



Excel 2010 OLAP Pivot Tables - Can a Date Dimension name be used as a Date not String?


Scenario:  Have built a SSAS 2008 cube and am using Excel 2010 pivot tables as the UI to access the data.  The end-users want to be able to treat the Date Dimension name value as an Excel friendly date, mainly to apply custom formats for pivot charts (such as mmm-yy or dd/mmm/yyyy) and to be able to sort ascending and descending.

Problem:  Although the Date dimension attribute is setup with 'mmm-yy' formatted text as the name and a datetime value as the value, Excel refuses to treat the dimension name as a date.  The result being custom formatting is ignored and pivot sorting results in text sorting only (Apr-00, Apr-01 etc).

Investigation:  Have tried several string combinations for dimension attribute name (even using the Excel datevalue numbers) with no success. Have found some earlier blogs which go part of the way in explaining.  It appears that no matter what you set as the name value you are always going to end up with a string label that can't be formatted or sorted in any other way other than text, as indicated by Darren Gosbell answer to a similiar question.

“...When you display a dimension attribute in an Excel pivot table what you are seeing is the name of each member. The name property of

"Date Modified" not working in Explorer View if document is created in Explorer View


Windows 7 SP1 with Webclient (webdav), Office 2010 Professional Plus, connects to a Sharepoint 2010 Document Library, using Explorer View, mapped to a disc drive letter. Document library has check out requirement turned off.

Customers work only in Windows Explorer with Webclient, they dont want to use web interface to Sharepoint.

Information to recreate the problem:

CASE1) Let's say a new docx document is created in Explorer View (in Explorer by  right-click-new Word document). Customer waits couple of minutes, enters and changes the document in Explorer View, and then saves it and closes Word. Result: Column "Date Modified" in Explorer view is unchanged, when it should have been changed. (!)

CASE2) Let's say I created new docx document in Web View (all documents view). Customer waits couple of minutes, enters the same document IN EXPLORER VIEW, changes it and then saves it and closes Word. Result: Column "Date Modified" in Explorer view is CHANGED to the new save time so now it works!

Why is this happening and is there any fix for this. Is this problem of WebDAV or of Sharepoint? It happends with .docx and .xlsx files, and not with .txt files and some othes which work correct when created in Explorer View.

Also when I copy/paste to Explorer View , column Date

Using a CompareValidator to check input is a valid date

The CompareValidator can do more than just compare two controls. You can also compare it against several of the main .net data types such as Date, Integer, Double and Currency.

To do this you would set Operator="DataTypeCheck" and instead of setting the ControlToCompare or ValueToCompare attributes as you normally would you use the Type="Date" (or any of the data types I have listed above).

Binding the multiple drop down boxes in GridView using JQuery in ASP.NET

Here is the code for binding the multiple drop down boxes in GridView using JQuery in ASP.NET

How To Set a Date Format In GridView Using ASP.NET 2.0

A very common desire is to set a column of a gridview to display just the month, day and year of a DateTime type. The problem is the by default, the HtmlEncode property of the boundfield attribute (
The problem is that if this field is enabled, you can not pass format information to the boundfield control. That is, if you try the following code, you will not get the desired result.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

jQuery Date picker Implementation in ASP.NET

I've posted a wrapper ASP.NET around the jQuery.UI Datepickercontrol. This small client side calendar control is compact, looks nice and is very easy to use and I've added it some time back to my control library.

This is primarily an update for the jQuery.ui version, and so I spend a few hours or so cleaning it up which wasn't as easy as it could have been since the API has changed quite drastically from Marc's original implementation. The biggest changes have to do with the theming integration and the resulting explosion of related resources.

If you want to use this component you can check it out a sample and the code here:

How to format datetime & date with century?

Execute the following Microsoft SQL Server T-SQL datetime, date and time formatting scripts in Management Studio Query Editor to demonstrate the usage of the multitude of temporal data formats available and the application of date / datetime functions.

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