|
 |
 |
 |
|
Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms
of Use |
Trademarks
Article ID: 270119 - Last Review: February 22, 2007 - Revision: 4.3 Error messages when you perform an UPDATE, INSERT, or DELETE Transact-SQL statement on a remote table by using the OpenQuery function: "7357" and "7320"This article was previously published under Q270119 Distributed queries that use the OpenQuery function to update, delete, or insert data in the following way
exec sp_dropserver 'linked1', 'droplogins'
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. If you are using Microsoft SQL Server 2005, you receive the following error message:
Server: Msg 7357, Level 16, State 2, Line 1 Cannot process the object "update
testlinked set ssn=ssn". The OLE DB provider "SQLOLEDB" for linked server "ServerName" indicates that either the object has no columns or the current user does not have permissions on that object. 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. 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:
update openquery(linked1, 'select ssn from testlinked where ssn=2')
set ssn=ssn + 1
insert openquery(linked1, 'select ssn from testlinked where 1=0') values (1000)
delete openquery(linked1, 'select ssn from testlinked where ssn>100')
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
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
APPLIES TO- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Workgroup Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
|
 |
|
 |
|
|
|
| |