Records Getting Deleted Mysteriously..

Jul 20, 2005

I'm running a DB using MSDE (2000) that is interfaced by 2 different
ades running on PCs with Access 2000 Runtime. One of the ADEs is a
package accounting system that is very solid and stable, the other is
a custom application that I wrote (much less solid and stable). The
custom app only deals with a select few tables in the database, and
the table in question is not one of those.
With alarming regularity(daily), records are getting deleted out of a
particular table. I've set up a couple of dummy records in the table
and 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 are
deleted.
The deletion (all records in the table) always occurs during business
hours (never over the weekend or at night) and the user responsible
varies among 3 or 4 different users. 2 of those users don't even have
rights to that table, so I'm really confused how those logins could
cause 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 or
machine or anything else that is behind the deletion?

View 2 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Renumbering Remaining Records In A Table After Some Records Deleted

Dec 3, 2014

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).

View 5 Replies View Related

Records Don't Get Deleted From The Database!

Sep 17, 2007

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

View 4 Replies View Related

Recover Deleted Records

Mar 22, 2005

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:

View 4 Replies View Related

Restoring Deleted Records

Sep 21, 2001

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.

View 2 Replies View Related

How To Recover All Deleted Records

Feb 20, 2005

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

View 2 Replies View Related

Possible To Tell Who Deleted Records In My SQL Table?

Dec 12, 2005

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

View 5 Replies View Related

How To Restore The Deleted Records?

May 14, 2006

Hi,
Plz help me to restore deleted records.Pl zsuggest the ways to do it.
Thanks!!

View 2 Replies View Related

Backup My Deleted Records

May 7, 2004

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

View 4 Replies View Related

SSIS And Deleted Records

Mar 4, 2006

I 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.

View 6 Replies View Related

Recovering Space Used By Deleted Records On SQL 7

May 14, 2001

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

View 3 Replies View Related

Protecting Records From Being Updated And Deleted

Feb 28, 2008

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

View 9 Replies View Related

Recover Deleted Records From A Table

Feb 19, 2009

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 Related

Can LOG PI Or Log Explorer Recover Deleted Records BEFORE

Jan 11, 2006

installation 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 Related

Find Missing/deleted Records?

Jul 20, 2005

I 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 Related

Shocked To See That The Records Are Deleted In The SQL Table

Jan 16, 2007

Hello,

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

View 1 Replies View Related

Deleted Records In SQL Server To Dataset

Jul 16, 2006

Hi all..

How to reflect deleted records in Sql Server table to the Dataset?

-- using a web service --



Thank you..

View 1 Replies View Related

How To Restore Deleted Records From Table

Jul 2, 2015

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.

View 5 Replies View Related

Put Trigger On Table X Where Records Are UPDATED Or DELETED

Mar 25, 2013

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.

View 14 Replies View Related

Dealing With Deleted Records In Source Data

Jan 29, 2008

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 :-)

View 1 Replies View Related

Order Of Records In The INSERTED/DELETED Tables In A Trigger

Nov 29, 2007

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

View 1 Replies View Related

Can I Debug/watch On The Trigger's INSERTED And DELETED Records/values?

Jan 25, 2006

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 Related

DB Engine :: Can Deleted Records Be Recovered In Simple Recovery Model?

Aug 21, 2015

Can the deleted records be recovered in Simple Recovery model database? Are the delete logs present there?

View 3 Replies View Related

Is There A Hack That Would Allow You To Reuse Identity Numbers That Were Orphaned By Deleted Records?

Aug 24, 2006

I 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.

View 4 Replies View Related

Source Data Base Deleted Records Reflection On Dataware House DB

Jun 27, 2007

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

View 3 Replies View Related

Transact SQL :: Set Child Records To Inactive When Parent Record Deleted From Table

Oct 16, 2015

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.

View 12 Replies View Related

Transact SQL :: Delete All Records Using FOREIGN KEY Constraints If Main Table Entity Is Deleted

Oct 29, 2015

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]...

View 3 Replies View Related

Mysteriously Max Out On User Connections

Mar 1, 1999

Recently, my SQL Server has started getting:
"Unable to connect. The maxinum of '100' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure."
The server has to be rebooted to recover.

What's odd is that this occurs during the weekend when only about 5-8 connections are used by various system processes. I can't tell yet what is sucking up the connections.

We use SQL 6.5 sp4 on NT4.0 sp3 for DEC Alpha. We are also using SQL Mail, Seagate Backup (with SQL drivers) and some cgis that talk to the database.

Any thoughts would be appreciated.

Darin Drewrey
DBA
Extensis Corp

View 3 Replies View Related

NEW COLUMN BEHAVES MYSTERIOUSLY

Apr 25, 2007

I select these columns:

TABLEONE: SEC
TABLETWO: B_DATE and E_DATE (they are numbers – not dates); RATE (is a number)

