Article ID: 270119
Article Last Modified on 2/22/2007
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
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' 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:
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:
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:
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')
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.
MORE INFORMATION
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:
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
Additional query words: 7357 7320
Keywords: kbprb KB270119