can not call a Stored Procedure using odbc stage

acool · Post by **acool** » Thu Aug 12, 2004 12:24 pm

Hi every, I tried to call a stored procedure using an odbc stage.

here is the error message, does anyone know what's going on? Thanks.

|LdUserDetail_0812.ODBC_50: DSD.BCIConnect connecting to userDB, call to SQLConnect failed.
SQLSTATE=IM002, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Data source name not found and no default driver specified|

tonystark622 · Post by **tonystark622** » Thu Aug 12, 2004 2:31 pm

It appears from the message that you do not have an ODBC DSN set up on your DataStage server with the name that you specified in the ODBC stage. If so, it doesn't appear to be set up correctly.

Tony

chulett · Post by **chulett** » Thu Aug 12, 2004 2:35 pm

You might want to consider using an OCI stage, or better yet (if on 7.5) the Stored Procedure stage.

neena · Post by **neena** » Thu Aug 12, 2004 2:56 pm

Check your ODBC connectivity parameteres.. Your connect string DSN is not pointing to RIGHT direction

Thanks
Neena

acool · Post by **acool** » Thu Aug 12, 2004 3:13 pm

chulett wrote:You might want to consider using an OCI stage, or better yet (if on 7.5) the Stored Procedure stage.

I tried OCI stage, and it works. But is there any disadvantage if I use OCI stage instead of odbc stage?

chulett · Post by **chulett** » Thu Aug 12, 2004 3:41 pm

Not that I am aware of. In my opinion, it's always better to use a 'native' stage rather than a generic one, if available.

ray.wurlod · Post by **ray.wurlod** » Thu Aug 12, 2004 10:04 pm

If you use a Dynamic Relational Stage you can flick between the two and figure out the differences.

rajeev_prabhuat · Post by **rajeev_prabhuat** » Wed Dec 29, 2004 10:39 pm

Hi,

Can you tell me how did you call Procdure in Oracle OCI stage.

Regards,
Rajeev Prabhu

acool wrote:
chulett wrote:You might want to consider using an OCI stage, or better yet (if on 7.5) the Stored Procedure stage.
I tried OCI stage, and it works. But is there any disadvantage if I use OCI stage instead of odbc stage?

madhavgp · Post by **madhavgp** » Tue Jan 11, 2005 6:47 am

Hi acool,
I have successfully called a DB2 stored procedure using a ODBC stage using 'user defined SQL query' option and writing:

call procedurename(argument list);

in the 'enter sql' tab

but was unsuccessful using 'stored procedure' option due to:

1.) it required that i import that s proc defn beforehand

2.) when i tried to do that i could locate the s procedure but while importing it asked to enter arguments and i did taht then the query it generated was out of this world.

thanks,
Madhav

madhavgp · Post by **madhavgp** » Tue Jan 11, 2005 6:50 am

Hi,
Can anybody tell me how to call a db2 stored procedure using 'stored procedure' option in outputs page of a ODBC stage...

Thanks,
Madhav