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?