SQL syntax in openquery - apostrophes inside query

Question

I have the following issue, I trying to obtain data via linked server in sql server 2008 from BMC Remedy

Everything is fine with connection, but when I added WHERE "Assigned Group" LIKE '*scri%'*, I get error in sql server because of apostrophes which I have to use because BMC Remedy demands it.

Do you know how to create correct syntax or force sql server to use quotation marks instead of apostrophes, or disable spell checking

SELECT *

FROM OPENQUERY(Remedy, 

**'**

SELECT
 Incident_Number
 FROM
 HPD_Help_Desk
 WHERE
 "Assigned Group" LIKE ' scri% '

 **'**

)

Answer 1

There maybe a white spaces that cause you a problems. You can also try this one:

SELECT Incident_Number
FROM HPD_Help_Desk
WHERE Assigned_Group LIKE '%scri%'

Or you can try to run this one if you run sql on DB:

SELECT r.Incident_Number
FROM ARADMIN.HPD_Help_Desk as r
WHERE r.Assigned_Group LIKE '%scri%'

Because you're running OPENQUERY, maybe double apostrophes will be needed or double quotes instead of one quote (" intead of ').

Good Luck

Answer 2

When doing SQL queries from within Remedy, I usually create a new field and use workflow to build the SQL query.

Also the syntax of the where clause you specified isn't correct. Try this instead:

SELECT
  Incident_Number
FROM
  HPD_Help_Desk
WHERE
  Assigned_Group LIKE 'scri%'