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

Hotfix Download Available
View and request hotfix downloads

View products that this article applies to.

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.

Back to the top

RESOLUTION

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 (http://support.microsoft.com/kb/884103/ ) How to obtain the latest MDAC 2.8 service pack

Back to the top

Hotfix information

A supported hotfix is 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 software update that contains this hotfix.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if 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. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site:

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

Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

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 (http://support.microsoft.com/kb/839801/ ) FIX: Hotfixes are available for MDAC 2.8

Back to the top

WORKAROUND

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

Back to the top

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

Back to the top

MORE INFORMATION

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.
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.
Save the form, and then run the form. You receive the error message that is mentioned in the "Symptoms" section.