Delete Statement Times Out And Blocks Reads

Aug 21, 2007

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 1 Replies


ADVERTISEMENT

Statistics IO Logical Reads Sometimes 15 Million And Other Times Down To 90 Thousand?

Mar 4, 2006

I am running a query in SQL 2000 SP4, Windows 2000 Serverthat is not being shared with any other users or any sqlconnections users. The db involves a lot of tables,JOINs, LEFT JOINs, UNIONS etc... Ok it's not a prettycode and my job is to make it better.But for now one thing I would like to understand with yourhelp is why the same SP on the same server and everythingthe same without me changing anything at all in terms ofSQL Server (configuration, code change, ...) runs inQuery Analyzer in 1:05 minute and i see one table get ahit of 15 million logical reads:Table 'TABLE1'. Scan count 2070, logical reads 15516368,physical reads 147, read-ahead reads 0.This 'TABLE1' has about 400,000 recordsThe second time i ran right after in Query Analyzer again:Table 'TABLE1'. Scan count 2070, logical reads 15516368,physical reads 0, read-ahead reads 0.I can see now the physical reads being 0 as it isunderstandable that SQL is now fetching the data frommemory.But now the third time I ran:Table 'TABLE1'. Scan count 28, logical reads 87784,physical reads 0, read-ahead reads 0.The Scan count went down from 2070 to 28. I don'tknow what the Scan count is actually. It scanned thetable 28 times?The logical reads went down to 87,784 reads from 15million and 2 seconds execution time!Anybody has any ideas why this number change?The problem is i tried various repeats of my test, irebooted the SQL Server, dropped the database, restoredit, ran the same exact query and it took 3-4-5 secondswith 87,784 reads vs 15 million.Why i don't see 15 million now?Well i kept working during the day and i happen to run intoanother set of seeing 15 million again. A few runs wouldkeep running at the paste of 15 million over 1 minute andeventually the numbers went back down to 87,784 and 2seconds.Is it my way of using the computer? Maybe i was openingtoo many applications, SQL was fighting for memory?Would that explain the 15 million reads?I went and changed my SQL Server to used a fixed memoryof 100 megs, restarted it and tested again the samequery but it continued to show 87,784 reads with 2 secondsexecution time.I opened all kinds of applications redid the same testand i was never able to see 15 million reads again.Can someone help me with suggestions on what could bethis problem and what if i could find a way to come tosee 15 million reads again?By the way with the limited info you have here about thedatabase I am using, is 87,784 reads a terrible number ofreads, average or normal when the max records in the manytables involved in this SP is 400,000 records?I am guessing it is a terrible number, am I correct?I would appreciate your help.Thank you

View 4 Replies View Related

Update Statement - Correlation Name Is Specified Multiple Times In FROM Clause

Jun 11, 2014

I have this update statement that I need to have joined by MSA and spec.

I keep getting an error.
Msg 1011, Level 16, State 1, Line 3
The correlation name 't1' is specified multiple times in a FROM clause.

Here is my statement below. How can I change this?

UPDATE MSA
SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t1 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])

View 3 Replies View Related

Updating The Same Column Multiple Times In One Update Statement

Jul 23, 2005

I have a single update statement that updates the same column multipletimes in the same update statement. Basically i have a column thatlooks like .1.2.3.4. which are id references that need to be updatedwhen a group of items is copied. I can successfully do this withcursors, but am experimenting with a way to do it with a single updatestatement.I have verified that each row being returned to the Update statement(in an Update..From) is correct, but that after the first update to acolumn, the next row that does an update to that same row/column combois not using the updated data from the first update to that column.Does anybody know of a switch or setting that can make this work, or doI need to stick with the cursors?Schema detail:if exists( select * from sysobjects where id = object_id('dbo.ScheduleTask') and type = 'U')drop table dbo.ScheduleTaskgocreate table dbo.ScheduleTask (Id int not null identity(1,1),IdHierarchy varchar(200) not null,CopyTaskId int null,constraint PK_ScheduleTask primary key nonclustered (Id))goUpdate query:Update ScheduleTask SetScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.')FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0Query used to verify that data going into update is correct:selectScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.'FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0

View 8 Replies View Related

Same Statement Executes 10 Times Faster As Raw Sql In Query Analyzer Then In A Stored Proc

Aug 15, 2007



Hi,


I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.


We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.


Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?


Thank you,


Michael Tzoanos

View 4 Replies View Related

One DELETE Sql Statement To Delete From Two Tables

Aug 12, 2007

I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?

View 5 Replies View Related

Help With Delete Statement/converting This Select Statement.

Aug 10, 2006

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 1 Replies View Related

SQL Server 2008 :: Merge Statement Takes Several Times Longer To Execute Than Equivalent Update

Jun 20, 2013

Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?

I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.

Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.

Server Configuration:

SQL Server 2008 R2 SP1, Enterprise Edition
3 x Quad-Core Xeon Processor
Max Degree of Parallelism = 8
148 GB RAM

SQL Code:

Target Table:
USE TPS;
IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL
DROP TABLE dbo.ParticipantResponse;

[code]....

View 9 Replies View Related

Delete Statement

Apr 11, 2007

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 1 Replies View Related

Help With DELETE Statement

Nov 13, 2007

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 2 Replies View Related

Sql Delete Statement

Sep 15, 2004

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 10 Replies View Related

DELETE Statement

Dec 14, 2001

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 7 Replies View Related

SQL Delete Statement

Oct 2, 2006

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 3 Replies View Related

DELETE Statement Conflicted

Dec 6, 2006

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 6 Replies View Related

Trigger On Delete Statement

Sep 26, 2007

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 2 Replies View Related

Only One Of My SQL Delete Statement Are Firing

Mar 12, 2006

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 2 Replies View Related

Recover After Delete Statement

Sep 6, 2001

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 2 Replies View Related

Delete Statement Won&#39;t Work!!

Feb 1, 2001

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 1 Replies View Related

Delete Statement Very Slow

Dec 12, 2005

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 6 Replies View Related

Please Help Me With A Complex DELETE Statement

Jan 27, 2005

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 5 Replies View Related

Tuning A Delete Statement

Oct 26, 2004

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 8 Replies View Related

Delete Statement Using A Join Plz Help

Mar 2, 2005

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 1 Replies View Related

Question About Delete Statement

Nov 4, 2007

Hi,My question is :
CREATE TABLE t1
(c1 INTEGER,
c2 INTEGER,
c3 DECIMAL(15,0 ))
INSERT INTO t1 VALUES (1, 2, 3.0)
How can i define a SQL commend will cause C1 to be decremented each time a row is deleted from the T2 table?
Thanks !

View 6 Replies View Related

Very Tricky Delete Statement

Apr 29, 2008

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 10 Replies View Related

Writing Delete Statement

Jul 30, 2013

i have problem to discuss regarding delete statement. Due to the bad design of application i have added some duplicate values in table. i have fixed the issue for new insert but how to delete my old bad id. i have table having three column,

ID1 ID2 ID3
101 2 3
102 2 3
103 2 4
104 3 4

in my case first two record are duplicate i want to keep first one and delete the other.

View 8 Replies View Related

How To Delete Row Number On Where Statement

Nov 7, 2013

I have duplicate rows of data with the exact same data, for multiple sets of data.For example,

1 Fred Flintstone
1 Fred Flintstone
2 Barney Rubble
2 Barney Rubble
etc...

If I use the delete and where command, it will delete both rows and I want to keep one of them. I don't want to copy down each one and re insert as there are too many columns for each one and there are too many rows also.I was thinking of a delete row-number command in a where statement. but tried multiple forums and its not working.

View 2 Replies View Related

How To Add Subquery To Delete Statement

Apr 28, 2015

I have to write a delete statement that deletes all customers that have not put in an order I must use a subquery in the exist operator.

View 5 Replies View Related

Delete Statement In Function

Jul 19, 2006

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 6 Replies View Related

Insert And Delete In One Statement

Jan 2, 2007

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 4 Replies View Related

Delete Statement Question

Mar 1, 2008



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 8 Replies View Related

512 Error In Delete Statement

Mar 1, 2008



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 7 Replies View Related

Update And Delete Statement Issues

Oct 7, 2007

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 2 Replies View Related

SQL Data Source Delete Statement

Feb 3, 2008

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 2 Replies View Related







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