Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







How To Delete Data Older Than X Days, Without Considering Time


When running the following SQL statements, I get the same results.
Though I need to count only -30 days. Both statements below also
consider the time of the day as well, which is not desired


DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < GETDATE()- 30

DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())


Here is the format of the values in column
MNT_R.TIMESTAMP
2005-08-09 06:06:44.577
2005-08-09 06:06:46.810
2005-08-09 06:06:49.060

So, since data are inserted into the MNT_R table every few seconds, my
delete statement will delete different number of rows, according to the
time of the day it runs.

Can you please post a SQL query that will not give me this headache?

thanx a lot all


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Delete Records Older Than 5 Days .
Hello ,

I am little confused in writing the exact query i.e filling the correct details in the query .
To simplify let me explain....

I am using the query as

delete MYTABLE
where datediff(dd,loaddate,getdate())>5

I have a table now with loadate column which gets the default date and time . The loadate shows correct date and time when the data was imported in the table .

Now suppose if i want to delete previous 5 days records from today
( for e.g today is 20/08/2002 3:40:00 PM ) ideally it should delete all records which are 5 days older from today . i.e from 20/08/2002 3:40:00 PM to 15/08/2002 3:40:00 PM )
But when i execute the datediff command , it deletes the records previous than 15/08/2002 till 15/08/2002.
The records from 15/08/2002 to 20/08/2002 remain intact .

I am getting some different results .

Am i missing something in the query or i am confused about the calculation of the dates the datediff command performs .

Is the logic correct or i am missing someting important ?

Thanks and Regards
Admin001

View Replies !   View Related
Delete Records In A Table Older Than 90 Days . Help
Hi ,

I have a scheduled job which does an text file import in my database . The data gets appended in my table every day from this import job .

Since my table is growing every day , i want to truncate the table after the data has been collected for three months i.e 90 days . The table will be empty and the new data will flow in through the import .

Any thoughts how to do it through query and schedule it ???

Thanks

View Replies !   View Related
Delete Files Older Than 3 Days Using SSIS Tasks
I want to delete all files in a given folder that are older than 3 days from today's date.

I tried using a "Foreach Loop container"  with a "File System" task inside it but found I couldn't access any file properties such as the file creation date. Am I using the wrong task for this job?

 

TIA,

 

Barkingdog

 

P.S. History of the problem:

I used the sql 2005 Database maintenance program to setup our database backup jobs. One day I noticed that the free space on the drive where we keep the dumps had grown small. I found that we had at least 4 weeks of dumps in there, not the 3 days I wanted to keep!  I looked but could not find the "delete file" option in the SSIS package generated by the Database Maintenance Wizard. No wonder the files were piling up.

View Replies !   View Related
Deleting Backup Files Older Than 2 Days
Friends -
I am looking for a windows script (bat file) to delete backup files which are older than 2 days.

Please provide scripts on this.

Appreciate your support

Cheers :)
Satish

View Replies !   View Related
Deleting Backup Files Older Than 5 Days Old.
I am using the backup task and backing up a database but want to delete all backup files older than 5 days old.  I am using the file task for this and have built the path in a variable but am trying to use a wildcard for the time.  I am getting illegal character in path.  How can I go about this.

I currently have E:MSSQL.1MSSQLBackupdatabasename_backup_20070309*.bak in my input variable and am trying to delete the file databasename_backup_200703091532.bak

View Replies !   View Related
Delete Backup Files Older Then...
Ok in SQL 2000 this is within the maintaince plan, where as i have to create a seprate one for sql 2005 being i assume the 'clean up history' I choose the backup andrestore option choose 2 weeks and then run the job once created it runs sucssessfully but yet it dosnt delete a thing. I have backups going back almost 2 months now.
What on earth am i not doing?

Thanks in advanced

View Replies !   View Related
Maintenance Plan Fails Trying To Delete Files Older Thean One Day. Running SQL Server 2005
I get the following message when I execute a mantenance plan to delete files older than 1 day.
 
