FIX: You may receive the "Cannot insert or update columns from multiple tables" error message when you try to insert or to update data in a multi-table view by using a server-side cursor (835677)

MORE INFORMATION

Steps to reproduce the problem

1. Start SQL Query Analyzer.
2. Connect to your instance of Microsoft SQL Server 2000.
3. Run the following Transact-SQL script for creating two tables, a view, and an INSTEAD OF trigger for the INSERT Transact-SQL statements on the view:

   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestInsTrigger]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
   drop trigger [dbo].[TestInsTrigger] 
   GO
   
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
   drop view [dbo].[TestView1]
   GO 
   
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[TestTab1]
   GO
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[TestTab2]
   GO 
   
   CREATE TABLE TestTab1
   (
   	Col_1 char(10) PRIMARY KEY CLUSTERED,
   	col_2 char(10)
   )
   GO 
   
   CREATE TABLE TestTab2
   (
   	Col_a char(10) PRIMARY KEY CLUSTERED,
   	Col_b char(10)
   )
   GO 
   
   
   CREATE VIEW TestView1 AS SELECT TestTab1.*, TestTab2.* 
   FROM TestTab1 INNER JOIN TestTab2 
   ON TestTab1.Col_1 = TestTab2.Col_a
   GO
   
   CREATE TRIGGER TestInsTrigger ON TestView1 Instead Of Insert AS 
   Select 'TestInsTrigger trigger fired instead of insert'
   GO
   
   

4. Create a new Microsoft Visual Basic 6.0 Standard EXE project.
5. Double-click Form1.
6. Replace the code in the code window with the following code.
   
   Note The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run correctly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later). To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.

   Private Sub Form_Load()
   
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Set cn = New ADODB.Connection
   cn.ConnectionString = "Provider=SQLOLEDB.1; User Id = <User ID>; Password= <Password>; " & _
                         "Initial Catalog=<Database Name>; " & _
                         "Data Source=<SQL Server Instance Name>"
   cn.Open
   
   Set rs = New ADODB.Recordset
   rs.LockType = adLockOptimistic
   rs.CursorLocation = adUseServer
   rs.CursorType = adOpenDynamic
   rs.Open "Select * from TestView1", cn
   
   rs.AddNew
   rs.Fields("Col_1").Value = "Column 1"
   rs.Fields("Col_2").Value = "Column 2"
   rs.Fields("Col_a").Value = "Column 1"
   rs.Fields("Col_b").Value = "Column b"
   
   rs.Update
   rs.Close
   cn.Close
   End Sub

   Note Replace <SQL Server Instance Name> with the name of your instance of SQL Server. Replace <Database Name> with the name of your database where you created the tables, the view, and the trigger. Replace <User ID> with the appropriate user name, and replace <Password> with the appropriate password.
7. Save the form, and then run the form. You receive the error message that is mentioned in the "Symptoms" section.