Using a @variable in an Openquery and saving the results to a #tempfile

  • I am between a rock and a hard spot. I need to run a stored procedure for a batch job. Within this stored procedure, I would like to use temporary tables and also a @variable

    I have tried this several ways, but if I want to use a @variable in a stored procedure, I have to save the query in a @variable (see below) and and execute it but cannot save the data as a #tempfile. If I hardcode the yearmonth and run the query without saving it in a @variable. I can save the #tempfile but cannot use @yearmonth. Right now, I save the data as a permanent table (TBL_Whatever) but would like to use tempfiles to make it cleaner.

    (I know this is not the clearest way to explain what I am trying to do)

    Is there a way to save a tempfile in an openquery using a @variable?

    Any thoughts?

    Thanks

    DECLARE @Yearmonth AS VARCHAR(7)

    Set @Yearmonth = '2014001'

    DECLARE @SQL VARCHAR(MAX)

    SET @SQL = '

    SELECT *

    into #tempfile

    FROM OPENQUERY (aaaa_TERADATA,

    ''

    SELECT

    yearmonth,

    field1,

    field2,

    field3

    FROM Terrada.terra001

    where Yearmonth= ''''' + @yearmonth + '''''

    '')

    '

    exec(@SQL)

  • I might be misunderstanding what it is you're trying to do, but I'm guessing you're trying to hold the results of your OPENQUERY execution into a temp table so they can be used later; if that's the case, that's doable with some rewriting. First, your query, formatted a bit:

    DECLARE @Yearmonth AS VARCHAR(7)

    SET @Yearmonth = '2014001'

    DECLARE @SQL VARCHAR(MAX)

    SET @SQL = '

    SELECT *

    into #tempfile

    FROM OPENQUERY (aaaa_TERADATA,

    ''

    SELECT

    yearmonth,

    field1,

    field2,

    field3

    FROM Terrada.terra001

    where Yearmonth= ''''' + @yearmonth + '''''

    '')

    '

    EXEC (@SQL)

    Now, the main problem is that you're creating #tempfile in your @SQL statement, then executing @SQL; this means that #tempfile will exist for the execution of @SQL, and then disappear. It can't be referenced outside of the EXEC statement.

    To fix this, we can do like so:

    DECLARE @Yearmonth AS VARCHAR(7)

    SET @Yearmonth = '2014001'

    CREATE TABLE #tempfile(yearmonth varchar(7),

    field1 varchar(100),

    field2 varchar(100),

    field3 varchar(100)

    )

    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = '

    SELECT *

    FROM OPENQUERY (aaaa_TERADATA,

    ''

    SELECT

    yearmonth,

    field1,

    field2,

    field3

    FROM Terrada.terra001

    where Yearmonth= @yearmonth

    '')

    '

    INSERT INTO #tempfile

    EXEC sp_executesql @SQL,N'@Yearmonth varchar(7)',@Yearmonth=@Yearmonth

    Basically, with this, we'll be INSERTing the result of the dynamic SQL execution into #tempfile, rather than creating it inside of the dynamic SQL. This means that we'll be able to query #tempfile after it's gotten the results of OPENQUERY.

    I did have to take some liberties with the datatypes used for field1, field2, and field3; change those to match what you've got in your environment. I also changed your EXEC to EXEC sp_executesql, and parameterized your variable into the statement; I don't know how your code is used on your end, but it was a SQL injection vulnerability if user input was going to be routed into it rather than a hardcoded value.

    If I've completely misunderstood what you're shooting for, let me know, and I'll fix things up better 😀

    - 😀

  • Thank you so much for responding so quickly.

    The code that I sent earlier didn't need a yearmonth, so I re-wrote it with the temp variable.

    This code does have a yearmonth variable, and when I re-wrote it, I got the error below.

    declare @Yearmonth Varchar(7)

    set @Yearmonth = '2014008'

    create table #tbl_Budget (Costcent varchar(5),Budget Numeric(38,2))

    DECLARE @SQLBUD NVARCHAR(MAX)

    SET @SQLBUD = '

    SELECT *

    into #Tbl_budget

    FROM OPENQUERY (Some_Teradata,

    ''

    SELECT

    substring(Costcenter,1,5) as Costcent,

    sum(Amount) as Budget

    FROM Terradata.Table

    where

    fiscper = @Yearmonth

    and COSTCENTER in (''''CCTR1'''',''''CCTR1'''',''''CCTR1'''')

    and VTYPE=99

    and CURTYPE=''''AA''''

    and VERSION EQ ''''CUR''''

    Group by Costcenter

    '')

    '

    print @SQLBUD

    Insert into #Tbl_budget

    EXEC sp_executesql @SQLbud, N'@Yearmonth varchar(7)',@Yearmonth = @Yearmonth

    This is the error Message:

    OLE DB provider "MSDASQL" for linked server "EENG477_TERADATA" returned message "[Teradata][ODBC Teradata Driver][Teradata Database] A SQL command cannot contain both host variables and parameter tokens. ".

    Thanks again for your help.

  • Ah, looks like you're hitting a Teradata error of some sort. I'm not sure exactly how to get past that one, but if it's the temp table, the bit that needs correcting is in your query here:

    ...

    SELECT *

    into #Tbl_budget

    FROM OPENQUERY (Some_Teradata,

    ''

    ...

    The #Tbl_budget table can be removed from that bit, since it's not being used in the Teradata portion of the query; the CREATE TABLE that makes #Tbl_budget outside of your Teradata query is what's making the container for the query, so making another one isn't needed.

    However, if that doesn't fix it, I'll need to do a bit of digging; I'm not certain of how Teradata expects queries to be formatted, so I could be oblivious to something here. If it's just the temp table in the dynamic part, though, all the better 🙂

    - 😀

  • Thank you very much, Andrew. You gave me the solution:

    First I removed into #Tbl_budget

    SET @SQLBUD = '

    SELECT *

    >>>>>into #Tbl_budget<<<<<

    FROM OPENQUERY (aaaa_TERADATA,

    Then I changed this:

    fiscper = @Yearmonth

    to

    fiscper = ''''' + @Yearmonth + '''''

    I saved this as a Stored Procedure and ran it. It works great now. Thanks again for your help.

    Carroll

  • Ah, gotcha :-). Good to see that worked. Is it possible for you to keep the variable as it originally was without the error occurring, though? Like so:

    fiscper = @Yearmonth

    I'm not sure if Teradata will complain over it or not; however, as it's written in your last post, the concatenation of the variable could be an injection vulnerability. Granted, if you're always going to hardcode it or draw it out of storage somewhere, the risk is smaller, but still present. A healthy dose of DBA paranoia could be a good thing here, but you certainly know the intricacies of your setup better than I do 😀

    - 😀

  • No, it wouldn't work for me. I had to add the tick marks. I can run it past my Teradata group to see if they have heartburn. 😀

    Thanks again

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply