OLEDB stage

justlrng · Post by **justlrng** » Mon Aug 09, 2004 11:54 am

Hi,

I am wondering if it is possible to use an OLEDB stage (connecting to SQL server) to create temp. tables on the fly and then read in the results of the temp. table to DataStage for further processing.

Thanks.

rasi · Post by **rasi** » Mon Aug 09, 2004 6:24 pm

Read the documention for Temp tables in SQL Server.

You can create temp tables using datastage. But when the session is closed the temp table is also removed from the database. As long you can do it in one single session yes you can use it.

Thanks
Siva

justlrng · Post by **justlrng** » Tue Aug 10, 2004 7:52 am

I have tried creating a temp table and then reading the results back in another query all within the same stage, however, I get a "Link initialization failure" every time. Therefore I wasn't sure that temp tables were possible within DataStage but if it is possible I will have to keep trying. Thank you Siva.

shawn_ramsey · Post by **shawn_ramsey** » Tue Aug 10, 2004 8:18 am

justlrng wrote:I have tried creating a temp table and then reading the results back in another query all within the same stage, however, I get a "Link initialization failure" every time. Therefore I wasn't sure that temp tables were possible within DataStage but if it is possible I will have to keep trying. Thank you Siva.

I have done this before where the temp table is created and populated in the before SQL then read in the same stage's user defined SQL. I think that this is about the only way you could use the temp table since (from what I can tell) each OLEDB stage in the job has it own OLEDB session.

justlrng · Post by **justlrng** » Tue Aug 10, 2004 8:36 am

shawn_ramsey wrote: I have done this before where the temp table is created and populated in the before SQL then read in the same stage's user defined SQL. I think that this is about the only way you could use the temp table since (from what I can tell) each OLEDB stage in the job has it own OLEDB session.

I never thought about using the Before SQL - I'll try that. Thanks.