- 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).
Msg 16955, Level 16, State 2, Line 1
Could not create an acceptable cursor.
Procedure to create the linked server (on SQLA)
- First create an ODBC DSN using SQL Server ODBC driver.
- Open Control Panel.
- Click Administrative Tools.
- Click Data Sources (ODBC).
- Click the System DSN tab on the ODBC Data Source Administrator screen and then click Add.
- Select SQL Server from the list of available drivers and then click Finish.
- Type a name and description for the Data Source. Type the server name and then click Next.
- Click Next on the following screens and then click Finish.
- Run the following statement to create a linked server in Management studio:
@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.
- Consider you have two SQL Servers SQLA and SQLB.
- Create a view that uses a union all statement on SQLB and define an update trigger on this view.
- Create a SQL linked server from SQLA to SQLB using SNAC.
- Attempt to update the view via a linked server query from SQLA.