Executing an INSERT statement on a View in linked server
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
https://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
https://msdn.microsoft.com/en-us/library/ms187956.aspx
sp_cursoropen (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ff848737.aspx
Author : Aruna , SQL Developer Engineer , Microsoft
Reviewed by : Kamil, SQL Escalation Services , Microsoft