Error # -1073548784
 
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-09-30T07:56:09'
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any help would be appreciated!!

View Replies !   View Related
Delete 2 Days Old Files - Xp_cmdshell
Folks:

I want to delete files on windows from a directory which are 2 days old. I understand we can do that using xp_cmdshell. Anybody with a script would really help me.


Thanks !

View Replies !   View Related
Query With Time Period Spanning Two Days
I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and

'17:00:00' order by [DateTime]

However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and

'09:00:00' order by [DateTime]

I need a way to indicate that the start and end times span two days. Can anybody help with this?

View Replies !   View Related
Returning Time Difference As Days/ Hours/ Minutes
I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:

CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
BEGIN
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric

IF @EndTime IS NULL
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
END
ELSE
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)
END

IF(@Days <0)
BEGIN
SET @Days = @Days - @Days - @Days
END

IF (@Hours < 0)
BEGIN
SET @Hours = @Hours - @Hours - @Hours
END

IF (@Minutes <0)
BEGIN
SET @Minutes = @Minutes - @Minutes - @Minutes
END

RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'

END

View Replies !   View Related
SQL Server - Select Records Added Today, Last 3 Days, 7 Days...
Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly).   -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out?  Thanks so much in advance.

View Replies !   View Related
Add At The Same Time Delete
what i want is that everytime i add something to my sell table the stocks table deletes
here is the file i want that everytime i buy something the stocks would lessen..

http://rapidshare.com/files/85111869/add_at_the_same_time_delete.zip.html

View Replies !   View Related
Add At The Same Time Delete
[QUOTE=Dragon_EPT;14362]what i want is that everytime i add something to my sell table the stocks table deletes

<html>
<body>

<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.util.*"%>
<%
try {
Connection con;
Statement stmt;
ResultSet rs;
String brokers = request.getParameter("brokers");
String stock = request.getParameter("stock");
String qty = request.getParameter("qty");
String price = request.getParameter("price");

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:sherwin","","");
stmt = con.createStatement();

rs = stmt.executeQuery("INSERT INTO sell (brokers,stock,qty,price) VALUES ('"+brokers+"','"+stock+"','"+qty+"','"+price+"')");
rs = stmt.executeQuery("DELETE FROM stocks WHERE brokers = 'one'");


response.sendRedirect("main.jsp");
con.close();
} catch (Exception e){
System.out.println("SQL Exception : " + e.getMessage());
}
response.sendRedirect("main.jsp");
%>

</body>
</html>

[/QUOTE]

View Replies !   View Related
Making A Row Delete Itself At A Certain Time?
Is there any possible way to make a row delete itself at a certain date and time? I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application. So is it possible to have SQL responsible for deleting a row at a certain time and date?

Also, I have another question:
when I asign a PK Identity to a certain column is there a way I can enforce consecutive order of PK values? For example if I delete a row and the PK value was 5 it will reoder the whole table so all PK values are in consecutive order:


PK-----------Item----------price
1------------XYZ1----------$$$$
2------------XYZ2----------$$$$
3------------XYZ3----------$$$$

when I delete PK 2 this is what happens

PK-----------Item----------price
1------------XYZ1----------$$$$
3------------XYZ3----------$$$$

the PKs are not in consecutive order


What I want is this to happen: when PK 2 is deleted I need it to show as follows

PK-----------Item----------price
1------------XYZ1----------$$$$
2------------XYZ3----------$$$$

The PKs stay in consecutive order.



See how the PK are still in consecutive order?

View Replies !   View Related
Queue DELETE Time
Hello!

In running some performance tests on a Queue using a message size of ~5KB, we found that we can process (SEND and RECEIVE) on the order of 600 - 800 messages / second.  However, we have found that INSERTs of new messages to the Queue appear to take great precedence over DELETEs of received messages from the queue.  In particular, we found that during heavy use the total size of the Queue (as determined using the sp_spaceused procedure) equals about the number of total messages processed, not the number of messages on the queue.

