Solved

How delete records use SQL Transactional Language in MSSQL Server 2005

Posted on 2014-10-01
3
281 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 49

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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