user27768 user27768 - 3 months ago 9
SQL Question

Execute a literal string as a SQL statement - placement of single quotes

I have the following SQL string pulls data from a ODBC Progress Database via a linked server. This query works fine.

SELECT *
INTO [TABLE]
FROM
OPENQUERY
(
"ODBC DRIVER",
'SELECT CAST(SCHEMA."AB_ACCESSIBILITY"."ACCESS_CODE" AS VARCHAR(20)) AS "ACCESS-CODE"
,CAST(SCHEMA."AB_ACCESSIBILITY"."DESCRIPTION" AS VARCHAR(60)) AS "DESCRIPTION"
,CAST(SCHEMA."AB_ACCESSIBILITY"."ORG_CODE" AS VARCHAR(16)) AS "ORG_CODE"
,CAST(SCHEMA."AB_ACCESSIBILITY"."POINTS" AS INT) AS "POINTS"
FROM SCHEMA."AB_ACCESSIBILITY" with (nolock)'
)


However, I would like to convert this to a literal string so that I can execute this from a stored procedure using
exec sp_executesql
.

However, I'm having trouble with correct placement of apostrophes so that
exec sp_executesql
can read it as a properly formed SQL statement.

Could someone please show me where to add additional apostrophes to the above statement so that it will work with exec sp_excutesql?

Thanks!

Edits for clarity:


  1. With (nolock)
    is fine for the above

  2. The purpose of the above is to loop over a number of SQL statements in a list to import data from a Progress DB.


Answer

Looks like you just need to escape the ' characters with ''

SELECT * 
INTO [TABLE] 
FROM 
OPENQUERY
(
"ODBC DRIVER",
''SELECT CAST(SCHEMA."AB_ACCESSIBILITY"."ACCESS_CODE" AS VARCHAR(20)) AS "ACCESS-CODE"
,CAST(SCHEMA."AB_ACCESSIBILITY"."DESCRIPTION" AS VARCHAR(60)) AS "DESCRIPTION"
,CAST(SCHEMA."AB_ACCESSIBILITY"."ORG_CODE" AS VARCHAR(16)) AS "ORG_CODE"
,CAST(SCHEMA."AB_ACCESSIBILITY"."POINTS" AS INT) AS "POINTS" 
FROM SCHEMA."AB_ACCESSIBILITY" with (nolock)''
)