Solved

How delete records use SQL Transactional Language in MSSQL Server 2005

Posted on 2014-10-01
3
261 Views
Last Modified: 2014-10-02
I need to reduce size of DB in preperation for migration to another host.

I truncated two large tables ok in studio.

There is one large table I need to do selective delete. When I run it as a coldfusion template it just hangs ( I put in a max rows just as a test)
How do I put this in studio transaction sql language - may run better in studio. So hard code a date

Notification_Date is smalldateint and is indexed

<!--- delete old notifications ---->
<CFMODULE TEMPLATE='getnewdate.cfm' Days=600>
<cfoutput> #newdate# </cfoutput>
<cfquery name="DeleteMembers"
         datasource="HouseCare2"
         dbtype="ODBC"
         maxrows="1000">
		 
Delete Notification 
		WHERE
          Notification.Notification_Date < #NewDate#	
            
</cfquery>

Open in new window


The page threw an error - timeout - even though I just selected 1000 rows

The Application Has Encountered an Error!
Here is some of the information that has been submitted to the site administrator.

Date of the Error: Wed Oct 01 18:59:13 EDT 2014
Browser being used: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.124 Safari/537.36
Remote Address: 10.0.0.72
Page on which error occurred:
HTTP Referrer
Template /test/Utility_oldnotifications.cfm
Query String
HTML Code Format
The request has exceeded the allowable time limit Tag: CFQUERY  <br>The error occurred on line 19.

Open in new window

0
Comment
Question by:Ian White
3 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40356227
In general it is:

DELETE FROM table_name
WHERE ...

see the documentation

so it looks like it would be:

DELETE FROM Notification
WHERE  Notification.Notification_Date < '20140101' -- or whatever the date is, the best format to use is like this
0
 

Author Closing Comment

by:Ian White
ID: 40356231
Thanks
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40357167
As an aside, "maxrows" only applies to operations that return a resultset, like a SELECT.  Delete's do not return any results, so it has no effect here.

Also, if you did want to limit the results, better to use db commands like TOP.  The reason is some versions of CF apply "maxrows" after the fact.  So if you're querying a million row table, the db will still return all 1 million rows. CF just drops everything but 1000 afterward. Very inefficient.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now