Stored Procedure fails with incorrect SET options

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
stpat389
Participant
Posts: 5
Joined: Thu Sep 04, 2008 4:00 pm
Location: Minneapolis
Contact:

Stored Procedure fails with incorrect SET options

Post by stpat389 »

I am trying to get a Stored Procedure to run from DataStage 8.0 to MS SQL server. The Store Procedure works great on SQL Server 2005, but when called from Data stage, I get the following error:

APT_CombinedOperatorController,0: [DataDirect][ODBC SQL Server Driver][SQL Server]SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET option [odbcUtils.C:1289]

Where are the SET options set? Is there any documentation on what is the correct settings?
Kris Johnson
Application Consultant
Piper Jaffray, Inc.
betterthanever
Participant
Posts: 152
Joined: Tue Jan 13, 2009 8:59 am

Re: Stored Procedure fails with incorrect SET options

Post by betterthanever »

Are you calling the store procedure in a OPEN/CLOSE command in ODBC Enterprise stage???
stpat389
Participant
Posts: 5
Joined: Thu Sep 04, 2008 4:00 pm
Location: Minneapolis
Contact:

Post by stpat389 »

I don't use any Open or Close statements in the Store procedure. The unique item in the store procedure is I am reading an XML message and using path to parse the xml message to insert to a table.
Kris Johnson
Application Consultant
Piper Jaffray, Inc.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

That's not what was asked. Where/how exactly are you calling this stored procedure?
-craig

"You can never have too many knives" -- Logan Nine Fingers
stpat389
Participant
Posts: 5
Joined: Thu Sep 04, 2008 4:00 pm
Location: Minneapolis
Contact:

Post by stpat389 »

The Store Procedured is spInsertIdealTrade which was imported into the DS as a Store Procedure. It is called from the Store Procedure stage with the folloowiing call

{? = CALL spInsertIdealTrade;1 (?)}

I was able to get past the original problem by adding the following 3 statements to the Stored Procedure

SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON

Now the Stored procedure fails with the following error

InsertIdealData,0: Warning: FileInsertIdealData.InsertIdealData: SQLSTATE = , fNativeError = 0 [dscapiop.C:1896]
Kris Johnson
Application Consultant
Piper Jaffray, Inc.
betterthanever
Participant
Posts: 152
Joined: Tue Jan 13, 2009 8:59 am

Post by betterthanever »

does the SP runs with no issues when you run on the DB directly???
stpat389
Participant
Posts: 5
Joined: Thu Sep 04, 2008 4:00 pm
Location: Minneapolis
Contact:

Post by stpat389 »

Yes, general ODBC enterprise select and inserts work fine. In most cases the Store procedure works if it is very simple and don't pass nulls. The store procedure I am using reads values from an xml message to fill in columns of the table I am inserting.
Kris Johnson
Application Consultant
Piper Jaffray, Inc.
suryapkakani
Participant
Posts: 47
Joined: Wed Aug 20, 2008 7:31 am
Location: New jersey

Post by suryapkakani »

how did you solve this problem as iam facing the same problem now...
sunny
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

:!: You can't be having "the same problem". Please start your own post with the relevant details of your issue.
-craig

"You can never have too many knives" -- Logan Nine Fingers
suryapkakani
Participant
Posts: 47
Joined: Wed Aug 20, 2008 7:31 am
Location: New jersey

Post by suryapkakani »

chulett wrote::!: You can't be having "the same problem". Please start your own post with the relevant details of your issue. ...
Hi Chulett,

I am on a Server Edition...and I was browsing out for the error I had...so was curious to know the solution he had.....

I have a stored procedure which does not have any parameters or not returning any Value. I just wanted to execute this by having an ODBC having called a stored procedure in it, and giving a dummy column value and passing it to a sequential file.

I could solve this issue by using a user defined sql to call the stored procedure....and basically returnig a Binay value for success status.


Regards
sunny
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

suryapkakani wrote:I am on a Server Edition...
Yet another reason that you don't have the "same" error. It may be similar but it cetainly isn't the same. And the proper thing to do here, as I noted earlier, is to not hijack someone else's post but instead start your own in the proper forum (which is not this one) and if you think it would add value, include a link back to this or any other post you like.

Then we get your particulars, your details and you can mark the problem as Resolved if it gets to that point. When you do that we'll be happy to help you as best we can, but I for one am not prepared to continue this conversation in this thread.

Hope you understand.
-craig

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