Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Updating a view via Linked server query may fail

Updating a view via Linked server query may fail

View products that this article applies to.

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.

↑ Back to the top

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. 

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2520691
Revision : 1
Created on : 1/7/2017
Published on : 4/14/2011
Exists online : False
Views : 633