Issue while executing a sql server stored procedure

xch2005 · Post by **xch2005** » Sun Sep 29, 2013 10:10 am

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.

stephan.zahariev · Post by **stephan.zahariev** » Sun Sep 29, 2013 12:27 pm

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.

ArndW · Post by **ArndW** » Mon Sep 30, 2013 1:12 am

Could it be that you neglected the find "#" mark in your parameter, which should read "#calc_val#"?

xch2005 · Post by **xch2005** » Mon Sep 30, 2013 7:38 am

Hi ArndW,
The '#' was just a temp table inside the procedure and not a parameter.

Hi stephan.zahariev
Can you please let me know how I can use SQL Server Profilier from Datastage?

Thanks

stephan.zahariev · Post by **stephan.zahariev** » Mon Sep 30, 2013 2:49 pm

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.

lstsaur · Post by **lstsaur** » Mon Sep 30, 2013 3:15 pm

Take a look the manual on how DS handles the table name that starts with a # character.

xch2005 · Post by **xch2005** » Mon Sep 30, 2013 8:02 pm

Thanks stephan.zahariev

Yeah, I tried using sql server stored procedures, it does work fine.

Actually when I use the ETL to run the stored procedure, it aborts and the procedure does not run fine. When I run the procedure manually on sql server it runs fine.

Thanks

prasannakumarkk · Post by **prasannakumarkk** » Mon Oct 07, 2013 12:38 am

Invalid object name '#calc_val'

1) What stage you used?
2) What user/schema you used in stage for connection? With which user you succesfully executed outside datastage
3) Is the temp table created with in procedure

Please try using the schema name and check for user privileges.