OpenQuery + Dynamic SQL + Update | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

OpenQuery + Dynamic SQL + Update

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.