OpenQuery Update statment not working

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 Development (2000)

OpenQuery Update statment not working

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-07 : 09:17:51

Found this pice of code on severl diffenretn places on how to use parameters within an openquery update statment. however I get the following error on mine, not sure why.

(0 row(s) affected)
OLE DB provider "OraOLEDB.Oracle" for linked server "SIDPERS" returned message "ORA-00933: SQL command not properly ended".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "Select STA, DT_TM_COMPL, NOTES, LAST_UPDT_NM, LAST_UPDT_DT FROM DOIM_RO2.CHECKLIST
WHERE TASK_CTRL_SEQ_ID = intMPDVID AND
UNIT_ID = '000000000'
SET STA = 'intAnswer',
DT_TM_COMPL = 'dtLogged',
NOTES = 'strRemarks',
LAST_UPDT_NM = 'strLogged',
LAST_UPDT_DT = 'SYSDATE'" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "SIDPERS".

ALTER PROC [dbo].[sp_UpdateMPDV] (@SSN varchar(9))
AS
BEGIN

declare @execsql nvarchar (4000)

Select ed.intquestionId,
q.intMPDVID,
Case When ed.intAnswer = 0 THEN 'GO' WHEN ed.intAnswer = 1 THEN 'NO GO' ELSE 'NO GO' END intAnswer,
Convert(varchar(10), ed.dtLogged, 101) dtLogged,
ed.strRemarks,
ed.strLogged
From tblSRPEventData as ed INNER JOIN
tblSRPQuestion as q on q.intQuestionId = ed.intQuestionId
Where q.intMPDVID IS NOT NULL AND intPersonnelID = @SSN
Order by intQuestionId, ed.intAnswer

set @execsql = 'Select STA, DT_TM_COMPL, NOTES, LAST_UPDT_NM, LAST_UPDT_DT FROM DOIM_RO2.CHECKLIST
WHERE TASK_CTRL_SEQ_ID = intMPDVID AND
UNIT_ID = '''+ @SSN +'''
SET STA = intAnswer,
DT_TM_COMPL = dtLogged,
NOTES = strRemarks,
LAST_UPDT_NM = strLogged,
LAST_UPDT_DT = SYSDATE'

SET @execsql = N'select * from OPENQUERY(SIDPERS, ''' + REPLACE(@execsql, '''', '''''') + ''')'
EXEC (@execsql)
END

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 09:27:37

shouldn't it be
update tbl
set ...
where ...

rather than
select ...
from ...
where ...
set ...

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

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-07 : 10:10:34

hi,

Every example for a Update statment via an openquery shows me the way I am doing it. Regularly it sould be as you have shown. When i do aprint it's as if its not getting the values of the select statement.

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-07 : 10:14:03

Ah,
I see I originally put in a select instead of an update to the openquery
This
SET @execsql = N'Update OPENQUERY(SIDPERS, ''' + REPLACE(@execsql, '''', '''''') + ''')'

Instead of this
SET @execsql = N'Select * from OPENQUERY(SIDPERS, ''' + REPLACE(@execsql, '''', '''''') + ''')'

but alas now i get this error
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 10:36:17

From bol
UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')
SET name = 'ADifferentName';

You are closer with your update statement but the set clause needs to be outside the openquery clause.

Are you really using v2000? Check the exec statment and see if
exec ('....') at linkedserver
is available, if so that's a lot simpler than openquery.

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

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-07 : 11:10:23

No using sql 2008, I actidentally started a post inside this forum while I was looking at an example for open queries.

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-07 : 11:15:05

Ok,
I think I got the seperation to work, but now the problem is getting the values into the statement, which I guess I am lost on.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 11:15:29

Continued at
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=162696

==========================================
Cursors are useful if you don't know sql.
SSIS 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