Updating a view via Linked server query may fail

Applies to: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Express More

Symptoms


Consider the following scenario for two SQL Servers SQLA and SQLB:
  • You define a view on SQLB and the view's definition includes UNION ALL statement.
  • To make the view updateable (UNION ALL views are not updateable by default) you define an update trigger on this view.
  • You configure a linked server from SQLA to SQLB using SQL Native client (or SNAC) OLEDB provider (this is the default option when you define a linked server from one SQL Server to another).
In this scenario, if you try to update the view on SQLB via a linked server query from SQLA, the update trigger does not execute on SQLB and you may receive an error that is similar to the following:

Msg 16955, Level 16, State 2, Line 1
Could not create an acceptable cursor.

Cause


Updating views that use UNION ALL statement remotely via Linked server may fail when using SNAC because SQL Server does not allow the client to create updateable cursors. 

Workaround


Configure your linked server using Microsoft OLEDB provider for ODBC (MSDASQL). This provider parses the update statement as delete/insert pairs and hence does not run into the same problem as SQL Native Client Provider (SNAC).

Procedure to create the linked server (on SQLA)
  1. First create an ODBC DSN using SQL Server ODBC driver.
      1. Open Control Panel.
      2. Click Administrative Tools.
      3. Click Data Sources (ODBC).
      4. Click the System DSN tab on the ODBC Data Source Administrator screen and then click Add.
      5. Select SQL Server from the list of available drivers and then click Finish.
      6. Type a name and description for the Data Source. Type the server name and then click Next.
      7. Click Next on the following screens and then click Finish.
  2. Run the following statement to create a linked server in Management studio:
     
    EXEC sp_addlinkedserver 
    @server = N'MSDASQL_SQL2008', ß arbitrary name
    @srvproduct = N'',
    @provider = N'MSDASQL', -- this is the odbc provider
    @datasrc = N'sql2008' -- name of odbc system DSN you just created in Step 1 of this procedure.

More Information


Steps to Repro:
  1. Consider you have two SQL Servers SQLA and SQLB.
  2. Create a view that uses a union all statement on SQLB and define an update trigger on this view.
  3. Create a SQL linked server from SQLA to SQLB using SNAC.
  4. Attempt to update the view via a linked server query from SQLA.