Stored Procedure fails with incorrect SET options
Moderators: chulett, rschirm, roy
Stored Procedure fails with incorrect SET options
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?
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.
Application Consultant
Piper Jaffray, Inc.
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
Re: Stored Procedure fails with incorrect SET options
Are you calling the store procedure in a OPEN/CLOSE command in ODBC Enterprise stage???
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]
{? = 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.
Application Consultant
Piper Jaffray, Inc.
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
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.
Application Consultant
Piper Jaffray, Inc.
-
- Participant
- Posts: 47
- Joined: Wed Aug 20, 2008 7:31 am
- Location: New jersey
-
- Participant
- Posts: 47
- Joined: Wed Aug 20, 2008 7:31 am
- Location: New jersey
Hi Chulett,chulett wrote: You can't be having "the same problem". Please start your own post with the relevant details of your issue. ...
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
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.suryapkakani wrote:I am on a Server Edition...
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
"You can never have too many knives" -- Logan Nine Fingers