1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user27768
  • 13
  • 5
  • 2
    Slight detour...I notice you are using the NOLOCK hint here. Are you cool with randomly getting missing and/or duplicate rows? In other words, is it ok if your query is mostly accurate most of the time? You might want to read up a bit about that hint to make sure you fully understand it. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ – Sean Lange Aug 09 '16 at 19:11
  • 1
    Why do you think you need to run this as dynamic sql? – Sean Lange Aug 09 '16 at 19:12
  • @SeanLange Thanks - updated main question for clarity – user27768 Aug 09 '16 at 19:19
  • I think @Sean's questions about the use of the `NOLOCK` hint were meant to be rhetorical. :) – sstan Aug 09 '16 at 19:39
  • My comments about NOLOCK are because in my career I have seen it used over and over saying "dirty reads are fine" without that person understanding that it is much more sinister than that. – Sean Lange Aug 09 '16 at 20:56
  • What I still don't understand though is why you need to execute this as dynamic sql. You don't have to run this as dynamic sql from inside a stored procedure, unless the target of the insert is dynamic. If that is the case I would question that something else is going on there. – Sean Lange Aug 09 '16 at 20:58
  • Thanks Sean, both good points to take on board. – user27768 Aug 09 '16 at 22:49

1 Answers1

1

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)''
)
Joe C
  • 3,925
  • 2
  • 11
  • 31