Openquery Update statement not grabbing values

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 2008 Forums

Transact-SQL (2008)

Openquery Update statement not grabbing values

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-07 : 11:08:38

I am trying to create an update stamtne over a linked server from sql to Oracle. However it does not grab the values of the select statement and then it needs a ') after the where statement and I have tried to put one in but get further errors. How can I make this work.

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

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'Update OPENQUERY(RCASDBOR, ''' + REPLACE(@execsql, '''', '''''') + ''')'
print(@execsql)
END

here is how it print out
Update OPENQUERY(RCASDBOR, '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')

vaari
Starting Member

15 Posts

Posted - 2011-07-07 : 11:13:45

Can you check if this works (relocated the closing bracket):

Update OPENQUERY(RCASDBOR, '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'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 11:13:49

Hasn't this been dealt with already - the problem is your syntax

Update OPENQUERY(RCASDBOR, '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'

It would be easier as
exec ('Update Select STA, DT_TM_COMPL, NOTES, LAST_UPDT_NM, LAST_UPDT_DT FROM DOIM_RO2.CHECKLIST
SET STA = intAnswer,
DT_TM_COMPL = dtLogged,
NOTES = strRemarks,
LAST_UPDT_NM = strLogged,
LAST_UPDT_DT = SYSDATE
WHERE TASK_CTRL_SEQ_ID = intMPDVID AND
UNIT_ID = '000000000'
) at RCASDBOR

==========================================
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:18:21

Hi,
I relocated the bracket but still cannot get the values from the select statement to be put in the openquery statement

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-07 : 11:23:04

nigelrivett,

Sorry reposted under the 2008 forums, did not know if it made a difference for sql 2008 stuff. Of did as you suggested but then I get this error. I cannot touch the distant Oracle server.

Msg 7411, Level 16, State 1, Procedure sp_UpdateMPDV, Line 18
Server 'RCASDBOR' is not configured for RPC.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources