Error using - XQUERY in ODBC Connector stage

[Naren12345babu]

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'

[chulett]

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.

[Naren12345babu]

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)

[chulett]

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.

And you're certain that they are "correct for use" as noted in the error? If so then someone else will have to help.

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.

[qt_ky]

I don't think that SET and GO are valid SQL.

[chulett]

Which is why I was thinking they needed to go 'before'... but a quick search seems to imply that they are for use in stored procedures rather than SQL.