Solved

How delete records use SQL Transactional Language in MSSQL Server 2005

Posted on 2014-10-01
3
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to Decrypt in C# a string that is encrypted with Coldfusion Encrypt(string, seed) function 11 156
Cold Fusion form reset button issues. 11 42
SQL- GROUP BY 4 44
SQL query 7 43
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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

742 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