April 7, 2010 at 4:50 pm
CirquedeSQLeil (4/7/2010)
...I'm curious what effects these ansi_nulls off settings in the CRM procs and functions has that requires it to be that way.
I am too, that's why I asked if there was a reason to use Ansi_nulls off. I thought about changing this in the CRM but my experience there is that things break and it is better not to tinker with it. I vaguely recall way back, maybe even pre sql 2000 setting this off, but I can't remember why. This has been eating at me all day.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
April 7, 2010 at 5:01 pm
Trey Staker (4/7/2010)
CirquedeSQLeil (4/7/2010)
...I'm curious what effects these ansi_nulls off settings in the CRM procs and functions has that requires it to be that way.I am too, that's why I asked if there was a reason to use Ansi_nulls off. I thought about changing this in the CRM but my experience there is that things break and it is better not to tinker with it. I vaguely recall way back, maybe even pre sql 2000 setting this off, but I can't remember why. This has been eating at me all day.
Like you, I really don't want to screw with any of the CRM internals like that. I am sure it would break just like Biztalk or MOSS is prone to break when you tinker.
Which makes me wonder, do BizTalk and MOSS do the same thing?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 6:23 pm
RBarryYoung (4/7/2010)
Paul:Now I'm curious to know how you did on mine...? (here: http://www.sqlservercentral.com/questions/T-SQL/63632/) Its still got one of the lowest correct percentages that I know of.
The "Exponent Engima" was one of the best questions I have seen on this site. I got it right because I happened to remember the meaning of the ^, but what I found to be really fascinating was the fact that the error was not trappable. It is only sev 11, yet you cannot trap it. For example:
set nocount on;
declare @a bigint, @b-2 bigint;
set @a = 16;
begin try
select 1/0;
select 'A';
select sqrt(@a ^ @a - 17);
end try
begin catch
select error_number() number, error_severity() severity,
cast(error_message() as varchar(40)) [message];
end catch;
print 'test passed';
set nocount off;
go
This runs just fine because 1/0 raises sev 16 error so the flow travels to the catch block as expected, provides the error info, goes passed the catch to print "test passed". The result to text of the above yields nicely looking
-----------
number severity message
----------- ----------- ---------------------------------
8134 16 Divide by zero error encountered.
test passed
Comment out the line select 1/0; and the result is not what one would expect. The flow does not travel to the catch block and the batch is aborted on the spot. The results window shows
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded
all in red. This is despite the fact that 16 is indeed greater than 11. I suspect that this is because the "A domain error occurred." sev 11 warning is followed by something much higher internally. This remains a mystery for me to this day.
Oleg
April 7, 2010 at 6:54 pm
CirquedeSQLeil (4/7/2010)
Trey Staker (4/7/2010)
CirquedeSQLeil (4/7/2010)
...I'm curious what effects these ansi_nulls off settings in the CRM procs and functions has that requires it to be that way.I am too, that's why I asked if there was a reason to use Ansi_nulls off. I thought about changing this in the CRM but my experience there is that things break and it is better not to tinker with it. I vaguely recall way back, maybe even pre sql 2000 setting this off, but I can't remember why. This has been eating at me all day.
Like you, I really don't want to screw with any of the CRM internals like that. I am sure it would break just like Biztalk or MOSS is prone to break when you tinker.
Which makes me wonder, do BizTalk and MOSS do the same thing?
I cannot say about BizTalk, but as far as MOSS goes, CRM is much more tolerant of and even supportive of certain kinds of database customizations.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 7, 2010 at 7:05 pm
Oleg Netchaev (4/7/2010)
Paul White NZ (4/7/2010)
Toreador (4/7/2010)
Is there any situation where setting ansi_nulls off is a good idea?I have never come across one. Apart from the bizarre rules illustrated by the question, ANSI_NULLS OFF is going away in a future version. Attempting to set ANSI_NULLS OFF will generate an error message in that future version. Will be a huge change for some people.
This is a very good question and excellent explanation. Thank you Paul.
The best part for me was to thoroughly enjoy one more read about the ansi_nulls off going away. I cannot wait for this to happen. It would be interesting to see how the migration of the old procs is going to go. Since ansi_nulls is one of the 2 "sticky" settings, there might be a plenty of procs out there which accidentally inherited the settings from the connections which was used to create them. What I mean is that if someone creates a proc on the connection which has the setting off due to user options set incorrectly or for whatever reason then doing the right thing inside the body of the proc does not unfortunately have any effect...
Hey Oleg,
Thanks! Also for posting that great example, which will continue to trap the unwary until this strange behaviour finally goes away one day. The 'caveman' in question might also think that the setting inside the procedure can be made to 'stick' with a judiciously-placed OPTION (RECOMPILE), or by adding WITH (RECOMPILE) to the definition...but no! The dogged determination of the stored ANSI_NULLS setting will surprise many people.
Awesome feedback, thanks again.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 7:54 pm
CirquedeSQLeil (4/7/2010)
Paul,I hope you don't mind. I took your great script and made a modification to it....There are some display issues with < > type symbols - but I think it is quite useful.
...this version handles those characters:
SELECT object_type = OBJ.type_desc,
[schema_name] = SCHEMA_NAME(OBJ.[schema_id]),
[object_name] = OBJ.name,
proc_definition =
(
SELECT M.[definition] AS [processing-instruction(definition)]
FROM sys.sql_modules M
WHERE M.[object_id] = OBJ.[object_id]
FOR XML PATH(''), TYPE
)
FROM sys.objects OBJ
JOIN sys.sql_modules SM
ON SM.[object_id] = OBJ.[object_id]
WHERE OBJ.type_desc IN
(
N'SQL_STORED_PROCEDURE',
N'SQL_SCALAR_FUNCTION',
N'SQL_INLINE_TABLE_VALUED_FUNCTION',
N'SQL_TABLE_VALUED_FUNCTION',
N'SQL_TRIGGER',
N'VIEW'
)
AND SM.uses_ansi_nulls = 1
AND OBJ.is_ms_shipped = 0
ORDER BY
object_type,
[schema_name],
[object_name];
😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 7:58 pm
weitzera (4/7/2010)
When I first came to use SQL, I was flabbergasted that anyone could think it wise to design a system where x=y could be false and x <> y could also be false. Nevertheless, it was beaten into me, and I adapted. I have never tried turning that setting off, because I always fell that sticking to standards will help you as a developer in the long run. Of course I got this question wrong, and now I'm even more flabbergasted that turning off this setting makes _less_ sense (to the uninitiated) than the regular null weirdness! Well, live and learn... Thanks for the great question Paul.
Thanks for the feedback. I didn't aim to write a hugely difficult question - just one that highlights behaviour I find counter-intuitive - so I am glad it has proved to be thought-provoking!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 8:01 pm
RBarryYoung (4/7/2010)
Heh. I have to admit that I got it wrong too. I haven't done anything with ANSI_NULLs disabled in a very, very long time, and I couldn't decide if looking up the setting effects would be cheating or not... so I didn't and yup, only got 50% of the options right (which means 0% of the question entire).Good, tough question Paul. 🙂
I wrote this question several months ago now... and I had to stop and have a good think to remember!
I think looking up the settings would be absolutely fine for this question - only running the code would be cheating 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 8:05 pm
RBarryYoung (4/7/2010)
Paul:Now I'm curious to know how you did on mine...? (here: http://www.sqlservercentral.com/questions/T-SQL/63632/) Its still got one of the lowest correct percentages that I know of.
Managed to get it right...but only because I recognised the error message 'a domain error has occurred'...which I think used to appear for LOG(0) too, though the error message has changed now (at least in 2008). I have had the dubious honour of fixing code that threw this exception in a production system - some crazing ordering logic based on LOG...scary stuff.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 8:09 pm
Oleg Netchaev (4/7/2010)
Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded
Was a bug. Fixed now, and the code (with 1/0 commented out) produces error 3623, level 16:
[font="Courier New"]An invalid floating point operation occurred.[/font]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 8:14 pm
sknox (4/7/2010)
What's MORE important is how NULLS improve queries and and designs when used properly and when necessary. Sadly that's usually the exception...
Yes. I do use NULL columns occasionally in my designs - and I hope I do so correctly.
Tricky subject though, and always a bit of a compromise.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 8:15 pm
Paul White NZ (4/7/2010)
RBarryYoung (4/7/2010)
Paul:Now I'm curious to know how you did on mine...? (here: http://www.sqlservercentral.com/questions/T-SQL/63632/) Its still got one of the lowest correct percentages that I know of.
Managed to get it right...but only because I recognised the error message 'a domain error has occurred'...which I think used to appear for LOG(0) too, though the error message has changed now (at least in 2008). I have had the dubious honour of fixing code that threw this exception in a production system - some crazing ordering logic based on LOG...scary stuff.
Yup, AFAIK SQRT, LOG and maybe POWER are the only things that return this error.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 7, 2010 at 8:34 pm
RBarryYoung (4/7/2010)
Yup, AFAIK SQRT, LOG and maybe POWER are the only things that return this error.
Ooo - I had never tried POWER. Have now, and you're right - though POWER (0, -1) gives me a divide-by-zero, rather than a domain error or invalid floating point operation.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 9:01 pm
Paul White NZ (4/7/2010)
RBarryYoung (4/7/2010)
Yup, AFAIK SQRT, LOG and maybe POWER are the only things that return this error.Ooo - I had never tried POWER. Have now, and you're right - though POWER (0, -1) gives me a divide-by-zero, rather than a domain error or invalid floating point operation.
Yeah, there's just so many reasons that ones wrong, it's hard to pick just one. Almost as bad as POWER(0, 0). 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 7, 2010 at 9:11 pm
Paul White NZ (4/7/2010)
CirquedeSQLeil (4/7/2010)
Paul,I hope you don't mind. I took your great script and made a modification to it....There are some display issues with < > type symbols - but I think it is quite useful.
...this version handles those characters:
SELECT object_type = OBJ.type_desc,
[schema_name] = SCHEMA_NAME(OBJ.[schema_id]),
[object_name] = OBJ.name,
proc_definition =
(
SELECT M.[definition] AS [processing-instruction(definition)]
FROM sys.sql_modules M
WHERE M.[object_id] = OBJ.[object_id]
FOR XML PATH(''), TYPE
)
FROM sys.objects OBJ
JOIN sys.sql_modules SM
ON SM.[object_id] = OBJ.[object_id]
WHERE OBJ.type_desc IN
(
N'SQL_STORED_PROCEDURE',
N'SQL_SCALAR_FUNCTION',
N'SQL_INLINE_TABLE_VALUED_FUNCTION',
N'SQL_TABLE_VALUED_FUNCTION',
N'SQL_TRIGGER',
N'VIEW'
)
AND SM.uses_ansi_nulls = 1
AND OBJ.is_ms_shipped = 0
ORDER BY
object_type,
[schema_name],
[object_name];
😀
Yeah that is much better. I see what you are doing with that and it makes sense. I think I will have to make that change to a few other scripts that I use the same thing in.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 46 through 60 (of 81 total)
You must be logged in to reply to this topic. Login to reply