Recently I had to do some data manipulation against a Sybase linked server running on a Unix box. When you have a linked server you can use OPENQUERY do perform DML statements like delete, update and inserts but the syntax is a little different

So let’s look at some code, first create a linked server on your machine that is linked to your machine. You can add a linked server through the wizard or through the sp_addlinkedserver stored proc

EXEC master.dbo.sp_addlinkedserver @server = N'localhost', @srvproduct=N'SQL Server'

Test that the linked server was created and that you can execute queries against it

select * from openquery(localhost,'select * from sysobjects')

Now that this is done we need to create a table and insert one row of data

use tempdb
go


create table test(id int primary key, col1 varchar(20))
go

insert test values(1,'test1')

Select from the table

select * from test

Output

—————-

id col1
1   test1

SELECT

A select statement with openquery looks like this

select * from openquery(localhost,'select * from tempdb.dbo.test')

As you can see that was pretty standard

INSERT

an insert statement is a little odd looking because you still have the select in the openquery but the values that you want to insert look like a select statement that come after the openquery. It looks like this

INSERT  OPENQUERY(localhost,'SELECT id,col1 from tempdb.dbo.test ')
SELECT 2,'Test2'

Verify that the data is correct

select * from test

Output

—————-

id col1
1   test1
2   Test2

UPDATE

The update has a select with a where clause inside the openquery statement and the set statement comes after that. Here is what this looks like

UPDATE OPENQUERY(localhost, 'select id,col1 from tempdb.dbo.test where id = 1 ')
SET id = 3,col1 = 'Test3'

Verify that the data is correct

select * from test

Output

—————-

id col1
2   Test2
3   Test3

DELETE

Finally a delete will have the where clause inside openquery

DELETE OPENQUERY(localhost, 'select id,col1 from tempdb.dbo.test where id = 2 ')

Verify that the data is correct

select * from test

Output

————–

id col1
3   Test3

When it doesn’t work

I showed you what worked, now let’s look at some stuff that doesn’t

Create another table that looks like the table we had before but without a primary key

create table test2(id int, col1 varchar(20))
go

insert test2 values(1,'test1')

The select statement works without a problem

select * from openquery(localhost,'select * from tempdb.dbo.test2')

Output

—————-

id col1
1   test1

The insert statement also works

INSERT  OPENQUERY(localhost,'SELECT id,col1 from tempdb.dbo.test2 ')
SELECT 2,'test2'

Verify that the data is correct

select * from test2

1  test1
2   test2

If you run the following update query

UPDATE OPENQUERY(localhost, 'select id,col1 from tempdb.dbo.test2 where id = 1 ')
SET id = 3,col1 = 'test2'

you will be greeted with this message

_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.]

OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ ICommandText::Execute returned 0x80040e21: select id,col1 from tempdb.dbo.test2 where id = 1 [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]]._

The delete has the same issue as the update statement, running the following

DELETE OPENQUERY(localhost, 'select id,col1 from tempdb.dbo.test2 where id = 2 ')

will produce this error message

_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.]

OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ ICommandText::Execute returned 0x80040e21: select id,col1 from tempdb.dbo.test2 where id = 2 [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]]._

So what those errors are telling us is that a row lookup could not be performed, this is of course because we don’t have a key on this table. So just be aware of that!

Now that we are done we can drop the linked server, you can use sp_dropserver to do that

sp_dropserver 'localhost'

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum