Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38

    Unanswered: OpenQuery using a variable

    Hi,

    Here's what I did:

    1) I declared a new VARCHAR(2000) variable called CQUERY like this:
    DECLARE @CQUERY VARCHAR(2000)
    2) I put a string query in the variable:
    SET @CQUERY = 'SELECT ...'

    Now, when I try to execute the OpenQuery method using that variable, it fails.

    Here's the call:
    SELECT * FROM OPENQUERY(OracleSource, @CQUERY)

    I get the following error:
    Server: Msg 170, Level 15, State 1, Line 13
    Line 13: Incorrect syntax near '@CQUERY'.

    Don't tell me I can't use a variable instead of a static query? What am I doing wrong?

    Thanks,

    Skip.

  2. #2
    Join Date
    Jan 2004
    Posts
    40
    i don't think you can do that, putting a variable in the from clause

    you'll have to use dynamic sql

    so put that statement in a EXEC(......)

  3. #3
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38
    Alright,

    I tried it but I'm still having troubles with it. Here's my code (simplified version):

    DECLARE @CQUERY
    SET @CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
    EXECUTE(@CQUERY)

    When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:

    SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')

    It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:

    SELECT * FROM mytable WHERE last_name =

    which is incorrect.

    Any other suggestions?

    Thanks again,

    Skip.

  4. #4
    Join Date
    Jan 2004
    Posts
    40
    sorry if i mislead you the first time, what i meant is use EXEC if you are going to use a variable for openquery.

    if you are not using a variable for openquery, then just do this:
    SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = ''DOE''')

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Skippy_sc
    Alright,

    I tried it but I'm still having troubles with it. Here's my code (simplified version):

    DECLARE @CQUERY
    SET @CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
    EXECUTE(@CQUERY)

    When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:

    SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')

    It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:

    SELECT * FROM mytable WHERE last_name =

    which is incorrect.

    Any other suggestions?

    Thanks again,

    Skip.
    Try this for instance:


    DECLARE @CQUERY varchar(8000)
    SET @CQUERY = 'SELECT * FROM OPENQUERY(MSSQL20,
    ''SELECT top 10 * FROM master.dbo.sysobjects where name='''''+'sysobjects'+''''''')'
    select @CQUERY
    EXECUTE(@CQUERY)

  6. #6
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38
    Thank you very much fattyacid, it works fine now!

    Skip

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •