• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 862
  • Last Modified:

Updating a view using linked server in SQL 2008 causes error - Could not create an acceptable cursor

I am updating a view within a stored procedure , this view is on a different database server than the stored proc
I am using the LINKEDSERVER.database.dbo.VIEW syntax to update the view within the stored procedure

I get this error
Could not create an acceptable cursor, OLE DB provider SQLNCLI10 for linked server XYZ returned message. Multi step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.
0
countrymeister
Asked:
countrymeister
  • 4
  • 3
1 Solution
 
LowfatspreadCommented:
post the view definition
post the stored procedure and indicate where the error is generated

do you have any status codes?

explain the environment ...

is this an sql2008 procedure trying to update a sql2005 database?
which editions and versions of sql server are involved?
0
 
countrymeisterAuthor Commented:
the view is generated from another base view which is then generated from a union of two tables

CREATE  VIEW [dbo].[v_View]
as
Select c1, c2 from v_View2

View2 is a unin of two tables ti and t2

Both thes tables are identical in column structure and the primaykey is BusDate and RowID

My update does qualify the two coulmns of busdate and RowID
Both database servers are SQL 2008
0
 
LowfatspreadCommented:
you cannot update a union CERTAINLY NOT WHEN DIFFERENT TABLES ARE INVOLVED

(unless of course you have an instead of trigger on it...)

you need to specify the updates to the actual tables involved...

you could of course update via

insert into table
select ,,,, from myview
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
countrymeisterAuthor Commented:
Lowfatspread:

I am able to update the identical view2 on the server where the stored proc is running from, but not able to update view2 on the remote server.
The remote sever is like our archiving server and has pretty much the same view2 structure with the union of two tables as the view2 on the  server the stored proc is running on.

Could I do the following
Create a stored procedure on the remote server, pass a temp table to this new stored proc on the remote server with the records to update and then apply the update.
0
 
LowfatspreadCommented:
i am having great difficulty understanding what sort of union in a view would allow a table to be updated..

can you post the view ddl , and the update statement that works, as well as the one that doesn't...

>>Could I do the following
Create a stored procedure on the remote server, pass a temp table to this new stored proc on the remote server with the records to update and then apply the update.

if it wirkks locally then that would appear to be a possibilty, why not try...
0
 
countrymeisterAuthor Commented:
This is view2
-----------------
CREATE view [dbo].[v_ReportHistory] as
select
RowID
,BusDate
,ReportLastUser
,OutputFormat
,DaysToArchive
,GroupCode
FROM v_Report

This is the base from view from which the above view gets its data
----------------------------------------------------------------------------------
CREATE view [dbo].[v_Report] as
select * from ReportArc_0009.dbo.Report_2009
union all
select * from ReportArc_0010.dbo.Report_2010

--Here is the stored procedure code, #tmpOutputRows is a temporary table
--Update of view2 on local REPORTDB sever, this works
------------------------------------------------------------------------
update rpt
    set rpt.ReportPortfolio          = lo.ReportPortfolio  
  , rpt.ReportLastUser                  = lo.ReportLastUser
FROM #tmpOutputRows lo (nolock)
INNER JOIN REPORTDB.reports.dbo.v_ReportHistory rpt (nolock)  
 ON  rpt.RowID =   lo.RowID
 AND rpt.BusDate = lo.BusDate  

-- This fails with the failed to generate acceptable cursor error
-----------------------------------------------------------------
update rptarc
    set  rptarc .ReportPortfolio          = lo.ReportPortfolio  
  ,  rptarc .ReportLastUser                  = lo.ReportLastUser
FROM #tmpOutputRows lo (nolock)
INNER JOIN REPORTARCDB.reports.dbo.v_ReportHistory  rptarc (nolock)  
 ON   rptarc .RowID =   lo.RowID
 AND  rptarc .BusDate = lo.BusDate
0
 
countrymeisterAuthor Commented:
I found a work arround to update the view on the remote server rather than using a linked server
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now