I have created a dataflow that in separate transform boxes does the following:
- Drops ta Stored Procedure
- Creates a Stored Procedure
- Calls the Stored Procedure
The Drop and Create transform return valid sql and seem to be working fine.
But the Call (step 3) fails indicating that the table that I created in the procedure doesn't exist.
Near the beginning of the stored procedure, I create the tables as follows:
TRUNCATE TABLE STG5Adjustments;
CREATE TABLE IF NOT EXISTS STG5Adjustments(
Field1 int,
Field2int,
FiscalYear int,
FieldValue decimal(18,2),
FieldAdjustment decimal(18,2)
);
Then as I loop thru the records in a cursor I insert the derived output records into my table as shown below:
INSERT INTO STG5Adjustments(Field1, Field2, FiscalYear, FieldValue, FieldAdjustment)
VALUES(v_Field1, v_Field2, v_FiscalYear, v_FieldValue, v_FieldAdjustment);
When I call the procedure (in a separate transform), it cannot find the table named STG5Adjustments that I created.
How do access this table that I created in my stored procedure???