SQL Server Performance Forum – Threads Archive

OpenQuery + Dynamic SQL + Update

Hi,
I am trying to update an Oracle table from MS SQL Server using linkedServer. I have been getting error message using the following code: Unclosed quotation mark before the character string ‘
Line 23: Incorrect syntax near ‘ select @str = ‘Update openquery(link_vptpilot,’ + ”” +’
select * from enterprise_resources where e_res_id = ‘ + convert(char,@res_uid) + ””+’)
set e_res_name = ‘ + ””+ convert(char,@res_name)+””+’,’
‘e_res_date = ‘getdate()’ @res_uid Number field,
@res_name text field and
@e_res_date date fields are to be set on Enterprise_resource table in Oracle. Understanding the syntax is difficult, not sure where to put what ie., ‘,",(,)….. If you have a general syntax for this or if you can correct the code above that would be great. Thanks in advance. Navin

---

Openquery often tends to be messy with quotation marks. The only advice I can give is to split up the query in smaller chunks. Assign parts of the query to variables and then put those variables together.

---

]]>