Hi All,
I have just one corrupted record on a table: I copied everything else to another table but I can't delete or rename the old one!
Is restoring the whole DB my only way out there?
Any help or suggestion would be much appreciated!
Cheers
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
DELETE FROM Broker_Rates
WHERE (Broker_Rates.BID <> Broker.BID)
Thanks
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View 3 Replies View RelatedHello,
I am facing an issue with a table in my database.
1) When I run a query Select * from MY_Table the query never completes. If I press cancel in Query Analyzer after 1 second or 2 minutes, it always shows the same 174 records. The total table size is 800 KB with 3148 rows .. according to TaskPad
2) No queries, even queries that would return only 1 record complete execution against that table
3) I am unable to rename the table, if I try to rename the table Enterprise Manager becomes unresponsive and has to be closed.
4) If I try to browse the rows through Enterprise manager by scrolling through the records, it allows me to go down through a number of rows and then Enterprise manager gives me a timeout expired error.
5) I ran a DBCC CheckDB command ran for 1 hr and 7 minutes. It says there are 0 allocation errors and 0 consistency errors in the database.
6) The other database tables seem to be OK and the Database is operational except for calls involving the 1 Table
7) I am unable to Drop the Table
8) DBCC CheckAlloc finds 0 allocation errors and 0 consistency errors.
9) DBCC CheckTable on MY_Table returns with: There are 3148 rows in 33 pages for object 'MY_Table' .. no errors are printed.
Can one of the SQL Gurus on this forum please recommend a course of action.
Thanks in advance.
I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.
Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime
Child Table: TBL_VERIFICATION
PK: Serial Number
Thanks,
Sean
I will try my best to explain this, We have a shopping cart on our website, the person that was developing this has now left the company and I've been given the job to finish it off.
When I load all the items that the user has entered in to his/her cart I return the Item ID and the RowNumber (ROW_NUMBER() OVER (Order by Id) AS RowNumber)
I'm trying to delete the item from the table using the following query
DELETE FROM [dbo].[Cart.Items] WHERE UniqueID = UniqueID and ItemID = @ItemID and @RowNumber IN (
SELECT ROW_NUMBER() OVER (Order by Id) AS RowNumber)
Now the reason we are using the RowNumber is because the user can add the same Item as many times as they like so for example you buy 3 different mobile phones, and you want three screen protectors, they will click screen protector 3 times which will add 3 records in to the db with the same id. so the row number is used to find the correct one.
But the above delete is not working.
Hi, I want to delete from table when the record count is over 5000. That is, the table's record cannot over 5000 count. How? row_number() seem to be used...
View 11 Replies View RelatedI like to give First Name and Last Name in two different text box and then hit the delete button (command button). Then it will do a query to find the person and delete the corresponding record from the table. Any kind of help will be appreciated. Thank you.
View 1 Replies View RelatedI just wanted to delete a record from a filetable by "delete from dbo.DocumentStore where stream_id = 'A322276D-AE65-E511-8266-005056C00008'".
Then i received error 1934:
Meldung 1934, Ebene 16, Status 1, Zeile 578
Fehler bei DELETE, da die folgenden SET-Optionen falsche Einstellungen aufweisen: 'ANSI_PADDING'. Überprüfen Sie, ob die SET-Optionen für die Verwendung mit indizierte Sichten und/oder Indizes für berechnete Spalten und/oder gefilterte Indizes und/oder Abfragebenachrichtigungen
und/oder XML-Datentypmethoden und/oder Vorgänge für räumliche Indizes richtig sind.
Something that i made a mistake with the SET-option for 'ANSI_PADDING' with indexted views and/or calculated rows and/or filtered indeces ...
I have two tables;
Table A
id, name
101, jones
102, smith
103, williams
104, johnson
105, brown
106, green
107, anderson
Table B
id, name, city, state
101, jones, des moine, Idaho
103, williams, Corvallis, Oregon
104, johnson, Grand Forks, North Dakota
105, brown, Phoenix, Arizona
107, anderson, New York, New York
I need to delete records from Table A that are not in Table B. My front end is writen in .net and I am using Data Access Layer along with a Business Logic Layer for data interaction.
I have tried at least seven variations of joining, right outer join, left outer join resulting in wiping our the entire table or nothing at all; not to mention deleting the record that ought to remain and keeping the record that needs to be deleted!
In my BLL I tried to capture the rowsAffected for the deletion by using-without success.
Dim rowsAffected As Integer = Adapter.ID_Deletion(ID)
If rowsAffected = 1 Then
Exit Function
Else
Return rowsAffected = 1
End If
Please help.
MsMe.
Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Thanks
Hi,
I have many sql 2000 DTS packages that I support from my development workstation running v2000 sp4. Packages are altered on the development machine and then go through a normal release mechanisms to production via testing servers etc.
I have recently installed the client tools for SQL Server 2005 on my desktop to evaluate the product. The 2005 DB instance is running on a seperate server.
So, I have dev edition of sql 2000 and 2005 client tools (including BI Dev studio etc) on my workstation.
I have recently had to make changes to a 2000 DTS package and used my 2000 enterprise manager to do so. No Problem- saved and tested fine on my workstation.
But when I try and release it to another server, or open the package using enterprise manager from another machine that does not have sql 2005 installed - I get an error message 'Unspecified error'. This I've seen before when trying to open packages created in v2000 , using v7 or where the service packs are different between machines.
Digging around my workstation and comparing some of the DLLs I know to be required to distribute DTS packages (from RDIST.txt) it seems that some of the SQL 2000 dll files have been updated by my 2005 installation.
E.g
DTSFFILE.DLL on my machine is 2000.85.1054.0 whilst on any 'clean' 2000 machine is at version v2000.80.760.0
Surely it cant be right that SQL 2005 has newer versions of components for SQL 2000 than is available with the latest SP for the actual product! Especially considering that the installation of 2005 does not even allow you to edit 2000 DTS packages through the management studio without a 'special' download' of the feature pack,(whihc by the way does not work very well either)
So am I to conclude that you can not run side by side installations of SQL 2000 and 2005 on a single machine and expect 2000 to run as it did previously
Hello,I am trying to recover a SQL Server 7 database from another hard diskdrive that has a corrupted Windows 2000 Advanced Server installation. Iam not able to repair the corrupted Windows 2000 Advanced Serverinstallation but the file system is intact. I have installed a new copyof SQL Server 7 onto a new hard disk and have used the sp_attach_dbsystem stored procedure to attach the database from the old hard driveinto the new installation of SQL Server 7 on the new hard drive. Thedatabase shows up in the Enterprise Manager View and all the data andstructure are there, but when I try to run a Web site that uses ODBCconnections to the database I get the following error:ODBC Error Code = 08001 (Unable to connect to data source)[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL servernot found.I have double-checked that all the NTFS permissions, ODBC DSNs, and WebServer settings are correct and when I run the SQL Server ODBC drivertest at the completion of each ODBC DSN setup, it is successfull.1) Why am I getting this ODBC error?2) What steps have I missed in the recovery process?3) If I have done something wrong recovering my database, can I stillrecover it since I still have: a) The old master database file and logfile; b) The old database (non-master) file and log file; c)A backed-up(Enterprise Manager -> Right Click A Database -> All Tasks -> BackupDatabase) copy of the old database (non-master) file; ?My System Specifications:-Windows 2000 Advanced Server-SQL Server 7-IIS 5.0-Cold Fusion Server 4.5 Professional Full EditionThanks For Your Time,Nathan
View 2 Replies View RelatedSQL Server 6.5.
Hi!
When I try to create PK, Clustered index on the column in the table I have got error message: 1105 Couldn't not allocate space for object table1......
But it is a big amount of free space in the database. And actually I can create any indexes, exsept clustered with no problem.
DBCC CHECKALLOCK for this table shows: extent not ih the segment.
Does it mean the table structure is corupted? What Can I do to resolve the problem?
Thank you,
Elena.
Team,
I am having a critical problem.
We a team of 10 members accessing a particular database with a common user name and password . Some one have deleted the data from a business critical table. Transaction log backup is done every half an hour. Considering backup is taken at 10AM and my data is lost at 10.26AM. How to recover the lost data?
Is it possible to recover the data from log file or any other way to achieve the data recovery?
Is is possible to trace the person who have done this job. (By IP address or any other way)?
Regards,
Venkatesan Prabu . J
Hi,
I have a corrupted table wich has some valuable data in it. I found out that there is only one raw is corrupted. How do I find out which row of the table is it?
Can any one Help me in this matter Please?
Thanks Very much.
Jay k
Hi seniors
there are two tables involve in replication let say table1 and replicated table is also rep.table1.
we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial.
plz let me know where am i wrong and how i put it into right way.
there is no triggers on published tables and noother trigger is created on published table.
regards
Ahmad Drshen
this is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you
When my colleague makes any change to a query containing a derived table, the word 'WITH' followed by a lot of graphical characters appears after the alias, rendering the query unusable. We have tried this with various existing and new, simple queries, all to no avail. We are editing the queries in Visual Studio 2003, and he does not get this effect when using Visual Studio 2005. I can edit the query in 2003 on my laptop with no ill effects.
Help!
Karen
We are facing a weird scenario in which the snapshot is getting corrupted after insertupdate few million records in to a table .
SQL Server 2012
windows server 2008 R2
service pack 1
64-bit OS
Hi
I am stuck while doing synchronization between SQL Server 2000 and SQL Server CE 2.0.
getting an Error "Header information is either corrupted or missing."
My Specification is follow :
Windows XP with SP2
Visual Studio 2005 Enterprise edition
SQL Server 2000 (SP3a)
SQL Server CE 2.0
Application is Smart Device application (Pocket PC 2003) using C#.
Microsoft ActiveSync 4.5
IIS 5.1
I followed steps as per given in this URL :
http://msdn2.microsoft.com/en-us/library/ms839425.aspx#sql_serverce_replication_net_topic2I performed eeach steps successfully, My code is as per given below.
private void button1_Click(object sender, EventArgs e)
{
getSyncReady();
}
SqlCeReplication loSqlCeReplication;
private void getSyncReady()
{
try
{
String lsDBFile = "Northwind.sdf";
loSqlCeReplication = new SqlCeReplication();
loSqlCeReplication.InternetUrl = "http://192.168.0.5/Northwind/sscesa20.dll";
loSqlCeReplication.Publisher = "C99";
loSqlCeReplication.PublisherSecurityMode = SecurityType.NTAuthentication;
loSqlCeReplication.Publication = "NorthwindProducts";
loSqlCeReplication.PublisherDatabase = "Northwind";
loSqlCeReplication.Subscriber = Dns.GetHostName();
loSqlCeReplication.SubscriberConnectionString = "Data Source=" + lsDBFile;
if (!System.IO.File.Exists(lsDBFile))
{
MessageBox.Show("The application requires synchronization", "Replicator",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
loSqlCeReplication.AddSubscription(AddOption.CreateDatabase);
}
loSqlCeReplication.Synchronize();
}
catch (Exception foException)
{
throw foException;
}
}
I am not getting whats going wrong.
My Firewall is disable and i have not installed any antivirus softwares (including Norton internet Security....in many forums i got this suggestion but i think its not an issue,even though for solving this issue i performed new installation of Windows XP SP-2 )
I accidently made a duplicate record and I am unable to delete it. Everytime I try to delete, it gives me an error message saying:
Insufficient oe incorrect key coloumn information; too many rows affected by the update.
I am unable to delete either records. Is there another way of accomplishing this task. Thanks for your help in advance.
Bud
Hello i am new to MS SQL. Just moved in from MS Access.
I used to program in VB6.
mY Question is when i attemp to delete a record the followin error message displays
"Key column information is insufficiant or incorrect.Too many rows were affected by the update."
I have put the as code for the 'DELETE Record' command button in VB6 as follows.
adomain.Recordset.Delete adAffectCurrent
Please help me to safley delete a record from the SQL database using VB6.
Hi,
How can i delete a record from a table . table contains approx 3 million data and when i execute a query for delete it give error message from transaction log space problem.
Is there any way to stop the log in transaction log and delete the record.
thanks
please helpi was trying to delete a specific record on a table but it still showsthe same record that should be deleted. i dont know how.
View 3 Replies View Relatedwhats the best way to delete a parent record? for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?my code below: The T-SQL Stored ProcedureALTER PROCEDURE dbo.DeleteInventory
(
@InventoryID int
)
AS
DELETE FROM Booking
WHERE InventoryID = @InventoryID
DELETE FROM Inventory
WHERE InventoryID = @InventoryID
The code in the Code Behind to execute when a button(delete) click Try
Dim temp As Integer = SqlDataSource4.Delete
If temp > 0 Then
lblDeleteDtatus.ForeColor = Drawing.Color.Blue
lblDeleteDtatus.Text = temp & " Records/Rows Deleted."
Else
lblDeleteDtatus.ForeColor = Drawing.Color.Orange
lblDeleteDtatus.Text = "No records Deleted!"
End If
Catch ex As Exception
lblDeleteDtatus.ForeColor = Drawing.Color.Red
lblDeleteDtatus.Text = "An Error Occured.<br />" & ex.Message.ToString
End Try any better methods anyone wants to share?
OK,
This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:
DELETE FROM Invoices WHERE InvoiceID = 153345
Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.
TIA
Hi, I tried to delete a record in tblA which has an ID in tblB, i got error msg saying referential problem, please tell me what should i do? thanks.
View 2 Replies View RelatedHi ,
How can i delete the duplicate record from a table
use Northwind
create table Emp (Ecode char(2), Ename char(10))
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A4','D')
Insert into Emp(Ecode, Ename) values('A4','D')
select * from emp order by Ecode
Thanks
ASM
Hi everyone... I have a problem on deleting a record. I accidentally duplicate the record.
I need to delete other records and left only 1 record based on each date and employeenumber.
supposing i have a table hrempshiftschedule
i have 3 fields... empno, shiftdate, and shifttype
sample data...
empno shiftdate shifttype
1 1/1/2008 S1
1 1/1/2008 S1
2 1/1/2008 S2
2 1/1/2008 S2
2 1/1/2008 S2
2 1/2/2008 S2
2 1/2/2008 S2
2 1/3/2008 S3
3 1/1/2008 S3
4 1/4/2008 S4
4 1/5/2008 S5
expected output....
empno shiftdate shifttype
1 1/1/2008 S1
2 1/1/2008 S2
2 1/2/2008 S2
2 1/3/2008 S3
3 1/1/2008 S3
4 1/4/2008 S4
4 1/5/2008 S5
pls help and guide me.... thanks
RON
________________________________________________________________________________________________
"I won't last a day without SQL"
how can i recover delete record from a table. is possible to find who delete record from a table.
View 3 Replies View RelatedHi i have a very annying problem that i cant seem to solve by myself. I have developed a content managment system for a webpage where people can manage the page. It's almost done except for the fact that i cant seem to delete records from my sql express database.
To access the database i use an sql login in my code to delete witchever record is retrived from a querystring in the URL field. Below is a sample of my delete code when a button is pressed. protected void ButtonDelete_Click(object sender, EventArgs e)
{
string dID = Request.QueryString["dID"];
string myConnectionString = @"Data Source=SRVWEBSQLEXPRESS;Initial Catalog=se;User ID=xx;Password=xx";
SqlConnection myConnection = new SqlConnection(myConnectionString);
string myDeleteQuery = "DELETE FROM drift WHERE dID = @dID";
SqlCommand myCommand = new SqlCommand(myDeleteQuery);
myCommand.Parameters.AddWithValue("dID", dID);
myCommand.Connection = myConnection;
myConnection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
Response.Redirect("list.aspx");
}
When the i run the code on my development machine located at 10.12.0.80 and the server is located at 10.12.1.65 and this is where the databse is located. The strangest thing is that when i press my deletebutton while debugging in VS2008 on my devmachine the record is deleted! BUT when i run the code live on the server i get an error. See the below log file taken from the windows 2003 server application log.
Event Type: InformationEvent Source: ASP.NET 2.0.50727.0Event Category: Web Event Event ID: 1314Date: 2008-04-07Time: 10:26:45User: N/AComputer: SRVWEBDescription:Event code: 4011 Event message: An unhandled access exception has occurred. Event time: 2008-04-07 10:26:45 Event time (UTC): 2008-04-07 08:26:45 Event ID: 8bdda96aeee44448b570891c593bdb3e Event sequence: 242 Event occurrence: 1 Event detail code: 0 Application information: Application domain: /LM/W3SVC/1015505475/Root-1-128520196339603398 Trust level: Full Application Virtual Path: / Application Path: C:wwwwebsite Machine name: SRVWEB Process information: Process ID: 5156 Process name: w3wp.exe Account name: NT AUTHORITYNETWORK SERVICE Request information: Request URL: http://website/cms/drift/editdrift.aspx?dID=19 Request path: /cms/drift/editdrift.aspx User host address: 10.12.1.1 User: webmaster Is authenticated: True Authentication Type: Forms Thread account name: NT AUTHORITYNETWORK SERVICE
Could anyone help me solve this problem. Thanks.
If I script the SQL statement with a constant, deleting the record from the database works.
If I script the SQL statement to delete based on the WHERE clause being a variable name, it will not delete the record.
The value being compared in the WHERE clause comes directly from the Sequel database.
I have a dropdown box that is filled from the database. The dropdown1.selecteditem.text is placed in a variable. The script is to delete a record from the database where the table.name in the database equals the item name selected from the dropdown box.
This querystring does not delete the record from the database:
dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'variableCompareText')"
This querystring does delete the recrod from the database:
dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'John')"
Why can't I delete a record with a variable in the querystring? Otherwise, you would have to always know in advance which record to specify rather than being deleted dynamically.