I first calculate the difference between E_DATE and B_DATE and between a chosen date and B_DATE
creating the new columns GG_CED_C and GG_MAT_C.
These new columns are calculated perfectly (they make the difference
between the two dates according to the 30/360 date count convention) and don’t give me any problems. They are all filled with numbers.
I’m able to use these new columns to make other calculations. For example I succeed in calculating
RATE/GG_CED_C* GG_MAT_C
but if I try to calculate
RATE/360*GG_MAT_C AS RATEO
the result is that in some records I have my new column RATEO correctly calculated, while in some other records there is no value in the column.

For example:

B_DATE E_DATE RATE GG_CED_C GG_MAT_C RATEO
20061229 20070630 3,995 180 90 0,99875
20061229 20070630 4,185 180 90

Dates are identical and so are GG_CED_C and GG_MAT_C: why don't
I have the result in the second record????

SELECT

SEC, B_DATE, E_DATE, RATE, GG_CED_C, GG_MAT_C,
RATE/ 360 * GG_MAT_C AS RATEO

FROM (
SEC, B_DATE, E_DATE, RATE, GG_CED_C, GG_MAT_C

(YEAR2 - YEAR1)*360 + (MONTH2 - MONTH1)*30 + (DAY2 - DAY1) AS GG_CED_C,
(YEARRIL - YEAR1)*360 + (MONTHRIL - MONTH1)*30 + (DAYRIL - DAY1) AS GG_MAT_C,
FROM (
SELECT
A.SEC, , B_DATE, E_DATE, RATE,
TRUNC(B_DATE/10000,0) AS YEAR1 ,
TRUNC(MOD(B_DATE,10000)/100,0) AS MONTH1 ,
CASE
WHEN MOD(B_DATE,1000)= 229 THEN 30
WHEN MOD(B_DATE,1000)= 228
AND MOD(TRUNC(B_DATE/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(B_DATE,100),30)
END AS DAY1,
TRUNC(E_DATE/10000,0) AS YEAR2 ,
TRUNC(MOD(E_DATE,10000)/100,0) AS MONTH2 ,
CASE
WHEN MOD(E_DATE,1000)= 229 THEN 30
WHEN MOD(E_DATE,1000)= 228
AND MOD(TRUNC(E_DATE/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(E_DATE,100),30)
END AS DAY2,
TRUNC(&DRIL/10000,0) AS YEARRIL ,
TRUNC(MOD(&DRIL,10000)/100,0) AS MONTHRIL ,
CASE
WHEN MOD(&DRIL,1000)= 229 THEN 30
WHEN MOD(&DRIL,1000)= 228
AND MOD(TRUNC(&DRIL/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(&DRIL,100),30)
END AS DAYRIL,

FROM TABLEONE A, TABLETWO C

What's happening?
After what I've seen in this query I'm wondering if SQL is reliable
when it comes to calculations (after all its main duty is to query data and not to make calculations among them)(?????.

Please give an explanation to this mystery.

Thank you.

Anna - Verona (Italy)

View 10 Replies View Related

(Mysteriously One Day) Studio Express Won't Connect To DB

Oct 12, 2007



So after 7 months of using SQL Express DB(on a dedicated server), sql management studio on two pc in my office to connect to said DB, and also connecting to DB with query analizer with no connection problems at all, I came into the office yesturday morning to find that the two computers with Studio could not connect to the DB?!?!?! Now, query analizer connects fine to the DB still, with the exact same ip/server instance and with windows authentication??? ive tried reloading studio, disabling firewall both on the client pc and on the server... the instance on the server is set to accept incoming connections (verified i guess by the fact that query analyzer can still connect to it)... i dont get it??? any suggestions????

~ Mauricio

Here is the error message from studio


TITLE: Connect to Server
------------------------------
Cannot connect to 74.***.***.**sqlexpress.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

View 4 Replies View Related

Merge Replication Mysteriously Deleting Rows

Aug 23, 2006

I am running a simple merge replication in SQL Server 2000. I have one database that is the publisher, and a second database that is the subscriber. When I add a new row to the subscriber it will replicate to the publisher as expected. However, the new row at the subscriber will then be deleted without explanation. The row will remain at the publisher though.

Does anyone know why it is doing this?

View 8 Replies View Related

Mysteriously Dropped Rows Using Data Flow Task

Nov 2, 2006

I'm using the Data Flow Task to load data from a flat file into a SQL table and I'm missing rows. And there doesn't see to be any consistent or obvious reason why.

When I use the Bulk Insert Task I import the correct number of rows from the flat file. But when I use the Data Flow task and use a Flat File Source connected to a OLE DB Destination I get about 1/3 the right number of rows. So looking at these loaded tables at the same time I notice that the Data Flow Task method just skips rows sometimes.

Why does this happen??

View 3 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved