Openquery and Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Openquery and Dynamic SQL

Author

Topic

asabat
Starting Member

2 Posts

Posted - 2004-05-28 : 06:36:53

Openquery and Dynamic SQL.

Serious headache!I am trying to update one record on a linked server using Openquery and Dynamic SQL.
I have tried several options but am now at a loss. Is it my T-sql that's wrong (probably!) or is there a MSDTC/OLDB problem?

What I want to achieve is :

Set exported to 1 where RecordID = 'MyrecordID'

Any pointers gratefully accepted.
I have tried :

Strike 1 - with the parameters and the update inside the openquery
declare @cmd varchar(500)
declare @alink varchar(50)
set @alink = 'MyrecordID'
set @cmd = 'select * from openquery(MYLINKEDSERVER,' + '''' +' update MYDatabase.dbo.audit set exported = 1 where RecordID= ' + @alink + ''''+') '
select @cmd as command
exec ( @cmd )

Strike 1 Result =
(1 row(s) affected)
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object ' update MYDatabase.dbo.audit set exported = 1 where RecordID= MyrecordID'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns
Strike 2 with the update and parameters outside the openquery
declare @cmd varchar(500)
declare @alink varchar(50)
set @alink = 'MyrecordID'
set @cmd = 'update openquery(MYLINKEDSERVER,' + '''' +' select * from MYDatabase.dbo.audit' + '''' + ')' + 'where RecordID= @alink set exported = 1'
select @cmd as command
exec ( @cmd )

Strike 2 Result =
(1 row(s) affected)
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'where'.

Strike 3 - mix and match desperation!
declare @cmd varchar(500)
declare @alink varchar(50)
set @alink = 'MyrecordID'
set @cmd = 'update openquery(MYLINKEDSERVER,' + '''' +' select * from MYDatabase.dbo.audit where RecordID= ' + @alink + ''''+') ' + 'set exported = 1'
select @cmd as command
--exec ( @cmd )

Strike 3 Result =
(1 row(s) affected)
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 * from MYDatabase.dbo.audit where RecordID= MyrecordID [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]].
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query= update MYDatabase.dbo.audit set exported = 1 where RecordID= MyrecordID']..

Many thanks

A.Sabat

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-28 : 12:39:25

Instead of EXEC (@cmd), run PRINT @cmd and post the results here. We need to see if the query is not formed correctly.

Tara

asabat
Starting Member

2 Posts

Posted - 2004-06-01 : 09:41:31

The Dynamic code that is run looks like this:

Strike 1
select * from openquery(MYLINKEDSERVER,' update MYDatabase.dbo.audit set exported = 1 where RecordID= MyrecordID')

Strike 2
update openquery(MYLINKEDSERVER,' select * from MYDatabase.dbo.audit') where RecordID= @alink set exported = 1

Strike 3
update openquery(MYLINKEDSERVER,' select * from MYDatabase.dbo.audit where RecordID= MyrecordID') set exported = 1

Sorry for delay it was a long week end here in the UK.
Many thanks
Alicia

A.Sabat

mjia
Starting Member

2 Posts

Posted - 2004-06-01 : 11:32:47

For strike1, is myrecordID a column name in table MYDatabase.dbo.audit? If it's not, then it's not valid SQL, I guess you might want the actual SQL as :

select * from openquery(MYLINKEDSERVER,' update MYDatabase.dbo.audit set exported = 1 where RecordID= ''MyrecordID''')

so it should be:

set @cmd = 'select * from openquery(MYLINKEDSERVER, ''update MYDatabase.dbo.audit set exported = 1 where RecordID= ''''' +
@alink + '''''')'

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-01 : 14:11:07

try
select * from openquery(MYLINKEDSERVER,' update MYDatabase.dbo.audit set exported = 1 where RecordID= MyrecordID select a=1')
or
select * from openquery(MYLINKEDSERVER,'select a=1 update MYDatabase.dbo.audit set exported = 1 where RecordID= MyrecordID')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources