Problem with View migrated from SQL Server 2000 into 2005

Question

We are in the process of migrating from SQL Server 2000 to 2005.  We encountered a problem with one of our web applications (ASP) when attached to the new 2005 database.  We do not get this error when the application is attached to the 2000 database.

During execution of the following code: 

-----------------------------------------------------------------------------------------------------------------

sub OpenRS_TicketDetails(iTicketID)

strSQL="SELECT * from vwexTicketDetails WHERE TicketID =" & iTicketID

rs.open strSQL, cnReadWrite, adOpenStatic, adLockOptimistic

end sub

------------------------------------------------------------------------------------------------------------------

We encountered the following error: 

Microsoft OLE DB Provider for ODBC Drivers error '80040e23'

Row handle referred to a deleted row or a row marked for deletion.

The following is the select statement related to the view:

SELECT     T.TicketID, T.TicketDate, T.Problem, T.Technician_Assigned, T.Closed_Date, T.PersonID, T.SiteId, T.ProgramId, T.StatusId, T.PriorityId, T.CategoryId,
                      CMT.Comment, RTRIM(P.LastName) + ', ' + RTRIM(P.FirstName) AS FullName, P.Phone, P.WorkLocation, CT.CategoryName AS Category,
                      PR.priorityDesc AS Priority, ST.statusDesc AS Status
FROM         dbo.dtTickets AS T LEFT OUTER JOIN
                      dbo.dtComments AS CMT ON T.TicketID = CMT.TicketID LEFT OUTER JOIN
                      DIVCommon.dbo.dtPersonnel AS P ON T.PersonID = P.PersonID INNER JOIN
                      dbo.vtCategory AS CT ON T.CategoryId = CT.CategoryID INNER JOIN
                      dbo.vtPriority AS PR ON T.PriorityId = PR.priorityId INNER JOIN
                      dbo.vtStatus AS ST ON T.StatusId = ST.statusId

We tracked the problem to the dtComments table and were able to come up with a workaround to our problem.  When we added a primary key to the dtComments table, the application ran fine.

CREATE TABLE [dbo].[dtComments](

[CommentId] [int] IDENTITY(1,1) NOT NULL,

[TicketID] [int] NULL,

[Comment] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LastModUser] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LastModDate] [datetime] NULL)

Can someone explain to me why we are experiencing this problem in the 2005 environment and if there is a better solution.  Please let me know if you need additional information about this situation.

Thanks, Doug

Answer 1

I can't say why it is a problem in 2005 rather than 2000. But I can explain why the problem, its not simple and to do with some inner workings of ADO, metadata and optimistic locking

You are specified to have a optimistic locked recordset based on a view. Firstly you shouldn't do this with views because you can end up with orphaned rows if you try and insert data.

How ADO handles the optimistic locking is it needs to know how to identify the row that is being updated and also how to identify what determines the record has changed so that it can verify the update. However your dtComments table doesn't have a PK thus the problem. ADO does try and obtain the information needed in the absence of a PK but this is what is causing the problem.

Putting a PK should be your answer, all tables should have one unless a specific reason not to.

You could also try changing it to a readonly recordset.