ADO UpdateBatch from Inner Join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ADO UpdateBatch from Inner Join

Author

Topic

g_byte
Starting Member

1 Post

Posted - 2004-12-07 : 16:47:36

I am using the following stored procedure to generate a client-sided Batch Optimistic ADO Recordset:


CREATE PROCEDURE eml_ListEmailAttachment
	@AcctID int = NULL,
	@FolderID int = NULL
AS

SELECT  
    a.AttachmentID, a.EmailID, a.DisplayName, a.PathName
FROM [EmailAttachment] a
INNER JOIN Email e ON a.EmailID = e.EmailID
WHERE
	(@AcctID IS NULL OR e.AcctID = @AcctID)
	AND (@FolderID Is Null OR e.FolderID = @FolderID)

As you can see, the only fields being selected are from the EmailAttachment table. However, when I delete some records on the client and then call UpdateBatch, ADO sends the following SQL to the server and fails with a message saying "Row could not be located for updating...blah, blah, blah.":


exec sp_executesql N'DELETE FROM "WSPC".."Email" WHERE "EmailID"=@P1; DELETE FROM "WSPC".."Email" WHERE "EmailID"=@P2', N'@P1 int,@P2 int', 119, 119

exec sp_executesql N'DELETE FROM "WSPC".."EmailAttachment" WHERE "AttachmentID"=@P1; DELETE FROM "WSPC".."EmailAttachment" WHERE "AttachmentID"=@P2', N'@P1 int,@P2 int', 34, 35

It is trying to delete from the Email table once for each EmailAttachment record that I deleted. So, I saved the recordset to a file using the XML persistence format to see the schema, and I see this near the bottom:


<s:AttributeType name='c4' rs:name='EmailID' rs:number='5' rs:basecatalog='WSPC' rs:basetable='Email' rs:basecolumn='EmailID'
			 rs:keycolumn='true' rs:hidden='true' rs:autoincrement='true'>
	<s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>

Does anyone know how to avoid this? I could re-write the query using an IN() statement to get the same rows, but in my experience an Inner Join works faster (please tell me if this is false).

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources