Hi
I am using an XQUERY to extract XML Data in SQL Server table. I have used certain SET options as well. I am getting error while viewing data through ODBC Stage. I am calling Select statement through file. Please find the query below:
set quoted_identifier on
SET NOCOUNT ON
SET ARITHABORT ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
GO
SELECT
RequestPayload.value('declare namespace ns="http://guidewire.com/cc/gx/qbe.cc.outbo ... archar(50)') AS Clm_Pol_SSR_Code
FROM
GWCC_Custom_Dev2.DBO.PAYLOAD_QUEUE_ITD;
SET NOCOUNT OFF
SET ARITHABORT OFF
SET ANSI_NULLS OFF
SET ANSI_PADDING OFF
SET ANSI_WARNINGS OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET NUMERIC_ROUNDABORT ON
GO
ERROR:-
ODBC function "SQLExecute" reported: SQLSTATE = 42000: Native Error Code = 102: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Legacy Driver][SQL Server]Incorrect syntax near 'GO'
Error using - XQUERY in ODBC Connector stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 76
- Joined: Wed Jan 06, 2016 9:40 am
- Location: Bangalore
Error using - XQUERY in ODBC Connector stage
Thanks
Naren Babu Ch
Naren Babu Ch
Don't "do" SQL Server but I would suggest you put the SET commands in the "Before SQL" area, perhaps without the GO, and leave just the select there as the source query to run. And there's no need for the second SET statements to undo everything.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 76
- Joined: Wed Jan 06, 2016 9:40 am
- Location: Bangalore
Hi
I have removed Set commands from the query and put it in Before SQL, Now again I am getting below error which I got when I did not use SET commands at all.
ODBC_PAYLOAD_QUUE_ITD,0: ODBC function "SQLExecute" reported: SQLSTATE = 42000: Native Error Code = 1934: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Legacy Driver][SQL Server]SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. (CC_OdbcDBStatement::executeSelect, file CC_OdbcDBStatement.cpp, line 1528)
I have removed Set commands from the query and put it in Before SQL, Now again I am getting below error which I got when I did not use SET commands at all.
ODBC_PAYLOAD_QUUE_ITD,0: ODBC function "SQLExecute" reported: SQLSTATE = 42000: Native Error Code = 1934: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Legacy Driver][SQL Server]SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. (CC_OdbcDBStatement::executeSelect, file CC_OdbcDBStatement.cpp, line 1528)
Thanks
Naren Babu Ch
Naren Babu Ch
And you're certain that they are "correct for use" as noted in the error? If so then someone else will have to help.Naren12345babu wrote:Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
From an Oracle standpoint one can issue "ALTER SESSION SET xxx" commands from Before SQL and they apply to all of the work done, was assuming SQL Server would have the same capability.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers