Stored procedure when executed in before sql does not work

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
xch2005
Participant
Posts: 85
Joined: Fri Apr 29, 2005 3:13 am
Location: India

Stored procedure when executed in before sql does not work

Post by xch2005 »

Hi,

Hope all are doing great. We migrated 8.5 version of jobs (Unix env.) to 11.5 version (Linux env.) recently. We are having trouble with a job that is executing a SQL Server stored procedure from ODBC connector. The same job runs fine in 8.5 but in 11.5 job completes but does perform the required activity, it completes in 0.02 sec in 11.5 version and takes 3 mins in 8.5.

Code: Select all

ODBC connector -> peek 
The design is simple and straight forward, ODBC connector and peek, just kicks off the SQL Server stored procedure in the "Before SQL" section.

Used this in the Before SQL section:

Code: Select all

Begin
exec dbname.dbo.sp_name
End;
Please note this code is working fine in 8.5 version.

Could you please let me know if anything is missing or needs correction for having it run in 11.5 version?

Thanks in advance.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Assuming you meant to say "but does not perform the required activity", yes? The new timing would certainly indicate it is doing nothing mighty fast but a bit surprised no error is generated if there's a problem. Have you checked to see if you are missing any fixpacks for your version?
-craig

"You can never have too many knives" -- Logan Nine Fingers
xch2005
Participant
Posts: 85
Joined: Fri Apr 29, 2005 3:13 am
Location: India

Post by xch2005 »

Yes, that is correct, it was a typo. I meant that "does NOT perform the required activity".

The version that I have is 11.5.0.2.

Unfortunately not getting a clue on why the job behaves like this.

Thanks.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Once upon a time with the Oracle Connector stage, I found that I had to CALL the SP instead of EXEC the SP. Hope that helps. The end.
Choose a job you love, and you will never have to work a day in your life. - Confucius
xch2005
Participant
Posts: 85
Joined: Fri Apr 29, 2005 3:13 am
Location: India

Post by xch2005 »

I tried to use CALL instead of EXEC to run the stored procedure (in ODBC connector) and unfortunately that did not work either.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Have you compared the ODBC driver being used and the driver settings?

Have you tried an ODBC trace?

Have you opened a support case?
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

You never answered the fixpack question and definitely need to open a support case if you haven't done so already.
-craig

"You can never have too many knives" -- Logan Nine Fingers
jerome_rajan
Premium Member
Premium Member
Posts: 376
Joined: Sat Jan 07, 2012 12:25 pm
Location: Piscataway

Post by jerome_rajan »

xch2005 wrote:I tried to use CALL instead of EXEC to run the stored procedure (in ODBC connector) and unfortunately that did not work either.
Try wrapping your SP with a BEGIN ... END. The workaround worked for me
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn

Life is really simple, but we insist on making it complicated.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Also known as an "anonymous block".
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Also known as an "anonymous block".
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply