Unable to execute sql query using CTE and Synonynm

1

Hi,

I am trying to load data into one table using below query which includes CTE and SYNONYM.

;WITH Split_Names (
content_id
,NAME
,xmlname
,value
,culture
,filename
)
AS (
SELECT content_id
,[key]
,CONVERT(XML, '' + REPLACE([Key], '', '') + '') AS xmlname
,value
,culture
,reverse(substring(reverse(value), 1, charindex('/', reverse(value)) - 1))
FROM [dbo].[SYN_SHAREDDB_CONTENT]
WHERE STATUS = 1
AND [key] LIKE 'BackUp_ClientEmployee_Resources_Forms%'
-- AND culture = 'en-US'
)
SELECT content_id
,xmlname.value('/Names[1]/name[5]', 'varchar(100)') AS jurisdiction_name
,substring(xmlname.value('/Names[1]/name[6]', 'varchar(100)'), 1, (charindex('-', xmlname.value('/Names[1]/name[6]', 'varchar(100)')) - 1)) min_age_in_months
,reverse(substring(reverse(xmlname.value('/Names[1]/name[6]', 'varchar(100)')), 1, charindex('-', reverse(xmlname.value('/Names[1]/name[6]', 'varchar(100)'))) - 1)) max_age_in_months
,xmlname.value('/Names[1]/name[9]', 'varchar(100)') AS form_id
,xmlname.value('/Names[1]/name[10]', 'varchar(100)') AS form_version
,substring(filename, 1, (charindex('.', filename) - 1)) AS form_name
,culture
,CASE culture
WHEN 'en-CA'
THEN 'CAN'
WHEN 'en-GB'
THEN 'GBR'
WHEN 'en-IE'
THEN 'IRL'
WHEN 'en-IN'
THEN 'IND'
WHEN 'en-us'
THEN 'USA'
ELSE NULL
END country
into required_form
FROM Split_Names
END

when i execute this in SSMS it went fine but when i execute this query thru sql server agent job getting below error.

"SELECT INTO failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. 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. [SQLSTATE 42000] (Error 1934). The step failed."

synonym query is staright forward : CREATE SYNONYM [dbo].[SYN_SHAREDDB_CONTENT] FOR [SharedDB].[dbo].[content]

i couldn't break this issue/error can anyone help me on this?

2

try SET QUOTED_IDENTIFIER OFF

3

Thanks for quick response britton.

I have set quote identifier off just above cte statement as below.

"
BEGIN
SET QUOTED_IDENTIFIER OFF

--select * from biz.required_form
;WITH Split_Names (
content_id
,NAME
,xmlname
"

still i am getting the same error.

I am not getting why the query is executing successfully in management studio and failing in sql job.

4

Just to rule out any problem with the Synonym presumably:

SELECT TOP 10 *
FROM [dbo].[SYN_SHAREDDB_CONTENT]

retrieves some data?

5

Review this document: https://msdn.microsoft.com/en-us/library/ms174393.aspx

Based on this make sure you create the stored procedure with quoted identifiers turned on - and that any tables/views/etc... were created with the setting turned on also.

6

No problem in synonym, query is giving results