Hi all,
I have a db with 15 tables and I want to keep records that have been deleted.
Now I don't know where to start:
Must I keep the same structure as the main db or can I also dump all the data in one table?
what are the advantages and disatvantages of the named possibilities.
If someone knows anything else please help me out.
Thnx in advance
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
I am using this code to delete records from a table in the database that has two fields (both are primary keys) I am not getting any error, but nothing gets deleted. Thanks for the help.
Code:protected void btnRemove_Click(object sender, EventArgs e)
{
Remove();Response.Redirect("RegisteredCompanies.aspx");
}protected void Remove()
{
//update database with filename
//Save Changes
connection.Open();using (SqlTransaction trans = connection.BeginTransaction())
{
try
{ SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "spRemoveCompany", new SqlParameter("@Userid", getGdUserID(Profile.UserName)),new SqlParameter("@BusinessID", getBusinessID());
}catch (Exception err)
{
// throw exception
trans.Rollback();
lblStatus.Text = "An error has occurred in the delete process. Please try again.";
}
}
Here is the stored procedure:ALTER PROCEDURE [dbo].[spRemoveCompany]
@Userid uniqueidentifier OUTPUT,
@BusinessID uniqueidentifier OUTPUT
ASDELETE FROM Associations2 WHERE Userid = @Userid AND BusinessID = @BusinessID
RETURN
I want to know what to do to recover the deleted records on a specific table. Unfortunately, the dba was unable to activate Trace feature and I am not familiar in using the transaction logs to retrieve data.
please help. :confused:
Does anybody know if SQL 2000 has a tool to roll back (udelete) records?
Let's say Payroll dept deletes 500 records by mistake...
I know of 2 products that do that (Computer Assoc. - Unicenter Log Analyzer
and Red Matrix Techmologies' SQL Audit 1.7 - but I have never used them. Any comments? Thanks for your help.
Hi everyone
Is there any way to recover all deleted records.
By mistakes i deleted all records.
Please help me out
Waiting for reply.
Thanx in advance
Hello All.
Is there a way to check who has deleted records from my SQL tables? I asked because I notice records keep disappearing from my tables recently for no reason. The DB is only accessible by a few IT staff. Business users have no direct access to it so they can't do any harm and there is no application that update these record missing tables.
I don't need to know the exact records that have been removed. I need info on who has made a deletion activity on which table, date and time is good enough.
Thank you.
Best regards
Hi,
Plz help me to restore deleted records.Pl zsuggest the ways to do it.
Thanks!!
I'm running a DB using MSDE (2000) that is interfaced by 2 differentades running on PCs with Access 2000 Runtime. One of the ADEs is apackage accounting system that is very solid and stable, the other isa custom application that I wrote (much less solid and stable). Thecustom app only deals with a select few tables in the database, andthe table in question is not one of those.With alarming regularity(daily), records are getting deleted out of aparticular table. I've set up a couple of dummy records in the tableand put a delete trigger on the table that creates record in a 'log'table that tells me the user and the time that the records aredeleted.The deletion (all records in the table) always occurs during businesshours (never over the weekend or at night) and the user responsiblevaries among 3 or 4 different users. 2 of those users don't even haverights to that table, so I'm really confused how those logins couldcause a delete on the table they don't have access to!??!As far as I can tell, this is only happening to this particular table( I hope!).Is there a way that I can get more information on the process ormachine or anything else that is behind the deletion?
View 2 Replies View RelatedI am trying to use SSIS to upsize Visual FoxPro data. The data is in free tables. I'm using the VFP OLE DB provider in a connection pointed at a folder. When I click on preview, I see the right data--deleted records are not included. However, when I run the package, the deleted records are sent to SQL Server 2005. I can pack all the tables prior to executing the package as a work around, but I would think I shouldn't have to. I don't see any way to detect which records are marked for deletion in the data flow. I've tried to specify in the connection string Deleted=Yes, but to no good effect. Also, as I mentioned, when I preview the data, the deleted records are not included, so the connection seems to work properly .
Steps to reproduce:
In VFP:
CREATE TABLE Test (Field1 N(1))
INSERT INTO Test (Field1) VALUES (1)
INSERT INTO Test (Field1) VALUES (2)
GO TOP IN Test
DELETE
In SQL Server:
CREATE TABLE Test (Field1 Numeric(1))
In Visual Studio SSIS:
Create connection object using VFPOleDB provider pointed to the folder with the Test.DBF.
Create a connection object to SQL Server 2005 pointed to the database with the test table.
Create a DataFlow task with the OLE DB Source and the SQL Server destination. When you preview the Source object you will see just one record, but when you execute the package, you see 2 records transferred and 2 records are actually in SQL Server. Seems like a bug.
i backed up the database d1 and deleted it off my sql server.
when i innitiate the sql server instante is supposed to run the d1 DB. But since i deleted it, it pops up an error. How can i fix it?
We occasionally use TruncateTable to delete the contents of a number of tables on our SQL Server 7 database. We then build them anew from an outside source. I am finding that the size of the database (and corresponding backup file) has grown significantly. As I understand it, the space representing the deleted records is retained by the database. I have investigated using various DBCC commands, to no avail. Does anyone have a clue as to how I can recover this lost space? ... Thanking you in advance.
Mike Rosen - Amalgamated Bank
Hi I am using sql server 2005 express and would like to keep all my fields from being both updated and deleted.
In other words, once I create a new record, I would like to have it protected from being deleted and I dont want the field values to be updated/changed from the values initially entered. Is there a way to this without running triggers or changing database permissions and user roles?
I tried making the database read-only, but then of course i cant add new records.
Thanks
It happened accidently that we deleted records from a table in SQL server 2005 DB. We never took a full backup of the DB till then. Is there any way that we can recover the deleted records. Logs files are still present. ( *mdf and *ldf ).
View 3 Replies View Relatedinstallation of their softwares? If there has NEVER been a backup ofthe Database (.mdf), Recovery Model Set to Full, and there has neverbeen a backup of the Log (.ldf) files?Using the trial software for both, this was unclear.Thank you for your time,bd
View 2 Replies View RelatedI have 2 tables say table1 and table2 with the same structure. Each recordis identified by a field 'SerialNo'. Now there should be a total of 500000records in both tables with serialno from 1 to 500000. Either a record is intable1 or table2. I want to find records (or SerialNo's) that are inneither table (if deleted by accident etc). What would be the sql query?I'm using SQL 6.5thx
View 2 Replies View RelatedHello,
Can any one tell me ..is there any restriction on the total number of records that can be added to a table ( SQL Server 2005) .Because .. i added 100 records to a table through my program..but i was able to c ..only the last 20 records which is being inserted..can any one tellme any way to increase the total number of records..
pls..help me..solution is required immediately.........
Regards,
Sweety
Hi all..
How to reflect deleted records in Sql Server table to the Dataset?
-- using a web service --
Thank you..
I want to delete some records from dbo.ABC table using where clause.
And would like to restore the deleted records back into the same table.
I am having a hard time understanding triggers. My goal is to put a trigger on table x where records are UPDATED or DELETED. When this trigger fires I need to take the record ID and put the ID modified record into table y with the date modified. so basically logging the recordid changed with the getDate()
I don't quite understand how to get the rowid of the modified record.
Hi,
I have an SSIS package that runs each day from a live data source to create a data mart, which is then used for various things including SSAS and SSRS.
The problem is that certain records that will eventually go on to form fact tables are deleted from the live system (not a very robost database in the first place, hence the SSIS!) but these are not reflected in the SSIS transformation, creating plus figures when compared to the live system.
I currently use type 1 slowly changing dimension processes in each data flow (of which there are about 35) but I realise that this only updates records and does not delete.
The solution I have in place is to truncate the fact tables in the mart before the run starts using an Execute SQL task. This solves the problem though to me seems a little heavy-handed and renders the slowly changing dimension processes redundant (as it is currently only run once a day).
My question is, is there a better method of dealing with the above scenario? If there isn't, it would be a nice feature to add to future versions (*nudge nudge*).
Thanks in advance :-)
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
Thanks!
CB
Hello,
I created a test stored procedure in MS SQL 2000. When the problem in my app was fixed, I deleted this test stored procedure. But the backup job thinks this procedure still exist and so backup job would failed. How can I fix this problem?
Thank in advance for your assistance.
When i debug a trigger is it possible to add a WATCHon the INSERTED or DELETED?I think not, at least I couldn't figure out a way to do so.Does someone have a suggestion on how I can see the values?I did try to do something likeINSERT INTO TABLE1(NAME)SELECT NAME FROM INSERTEDbut this didn't work. When the trigger completed and Iwent to see the TABLE1, there were no records in it.Are there any documents, web links that describe waysof debugging the trigger's INSERTED and DELETED?Thank you
View 11 Replies View RelatedCan the deleted records be recovered in Simple Recovery model database? Are the delete logs present there?
View 3 Replies View RelatedI guess this is a fairly common topic but couldn't find the right words to find anything in a search.
What I'm getting at, is there any tsql functions or combination of commands for the following.
You have identity columns in your tables, if you set the a seed and autoincrement, I enter in rows 1 -10 and then I delete 4, 6, 7, 8.
My next new record uses 11. Is there any logic that allows you to check and reuse 4, 6, 7 & 8 described above? Not looking for something that consists of having to create an extra ID table for each table and handle configuring what the next available number is everytime an Insert or delete is called.
Thanks.
Iam using Sql Server Integration Service to transfer the data. I have to methods to transfer the data.
Method -1
Daily cleaning Dataware house Data base and transfering the data from Source database.
Method -2
Only new rows transfering from the Source database to dataware house data base.
If i use first method, Any performence issues's will come in the future?. In future my source data will have upto 6 lacks records.
If i use second method, daily date based i can transfer the data. But if they delete any previous records from the source database how can i reflect the same in Data ware house Data base.
Can you please provide the solutions.
Regards
Hanu
I need to create a trigger to meet following conditions.
When parent record is deleted from UI record becomes inactive in table. i need to create a trigger when this happens.
When parent record is deleted child records needs to be inactivated in table.
How to delete records from multiple tables if main table’s entity is deleted as constraints is applied on all..There is this main table called Organization or TblOrganization.and this organization have branches which are in Brach table called tblBranch and this branch have multiple applications let say tblApplication and these application are used by multiple users called tblUsers.What I want is: when I delete the Organization All branches, application and users related to it must be deleted also.How I can apply that on a button click in asp.net web forms..Right now this is my delete function which is very simple
Public void Delete(int? id){
var str=”DELETE FROM tblOrganization WHERE organizationId=”+ id ;
}
And My tables LOOK LIKE this
CREATE TABLE tblOrganization
(
OrganizationId int,
OrganizationName varchar(255)
[code]...
Hello.
I need to design a database structure that will keep ALL table records and not delete anything. Every record will have associated users, but I need to keep perminant track of what every user did to any record.
Can you suggest a reading or backup method? Should I keep older versions of a record within the same table or move them to another? Or backup to log files? Any suggestions?
Thank you in advance
- Jasem
Hi i am looking for some advice about sql databases and wondering if anybody can help me.
First the Background.
I am in the process of writing a payroll program that uses SQL as a data store. However rather then creating individual databases for each payroll that can be ran on the system i have created a core payroll table that generates a unique id for each payroll and then reference that thoughtout the system.
Now The Problem
The problem I now have is when I work out the final pay for the employees I want to make a quick copy of the various records just in case I need to undo the net pay calculations for adjustments to the data, it is effectively rolling back the transactions to the database but I want to be able to keep them till I decide that everything is correct.
I cant take a snapshot of the entrie database because the payrolls stored within may be in all sorts of states.
I need to keep the records for just the payroll I'm working on.
Any Ideas or Solutions !!!!!
Thanks
EJ Gibbons
"Where poets speak there hearts, then bleed for it" - U2
using below query
DELETE FROM Table_name
WHERE Date_column < GETDATE() - 30
am able to delete old records morethan 30 days, but i want to results to be saved in file.
before deleting i want to a craete a file and save the to be deleted records.