September 11, 2014 at 1:53 pm
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)
September 11, 2014 at 2:12 pm
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 😀
- 😀
September 11, 2014 at 3:43 pm
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.
September 11, 2014 at 3:58 pm
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 🙂
- 😀
September 11, 2014 at 4:53 pm
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
September 11, 2014 at 8:34 pm
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 😀
- 😀
September 12, 2014 at 7:28 am
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