June 14, 2005 at 1:09 pm
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....)
June 14, 2005 at 1:18 pm
Create a temp table with the same structure as the results then do this :
Insert into #temp (col1, col2, id) exec(@Statement)
June 14, 2005 at 1:20 pm
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)
June 14, 2005 at 1:22 pm
Thanx for confirming .
June 14, 2005 at 1:27 pm
I have to learn to type faster so I can get there first next time.
June 14, 2005 at 1:29 pm
You only have 2 minutes to cut down... shouldn't be too hard .
June 14, 2005 at 1:39 pm
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.
June 14, 2005 at 1:44 pm
Now if it's not a typing problem, I can't help you, otherwise there's always those typing teaching softwares .
June 14, 2005 at 2:16 pm
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?
June 14, 2005 at 2:18 pm
Length of the connection or until you explicitly drop it.
June 14, 2005 at 4:39 pm
...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