Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Tuesday, August 8, 2017 1:13 PM
Heloo All,
I have looked up many docs and this is not making any sense to me, please need advice. When i execute my sproc using the following command i'm getting the below error message
Msg 1934, Level 16, State 1, Procedure Load_DimRollBOLEDI, Line 73
INSERT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. 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.
Strangely when i right click on the sproc and click on execute it gives the following error
Msg 2627, Level 14, State 1, Procedure Load_DimRollBOLEDI, Line 71
Violation of PRIMARY KEY constraint 'pk_DimRollBOLEDI'. Cannot insert duplicate key in object 'dbo.DimRollBOLEDI'. The duplicate key value is (002C9433D17244BD70B4BC99D5B4AD70).
Which one should i believe?
My sproc has
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
(MYCODE)
Please need help thanks
Tuesday, August 8, 2017 1:20 PM
Just a guess. Can you run the below?
DROP PROCEDURE dbo.sp
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE dbo.sp......
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Tuesday, August 8, 2017 1:21 PM
What is happening is that when you run it through management studio you are getting a different set of ansi settings being used than when you run the proc through the client.
You then encounter the PK collision. You will need to address the PK collision.
What you will need to do is to determine what ansi setting you want to use. You can see what SQL Server Management Studio uses by going to Tools, Options, Query execution and ANSI.
You can tell what your stored procedure is using when executed via the client (ODBC, OLEDB, SQLCMD, or your .net provider, or EF) by using set options as documented here:
You might also want to ANSI_WARNINGS ON or OFF in your code to ensure that you get consistent results between Management Studio and however you execute your code, but you need to ensure that the ansi settings you use in your code make sense to you. They can have performance implications.
Tuesday, August 8, 2017 1:48 PM
Thanks Hilary Cotter
This is what i see from SSMS
Tuesday, August 8, 2017 1:50 PM
Thanks Uri
when i dropped and recreted am getting the error
Msg 2627, Level 14, State 1, Procedure Load_DimRollBOLEDI, Line 68
Violation of PRIMARY KEY constraint 'pk_DimRollBOLEDI'. Cannot insert duplicate key in object 'dbo.DimRollBOLEDI'. The duplicate key value is (002C9433D17244BD70B4BC99D5B4AD70).
Tuesday, August 8, 2017 1:56 PM
You will need to do some existence check to see if the row exists and if so skip the insert or do an update - the merge command is handy here.
Tuesday, August 8, 2017 1:57 PM
But that is a different problem..... You need to take care of duplicates , why do you use GIUID datatype, any reason?
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Tuesday, August 8, 2017 2:04 PM
Which one should i believe?
Both. The first error is a result of an ANSI_WARNINGS OFF session so no attempt is made to actually execute the INSERT. Perhaps that was executed from application code using an API with ANSI_WARNINGS OFF by default.
In the second case (SSMS), ANSI_WARNINGS ON is the default setting so the INSERT is actually attempted but failed due to the primary key violation.
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
Tuesday, August 8, 2017 2:59 PM
Unfortunately, "(MYCODE)" is not helpful. But the solution is obvious. Somewhere, somehow there is code that sets ansi_warnings to the "bad" value and that generates the error when your procedure is executed. You will need to debug the procedure logic to determine what specific statement is causing the problem - and then determine what to do. The first thing to look for is an insert statement (obviously, given the error) that inserts into an indexed view. Also review the logic of any triggers that might be executed by your procedure logic. If you find the culprit, then you will need todetermine WHY someone thought this was a good thing to do. This it the typical result of such shortcuts.
Tuesday, August 8, 2017 5:43 PM
Thanks Scott,
The duplicate records are taken care of and i still get the same error message ANSI_WARNINGS
Please advice
Tuesday, August 8, 2017 5:46 PM
Are there any triggers or filtered indexed that might have this setting set to off? Have you determined if you need to suppress these messages?
Tuesday, August 8, 2017 5:50 PM
Where exactly you're getting this error from? Is it from the application? You need to find out where that setting is being set incorrectly.
Also, you may find my article on the similar topic helpful
SQL Server: SET ANSI_PADDING Setting and Its Importance
For every expert, there is an equal and opposite expert. - Becker's Law
Tuesday, August 8, 2017 5:59 PM
There are no triggers and how to find about the filtered indices?
Thanks
Tuesday, August 8, 2017 6:00 PM
Thanks Naomi,
Both when i right click and execute the sproc
or execute dbo.sp_sproc
same message regarding the ANSI_WARNING
Tuesday, August 8, 2017 6:26 PM
So, if in SSMS you add SET ANSI_WARNINGS ON before running your sp
execute mySP
you will not have an error, right?
For every expert, there is an equal and opposite expert. - Becker's Law
Tuesday, August 8, 2017 7:54 PM
Strange, but I do not see my message I posted a while ago. Posting test message now.
UPDATE. Only after I posted this message I can now see the message I posted before.
Update 2. I can not see Erland's post either :(
For every expert, there is an equal and opposite expert. - Becker's Law
Tuesday, August 8, 2017 9:18 PM
Since we don't see exactly what you do, it's a little difficult to follow. But there are some features in SQL Server that requires ANSI_WARNINGS to be on, for instance indexed views or XML type methods. Since ANSI_WARNINGS should be ON, there is no reason to look for the exact features that causes this. (And it is necessarily not with the table you are inserting to, but it could be a trigger on that table that fails to compile because it in its turn accesses another table where this matter etc.)
If you are using SSMS, there is a trap when you script tables: SSMS may add the command SET ANSI_WARNINGS OFF after the script. And then if you run that command, you get in this bad moon.
So run
SET ANSI_WARNINGS ON
go
EXCEC your_sp
If you still get the error, there is something in your procedure, or something it calls that issues SET ANSI_WARNINGS OFF - a very bad idea!
Wednesday, August 9, 2017 12:51 PM
Thanks Erland and thanks to everyone as a temp solution i turned off the warnings
Wednesday, August 9, 2017 8:02 PM
I think you meant turn them on. Can you clarify what exactly did you do?
For every expert, there is an equal and opposite expert. - Becker's Law
Thursday, August 10, 2017 12:42 PM
Nope i did set ansi_warnings off
Thanks
Thursday, August 10, 2017 12:54 PM
That's weird. It is supposed to be ON, not OFF.
For every expert, there is an equal and opposite expert. - Becker's Law