Executing SQL Server Stored procedure

raj_cipher · Post by **raj_cipher** » Fri Jul 20, 2007 11:42 am

Hi,

I am calling a SQL Server stored procedure using an ODBC stage. The stored proc is inserting rows in few tables. The job runs to successful completion but there were no rows inserted. When the stored procedure is executed via query analyser it is inserting rows. I am not able to find out what is the issue. Is there any way that i could get more info from the log or to nail down the issue?

DSguru2B · Post by **DSguru2B** » Fri Jul 20, 2007 12:22 pm

Are you getting any warning messages? It is kind of hard to tell whats going on without any info on design and error message.

raj_cipher · Post by **raj_cipher** » Fri Jul 20, 2007 12:24 pm

No warning messages DSGuru :cry: Yes it is getting too hard for me.
I am looking for a way that would give me more log messages.

DSguru2B · Post by **DSguru2B** » Fri Jul 20, 2007 12:28 pm

Try with the STP stage.

raj_cipher · Post by **raj_cipher** » Fri Jul 20, 2007 12:29 pm

STP stage doesn't support SQL Server, right?

DSguru2B · Post by **DSguru2B** » Fri Jul 20, 2007 12:36 pm

Yea. You are right, I just checked the docs.
Coming back to odbc stage, are you calling the stored proc at the target level?

raj_cipher · Post by **raj_cipher** » Fri Jul 20, 2007 12:39 pm

My server version is 7.5.1.A. But the pdf says it supports DB2,Oracle and Sybase. But will give that a try.

raj_cipher · Post by **raj_cipher** » Fri Jul 20, 2007 1:27 pm

Got this resolved. There was a problem with the DB. DBA resolved it.

raj_cipher · Post by **raj_cipher** » Mon Jul 23, 2007 3:53 am

Yes DSGuru. The SP is called at the target level.

roblew · Post by **roblew** » Tue Jul 31, 2007 4:51 pm

Hi,

We're also trying to call a SQL Server stored procedure from DS. It seems that only the "ODBC" Server stage has the stored procedure option. I do not see the stored procedure option in the "ODBC Enterprise" or "Stored Procedure" PX/EE stages.

Am I missing something? Did you register the Server version of the ODBC stage in your parallel job?

thanks,
Rob

lstsaur · Post by **lstsaur** » Tue Jul 31, 2007 6:08 pm

Rob,
ODBC Enterprise Stage doesn't have a "stored procedure" option. You are not missing anything.

chulett · Post by **chulett** » Tue Jul 31, 2007 7:36 pm

roblew wrote:I do not see the stored procedure option in the "ODBC Enterprise" or "Stored Procedure" PX/EE stages.

What "stored procedure option" would you be looking for in the Stored Procedure stage?

roblew · Post by **roblew** » Wed Aug 01, 2007 11:41 am

chulett wrote:
roblew wrote:I do not see the stored procedure option in the "ODBC Enterprise" or "Stored Procedure" PX/EE stages.
What "stored procedure option" would you be looking for in the Stored Procedure stage?

sorry, I meant to say that there's no SQL server option in the stored procedure stage, which has already been mentioned.

Thanks