Hi,
I have a job, which executes a stp stage. The stp stage is a storeproc that takes no parameters . The store proc in sql server, it updates certain values ina table.
Now I am checking these values in the tabele, BEFORE and then AFTER I execute the sp, the problem is, the sp seems to execute only when I reimport the metadata for the sp
Once I import the metadata, and I execute, I see that the values have changed correctly. I execute the sp again...no change. I execute it again, still no change...then I re-import the metadata for the sp, and then execute the sp, then a change, what's going on?
Can any one please give an idea where iam going wrong?
Thanks
Stored proc Issue
Moderators: chulett, rschirm, roy
Hi,
Thanks Chulet for you reply ..
May be dont need a proc for this .. But i need to find out why it is behaving like this in datastage job with just only STP stage.
When i execute the same stored procedure in the database say 1st time iam geting the result as ( 0,1,1,0) ; 2nd time iam getting the result as (1,0,0,1) .
But iam calling the same stored procedure in the datastage with the STP stage.. 1st time when i ran the job and checked in the database and result i got was ( 1,0,0,1) .. 2nd time when i ran the same job iam getting the same result as ( 1,0,0,1) . But iam expecting the result as ( 0,1,1,0) since when i execute the same SP in the database iam getting the expected results.
Once I import the metadata, and I execute, I see that the values have changed correctly. I execute the sp again...no change. I execute it again, still no change...then I re-import the metadata for the sp, and then execute the sp, then a change.
Any light on this issue. Why i need to re-import the metadata eveytime to get correct values.
Iam getting a warning message when i try to import the metadata .. it says
" The stored procedure (procedure name) has failed to report a result set.If this stored procedure accepts parameters you may enter them below and click ok to attempt to generate a result set.Clicking the cancel accepts the current argument list but doesnot execute the procedure.
WARNING: Execution of a stored procedure may have an impact on the database "
Please suggest any idea on this..
Thanks
Thanks Chulet for you reply ..
May be dont need a proc for this .. But i need to find out why it is behaving like this in datastage job with just only STP stage.
When i execute the same stored procedure in the database say 1st time iam geting the result as ( 0,1,1,0) ; 2nd time iam getting the result as (1,0,0,1) .
But iam calling the same stored procedure in the datastage with the STP stage.. 1st time when i ran the job and checked in the database and result i got was ( 1,0,0,1) .. 2nd time when i ran the same job iam getting the same result as ( 1,0,0,1) . But iam expecting the result as ( 0,1,1,0) since when i execute the same SP in the database iam getting the expected results.
Once I import the metadata, and I execute, I see that the values have changed correctly. I execute the sp again...no change. I execute it again, still no change...then I re-import the metadata for the sp, and then execute the sp, then a change.
Any light on this issue. Why i need to re-import the metadata eveytime to get correct values.
Iam getting a warning message when i try to import the metadata .. it says
" The stored procedure (procedure name) has failed to report a result set.If this stored procedure accepts parameters you may enter them below and click ok to attempt to generate a result set.Clicking the cancel accepts the current argument list but doesnot execute the procedure.
WARNING: Execution of a stored procedure may have an impact on the database "
Please suggest any idea on this..
Thanks