Incorrect Syntax Open Query-CodePudding

I an doing update using open query on progress database which is connected to sql server using lined server . below is the query when I print :

   UPDATE OPENQUERY(PROGRESSDB,'SELECT * FROM PUB.workitem where 
 act_emp= 73870 and wtype='B'')
SET  start_time='81410',logonstat  = '1',realstrttme ='81410'

It gives Incorrect syntax near 'B'. Any idea where it went wrong.

CodePudding user response：

You are using a string inside the Openquery string. The easiest solution is to double up the single quotes around the B:

UPDATE OPENQUERY(PROGRESSDB,'SELECT * FROM PUB.workitem where 
 act_emp= 73870 and wtype=''B''')
SET  start_time='81410',logonstat  = '1',realstrttme ='81410'

CodePudding user response：

The problem is in the quotes. In SQL server when working with strings you need to put quotes around the string. The problem is that when you put basic quotes ('') you are going to face a problem from the fact that the statement itself is wrapped around quotes. And since the program doesn't understand what you want with the singular quotes it ends the statement there, and you get the error. There are different ways to fix the problem.

First would be to double the quotes like this:

UPDATE OPENQUERY(PROGRESSDB,
    'SELECT * FROM PUB.workitem WHERE act_emp= 73870 AND wtype=''B''')
SET start_time = '81410', logonstat = '1', realstrttme ='81410'

Second way is to use the UNICODE character for single quote - 39 and the plus sign ( ) to combine the strings, like this

UPDATE OPENQUERY(PROGRESSDB,
    'SELECT * FROM PUB.workitem WHERE act_emp= 73870 
    AND wtype = '   CHAR(39)   'B'   CHAR(39)   ')
SET start_time= '81410', logonstat = '1', realstrttme = '81410'

Another way would be to turn off the Quoted_Identifier. This would mean that instead of using single quotes around string you need to use double quotes, and that way using single quotes inside the text won't be a problem. Example:

SET QUOTED_IDENTIFIER OFF;
UPDATE OPENQUERY(PROGRESSDB,
    "SELECT * FROM PUB.workitem WHERE act_emp = 73870 AND wtype = 'B'")
SET start_time= "81410", logonstat = "1", realstrttme = "81410"