Help!!! Procedure Err when Executed from Datastage

roy · Post by **roy** » Mon Jul 27, 2009 6:45 am

Hi,
Do you run the DS job using the same user as you run it with the ManagementStudio? (if not it might be a privilages related issue)
There might also be an issue with the transactions, you might need to have the entire SP (Stored Procedure) wraped as a single transaction and not parts of it.
Since DS simply requests a SP to run, you might also want to consult your DBA.

IHTH (I Hope This Helps),

deeptikunupudi · Post by **deeptikunupudi** » Mon Jul 27, 2009 8:23 am

roy wrote:Hi,
Do you run the DS job using the same user as you run it with the ManagementStudio? (if not it might be a privilages related issue)
There might also be an issue with the transactions, you might need to have the entire SP (Stored Procedure) wraped as a single transaction and not parts of it.
Since DS simply requests a SP to run, you might also want to consult your DBA.

IHTH (I Hope This Helps),

Hi Roy,
Thank you very much for your reply.
The User ID used to run Procedure from DS Job and Management Studio are same(the issue with the Privileges can be ruled out since the ID as DDL Privileges).
Yes there might be an issue with the SP which I/MyDBA are still not able figure out

.
Please let me know if you have any more suggestions and I will update you if I find the answer.

-Thanks,
Deepti

jdmiceli · Post by **jdmiceli** » Wed Aug 26, 2009 4:11 pm

How are you calling your stored procedure (Stored Procedure stage, ODBC stage, RDS stage, other)? That can make a difference in how it runs. I have been fighting through some of the same issues this last week, but my transactions are working, so I am wondering if there is a difference in how we are calling things. My workflow is as follows:

runner_jobs = xfm --> odbc stage (this combination works well to run stored proces with return values used for validation)

JobSequence - calls runner_jobs and does the error handling

JobControl (Ken Bland's dsj at the AIX level) - handles predecessors and other control pieces

The runner_jobs are the stored procedure interfaces for my process and they appear to work fine. My DataStage installation is on AIX Unix, but my databases are all SQL Server 2000.