June 14, 2018 at 3:48 am
Hello All,
In the code below, this version which does not produce problems.
Removal of "SET QUOTED....", sometimes produces (but not consistently) :
Msg 1934, Level 16, State 1, Line 10
INSERT 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.
Is this a known issue ?
Thanks for you time and attention,
Ben
Code below is present in a file and run as a batch.
Removing the SET QUOTED_I..... does produce a difference.
Directly run from within the SSMS behaviour is not always the same as in the Batch situation.
SET ANSI_NULLS on
SET QUOTED_IDENTIFIER on
------------------------
INSERT INTO [TRN_AAAAAA_MUT]
SELECT REPL_DATE,'D3', [_T1].*
FROM [TRN_AAAAAA] AS [_T1]
INNER JOIN [BBBBBB] AS [_T2]
ON (([_T1].CCCCCC = [_T2].ID) ) and action in ('UPDY','WRIY','INSY','DELY')
--
--
INSERT INTO [TRN_AAAAAA_MUT]
SELECT [_T2].REPL_DATE,[_T2].action, [_T1].*
FROM [AAAAAA] AS [_T1]
INNER JOIN [BBBBBB] AS [_T2]
ON (([_T1].CCCCCC = [_T2].ID) ) WHERE action in ('UPDY','WRIY','INSY')
--
--
DELETE [_T1]
FROM [TRN_AAAAAA] AS [_T1]
INNER JOIN [BBBBBB] AS [_T2]
ON (([_T1].CCCCCC = [_T2].ID) ) and action in ('UPDY','WRIY','INSY','DELY')
--
--
INSERT INTO [TRN_AAAAAA]
SELECT [_T1].*
FROM [AAAAAA] AS [_T1]
INNER JOIN [BBBBBB] AS [_T2]
ON (([_T1].CCCCCC = [_T2].ID) ) WHERE action in ('UPDY','WRIY','INSY') -- No DELETE
------------------------
June 14, 2018 at 4:36 am
Ben
Are there any indexed views, indexes on computed columns , filtered indexes, query notifications, XML data type methods or spatial index operations involved?
Behaviour won't always be the same in SSMS as in other applications since different providers have different setting as the default.
John
June 14, 2018 at 5:34 am
Behaviour won't always be the same in SSMS as in other applications since different providers have different setting as the default.
Not sure but even running under the same coditions sometimes the code worked, sometimes it didn't work when "SET QUOTED_IDENTIFIER on" was ommitted.
Because this was part of a very large batchrun, where to catch this I produced a log of 100 K lines (took me some time), I wanted and still want to know why sometimes I could reproduce this error and sometimes (same circumstances ???) I could not. It was very difficult for me to isolate this. It took me a long time to produce a small sample where the behaviour was consistent within the same batch/application/code.
Thanks for your anwser.
And to keep the things practical, I will keep the SET code in place. (And leave it at that).
Ben
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply