Page 1 of 1
Executing SQL Server Stored procedure
Posted: Fri Jul 20, 2007 11:42 am
by raj_cipher
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?
Posted: Fri Jul 20, 2007 12:22 pm
by DSguru2B
Are you getting any warning messages? It is kind of hard to tell whats going on without any info on design and error message.
Posted: Fri Jul 20, 2007 12:24 pm
by raj_cipher
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.
Posted: Fri Jul 20, 2007 12:28 pm
by DSguru2B
Try with the STP stage.
Posted: Fri Jul 20, 2007 12:29 pm
by raj_cipher
STP stage doesn't support SQL Server, right?
Posted: Fri Jul 20, 2007 12:36 pm
by DSguru2B
Yea. You are right, I just checked the docs.
Coming back to odbc stage, are you calling the stored proc at the target level?
Posted: Fri Jul 20, 2007 12:39 pm
by raj_cipher
My server version is 7.5.1.A. But the pdf says it supports DB2,Oracle and Sybase. But will give that a try.
Posted: Fri Jul 20, 2007 1:27 pm
by raj_cipher
Got this resolved. There was a problem with the DB. DBA resolved it.
Posted: Mon Jul 23, 2007 3:53 am
by raj_cipher
Yes DSGuru. The SP is called at the target level.
Posted: Tue Jul 31, 2007 4:51 pm
by roblew
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
Posted: Tue Jul 31, 2007 6:08 pm
by lstsaur
Rob,
ODBC Enterprise Stage doesn't have a "stored procedure" option. You are not missing anything.
Posted: Tue Jul 31, 2007 7:36 pm
by chulett
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?
Posted: Wed Aug 01, 2007 11:41 am
by roblew
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