Recover After Delete Statement
One of our developers just deleted a ton of records- is there any way we can recover this data? (we can't use a backup since a ton of changes were made since we last backed up)
Thanks-
Jack
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement: SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View Replies !
View Related
Help With DELETE Statement
Hello there... I'm creating a ASP.net Web Application and storing all my data in SQL database.I'm trying to create a Stored Procedure to Delete info from a table:- I have 3 tables: A, B and C: A's primary key is aID and has no foreign key B's primary key is bID and has 1 foreign key: aID (linking this table to table A); C's primary key is cID and has 1 foreign key: bID (linking this table to table B);- As you can see, all 3 tables are linked (A to B and B to C)I want to be able to DELETE all info from C only by giving aID, is this possible in SQL? I can retrieve the info easily through a SELECT statement and using an INNER JOIN (*). I also know how to do solve this by coding it in C# but I'd have to create some unnecessary variables and more than 1 Stored Procedure when it's probably possible to do all in one!* SELECT cID FROM C INNER JOIN B ON B.bID = C.bIDWHERE A.aID= whateverIf anyone knows the solution to my problem, please don't hesitate :p Thanks,SuperJB
View Replies !
View Related
Delete Statement
Hi,I would like to delete a record from a table on the condition that a corresponding ID is located in another table, ie. deleting an email message if the user ID is listed as a recipient in a recipient table etc. Here is my SQL statement: DELETE FROM id_email_message WHERE (id_message IN (SELECT id_message FROM recipients WHERE id_user = 324) AND message.id_message_status = 2) OR (id_message IN (SELECT id_message FROM message WHERE id_owner = 324 and id_message_status = 2)) The problem is the multiple select statements paired with the delete statement is too much overhead for the server and I always get a timeout server error (at least that's what I'm guessing, the error page and tracing isn't much helpful). Is there a more efficient way to do this?Thanks.Eitan
View Replies !
View Related
Sql Delete Statement
hi, i want to delete some records from my table if there is more then 150 records (it should always be max 150records, it can be less and then it shouldent delete anything), so when it goes over to 151 records i want to delete the oldest record, so i get the new record + 149 old records, is there a simple way to do this?
View Replies !
View Related
Delete Statement
I accedentally doubled the amount of records in my table using a DTS package. How can I remove the records ? I beleive they were appended to the table. What would be the syntax in the statement that I need to do this? Thanks, Mark
View Replies !
View Related
DELETE Statement
When does the DELETE statement physically deletes the records? For example, if I execute the DELETE statement and in the middle of the execution I understand that it is wrong. What will happen if I stop it? Will it delete the records partially? I think the deletion happens when the full statement is done but need an expert answer. Thank you.
View Replies !
View Related
SQL Delete Statement
Hi what do i need to add to this stmt to delete the result ? --------------- select ct_cust1_text01,ct_address,ct_cust1_text09,count(*)from TABLE_NAME group by ct_cust1_text01,ct_address,ct_cust1_text09 having count(*) > 1 --------- i have tried delete * from TABLE_NAME where (select...) not great at SQL appreciate any help...
View Replies !
View Related
Trigger On Delete Statement
Hi! I am deleting some records from the database and at the same time inserting them into a new table. I am doing all this with sql querries. How can that be done with triggers. Basically on the delete, i'd like to insert the affected records. Thanks! James
View Replies !
View Related
DELETE Statement Conflicted
Hello I am trying to delete a row from one table and I expected it to also be removed from the subsequent child tables, linked via foreign and primary keys. However, when I tried to delete a row in the first table I saw this error: DELETE FROM [dbo].[Names_DB]WHERE [LName_Name]=N'andrews' Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_LName_Name'. The conflict occurred in database 'MainDB', table 'Category_A', column 'LName_Name'. I went to the very last table in the sequence and I was able to delete the row without problems, but it did not effect any of the other tables. Please advise. I need to make many changes in these tables, should I use a trigger instead, if so what is the code to trigger each table? I am new to triggers. Thanks Regards Lynn
View Replies !
View Related
Only One Of My SQL Delete Statement Are Firing
Hey guys... I am trying to tidy up my code a bit and have one SQL command (Sub class) to call when needing to insert, update, delete and select. I have got one class I am testing with that delete from a table support_ticket and then calls RunSQL() again to delete the corresponding tickets in Support_Reply. however it only seems to want to delete from one table at a time...as i commented out the first sql and it worked and the second fires...but if the first one is active it doesnt fire. Do anyone on the forum know why this has happened? Sub DeleteUserTicket(sender as Object, e as EventArgs) Dim strSQL1 = "DELETE FROM Support_Ticket WHERE (TicketID = " & txtticketID & ")" RunSQL(strSQL1) strSQL1 = "DELETE FROM Support_Reply WHERE (TicketID = " & txtticketID & ")" RunSQL(strSQL1) End Sub 'One class to run the sql statements for the entire page this will reduce in repetitve code as well as reduce code size Sub RunSQL(strSQL) Dim objCmd As SqlCommand Dim sqlConn = New SqlConnection(strConn) objCmd = New SQLCommand(strSQL, sqlConn) objCmd.Connection.Open() 'Check that the rows can be deleted if not then return a error. Try objCmd.ExecuteNonQuery() response.redirect("ticketsystemtest2.aspx") Message.InnerHtml = "<b>Ticket " & txtticketID & " Closed</b> <br/>" Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else Message.InnerHtml = "ERROR: Could not update record, please " _ & "ensure the fields are correctly filled out <br>" & ex.Message & " " & ex.Number Message.Style("color") = "red" End If End Try objCmd.Connection.Close() sqlConn = nothing objcmd = nothing End Sub cheers
View Replies !
View Related
Tuning A Delete Statement
Hi, I need to delete the following records (from enrollment_fact): SELECT a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID" FROM "dbo"."ENROLLMENT_FEX2" b, "dbo"."LOCATION_DIM" c LEFT OUTER JOIN "dbo"."ENROLLMENT_FACT" a on c."LOC_SID" = a."LOC_SID" WHERE b."LOC_KEY" = c."LOC_KEY" and a."DATE_SID" between b."MIN_DATE" and b."MAX_DATE" This is the approach (excuse the misuse of the concat function, but you get the idea) DELETE FROM "dbo"."ENROLLMENT_FACT" WHERE CONCAT (a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID") IN ( SELECT DISTINCT CONCAT (a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID" ) FROM "dbo"."ENROLLMENT_FEX2" b, "dbo"."LOCATION_DIM" c LEFT OUTER JOIN "dbo"."ENROLLMENT_FACT" a ON c."LOC_SID" = a."LOC_SID" AND a."DATE_SID" BETWEEN b."MIN_DATE" AND b."MAX_DATE") comments? better way? (without using an sp) thanks
View Replies !
View Related
Delete Statement Using A Join Plz Help
well i have 2 table one name detcom and another entcom stored in DB1 the key for both to join on is lets say A, B, C . I need to check if there are records based on the key A, B, C of both table where C EQUALS to '80_300_113' and if there are delete them and then grab data from another database named DB2 on same server (same instance) wich contains the same tables entcom and detcom and insert all the data from those tables into the same tables in DB1 based on the key and where C = '80_300_113' PLZ help
View Replies !
View Related
Delete Statement Won't Work!!
i am having problem running a simple delete statement against a table. it just hangs is there anything i should look at? the table has 4 primary keys and the index makes up of the 4 keys and ideas? i viewed the delete statement with the execusion plan and this is what i saw. delete -> index delete/delete -> sorting the input -> table delete/delete -> Top -> Index scan.
View Replies !
View Related
Delete Statement Very Slow
Hi, I've got a table with about 500 000 records and growing monthly by about 40 000 records When I perform the following query: DELETE from [myTable] WHERE Month = '07' AND Year='2005' This query will take about 10 minutes to execute. Columns Month & Year are both indexed. Surely MSSQL can't be this slow on only 500 000 records. Must I do some other database optimization ??? Thanks
View Replies !
View Related
Join In A Delete Statement???
Hi all, I need to do a delete statement that removes rows from table 2, where data was created before data in table 1. Here's my select statement that shows me the results that meet the criteria: Select DP_ACC.ACC_NO, DP_EVENT.EVENT, DP_ACC.CALL_DATE, DP_EVENT.EVENT_DATE1 FROM DP_ACC Inner Join DP_EVENT on DP_ACC.ACC_NO = DP_EVENT.ACC_NO Where CONVERT(DATETIME, DP_EVENT.EVENT_DATE1) < CONVERT(DATETIME, DP_ACC.CALL_DATE) How do I change this into a delete statement to remove the rows from DP_EVENT?
View Replies !
View Related
Please Help Me With A Complex DELETE Statement
Hello, currently I have a query like this: PHP Code: SELECT * FROM relations INNER JOIN paths ON relations.path = paths.path_id WHERE (paths.links = '161') AND (relations.node1 = 162) OR (paths.links = '161') AND (relations.node2 = 162) OR (paths.links = '162') AND (relations.node1 = 161) OR (paths.links = '162') AND (relations.node2 = 161) OR (paths.links LIKE '162%') AND (relations.node1 = 161) OR (paths.links LIKE '%162') AND (relations.node2 = 161) OR (paths.links LIKE '161%') AND (relations.node1 = 162) OR (paths.links LIKE '%161') AND (relations.node2 = 162) OR (paths.links LIKE '%161;162%') OR (paths.links LIKE '%162;161%') ORDER BY relations.node1 Don't pay attention to the 161 and 162 things, is just test data, now my problem is that I want to transform that into a DELETE statement, but I can't find the right way to do it, so far I managed to do something like: PHP Code: DELETE relations FROM relations INNER JOIN paths ON relations.path = paths.path_id WHERE (paths.links = '161') AND (relations.node1 = 162) OR (paths.links = '161') AND (relations.node2 = 162) OR (paths.links = '162') AND (relations.node1 = 161) OR (paths.links = '162') AND (relations.node2 = 161) OR (paths.links LIKE '162%') AND (relations.node1 = 161) OR (paths.links LIKE '%162') AND (relations.node2 = 161) OR (paths.links LIKE '161%') AND (relations.node1 = 162) OR (paths.links LIKE '%161') AND (relations.node2 = 162) OR (paths.links LIKE '%161;162%') OR (paths.links LIKE '%162;161%') But that would delete only from the relations table and not from the paths table. I need to delete from both tables. Can anyone help me please? Its kinda urgent. Thansk!
View Replies !
View Related
Delete Statement Question
I have a simple database program which uses the following statement to delete rows by a date range. In testing the SQL statement, it worked, but when using it in the VB program, it says it worked, but doesn't remove the row. I am using a local database. Please help. cmdDeletePayments.CommandType = Data.CommandType.Text cmdDeletePayments.CommandText = "DELETE Payments WHERE (CheckDate BETWEEN @BeginningDate AND @EndingDate)" cmdDeletePayments.Parameters("@BeginningDate").Value = CType(dtpBeginning.Text, Date) cmdDeletePayments.Parameters("@EndingDate").Value = CType(dtpEnding.Text, Date) Try conPayments.Open() iRows = cmdDeletePayments.ExecuteNonQuery() conPayments.Close() Catch ex As Exception MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try MessageBox.Show(iRows.ToString + " records deleted.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
View Replies !
View Related
512 Error In Delete Statement
I have a piece of code that uses the db-library with sql server 2000/2005 and runs the following delete statement: DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' ) AND Col3 IN( integer1 ) AND Col4 IN( integer2 ) AND Col5 IN( 'Some Val3' ) on TABLE1, uploads data into TABLE1 through bulk loading, calls a stored procedure that uses the data, and then deletes the data through the SAME delete statement with EXACTLY the same parameter values. The first delete statement is always successful, but the second statement intermittently gives the following error: 0,0,MS SQL Server Message : SQL Server message 512, state 1, severity 16: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. SQL Server message 3621, state 0, severity 0: The statement has been terminated. Note: I was initially using the equality operator instead of the IN operator in the query but that gave the same results. Can somebody tell me whats going wrong here? I can easily ignore this error because my work is done after the stored proc but I fear amassing a lot of useless data in the table over time. Also http://support.microsoft.com/kb/195491 talks about a case where the delete statement is actually successful but still causes an error when using ADO.
View Replies !
View Related
Insert And Delete In One Statement
i am creating an insert based on a select statement -- i need to delete the row from the select statment table after it has been inserted something like insert into table_insert(value1, value2) (select table_exclude_id, value1, value2 from table exclude) delete from table_exclude where table_exclude_id in "the select statement" can you do this?
View Replies !
View Related
Delete Statement In Function
Is it possible to create a function that deletes records from a table ? CREATE FUNCTION F_TSImported_Delete() returns int as Begin delete from ts_imported return 0 end GO This throws error like this: Invalid use of side-effecting or time-dependent operator in 'DELETE' within a function.
View Replies !
View Related
Very Tricky Delete Statement
I have a rather tricky SQL delete query I’m trying to perform, preferably without cursors. I have the following data set, where I want each of the first changes to the status. IDStatus 1Good 2So-So 3So-So 4Bad 5Bad 6Bad 7Bad 8Bad 9So-So 10Good So given the above data set I would want to delete the following IDs: 3, 5, 6, 7, and 8. As they not the first in order. The problem is that while I can get isolate and save first instance of each status (in this case 1, 2, and 4), I can’t get the what I want. Is there a SQL way of accomplishing this.
View Replies !
View Related
Delete Statement With Parameters Not Working
When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok? // Set up SqlCommand, connection to db, sql statement, etc. SqlCommand DeleteCommand = new SqlCommand(); DeleteCommand.Connection = DBConnectionClass.myConnection; DeleteCommand.CommandType = CommandType.Text; // Store Primary Key photoID passed here from DeleteRows_Click // in a parameter for DeleteCommand SqlParameter DeletePrimaryKeyParam = new SqlParameter(); DeletePrimaryKeyParam.ParameterName = "@PhotoID"; DeletePrimaryKeyParam.Value = photoID.ToString(); // Insert new parameter into command object DeleteCommand.Parameters.Add(DeletePrimaryKeyParam); // Delete row, open connection, execute, close connection DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)"; Response.Write(DeleteCommand.CommandText); // DeleteCommand.Connection.Close(); DeleteCommand.Connection.Open(); DeleteCommand.ExecuteNonQuery(); DeleteCommand.Connection.Close();
View Replies !
View Related
Is The Sql Delete Statement Case Sensitive?
I need to delete a record from a SQL server database table. I need to target a specific column with a query that looks like the following: delete from NameTable where Name = 'Barbosa' The problem is that their are two other records with the name spelled similarly, only differing by case sensitivity. Like as follows: 'Barbosa' 'barbosa' 'BarBosa' Is sql case sensitive? Will my Sql delete statement only delete the record with the word 'Barbosa' in that column. Or will it delete all of the records with the names 'Barbosa' 'barbosa' 'BarBosa'
View Replies !
View Related
SQL Data Source Delete Statement
Hey everyone, Currently I have a problem that with a gridview delete command because the database I currently want to delete from is a PK to 1 other table. So how can I delete from that "Membership" table before I use the gridview to delete from the "Events" table? Here is a very shortened version of the tables Events EventID (PK) Membership MembershipID EventID (FK) UserID (FK) I have to delete ALL the members from the event in the membership table first before I can delete the actual Event...So how can I do that when the gridview only allows you to delete from 1 table, if I understand this correctly. Here is the sqldatasource delete command as of right now. DeleteCommand="DELETE FROM [Events_Events] WHERE [EventID] = @original_EventID" Obviously I am getting a error because of the FK. Thanks, Chris
View Replies !
View Related
Update And Delete Statement Issues
I'm working on a address book where customers can add, edit and delete address book entries. For the life of me I can't figure out what I'm messing up with the Update and Delete statements of this feature. Can someone please help me.Here's my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:kalistadbConnectionString %>" DeleteCommand="DELETE FROM [Address] WHERE [AddID] = @original_AddID AND [AddNick] = @original_AddNick AND [AddFN] = @original_AddFN AND [AddLN] = @original_AddLN AND [AddCompany] = @original_AddCompany AND [AddAddress] = @original_AddAddress AND [AddCity] = @original_AddCity AND [AddProv_State] = @original_AddProv_State AND [AddPostal_Zip] = @original_AddPostal_Zip AND [AddCountry] = @original_AddCountry AND [AddPhone] = @original_AddPhone AND [CustID] = @original_CustID" InsertCommand="INSERT INTO [Address] ([AddNick], [AddFN], [AddLN], [AddCompany], [AddAddress], [AddCity], [AddProv_State], [AddPostal_Zip], [AddCountry], [AddPhone], [CustID]) VALUES (@AddNick, @AddFN, @AddLN, @AddCompany, @AddAddress, @AddCity, @AddProv_State, @AddPostal_Zip, @AddCountry, @AddPhone, @CustID)" OldValuesParameterFormatString="original_{0}" onselecting="SqlDataSource1_Selecting" OnInserting="SqlDataSource1_Inserting" SelectCommand="SELECT * FROM [Address] WHERE ([CustID] = @CustID)" UpdateCommand="UPDATE [Address] SET [AddNick] = @AddNick, [AddFN] = @AddFN, [AddLN] = @AddLN, [AddCompany] = @AddCompany, [AddAddress] = @AddAddress, [AddCity] = @AddCity, [AddProv_State] = @AddProv_State, [AddPostal_Zip] = @AddPostal_Zip, [AddCountry] = @AddCountry, [AddPhone] = @AddPhone, [CustID] = @CustID WHERE [AddID] = @original_AddID AND [AddNick] = @original_AddNick AND [AddFN] = @original_AddFN AND [AddLN] = @original_AddLN AND [AddCompany] = @original_AddCompany AND [AddAddress] = @original_AddAddress AND [AddCity] = @original_AddCity AND [AddProv_State] = @original_AddProv_State AND [AddPostal_Zip] = @original_AddPostal_Zip AND [AddCountry] = @original_AddCountry AND [AddPhone] = @original_AddPhone AND [CustID] = @original_CustID"> <SelectParameters> <asp:Parameter Name="CustID" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_AddID" Type="Int64" /> <asp:Parameter Name="original_AddNick" Type="String" /> <asp:Parameter Name="original_AddFN" Type="String" /> <asp:Parameter Name="original_AddLN" Type="String" /> <asp:Parameter Name="original_AddCompany" Type="String" /> <asp:Parameter Name="original_AddAddress" Type="String" /> <asp:Parameter Name="original_AddCity" Type="String" /> <asp:Parameter Name="original_AddProv_State" Type="String" /> <asp:Parameter Name="original_AddPostal_Zip" Type="String" /> <asp:Parameter Name="original_AddCountry" Type="String" /> <asp:Parameter Name="original_AddPhone" Type="String" /> <asp:Parameter Name="original_CustID" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="AddNick" Type="String" /> <asp:Parameter Name="AddFN" Type="String" /> <asp:Parameter Name="AddLN" Type="String" /> <asp:Parameter Name="AddCompany" Type="String" /> <asp:Parameter Name="AddAddress" Type="String" /> <asp:Parameter Name="AddCity" Type="String" /> <asp:Parameter Name="AddProv_State" Type="String" /> <asp:Parameter Name="AddPostal_Zip" Type="String" /> <asp:Parameter Name="AddCountry" Type="String" /> <asp:Parameter Name="AddPhone" Type="String" /> <asp:Parameter Name="CustID" /> <asp:Parameter Name="original_AddID" Type="Int64" /> <asp:Parameter Name="original_AddNick" Type="String" /> <asp:Parameter Name="original_AddFN" Type="String" /> <asp:Parameter Name="original_AddLN" Type="String" /> <asp:Parameter Name="original_AddCompany" Type="String" /> <asp:Parameter Name="original_AddAddress" Type="String" /> <asp:Parameter Name="original_AddCity" Type="String" /> <asp:Parameter Name="original_AddProv_State" Type="String" /> <asp:Parameter Name="original_AddPostal_Zip" Type="String" /> <asp:Parameter Name="original_AddCountry" Type="String" /> <asp:Parameter Name="original_AddPhone" Type="String" /> <asp:Parameter Name="original_CustID" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="AddNick" Type="String" /> <asp:Parameter Name="AddFN" Type="String" /> <asp:Parameter Name="AddLN" Type="String" /> <asp:Parameter Name="AddCompany" Type="String" /> <asp:Parameter Name="AddAddress" Type="String" /> <asp:Parameter Name="AddCity" Type="String" /> <asp:Parameter Name="AddProv_State" Type="String" /> <asp:Parameter Name="AddPostal_Zip" Type="String" /> <asp:Parameter Name="AddCountry" Type="String" /> <asp:Parameter Name="AddPhone" Type="String" /> <asp:Parameter Name="CustID" /> </InsertParameters> </asp:SqlDataSource>
View Replies !
View Related
Index Hint In Delete Statement?
According to what I see in BOL, the following should work: delete from dbo.tbl1 WITH (INDEX(idx_un01)) where tbl1_no = 1 Yet when I syntax check this I get: Msg 1069, Level 15, State 1, Line 2 Index hints are only allowed in a FROM clause. (Please ignore the fact that index hints are unnecessary / a bad idea / etc.)
View Replies !
View Related
Alter Statement To Delete The Default Value Set
Hi I want to delete the Default value for a specific column which is set to Null I've used ALTER TABLE SYSTEMS_PATIENT_LOG ALTER COLUMN SYSTEMS_LOGID DROP DEFAULT It is giving error Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'DEFAULT'. plz could any one tell me where I could be wrong
View Replies !
View Related
SQL Delete Statement In Relational Model
Hi, I have a large database with complex relations and I just can't figure out how to delete a single record and all its relations across database (not to mention that I can't understand why can't the DELETE statement work just like the SELECT). It would be impossible to let you know the structure through this message, but let's say that I have 5 tables in which there are relations like this (all the tables contains a unique ID for each record, but not all contains info of the main record): Production -> ProductionTypes Sales -> ProductionTypes Production -> Sales Sales -> Personnel Personnel --> Orders Orders --> Financial I hope we agree that if I delete the ProductionID.Production directly, there will be no way to know which Financial.Commission I have to delete because I just can't insert the ProductionID in every table on the database. What do I have to do ? I just couldn't find any solid information regarding this subject on the net. Many many thanks in advance ! George
View Replies !
View Related
NewBie: Problems With DELETE Statement
I am trying to delete records in a table if a flag set in a linked table is set, but seem to be having problems with it. Neither of the following work: DELETE FROM DSCar WHERE SerialNum IS NOT NULL AND DSCar.SerialNum=DSOrds.SerialNum AND DSOrds.StatusFlag='3' This reports DSOrds.SerialNum could not be bound DELETE FROM DSCar INNER JOIN DSOrds ON DSCar.SerialNum=DSOrds.SerialNum WHERE DSOrds.StatusFlag='3' This reports Incorrect syntax near the keyword 'INNER' But a similar SELECT statement works fine: SELECT * FROM DSCar INNER JOIN DSOrds ON DSCar.SerialNum=DSOrds.SerialNum WHERE DSOrds.StatusFlag='3' I think it is something simple but am having a real head scratcher. Thanks
View Replies !
View Related
Single Statement To Delete Record Into More Tables
Hi , I little question for you ... is it possibile to write a SQL statement to delete records in several tables at the same time? For example if I've two tables involved by join DELETE <...> from Customers A INNER JOIN CustomerProperties B ON A.CustomerID=B.CustomerID I Must use two statement to remove records from both the tables? Thx
View Replies !
View Related
Delete Statement Times Out And Blocks Reads
Hi. Periodically I need to run a delete statement that deletes old data. The problem is that this can timeout using ODBC (via the CDatabase and CRecordSet classes in legacy code). Also, while its running the delete, the table its operating on is locked and my application can't continue to run and operate on rows not affected by the delete. Are there any workarounds for this? Can the timeout be set in the connect string? Thanks, Brian
View Replies !
View Related
How To Write A Delete Statement In Linked Server
Hi all, We are retriving tables from DB2 and loading into a SQL server. while retriving temporary tables are getting created in the linked server. we need to delete the data in those temporary tables in Linked server. I don't know how to write delete statement for the tables in linked server. any pointers would be appreciated. Thanks in advance. Shriram
View Replies !
View Related
INSERT, UPDATE, And DELETE Statement Checkbox Inactive
Hi AllgI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advancehttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPGhttp://img139.a.com/img.php?image=28285_2_122_937lo.JPG
View Replies !
View Related
Getting An Error The DELETE Statement Conflicted With The REFERENCE Constraint &"FK_Detail_Header
Hello All, i have 2 files in excel which i am uploading them to a folder which is located in the root directory. then i am importing these two files into a sqldatabase. i needed some help in importing them and i got it from my previous post at http://forums.asp.net/t/1261155.aspx but now i have a problem. the import works perfectly first time but when i do it the second time i am getting an error on of the file (header). to explain clearly, what i am doing is , every time i upload a new file , i am deleting the data from the tables, so the new data can be inserted ( basically trying to achieve overwirting the existing data). now this technique works fine with the Detail table but not with the Header table. i think the reason is the header table has a primary key on OrderID and a relationship does exists between the Header and Detail. now how would i overcome this error. can some one please guide me. I really appreciate it.here is my code: // connection for header file protected OleDbCommand headExcelConnection() { // Connect to the Excel Spreadsheet string headConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/imports/headerorder.xls") + ";" + "Extended Properties=Excel 8.0;"; // create your excel connection object using the connection string OleDbConnection headXConn = new OleDbConnection(headConnStr); headXConn.Open(); // use a SQL Select command to retrieve the data from the Excel Spreadsheet // the "table name" is the name of the worksheet within the spreadsheet // in this case, the worksheet name is "Sheet1" and is expressed as: [Sheet1$] OleDbCommand headCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", headXConn); return headCommand; } // importing header information protected void BtnImpHeader_Click(object sender, EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = false; PanelImport.Visible = true; LabelImport.Text = ""; // reset to blank // Create a new Adapter OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); // retrieve the Select command for the Spreadsheet objDataAdapter.SelectCommand = headExcelConnection(); // Create a DataSet DataSet objDataSet = new DataSet(); // Populate the DataSet with the spreadsheet worksheet data objDataAdapter.Fill(objDataSet); // deleting the exisitng table before copy SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); SqlCommand SqlCmd = null; mycon.Open(); SqlCmd = mycon.CreateCommand(); SqlCmd.CommandText = "DELETE FROM Header"; ---- showing error over on second time SqlCmd.ExecuteNonQuery(); mycon.Close(); // entering the newer header information SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); mysqlcon.Open(); foreach (DataRow dr in objDataSet.Tables[0].Rows) { String sqlinsert = "insert into Header values(@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8,@param9,@param10,@param11,@param12,@param13,@param14,@param15,@param16,@param17,@param18,@param19,@param20,@param21,@param22,@param23,@param24,@param25,@param26,@param27,@param28,@param29,@param30,@param31,@param32)"; SqlCommand cmd = new SqlCommand(sqlinsert, mysqlcon); cmd.Parameters.AddWithValue("@param1", dr[0].ToString()); cmd.Parameters.AddWithValue("@param2", dr[1].ToString()); cmd.Parameters.AddWithValue("@param3", dr[2].ToString()); cmd.Parameters.AddWithValue("@param4", dr[3].ToString()); cmd.Parameters.AddWithValue("@param5", dr[4].ToString()); cmd.Parameters.AddWithValue("@param6", dr[5].ToString()); cmd.Parameters.AddWithValue("@param7", dr[6].ToString()); cmd.Parameters.AddWithValue("@param8", dr[7].ToString()); cmd.Parameters.AddWithValue("@param9", dr[8].ToString()); cmd.Parameters.AddWithValue("@param10", dr[9].ToString()); cmd.Parameters.AddWithValue("@param11", dr[10].ToString()); cmd.Parameters.AddWithValue("@param12", dr[11].ToString()); cmd.Parameters.AddWithValue("@param13", dr[12].ToString()); cmd.Parameters.AddWithValue("@param14", dr[13].ToString()); cmd.Parameters.AddWithValue("@param15", dr[14].ToString()); cmd.Parameters.AddWithValue("@param16", dr[15].ToString()); cmd.Parameters.AddWithValue("@param17", dr[16].ToString()); cmd.Parameters.AddWithValue("@param18", dr[17].ToString()); cmd.Parameters.AddWithValue("@param19", dr[18].ToString()); cmd.Parameters.AddWithValue("@param20", dr[19].ToString()); cmd.Parameters.AddWithValue("@param21", dr[20].ToString()); cmd.Parameters.AddWithValue("@param22", dr[21].ToString()); cmd.Parameters.AddWithValue("@param23", dr[22].ToString()); cmd.Parameters.AddWithValue("@param24", dr[23].ToString()); cmd.Parameters.AddWithValue("@param25", dr[24].ToString()); cmd.Parameters.AddWithValue("@param26", dr[25].ToString()); cmd.Parameters.AddWithValue("@param27", Convert.ToDecimal(dr[26].ToString())); cmd.Parameters.AddWithValue("@param28", Convert.ToDecimal(dr[27].ToString())); cmd.Parameters.AddWithValue("@param29", Convert.ToDecimal(dr[28].ToString())); cmd.Parameters.AddWithValue("@param30", Convert.ToDecimal(dr[29].ToString())); cmd.Parameters.AddWithValue("@param31", Convert.ToDecimal(dr[30].ToString())); cmd.Parameters.AddWithValue("@param32", dr[31].ToString()); cmd.ExecuteNonQuery(); // new SqlCommand (stmt, mysqlcon).ExecuteNonQuery(); LabelImport.Text = "Rows Inserted"; } mysqlcon.Close(); }// connection for detail file protected OleDbCommand detExcelConnection() { // Connect to the Excel Spreadsheet string detConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/imports/detailorder.xls") + ";" + "Extended Properties=Excel 8.0;"; // create your excel connection object using the connection string OleDbConnection detXConn = new OleDbConnection(detConnStr); detXConn.Open(); // create your excel connection object using the connection string // use a SQL Select command to retrieve the data from the Excel Spreadsheet // the "table name" is the name of the worksheet within the spreadsheet // in this case, the worksheet name is "Sheet1" and is expressed as: [Sheet1$] OleDbCommand detCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", detXConn); return detCommand; }// importing detail information protected void ButtonImport_Click(object sender, EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = false; PanelImport.Visible = true; LabelImport.Text = ""; // reset to blank // Create a new Adapter OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); // retrieve the Select command for the Spreadsheet objDataAdapter.SelectCommand = detExcelConnection(); // Create a DataSet DataSet objDataSet = new DataSet(); // Populate the DataSet with the spreadsheet worksheet data objDataAdapter.Fill(objDataSet); // deleting the exisitng table before copy SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); SqlCommand SqlCmd = null; mycon.Open(); SqlCmd = mycon.CreateCommand(); SqlCmd.CommandText = "DELETE FROM Detail"; SqlCmd.ExecuteNonQuery(); mycon.Close(); // entering newer detail information SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); mysqlcon.Open(); foreach (DataRow dr1 in objDataSet.Tables[0].Rows) { String sqlinsert = "insert into Detail values(@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8,@param9,@param10,@param11,@param12,@param13)"; SqlCommand cmd = new SqlCommand(sqlinsert, mysqlcon); cmd.Parameters.AddWithValue("@param1", dr1[0].ToString()); cmd.Parameters.AddWithValue("@param2", dr1[1].ToString()); cmd.Parameters.AddWithValue("@param3", dr1[2].ToString()); cmd.Parameters.AddWithValue("@param4", dr1[3].ToString()); cmd.Parameters.AddWithValue("@param5", dr1[4].ToString()); cmd.Parameters.AddWithValue("@param6", dr1[5].ToString()); cmd.Parameters.AddWithValue("@param7", dr1[6].ToString()); cmd.Parameters.AddWithValue("@param8", Convert.ToDecimal(dr1[7].ToString())); cmd.Parameters.AddWithValue("@param9", Convert.ToDecimal(dr1[8].ToString())); cmd.Parameters.AddWithValue("@param10", dr1[9].ToString()); cmd.Parameters.AddWithValue("@param11", dr1[10].ToString()); cmd.Parameters.AddWithValue("@param12", dr1[11].ToString()); cmd.Parameters.AddWithValue("@param13", dr1[12].ToString()); cmd.ExecuteNonQuery(); LabelImport.Text = "Rows Inserted"; } mysqlcon.Close(); } the error is as follows: Server Error in '/WebSite6' Application. The DELETE statement conflicted with the REFERENCE constraint "FK_Detail_Header". The conflict occurred in database "C:DOCUMENTS AND SETTINGSMEMY DOCUMENTSVISUAL STUDIO 2005WEBSITESWEBSITE6APP_DATADATABASE.MDF", table "dbo.Detail", column 'OrderID'.The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Detail_Header". The conflict occurred in database "C:DOCUMENTS AND SETTINGSMEMY DOCUMENTSVISUAL STUDIO 2005WEBSITESWEBSITE6APP_DATADATABASE.MDF", table "dbo.Detail", column 'OrderID'.The statement has been terminated.Source Error: Line 176: SqlCmd = mycon.CreateCommand();Line 177: SqlCmd.CommandText = "DELETE FROM Header";Line 178: SqlCmd.ExecuteNonQuery();Line 179: mycon.Close();Line 180: again i really appreciate.Thanks
View Replies !
View Related
Delete Statement For A List Of Items With Multiple Columns Identifying Primary Key
I frequently have the problem where I have a list of items to delete ina temp table, such asProjectId Description------------- ----------------1 test12 test43 test34 test2And I want to delete all those items from another table.. What is thebest way to do that? If I use two IN clauses it will do it where itmatches anything in both, not the exact combination of the two. I can'tdo joins in a delete clause like an update, so how is this typicallyhandled?The only way I can see so far to get around it is to concatenate thecolumns like CAST(ProjectId as varchar) + '-' + Description and do anIN clause on that which is pretty nasty.Any better way?
View Replies !
View Related
The DELETE Statement Conflicted With The REFERENCE Constraint &"FK__aspnet_Me__UserI__15502E78&".
Hello, I try to delete a user from the Membership table but I receive this error. "The DELETE statement conflicted with the REFERENCE constraint "FK__aspnet_Me__UserI__15502E78". The conflict occurred in database "E:INETPUBWEBSITE4APP_DATAASPNETDB.MDF", table "dbo.aspnet_Membership", column 'UserId'.The statement has been terminated."...<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" DataKeyNames="UserId" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:CommandField ShowDeleteButton="True" /> <asp:BoundField DataField="UserId" HeaderText="UserId" SortExpression="UserId" /> <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" /> <asp:BoundField DataField="LastActivityDate" HeaderText="LastActivityDate" SortExpression="LastActivityDate" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" DeleteCommand="DELETE FROM [aspnet_Users] WHERE [UserId] = @UserId" InsertCommand="INSERT INTO [aspnet_Users] ([ApplicationId], [UserId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate]) VALUES (@ApplicationId, @UserId, @UserName, @LoweredUserName, @MobileAlias, @IsAnonymous, @LastActivityDate)" ProviderName="<%$ ConnectionStrings:ASPNETDBConnectionString2.ProviderName %>" SelectCommand="SELECT [ApplicationId], [UserId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate] FROM [aspnet_Users]" UpdateCommand="UPDATE [aspnet_Users] SET [ApplicationId] = @ApplicationId, [UserName] = @UserName, [LoweredUserName] = @LoweredUserName, [MobileAlias] = @MobileAlias, [IsAnonymous] = @IsAnonymous, [LastActivityDate] = @LastActivityDate WHERE [UserId] = @UserId"> <InsertParameters> <asp:Parameter Name="ApplicationId" Type="Object" /> <asp:Parameter Name="UserId" Type="Object" /> <asp:Parameter Name="UserName" Type="String" /> <asp:Parameter Name="LoweredUserName" Type="String" /> <asp:Parameter Name="MobileAlias" Type="String" /> <asp:Parameter Name="IsAnonymous" Type="Boolean" /> <asp:Parameter Name="LastActivityDate" Type="DateTime" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="ApplicationId" Type="Object" /> <asp:Parameter Name="UserName" Type="String" /> <asp:Parameter Name="LoweredUserName" Type="String" /> <asp:Parameter Name="MobileAlias" Type="String" /> <asp:Parameter Name="IsAnonymous" Type="Boolean" /> <asp:Parameter Name="LastActivityDate" Type="DateTime" /> <asp:Parameter Name="UserId" Type="Object" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="UserId" Type="Object" /> </DeleteParameters> </asp:SqlDataSource></Content> ... cheers,imperialx
View Replies !
View Related
How To Trap &"DELETE Statement Conflicted With COLUMN REFERENCE Constraint&" Error
Hi, On my aspx Web page, I want to delete a member from database table 'tblMember', but if this MemberID is used as FK in another table, I want to display a user friendlier message like "You cannot delete this member, ....." I am using Try, Catch blocks in my Web Page. Currently it display this message: "DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_..._....' The conflict occurred in database '...', table 'tblMembers', column 'MemberID'. The statement has been terminated. " So how should I precisely trap this error? Does anybody know what Exception is it? or what error number in SQL server? Thanks
View Replies !
View Related
&&"Do Not Replicate DELETE Statement&&" Feature Problem
I am testing SQL Server 2005. I need a replication where "DELETE delivery format" field in Table Articles Properties is set to "Do not replicate DELETE statement". Unfortunately nothing I set in this dialog is saved and used. Doesn matter what I change the replication behaves the same and the next time I open "Table Articles Properties", every value is still default. Am I doing something wrong or is it an issue (bug) in 2005 replication? I googled about the issue and also searched in the SQL Server Replication threads on this site, both with no success. The thread named "Selective replication of DELETE transactions" doesn't provide enough information about SWL Server 2005 replication configuration. Use Case: I have a small "operational" database with live data. I need to keep a complete history for all records that ever appeared within the "operational" DB. It came to my mind that I can "easily" achieve this if I set up a replication that does not replicate a delete statement ever. I strongly prefer not to mess with the subscription stored procedures but to configer my publication properly instead.
View Replies !
View Related
Copy And Delete Table With &"Foreign Key References(...,...) On Delete Cascade?
Hello: Need some serious help with this one... Background: Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software... Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's. The Problem: There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name. This appears possible in SqlServer too --...as long as there are no CASCADE operations. Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table. So -- please correct me if I am wrong here -- it appears that the operations would be along the lines of: a) Remove the Foreign Key references b) Copy the table structure, and make a new temp table, adding the column c) Copy the data over d) Add the FK relations, that used to be in the first table, to the new table e) Delete the original f) Done? The questions are: a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'. b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant? c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated. Thanks! Sky
View Replies !
View Related
Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another
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
View Replies !
View Related
SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!
I am having great difficulty with cascading deletes, delete triggers and referential integrity. The database is in First Normal Form. I have some tables that are child tables with two foreign keyes to two different parent tables, for example: Table A / Table B Table C / Table D So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D. SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D. Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D. When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers????? This is an example of my delete trigger: CREATE TRIGGER [DeleteA] ON A FOR DELETE AS Delete from B where MeetingID = ID; Delete from C where MeetingID = ID; And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first. So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table. So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required). Hope this makes sense... Thanks, Josh
View Replies !
View Related
|
TRACKER
