Stored Procedure fails with incorrect SET options

[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?

[betterthanever]

Are you calling the store procedure in a OPEN/CLOSE command in ODBC Enterprise stage???

[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.

[chulett]

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

[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]

[betterthanever]

does the SP runs with no issues when you run on the DB directly???

[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.

[suryapkakani]

how did you solve this problem as iam facing the same problem now...

[chulett]

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

[suryapkakani]

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

[chulett]

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.