When we stop sending messages, the overall size of the Queue table appears to decrease slowly, so there is a background process that is obviously doing some work there to clean up the received messages from the Queue.  What I would like to know is if we can affect that background process in any way so that the messages are cleared out more quickly.  The performance has been determined to suffer appreciably once the Queue size grows to greater than about 3GB in size.  We also notice timeouts on the RECEIVE statements when the Queue size is that large.

Thanks for any help --

Robert

View Replies !   View Related
Delete Multiple Tables At A Time
 
Hi: I have 3 tables namely:
1  Category(CategoryID(int), CategoryName(varchar),
2  SubCategory( CategoryID(int),SubcategoryID(int),SubcategoryName)
3 Productlist (ProductID(int),ProductName(varchar),CategoryID(int), CategoryName(varchar),SubcategoryID(int),SubcategoryName(varchar))
how to delete correspoding subcategories of category from  SubCategory,Productlist  tables using triggers
Ex: Category :TV   Subcategory:ColorTV,Plasma,LCD...Plz Send me the query....
Thanks
 
 

View Replies !   View Related
How To Delete Records Based On Time?
Hello

I want to delete my record after ten days of the Posted date(field)..how do I do it? I'm able to insert date(short date , long time) to the database. I'm using sql server and C#. this is what I have so far.I would appreciate your help..

Thanks!!!<%@ Page Language="C#"%>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat=server>

void Page_Load(Object sender , EventArgs e)
{
SqlConnection conPubs;
string strDelete;
SqlCommand cmdDelete;

conPubs = new SqlConnection( @"Server=localhost;Integrated Security=SSPI;database=Book" );
strDelete = "Delete tblbook Where Posted_Date =???????????";
cmdDelete = new SqlCommand( strDelete, conPubs );
conPubs.Open();
cmdDelete.ExecuteNonQuery();
conPubs.Close();
Response.Write("Records Deleted!");
}
</script>
//

</script>
<html>
<head>
</head>
<body>
<form runat="server">
<!-- Insert content here -->
</form>
</body>
</html>

View Replies !   View Related
How Can I Delete All Rows In A Table At Same Time
hi,
here i am with a table containing 5columns and 100 rows.i want to delete all rows at the same time. pls suggest me a way on this

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

View Replies !   View Related
CAN I INSERT,DELETE,UPDATE2tables At The Same Time
i've read the transact-sql command,

i known that the select command use to retrieve many fields from many tables with one command

select * from table1,table2

yes,

but i ' ve not seen the way to add,delete or update those fields from those tables with one command...



Is it possible? why?

I don't have any idea , can u help me

I want to know the sql commands , if it's possible

thanks for reply,

mochi

View Replies !   View Related
Pivot Aging 30 Days, 60 Days && So On
Hi, I have some difficulties in creating an aging report using pivot.  I have try searching the google, & cannot really understand the example.
I need to create an aging report of <30 days, <=60 days, <=90, <=120 , > 120 days & Balance.
I have 1 table called mTrialBalance with fields called TBDate, Credit, Debit.
I have difficulty in finding the expression of creating the fields for pivot the the aging reports with the 30 days, 60 days as header of a column & Amount (Debit - Credit) into the respective columns & the total amount into Balance.
Can someone teach me how to use it?Thanks.

View Replies !   View Related
Retrieve Data For Working Days Using Date Function
Hi
I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 .
Any ideas?

Thanks...

View Replies !   View Related
Delete A Record That Exist In 6 Tables At The Same Time
Hi! Is there a way to delete a record from multiple tables at the same time? Thanks for the help!

View Replies !   View Related
CAN I Command (INSERT, DELETE, UPDATE) 2 Tables At The Same Time? POSSIBLE? HOW?
i've read the transact-sql command, 
i known that the select command use to retrieve many fields from many tables with one command
select * from table1,table2
yes,
but i ' ve not seen the way to add,delete or update those fields from those tables with one command...      
 
Is it possible? why?
I don't have any idea , can u help me
I want to know the sql commands , if it's possible 

thanks for reply,
mochi

View Replies !   View Related
Delete Multiple Rows One At A Time Based On A Condition
 

Hi,
 
I have the following scenario :
CustomerDetail
customerid
customername
status
app_no
 
[status = 0 means customer virtually deleted]
 
CustomerArchive
archiveno             [autoincrement]
customerid
customername
status
 

At the end of the month, I have to physically delete customers. I have written two stored procs:
 
proc1
create proc spoc_startdeletion
as
declare @app_no int
select @app_no = (select app_no from customerdetail where status=0)
EXEC spoc_insertcustomerarchive @app_no
-- After transferrin, physically delete
delete from customerdetail where status=0
 
proc2
create proc spoc_insertcustomerarchive
@app_no int
as
insert into customerarchive(customerid,customername,status)
select customerid,customername,status from customerdetail where app_no = @app_no
 
It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value'
 
How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred.
 
Vidkshi
 

View Replies !   View Related
SSE And Older Pc
I have an old Compaq Presario walmart pc. The stats on it are: 750MHz Duron, 512MB RAM, currently have 20GB and 30GB hard drives and planning on upgrading HD (100GB+), onboard nvidia vanta (8mb tnt2). If I get a larger hard drive, XP Pro, and put SQL Server Express on this computer, would I be able to get things done, or will this be extremely slowed down?

Thanks,
Nate

View Replies !   View Related
OLDER VALUES In DDL Trigger
Hi All
Can anyone help me to keep track of older view or stored Procedure that has been altered so that I can compare the two and make out the changes that have been made.At present I 'm able to get just the  altered view or storedProcedure but how to know that what has been changed.
This will be somewhat similar to Instead if and after in DML trigger but DDL triggers doesn't supports it.If anyone is aware of something then please help
THANKS in advance
Anisha
 

View Replies !   View Related
Deleting Older Files
I have a backup job that has failed.

The database size is 20.6 GB and the Transaction logs are 135MB

The amount of disk space I have left is 3.65MB. Which I know is not going to work.

However on the maintenance plan it is suppose to remove files older than 1 day.

I am wondering if a job works like this:

Step 1 create backup file
Step 2 Create Transaction Log Back up
Step 3 Delete old backup file
Step 4 Delete old Transaction log back up

Which tell me I would need to have double amount of disk space to accommodate 2 20 GB backup file and 2 135MB Transaction log file.

Is this correct??

Also is there a way that I can have step 3,4 done first.

Lystra

View Replies !   View Related
Comparing Dates (older Than)
Hi Guys,

I am really stuck on exactly how I can do this. I can sudo it in my mind but can't figure out how I would code it.

What i want to do is select all records from a table that the log_date is older than 7 days.

If I was comparing a date i would imagine i would do something like

log_date < NOW()-7

I have looked at:

SELECT player_name, player_last_logon_date,DATEDIFF(day, player_last_logon_date, GETDATE()) AS NumberOfDays from player

select player_name, player_last_logon_date, datepart(dd,player_last_logon_date) from player

but I dont think either is really right. any and all advice would be great!

Thanks

View Replies !   View Related
The Posts Should Not Be Older Then 2 Months In Database
Is it possible to send a email after a certain date has pass by. And 2 days after the email has been sent the post should be removed if the member doesn't answer the message.
example: The posts should not be older then 2 months. When they get older than 2 months the SQL-Server detects this post and sends a messegae to the member if he wants to renew it. If he ignors it for 2 days after the email the post will be removed from the database. (Some posts have images also, They should also be removed from the harddrive)
Is this possible in MSSQL-Server to send email?
If someone could tell me how to create this.

View Replies !   View Related
Deleting A Record Older Then 10 Minutes
Hey Guys,I have been trying to work out how I would delete a record that was created more then 10 minutes ago.I can use this to delete records older then a day.DELETE FROM DownloadQueue WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < GETDATE() - 1Just need something now that will do it for just 10 minutes.Cheers.

View Replies !   View Related
Older SQL Server Articles From SWYNK
I really enjoy the articles I get from SWYNK. My question is how do I see a list of all SQL Server articles that have been published through SWYNK? On the web site I see the most recent articles but nothing more that a month or two old. Are the older articles catalogued somewhere? How? Thanks.

Eugene Jenkins

View Replies !   View Related
Need Help Restoring Database From Older Backup
I am in the process of moving a database to a new location. My problem is that I do not have the most recent full backup file. I have a previous full backup file and I also have all of the transaction log backups from the time of the old backup to the present time.

My question is, do I need the most recent backup to restore the database, or can I do it with the older backup plus the transaction log backups?

Thanks in advance.

View Replies !   View Related
Deleting Older Rows In The Table
Hi,

I have a table which has around 50,000 rows.

Can I delete the rows older than one year?

If no, Can I delete the last 25,000 rows (older rows) from the table?

Can you assist me with the SQL Statement to do this?

Thanks

Santhosh

View Replies !   View Related
Installing SQL EXpress With Older SQL Server?
Hello,

Can I install SQL Express on Windows 2003 server which also has SQL Server 2000?

I already have .NET 2.0 Framework installed.

Should I download the full version of SSE?

Thanks

Frank

View Replies !   View Related
Clearing Out Older Records From Msdb.dbo.sysdtslog90
Can this table be truncated without negatively affecting the database engine or other tables? Is there a correct way of clearing this table, or can I simply run the DELETE statement on older records I don't need?

View Replies !   View Related
Delete Rows With Duplicate Column Data But Unique Row Data
Hello,

This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.

I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.

I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,

Kerry

View Replies !   View Related
Cannot Specify 'dimension' For: Maintenance Plan Remove Files Older Than
OS: Windows Server 2003 Standard Edition. MSDE 2000.

SQL Server Enterprise Manager Version 8.0.

The Maintenance Plan Wizard will not allow me to specify a value for
the 'Remove Files...' I can enter a number but there is nothing in the
drop down e.g. 3 'DAYS' perhaps?

Create then plan and then edit - yields the same result.

Any ideas on why there is no 'dimension' for Remove Files Older than ?
Appreciate any tips, ideas, etc.
 
Thank.s 

View Replies !   View Related
SQL SERVER 2005 DEVELOPER VALID BUT OLDER VERSION RAN UNTIL UPGRADED XP TO SP2
 DELETED ORIGINAL LOAD AND TRIED TO REINSTALL. CANNOT START LOCAL SERVER

View Replies !   View Related
Strange Problem With Time Series Data On Time Series Chart
Hi, all experts here,

 

Thank you very much for your kind attention.

 

I encountered a very strange problem again. Why the time series displayed on the chart are so strange? The Key time column I chose for my time series algorithm is cal_month(e.g 199001...), but why the date displayed on the time series chart is like :05/06/2448? (it should be like 199001..?) What is that data? And where exactly did it come from? What is the exact cause of this?

 

Hope it is clear for your help.

I am really confused on this and thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.

 

With best regards,

 

Yours sincerely,

 

View Replies !   View Related
How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone
 

Hi all,
 
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
 
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
 
For example if the date is August 07, 2007 10:00 AM UTC,
 
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.

 
Similarly for other Time Zones it should display the time accordingly.
 
Is this possible in SSRS 2005?
 
Any pointers will be usefull...
 
Thanks in advance
sudheer racha.

View Replies !   View Related
Delete Data
Hello I have a little problem with deleting data from database. I have to tables
Category:
CategoryID,CategoryName,CategoryImageID - collumns
CategoryImage - table
CategoryImageID,CategoryImage - collumns
I have foreign key between Category.CategoryImageID and CategoryImage.CategoryImageID
How can I delete category from Category table.

View Replies !   View Related
How Can I Delete A Data Row In C++ (MFC)
Hi,

i like to delete data. I can read and write into the database. But somehow i cant delete data from my Microsoft SQL Server. Always get error on CanUpdate().

Did i forgto something ???


CODE::::
void CListAllDlg::OnBnClickedDelete()
{
CDatabase db;
CProducts m_pData(&db);
CString m_ListItem="";
int nItem=0;

POSITION pos = m_list_all.GetFirstSelectedItemPosition();

//open Recordset
if(m_pData.Open()==0)
{
AfxMessageBox("Datenbank Verbindung fehlgeschlagen", IDOK);
return;
}
if (MessageBox("Möchten Sie diesen Datensatz wirklich löschen?","Datensatz löschen", MB_YESNO | MB_ICONQUESTION) == IDYES)
{
do
{
m_ListItem = m_list_all.GetItemText(nItem,1);
//delete data
while(!m_pData.IsEOF())
{
//found data then delete
if(strcmp(m_pData.m_articel,m_ListItem)==0)
{

m_pData.Delete(); //delete data in database
m_pData.Update(); //confirm & update database
nItem = m_list_all.GetNextSelectedItem(pos);
m_list_all.DeleteItem(nItem); //delete data in listbox
MessageBox("Datensatz wurde erfolgreich gelöscht","Datensatz löschen", MB_OK);
}
m_pData.MoveNext();
}//end while
}while (pos != NULL);
m_pData.Close();
}//end if
}//end OnBnClickedDelete

View Replies !   View Related
Delete Data
Hi,

I have two tables.

CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO

CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO


(I fact i created Primary & Foreign keys after inserting data in both of these tables.)

I want to delete data from these two tables.
How do i do that...Any Ideas?

View Replies !   View Related
How To Delete All The Data?
Hi,

I want to delete all the data in all tables.

But I have question for this.

1. I do not know how to loop over all the tables and delete data.
2. the database have database diagram so there are dependency with tables so the delete order is hard to decide.

a solutions is:

I temporarily delete the diagram and I loop all the tables and delete all the data.My question is how to loop over all the tables?

Please give me a idea.

Thanks

Mark

View Replies !   View Related
Delete Row When Data Is Numeric?
I'm using a DTS package to import a large CSV file. There is a particular column that contains text or numbers. I want to delete the row if that column has a number, I've used IsNumeric in the selection portion of the statement, but can't figure out how to use it as part of my where clause.

View Replies !   View Related
How To Delete A Big Amount Of Data
Hi, suppose i have 3 tablesmain tableCREATE TABLE table1 ([t1ID] [int] primary key ,[t1Name] nvatchar(50) NOT NULL,............................)and connections tables (t1ID is a foreign key in all connection tables)CREATE TABLE tbl2 ([t1ID] [int] not null,[gID] [int] not nullPRIMARY KEY (t1ID, gID))CREATE TABLE tbl3 ([t1ID] [int] not null,[pID] [int] not nullPRIMARY KEY (t1ID, pID))************************************************** *****t1ID CLISTERED INDEXin othre tables each primary key is clustered.gID and pID are keys from other table: gtable, ptableNow in case one row was deletes from table1 i need to delete all t1ID'sfrom the connection tables (each of connection may have e.g. 200000 rows).If u use delete on cascade it's take a lot of time, i want to delete let'ssay 1000 rows till all irrelevant rows will be deleted.How To accomplish this??Thanks.--Message posted via http://www.sqlmonster.com

View Replies !   View Related
Delete All Data In The Database
Good day!Currently I am using MS SQL Server 2000.I wish to delete all data in the database table except data in logintable and security table. Furthermore, TRUNCATE is done on stand-aloneand child tables, else DELETE is done if table has any foreign keyreferences (normally refer to parent tables).Is there any suggested script/stored procedure to automate theprocess?Thanks a million!

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved