Professional Web Applications Themes

guru advice needed on temporary table problem - Microsoft SQL / MS SQL Server

I often need to join tables in my SQL Server DB with tables in a linked Oracle server. I use OPENQUERY to do this. The problem is that OPENQUERY don't support parameters, so I find it hard to add a WHERE clause--for a specific date for example. I need a where clause or else I'll pull hundreds of thousands of records. My solution has been to dynamically construct the entire OPENQUERY statement and assign it to a variable. Using this pattern: DECLARE rSQL varchar(100) DECLARE SQL varchar(100) SELECT rSQL = 'SELECT ' + 'Getdate() as value' SELECT SQL = 'SELECT ...

  1. #1

    Default guru advice needed on temporary table problem

    I often need to join tables in my SQL Server DB with tables in a
    linked Oracle server. I use OPENQUERY to do this. The problem is that
    OPENQUERY don't support parameters, so I find it hard to add a WHERE
    clause--for a specific date for example. I need a where clause or else
    I'll pull hundreds of thousands of records.

    My solution has been to dynamically construct the entire OPENQUERY
    statement and assign it to a variable. Using this pattern:

    DECLARE rSQL varchar(100)
    DECLARE SQL varchar(100)

    SELECT rSQL = 'SELECT ' + 'Getdate() as value'
    SELECT SQL = 'SELECT * FROM OPENQUERY(SAKL61, ''' + rSQL + ''')'
    SELECT SQL

    THE PROBLEM IS THIS: now that my openquery statement is assigned to a
    variable, I can't JOIN my SQL tables to it anymore. So I'm attempting
    to write the data to a TEMP table so I can join aganist that table.

    I've gotten the "SELECT INTO #temp1..." statement to execute, but now
    when I select from my temp table, I get an error:

    Server: Msg 208, Level 16, State 1, Line 24
    Invalid object name '#temp1'.

    1) Please tell me what I'm doing wrong? Where is my temp table?; or,
    2) do you know a better way to accomplish this problem?

    Here is my SQL:

    DECLARE rSQL varchar(5000)
    DECLARE SQL varchar(5000)
    DECLARE StartDate varchar(10)
    DECLARE ActivityLogDate datetime
    SET ActivityLogDate = '8/8/2003'

    set StartDate = CAST(DATEPART(YYYY, ActivityLogDate) as char(4)) +
    '-'
    + RIGHT(CAST(100+Datepart(mm,ActivityLogDate) as char(3)),2) + '-'
    + RIGHT(cast(100+1 as char(3)),2)

    SELECT rSQL = 'SELECT RDCALLS, CDB_PROD_P_CDB_SITE.SITE_ID,
    CDB_PROD_P_CDB_SITE.SITE_NAME, CDB_PROD_P_CDB_REGION.REGION_ID,
    FLTW_ADMIN_FLT_METRIC_DAILY.EFFECTIVE_DT FROM CDB_PROD_P_CDB_SITE
    INNER JOIN CDB_PROD_P_CDB_DISTRICT ON
    CDB_PROD_P_CDB_SITE.DISTRICT_ID = CDB_PROD_P_CDB_DISTRICT.DISTRICT_ID
    INNER JOIN CDB_PROD_P_CDB_DIVISION ON
    CDB_PROD_P_CDB_DIVISION.DIVISION_ID =
    CDB_PROD_P_CDB_DISTRICT.DIVISION_ID INNER JOIN CDB_PROD_P_CDB_REGION
    ON
    CDB_PROD_P_CDB_REGION.REGION_ID = CDB_PROD_P_CDB_DIVISION.REGION_ID
    INNER JOIN FLTW_ADMIN_FLT_METRIC_DAILY ON
    CDB_PROD_P_CDB_SITE.SITE_ID = CAST(RIGHT(FAC_IDU, 5) AS integer) INNER
    JOIN CDB_PROD_P_CDB_SITE_LOB1 ON CDB_PROD_P_CDB_SITE.SITE_ID =
    CDB_PROD_P_CDB_SITE_LOB1.SITE_ID
    WHERE (CDB_PROD_P_CDB_REGION.AREA_ID = 5) AND (LOB_ID = 1) AND
    (LOB1_ID = 1) AND (OPR_STATUS_CD = ''''A'''') AND (EFFECTIVE_DT =
    ''''' + StartDate + ''''')'

    SELECT SQL = 'SELECT * INTO #temp1 FROM OPENQUERY(CorpDB, ''' + rSQL
    + ''')'

    exec (SQL)

    select * from #temp1

    ----------
    result is:

    (130 row(s) affected)

    Server: Msg 208, Level 16, State 1, Line 24
    Invalid object name '#temp1'.
    maxhodges Guest

  2. #2

    Default Re: guru advice needed on temporary table problem

    The temptable goes out of scope after the EXEC. I think you might be able to do this using
    sp_executesql. Search the archives and/or Books Online for more info on sp_executesql.

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver


    "maxhodges" <com> wrote in message
    news:google.com... 


    Tibor Guest

  3. #3

    Default Re: guru advice needed on temporary table problem

    Try creating the temp table first, so that its visible outside the exec
    statement.

    CREATE TABLE #temp1 (col1 int, col2 varchar(50))
    SET SQL = 'INSERT INTO #temp1(col1, col2) SELECT col1, col2 FROM
    OPENQUERY(CorpDB, ''' + rSQL+ ''')'
    EXEC (SQL)
    SELECT * FROM #temp1

    "maxhodges" <com> wrote in message
    news:google.com... 


    Anthony Guest

  4. #4

    Default Re: guru advice needed on temporary table problem

    Hello all,

    This works for a temp table, but not if I try a table variable, swapping
    #temp1 with a temp1 (and by changing to correct syntax for the table
    variable declaration). Is there a way to use table variables in dynamic
    query strings/exec statements, if diskwrites are an concern?

    Thanks,
    Jason Carlson


    "Anthony Faull" <co.za> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Jason Guest

  5. #5

    Default Re: guru advice needed on temporary table problem

    Unfortunately a table variables scope does not allow this type operation.
    You must declare and use it inside it's own batch.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Jason Carlson" <edu> wrote in message
    news:bi11o8$7ql$asu.edu... 
    > >
    > >[/ref]
    >
    >[/ref]


    Andrew Guest

Similar Threads

  1. Flash Guru needed
    By Launchfire in forum Macromedia Flash Ad Development
    Replies: 3
    Last Post: June 17th, 04:55 PM
  2. Temporary Table Creation Problem in CF7 not CF6.1
    By Ortho in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 24th, 04:20 PM
  3. Having a Problem Printing From InDesign. Advice Needed.
    By jamnau@adobeforums.com in forum Adobe Indesign Windows
    Replies: 3
    Last Post: July 19th, 03:31 AM
  4. Replies: 7
    Last Post: February 10th, 11:46 PM
  5. Serious ibook problem - Advice needed
    By Ryan Newman in forum Mac Portable
    Replies: 3
    Last Post: September 5th, 02:53 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139