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

T-SQL
1
EXEC master.dbo.sp_addlinkedserver @server = N'localhost', @srvproduct=N'SQL Server'
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

T-SQL
1
select * from openquery(localhost,'select * from sysobjects')
select * from openquery(localhost,'select * from sysobjects')

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

T-SQL
1
2
3
4
5
6
7
8
use tempdb
go
 
 
create table test(id int primary key, col1 varchar(20))
go
 
insert test values(1,'test1')
use tempdb
go


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

insert test values(1,'test1')

Select from the table

T-SQL
1
select * from test
select * from test

Output
—————-

id	col1
1	test1

SELECT
A select statement with openquery looks like this

T-SQL
1
select * from openquery(localhost,'select * from tempdb.dbo.test')
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

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

Verify that the data is correct

T-SQL
1
select * from test
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

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

Verify that the data is correct

T-SQL
1
select * from test
select * from test

Output
—————-

id	col1
2	Test2
3	Test3

DELETE
Finally a delete will have the where clause inside openquery

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

Verify that the data is correct

T-SQL
1
select * from test
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

T-SQL
1
2
3
4
create table test2(id int, col1 varchar(20))
go
 
insert test2 values(1,'test1')
create table test2(id int, col1 varchar(20))
go

insert test2 values(1,'test1')

The select statement works without a problem

T-SQL
1
select * from openquery(localhost,'select * from tempdb.dbo.test2')
select * from openquery(localhost,'select * from tempdb.dbo.test2')

Output
—————-

id	col1
1	test1

The insert statement also works

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

Verify that the data is correct

T-SQL
1
select * from test2
select * from test2
1	test1
2	test2

If you run the following update query

T-SQL
1
2
UPDATE OPENQUERY(localhost, 'select id,col1 from tempdb.dbo.test2 where id = 1 ')
SET id = 3,col1 = 'test2'
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

T-SQL
1
DELETE OPENQUERY(localhost, 'select id,col1 from tempdb.dbo.test2 where id = 2 ')
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

T-SQL
1
sp_dropserver 'localhost'
sp_dropserver 'localhost'

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