Microsoft KB Archive/835677

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

Article ID: 835677

Article Last Modified on 11/15/2007

-

APPLIES TO


 * Microsoft Data Access Components 2.8

-





SYMPTOMS
When you use a server-side cursor from a client application, and you try to insert or to update data in an updatable SQL Server view that is based on multiple SQL Server tables, you notice that the INSTEAD OF trigger that is defined on the view does not fire. Additionally, you notice that the data is not updated in the view, and you may receive an error message that is similar to the following:

Run-time error '-2147467259 (80004005)':

Cannot insert or update columns from multiple tables.

This problem occurs if you use Microsoft OLE DB Provider for SQL Server from the client application to connect to the instance of Microsoft SQL Server that contains the view.



Service pack information
To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.8. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

884103 How to obtain the latest MDAC 2.8 service pack

Hotfix information
A supported hotfix is now available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next service pack that contains this hotfix.

To resolve this problem, submit a request to Microsoft Online Customer Services to obtain the hotfix. To submit an online request to obtain the hotfix, visit the following Microsoft Web site:

http://go.microsoft.com/?linkid=6294451

Note If additional issues occur or any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. To create a separate service request, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

File information
The English version of this hotfix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.   Date         Time   Version            Size    File name --  22-Jan-2004  08:36  2000.90.736.0     147,456  Dahotfix.exe 28-Jan-2004 09:00                      4,889  Dahotfix.ini 28-Jan-2004 09:00                        647  Kb835677_dlvl.inf 28-Jan-2004 09:00                        641  Kb835677_win2k.inf 28-Jan-2004 09:00                        733  Kb835677_win2kx.inf 28-Jan-2004 09:00                        341  Kb835677_winme.inf 28-Jan-2004 09:00                        855  Kb835677_winmex.inf 28-Jan-2004 09:00                        603  Kb835677_winxp.inf 28-Jan-2004 09:00                        907  Kb835677_winxpx.inf 27-Jan-2004 04:33  2.80.1029.0       147,456  Msdart.dll 27-Jan-2004 04:34  2000.85.1029.0     24,576  Odbcbcp.dll 27-Jan-2004 04:33  2.80.1029.0       442,368  Oledb32.dll 27-Jan-2004 04:33  2000.85.1029.0    503,808  Sqloledb.dll 27-Jan-2004 04:33  2000.85.1029.0    401,408  Sqlsrv32.dll Note For a list of all the hotfixes available for MDAC 2.8, click the following article number to view the article in the Microsoft Knowledge Base:

839801 FIX: Hotfixes are available for MDAC 2.8



WORKAROUND
To work around this problem, use the SQL Native Client (SNAC) provider to connect to your instance of SQL Server.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section.



Steps to reproduce the problem
 Start SQL Query Analyzer. Connect to your instance of Microsoft SQL Server 2000.  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  Create a new Microsoft Visual Basic 6.0 Standard EXE project. Double-click Form1.</li>  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 = &quot;Provider=SQLOLEDB.1; User Id = <User ID>; Password= <Password>; &quot; & _ &quot;Initial Catalog= with the name of your instance of SQL Server. Replace  with the name of your database where you created the tables, the view, and the trigger. Replace  with the appropriate user name, and replace   with the appropriate password. </li> Save the form, and then run the form. You receive the error message that is mentioned in the &quot;Symptoms&quot; section.</li></ol>

<div class="references_section">