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.
LVL 1
countrymeisterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.