Return data from exec statement
-
I'm running a query against a Linked Server (AS400/DB2) using the openquery function. I need to pass in a date parameter, so I have to use the method described here. At the end of the script, I run an exec statement, and I get data in the bottom of query analyzer. My problem is I need to do further processing with the records returned, and cant quite figure out how to get the exec's return in a form I can use.
I've tried declaring a cursor to use the returns like this:
declare test cursor for
exec <SP>
<SP> being the script mentioned above, but I get a script error from QA...
I think I just don't quite understand whats going on w/ an exec's return. Can anyone clarify what exactly the return from the exec is?
Here's the code from the stored procedure whose resultset I need to process further (with the select statement changed to protect the innocent...):
create procedure SP
as
Begin
declare @SQL as varchar(1000)
declare @LinkedServer as varchar(30)
declare @Statement as varchar(1000)
declare @Date as datetime
set @LinkedServer = 'AS400'
set @Date = dateadd(dd,-2,getdate())
set @SQL = 'select * from table where date = ' + @Date
set @Statement = 'select * from OPENQUERY(' + @LinkedServer + ', '
set @Statement = @Statement + '''' + @SQL + ''')'
exec(@Statement)
End
Like I said earlier, when run from query analyzer, I see what I want, but can't figure out how to process the results further.
Thanks in advance for any help!
Tim C
(PS, before anyone asks, this query I have to do HAS to be run through an openquery function. if I just run the query adhoc, it can take 20 mins or more to complete. the underlying table is HUGE....)
-
Create a temp table with the same structure as the results then do this :
Insert into #temp (col1, col2, id) exec(@Statement)
-
If you know the data types of the data being returned from the dynamic sql you can insert the results of the exec in a temp table. Create the temp table first and then do:
insert #temptable (column_list) exec (@whatever)
And then again, I might be wrong ...
David Webb -
Thanx for confirming
. -
I have to learn to type faster so I can get there first next time.
And then again, I might be wrong ...
David Webb -
You only have 2 minutes to cut down... shouldn't be too hard
. -
I don't know. I've been at this for 20 years and I still just use 2 fingers. It seems I can't type any faster than I think.
And then again, I might be wrong ...
David Webb -
Now if it's not a typing problem, I can't help you, otherwise there's always those typing teaching softwares
. -
Haven't used temp tables before, so this is probably a very basic question ... What's the lifespan of the table? Length of the current connection? Some predefined timeout?
-
Length of the connection or until you explicitly drop it.
And then again, I might be wrong ...
David Webb -
...and it is placed in tempdb
just to mention that it should be huge enough to hold the result without problems
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply