MS SQLSERVER stored procedure with message causing error

ldesilvio · Post by **ldesilvio** » Thu Jul 13, 2006 4:50 pm

I'm using an ODBC stage to call an MS SQLSERVER stored procedure. The job runs fine if rows are returned. If no rows are found, the stored procedure is coded to print a return message. When this return message is printed, the job interprets it as a failure condition and aborts. Is there anyway to ignore the print message and prevent the job from aborting?

It prints the return message as an informational log in Director then fails with this log:

src_SDB_remit_collateral_sp..remit_collateral_sp.in_remit_collateral_sp: DSD.BCIOpenR call to SQLNumResultCols failed.
SQLSTATE=S1010, DBMS.CODE=0
[DataStage][SQL Client]Can't provide information at this time for this prepared statement.

kcbland · Post by **kcbland** » Thu Jul 13, 2006 4:54 pm

hmmm, I can't remember any switches in the ODBC stages that will work. Any reason you can't use the STP (Stored Procedure Stage), it has a lot of features in it?

I'm an idiot, never mind, it's only for Oracle. Can you verify your version doesn't have SQL-Server support?

ray.wurlod · Post by **ray.wurlod** » Thu Jul 13, 2006 5:30 pm

When this return message is printed, the job interprets it as a failure condition and aborts. Is there anyway to ignore the print message and prevent the job from aborting?

No. That's the documented behaviour for any programming using the ODBC API. If SQLNumResultCols() does not return a value, then the code can not determine how many variables need to be bound to the result set. It's actually nothing to do with the message per se - they just happen to coincide in time.

chulett · Post by **chulett** » Thu Jul 13, 2006 8:59 pm

kcbland wrote:I'm an idiot, never mind, it's only for Oracle.

While the original version only supported Oracle, the version in 7.5.1A supports Oracle, DB2 and Sybase. So still no SQL-Server but not just Oracle no more.

kcbland · Post by **kcbland** » Thu Jul 13, 2006 9:21 pm

Missed it by: that much!