Stored proc Issue

pradkumar · Post by **pradkumar** » Wed Nov 26, 2008 10:58 am

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

pradkumar · Post by **pradkumar** » Wed Nov 26, 2008 5:06 pm

Hi to add some more information.

I have a column in the table and in that 0 values need to change to 1 and the 1 value should change to 0.

Please someone could suggest ..

chulett · Post by **chulett** » Wed Nov 26, 2008 5:13 pm

Suggest you don't really need a proc for that.

As to what your particular proc / stage issue is... sorry, no clue.

pradkumar · Post by **pradkumar** » Thu Nov 27, 2008 4:59 pm

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

pradkumar · Post by **pradkumar** » Fri Nov 28, 2008 6:18 pm

Hi Everyone,

Any idea on this issue Please...

chulett · Post by **chulett** » Sat Nov 29, 2008 1:16 am

Have you read the stpstage.pdf documentation? A proc needs to be written in specific ways to be usable with the stage, your issue may lie there. Check the Additional Database Specifics section in particular.

pradkumar · Post by **pradkumar** » Sat Nov 29, 2008 7:02 pm

Ok thanks Chulett..

pradkumar · Post by **pradkumar** » Thu Dec 04, 2008 1:53 pm

Hi ,

The Stp stage is working and no need to continuously import metadata
By forcing it to use a parameter even though the stored proc does not need one.

Thanks for everyone sharing the knowledge and iam marking the topic as resolved.