0

UPDATE OPENQUERY Help

I am trying to update an Oracle 11 table via a linked server in SQL, now the linked server and select statements all appear correct and I think I am very close to cracking this, but I am having issues with the update. The other OPENQUERY commands work correctly e.g.

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT PL.place_id, PL.phy_svc_grp_b FROM OU_DBA.REQUEST RT INNER JOIN OU_DBA.PLACE PL ON RT.place_id = PL.place_id WHERE (RT.phy_svc_grp_b IS NULL) AND (PL.phy_svc_grp_b IS NOT NULL)')

This is performing as expected; this information is then placed into a temporary table and using a WHILE I create an update openquery;

SET @TEMP_COUNT = (SELECT COUNT(*) FROM #MissingRegions)
            WHILE @TEMP_INDEX < @TEMP_COUNT +1
                BEGIN
                -----Clear out the SQL String
                SET @SQL_STRING = ''

                -----Assign the Place ID.
                SET @PLACE_ID = (SELECT PLACE_ID FROM #MissingRegions WHERE ID =@TEMP_INDEX)

                -----Assign the Region.
                SET @REGION = (SELECT GROUP_B FROM #MissingRegions WHERE ID =@TEMP_INDEX)

                SET     @SQL_STRING ='UPDATE OPENQUERY(LinkedServer, ''SELECT PHY_SVC_GRP_B FROM [OU_DBA].[REQUEST] WHERE PLACE_ID =''' + @PLACE_ID + ''')' + ' SET PHY_SVC_GRP_B = ''' + @REGION + ''''
                PRINT   @SQL_STRING
                EXEC        (@SQL_STRING)

            ----Increment the index value by 1
                SET @TEMP_INDEX = @TEMP_INDEX +1
        END

The output from the Print command returns the following;

UPDATE OPENQUERY(LinkedServer, 'SELECT PHY_SVC_GRP_B FROM [OU_DBA].[REQUEST] WHERE PLACE_ID ='ES5159') SET PHY_SVC_GRP_B = '1000'

But I keep getting the following error for each record i try to update; Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'ES5159'. Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string ''.

If anyone could possibly suggest where I may be going wrong and provide a solution for this I would be extremely grateful.

Community
  • 1
  • 1
Gordon Barclay
  • 13
  • 1
  • 7
  • Looks like you're just missing a quote at the end after '1000' so that your whole SELECT statement is quoted. Change REGION + '''' to REGION + '''''' – Mark Leiber Apr 16 '15 at 18:34
  • Thanks Mark, I added the extra ' but I now get "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'ES4658'." for example when trying to execute the following openquery command - UPDATE OPENQUERY(M3Live_Link, 'SELECT PHY_SVC_GRP_B FROM [OU_DBA].[REQUEST] WHERE PLACE_ID ='ES4658') SET PHY_SVC_GRP_B = '2000''. Grrr! I know I must be missing something obvious but cannot see what it is. – Gordon Barclay Apr 17 '15 at 07:43
  • You need another ' so that it looks like 'ES4658''). Also, the end should look like '2000', not '2000". – Mark Leiber Apr 17 '15 at 10:38
  • Confused Mark, you said : Also, the end should look like '2000', not '2000", but earlier said Looks like you're just missing a quote at the end after '1000', which one is it? – Gordon Barclay Apr 17 '15 at 13:48
  • Sorry, that should have been single quote, not quote. – Mark Leiber Apr 17 '15 at 13:57

1 Answers1

0

You need your output to look like this:

UPDATE OPENQUERY(LinkedServer, 'SELECT PHY_SVC_GRP_B FROM [OU_DBA].[REQUEST] WHERE PLACE_ID =''ES5159''') SET PHY_SVC_GRP_B = '1000'

To do this, SET your string like this:

SET     @SQL_STRING ='UPDATE OPENQUERY(LinkedServer, ''SELECT PHY_SVC_GRP_B FROM [OU_DBA].[REQUEST] WHERE PLACE_ID =''''' + @PLACE_ID + ''''''')' + ' SET PHY_SVC_GRP_B = ''' + @REGION + ''''
Mark Leiber
  • 3,118
  • 2
  • 13
  • 22
  • Mark, I tried your suggestion `SET @SQL_STRING ='UPDATE OPENQUERY(M3Live_Link, ''SELECT PHY_SVC_GRP_B FROM [OU_DBA].[REQUEST] WHERE PLACE_ID =''' + @PLACE_ID + ''''')' + ' SET PHY_SVC_GRP_B = ''' + @REGION + ''''` but still no joy, using print I am trying to execute the following code `UPDATE OPENQUERY(M3Live_Link, 'SELECT PHY_SVC_GRP_B FROM [OU_DBA].[REQUEST] WHERE PLACE_ID ='ES1207'') SET PHY_SVC_GRP_B = '1000'` but get the message `Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'ES1207'.` If you can also let me know how to add carriage return in comments that would be great. – Gordon Barclay Apr 17 '15 at 13:57
  • Edited. Please try now. There's an extra ': 'ES1207'' – Mark Leiber Apr 17 '15 at 14:05
  • Copied your sample from above and I still get an error - sorry. Using your code above i get `UPDATE OPENQUERY(M3Live_Link, 'SELECT PHY_SVC_GRP_B FROM [OU_DBA].[REQUEST] WHERE PLACE_ID ='1502398') SET PHY_SVC_GRP_B = '2000''` And this returns the error `Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '1502398'.`. I also tried the extra quote as mentioned in your above edit, `UPDATE OPENQUERY(M3Live_Link, 'SELECT PHY_SVC_GRP_B FROM [OU_DBA].[REQUEST] WHERE PLACE_ID ='ES1657'') SET PHY_SVC_GRP_B = '1000''` and this also errored. – Gordon Barclay Apr 20 '15 at 09:37
  • @GordonBarclay: Edited again and tested on a SQL Server instance. This should do it. Be mindful of the single quotes around 'ES5159'. That's the tricky bit. – Mark Leiber Apr 20 '15 at 13:56
  • Thanks, tried this and then received an Oracle error, removed the [ ] brackets from schema and table name and all seems fine now. Many thanks for your help, I think tried variations of this but not double quotes at the beginning of the ES5159. – Gordon Barclay Apr 20 '15 at 15:19