Atlanta Custom Software Development 

 
   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools


PRB: 7357/7320 Error While Performing UPDATE, INSERT, or DELETE on Remote Table Using OpenQuery Via Linked Server

This article applies to…
This article was previously published under Q270119

SYMPTOMS

Distributed queries that use the OpenQuery function to update, delete, or insert data in the following way exec sp_dropserver 'linked1', 'droplogins'

Click here to copy the following block
exec sp_addlinkedserver 'linked1', 'SQL Server'
exec sp_setnetname 'linked1', '<servername>'
exec sp_addlinkedsrvlogin 'linked1', 'false', null, '<login name>', '<password>'

SET ANSI_NULLS ON
go
SET ANSI_WARNINGS ON
go
select * from openquery (linked1, 'update testlinked set ssn=ssn+1')
select * from openquery (linked1, 'insert into testlinked (ssn) values (1000)')
select * from openquery (linked1, 'delete from testlinked where ssn=1')


may generate the following error messages:

Server: Msg 7357, Level 16, State 2, Line 1 Could not process object 'update testlinked set ssn=ssn'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

Server: Msg 7357, Level 16, State 2, Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Could not process object 'update testlinked set ssn=ssn'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
The actual text message of the error may vary depending on the OLE DB provider and the operation (UPDATE, INSERT, or DELETE) being performed, but the error number is always 7357.


CAUSE

OpenQuery requires a result set to be returned, but UPDATE, DELETE, and INSERT statements that are used with OpenQuery do not return a result set.

WORKAROUND

You can work around this problem in the following ways:
Use four-part names (linked_server_name.catalog.schema.object_name) to do insert, update, or delete operations.
As documented in SQL Server Books Online, reference the OpenQuery function as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. The following queries demonstrate proper usage with the SQL Server OLE DB provider:

Click here to copy the following block
update openquery(linked1, 'select ssn from testlinked where ssn=2')
set ssn=ssn + 1
go
insert openquery(linked1, 'select ssn from testlinked where 1=0') values (1000)
go
delete openquery(linked1, 'select ssn from testlinked where ssn>100')


Note In the INSERT statement, a where 1=0 predicate is used to avoid retrieving data from the remote server, which can result in slower performance. Also, UPDATE and DELETE operations have special index requirements; see the "More Information" section for details.

Unique Index Requirement

The SQL Server OLE DB provider requires that a unique index exist on the underlying table for UPDATE or DELETE operations. If no unique index exists on a remote table, the following error occurs when an UPDATE or DELETE is attempted:

Server: Msg 7320, Level 16, State 2, Line 1 Could not execute query against OLE DB provider 'SQLOLEDB'. The provider could not support a required row lookup interface. The provider indicates that conflicts occurred with other properties or requirements. [OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
This applies both to OpenQuery and four-part named UPDATE and DELETE operations. The problem is resolved by adding a unique index on the remote table.

Dynamic Execution with OpenQuery

It may sometimes be desirable to use a dynamic query to achieve the same effect using OpenQuery, as shown in the following example: begin tran

Click here to copy the following block
SET QUOTED_IDENTIFIER OFF
SET XACT_ABORT ON
declare @cmd varchar(2500)
declare @cmd1 varchar(2500)
declare @var varchar(20)
set @var = 'White'
declare @var1 varchar(20)
set @var1 = 'White1'
declare @var2 varchar(20)
set @var2 = 'Johnson1'

select @cmd = "Update openquery(linked1,'select au_lname, au_fname from pubs.dbo.authors
where au_lname = ''"
+ @var + "''' )
set au_lname = '"
+ @var1 + "',
au_fname = '"
+ @var2 + "'"

exec ( @cmd )

commit tran
select * from <servername>.pubs.dbo.authors
           


Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  BlogsZappySys

© 2008 BinaryWorld LLC. All rights reserved.