Hi,
We actually execute a sql server stored procedure through a Datastage job and to capture the output of the procedure. The procedure on successfull execution would return 0 else -1 and return mesage either 'Success' or 'Failed' ie. 2 output paramenters from the procedure.
We the Datastage job is executed, the job aborts and the message that is there in the job log is like
'APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Invalid object name '#calc_val'. '
So I ran the procedure manualy, it completes and returns the return code and return message appropriately. The the temporary table is being created in the procedure and populated in the procedure and it exists when it access it.
Even though the procedure does not break when running manually not sure why the job aborts when running the procedure.
How to debug this kind of issue?
Approciate your help on this.
Thank you.
Issue while executing a sql server stored procedure
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 9
- Joined: Sun Feb 12, 2012 11:05 am
- Contact:
Hi,
What are you using to execute the stored procedure? Can you execute another stored procedure?
To debug this issue you can use the SQL Server Profiler which will allow you to inspect the SQL queries send from DataStage to the SQL Server. This should give you a clue whats wrong.
What are you using to execute the stored procedure? Can you execute another stored procedure?
To debug this issue you can use the SQL Server Profiler which will allow you to inspect the SQL queries send from DataStage to the SQL Server. This should give you a clue whats wrong.
Stephan
http://szahariev.blogspot.com
http://szahariev.blogspot.com
Could it be that you neglected the find "#" mark in your parameter, which should read "#calc_val#"?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 9
- Joined: Sun Feb 12, 2012 11:05 am
- Contact:
SQL Server Profiler is a tool that comes with SQL Server. You can read more here: http://technet.microsoft.com/en-us/libr ... 81091.aspx
You will need to have it installed locally on your workstation or in the target machine where SQL Server is running. Just keep in mind that you can easily bring down the entire SQL Server if you try to trace too much on heavily used system.
But first I would make sure that DataStage could invoke a simple stored procedure. You can start with this simple scenario.
You will need to have it installed locally on your workstation or in the target machine where SQL Server is running. Just keep in mind that you can easily bring down the entire SQL Server if you try to trace too much on heavily used system.
But first I would make sure that DataStage could invoke a simple stored procedure. You can start with this simple scenario.
Stephan
http://szahariev.blogspot.com
http://szahariev.blogspot.com
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India