Executing an INSERT statement on a View in linked server

[Krishnakumar_Rukmangathan]

First published on MSDN on Feb 22, 2011

Consider this scenario where you have a linked server from one SQL server to another SQL Server. Both the SQL Servers are SQL Server 2008 SP2 on Windows 2008.

Say the two SQL Servers are Server A and Server B.

Linked server from Server A to Server B is set up using SQL Native Client 10.0 provider.

On Server B, you have a VIEW that joins couple of tables, TABLE 1 and TABLE2 and a trigger that fires an INSERT into Table 2 when you INSERT into the View.

SERVER B

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

Create 2 tables, 1 view, 1 trigger (instead of)

CREATE TABLE T1 (c1 INT)

GO

CREATE TABLE T2 (c2 INT)

GO

CREATE VIEW vt (cv) AS SELECT c1 FROM t1 UNION ALL SELECT c2 FROM t2

GO

The View confirms to the rules of an Updateable View and a Partitioned View

CREATE VIEW

http://msdn.microsoft.com/en-us/library/ms187956.aspx

Create an INSTEAD OF TRIGGER to INSERT into physical table t2 when insert is fired against the VIEW.

CREATE TRIGGER vt_trig ON vt INSTEAD OF INSERT AS

BEGIN

INSERT INTO t2 (c2) SELECT i.cv FROM INSERTED i

END

SERVER A

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

Create linked server on Server A to Server B with default SNAC provider and call it SNACLinked. Also create another linked server with MSDASQL and ODBC DSN (set up ODBC DSN using SQLODBCsrv driver) and call it MSDASQL_SQL2008.

Set up linked server with MSDASQL and SQLODBC driver instead of SQLNCLI provider:

EXEC sp_addlinkedserver
@server = N'MSDASQL_SQL2008',
@srvproduct = N'',
@provider = N'MSDASQL',
@datasrc = N'sql2008' <- name of odbc system DSN

In this linked server, this will work:

INSERT INTO MSDASQL_SQL2008.INST4.dbo.vt (cv) VALUES (111)

When you INSERT into VIEW on Server A, using linked server created with SNAC, we get the below mentioned error:

INSERT INTO SNACLinked.Test.dbo.vt(cv) values ('16')

ERROR:

OLE DB provider "SQLNCLI10" for linked server "XXXX" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Msg 16955, Level 16, State 2, Line 1

Could not create an acceptable cursor.

Locally executing the INSERT on VIEW on Server A works fine.

INSERT into Test.dbo.vt(cv) values (9) – this executes fine.

But when using ODBC DSN for linked server, the INSERT on VIEW works fine from Server A also:

INSERT INTO MSDASQL_SQL2008.Test.dbo.vt(cv) values ('15') - works fine.

The behavior is the same when you execute INSERT statement with OPENQUERY. It fails with the SNAC linked server but works fine with ODBC DSN linked server.

INSERT OPENQUERY (SNACLinked, 'SELECT CV FROM TEST.dbo.vt')

VALUES ('16'); -- fails

INSERT OPENQUERY (MSDASQL_SQL2008, 'SELECT CV FROM TEST.dbo.vt')

VALUES ('16'); -- works fine

It would still fail with the same error when an INDEXED VIEW with a separate TRIGGER is created in place of INSTEAD OF TRIGGER.

CREATE VIEW vt_indexed (cvindexed) with SchemaBinding AS

SELECT c1 FROM dbo.t1 UNION ALL SELECT c2 FROM dbo.t2

GO

CREATE TRIGGER vt_indexed_trig ON vt_indexed INSTEAD OF INSERT AS

BEGIN

INSERT INTO t2 (c2) SELECT i.cvindexed FROM INSERTED i

END

The XACT_ABORT SET option is set to ON for INSERT.

Why INSERT on VIEW with SQLNCLI fails

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

SQL Server wants to do a rowset-based INSERT (cursor based INSERT) operation through the OLE DB API IRowsetChange interface.

SQL Server requests an Updateable rowset for the SELECT statement.

In the SQLNCLI case we are going directly to the OLE DB provider (SQLNCLI or SQLNCLI10).

In the SQLNCLI / OLE DB case we are getting a READ-ONLY cursor that just returns an error about the cursor and does not proceed further. The SQL engine cannot handle this scenario and throws an error.

In the ODBC case, we are really going through MSDASQL/ODBC Driver of 2 layers that interact with SQL Engine. In the ODBC case the cursor gets downgraded to a read-only cursor (you can see the message in in the Profiler, The cursor was not declared). However, MSDASQL has additional logic here to simulate an updateable cursor. It indicates to the SQL Engine that an Updateable rowset is returned. SQL Engine can continue with its logic.

Here is the Error message in Profiler:

Exception Error: 16955, Severity: 16, State: 2 Microsoft SQL Server test 2396 55 2011-02-15 07:35:59.663

User Error Message Could not create an acceptable cursor. Microsoft SQL Server test 2396 55 2011-02-15 07:35:59.663

Exception Error: 16945, Severity: 16, State: 2 Microsoft SQL Server test 2396 55 2011-02-15 07:35:59.663

User Error Message The cursor was not declared. Microsoft SQL Server test 2396 55 2011-02-15 07:35:59.663

Continuing with only the MSDASQL case, SQL Engine does a positioned update through IRowsetChange::InsertRow. MSDASQL generates an INSERT statement as a response to this by parsing the base table/view name, e.g. INSERT INTO Test.dbo.vt( c1) VALUES(?). Since there is a trigger on this read-only VIEW, SQL engine handles it by executing the trigger and everything works.

When opening an updateable rowset over a simple VIEW with a UNION, Rowsetviewer tries 2 things:

declare @p1 int

set @p1=0

declare @p3 int

set @p3=98305

declare @p4 int

set @p4=311300

declare @p5 int

set @p5=0

exec sp_cursoropen @p1 output,N'select * from vt',@p3 output,@p4 output,@p5 output

select @p1, @p3, @p4, @p5

go

declare @p1 int

set @p1=180150009

declare @p3 int

set @p3=8

declare @p4 int

set @p4=1

declare @p5 int

set @p5=1

exec sp_cursoropen @p1 output,N'select * from vt',@p3 output,@p4 output,@p5 output

select @p1, @p3, @p4, @p5

go

Case1, which fails:

scrollopt: 0x18001 = KEYSET_ACCEPTABLE, CHECK_ACCEPTED_TYPES, KEYSET

ccopt: 0x4C004 = OPTIMISTIC_ACCEPTABLE,UPDT_IN_PLACE, CHECK_ACCEPTED_OTPS, OPTIMISTIC

Case2 which succeeds:

scrollopt: 8 = FORWARD_ONLY

ccopt: 1 = READ_ONLY

So in short:

- SQL Server requests an updateable cursor

- For most views, that fails

- MSDASQL is simulating an updateable cursor with insert statements.

So the workaround for SQLNCLI would be to use a stored procedure executed remotely:

CREATE PROCEDURE [dbo].[UpdateViewStoredProc] @Param1 int

AS

BEGIN

SET NOCOUNT ON;

INSERT into Test.dbo.vt(cv) values (@Param1)

END

Execute this stored procedure from remote linked server:

exec SNACLinked.Test.dbo.UpdateViewStoredProc 22

References

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

CREATE VIEW

http://msdn.microsoft.com/en-us/library/ms187956.aspx

sp_cursoropen (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ff848737.aspx

Author : Aruna , SQL Developer Engineer , Microsoft

Reviewed by : Kamil, SQL Escalation Services , Microsoft