Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Wellington
    Posts
    2

    Unhappy Unanswered: Variables with Openquery - How do I use,?

    I get the following error when I try to use the following Openquery

    Error 170:Line 25: Incorrect syntax near '"+(@Library_symbol)+"'

    CREATE PROCEDURE sp_test
    @LibraryID varchar(5),
    @Interim bit,
    @Period varchar(20),
    @StartDate datetime,
    @EndDate datetime

    AS

    SELECT *
    FROM OPENQUERY (link, "SELECT DISTINCT
    hm.home_code,
    br.bibId
    FROM
    Odb.master_records mr,
    Odb.bib_record br,
    Odb.record_text rt,
    Odb.home hm
    WHERE
    hm.homeId = mr.homeId
    AND
    br.bibId = rt.bibId
    AND
    mr.recordId = br.recordId
    AND
    hm.homeCode =(" '"+(@LibraryID)+"' ")
    AND
    (NVL(rt.recodingLevel,1) != 3 OR (" '"+(@Interim)+"' ") =1)
    AND
    TRUNC(mr.dateCreate) +1 BETWEEN TRUNC(to_date(@StartDate)) AND TRUNC(to_date(@EndDate))

    ")

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    OPENQUERY does not accept variables as part of the query string. Here is how I handle this...

    Code:
    CREATE PROCEDURE sp_test (
     @LibraryID varchar(5)
    ,@Interim   bit
    ,@Period    varchar(20)
    ,@StartDate datetime
    ,@EndDate   datetime
    ,@Debug     bit = 0)
    AS
    declare @TSQL varchar(500)
    set @TSQL = 'SELECT * ' +
                  'FROM OPENQUERY (link, ''SELECT DISTINCT hm.home_code, br.bibId ' +
                                            'FROM Odb.master_records mr ' +
                                            'join Odb.bib_record br on mr.recordId = br.recordId ' +
                                            'join Odb.record_text rt on br.bibId = rt.bibId ' +
                                            'join Odb.home hm on mr.homeId = hm.homeId ' +
                                           'WHERE hm.homeCode = ''' + @LibraryID + ''' ' +
                                             'AND (NVL(rt.recodingLevel,1) != 3 OR ' + cast(@Interim as varchar) + ' = 1 ) ' +
                                             'AND dateadd(dd,1,mr.dateCreate) BETWEEN ''' + cast(@StartDate as varchar) + ''' AND ''' + cast(@EndDate as varchar) + ''''''')'
    if (@Debug = 1)
      select @TSQL
    else
      exec(@TSQL)
    
    return 0
    go
    exec sp_Test '~~~~~', 0, '~~~~~~~~~~~~~~~~~~~~', '01-Jan-1753', '01-Feb-1753', 1
    go
    Also I don't think NVL and TRUNC are valid SQL functions.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    Also when you want a space you can use either ' ' or Space(1) or char(32)

    I find the space command makes the script easier to read.

  4. #4
    Join Date
    Dec 2002
    Location
    Wellington
    Posts
    2
    The query only evaluates upto 255 characters of @TSQL string. What could be causing this

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What version & service pack are you running?

    you could also try...

    Code:
    alter PROCEDURE sp_test (
     @LibraryID varchar(5)
    ,@Interim   bit
    ,@Period    varchar(20)
    ,@StartDate datetime
    ,@EndDate   datetime
    ,@Debug     bit = 0)
    AS
    declare @TSQL1 varchar(255)
          , @TSQL2 varchar(255)
    set @TSQL1 = 'SELECT * ' +
                   'FROM OPENQUERY (link, ''SELECT DISTINCT hm.home_code, br.bibId ' +
                                             'FROM Odb.master_records mr ' +
                                             'join Odb.bib_record br on mr.recordId = br.recordId ' +
                                             'join Odb.record_text rt on br.bibId = rt.bibId ' +
                                             'join Odb.home hm on mr.homeId = hm.homeId ' 
    set @TSQL2 =                            'WHERE hm.homeCode = ''' + @LibraryID + ''' ' +
                                              'AND (NVL(rt.recodingLevel,1) != 3 OR ' + cast(@Interim as varchar) + ' = 1 ) ' +
                                              'AND dateadd(dd,1,mr.dateCreate) BETWEEN ''' + cast(@StartDate as varchar) + ''' AND ''' + cast(@EndDate as varchar) + ''''''')'
    
    if (@Debug = 1)
      select @TSQL1 + @TSQL2
    else
      exec(@TSQL1 + @TSQL2)
    return 0
    go
    exec sp_Test '~~~~~', 0, '~~~~~~~~~~~~~~~~~~~~', '01-Jan-1753', '01-Feb-1753', 1
    go
    Last edited by Paul Young; 12-18-02 at 10:33.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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