Stack Overflow
Stack Overflow

Questions tagged [ansi-nulls]

Use the "sql-null" tag instead.

17 questions
15
votes
3 answers

Update ANSI_NULLS option in an existing table

In our database there is a table which is created with ANSI_NULLS OFF. Now we have created a view using this table. And we want to add a clustered index for this view. While creating the clustered index it is showing an error like can't create an…
Mahesh KP
  • 6,248
  • 13
  • 50
  • 71
10
votes
3 answers

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics

I have checked over the whole web and couldn't find a solution that seems to work for me.. I have recreated my stored procedure, making sure to have these lines as first lines: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_WARNINGS…
Maude
  • 512
  • 3
  • 8
  • 23
7
votes
7 answers

T-SQL: What is NOT(1=NULL)?

I don't get the simple boolean algebra on my sql-server. According to msdn, the following statement should return "1", but on my server it returns "0". Can you help me? SET ANSI_NULLS ON SELECT CASE WHEN NOT(1=NULL) THEN 1 ELSE 0 END Please have a…
Andreas
  • 1,997
  • 21
  • 35
3
votes
1 answer

T-SQL / Unexpected NULL handling when ANSI_NULLS is turned OFF

I'm just struggling with the NULL value handling in SQL Server (tested on Version 12.0.5000.0). Basically, my intention is to get all rows that have a column value <> a static value (e.g. 999). I'm NOT searching for an alternative LIKE "use the…
MBauer
  • 33
  • 3
2
votes
1 answer

How to import SET ANSI_NULLS ON/OFF for stored procedures in VS2010 Database Projects?

I think my question is covered pretty well in my question, but here's some background: We have about 1500 stored procedures in our database. About half of them have SET ANSI_NULLS ON and half have SET ANSI_NULLS OFF. We want to use vs2010 database…
Jonas Lincoln
  • 9,567
  • 9
  • 35
  • 49
2
votes
4 answers

SQL to return parent rows even when no child rows

editing my question * I have a set of tables. When I filter on the second table, t2, I'd still like to get all rows of t1. SQL script is below. I feel like I'm getting close while tinkering, but I just can't make it happen. In short, I need t2's…
ChrisH
  • 975
  • 12
  • 21
1
vote
2 answers

SQL Server 2008R2: SET ANSI_NULLS OFF does not affect merge matching with null values

I have read up on the use of "SET ANSI_NULLS OFF" for a current session to be able to evaluate NULL = NULL to true, e.g. the following example shows the different between ANSI_NULLS ON AND ANSI_NULLS OFF: QUERY A: SET ANSI_NULLS OFF IF(NULL =…
Stephan Møller
  • 1,247
  • 19
  • 39
0
votes
2 answers

MS SQL Server Unexpected Behavior

I thought I understood SQL fairly well, but here is a simple example of NULL values introducing a bug that I didn't expect. Could someone explain why the first and second SELECT queries yield different results. DROP TABLE IF EXISTS #temp; CREATE…
MRodriguez
  • 379
  • 3
  • 10
0
votes
1 answer

Is there a way to import ANSI_NULLS ON/OFF for stored procedures in VS database projects?

We have around 1000 stored procedures with few having SET ANSI_NULLS ON and some OFF, depending on the scope. Using VS2019 and importing all procedures into a new project, the ANSI setting is not captured. How can I import all procedures with the…
Ak11
  • 1
0
votes
1 answer

Convert T-SQL to GCP BigQuery SQL. How to make null <> null in BQ SQL the same as SQL Server T-SQL?

Trying to convert SQL script from SQL Server T-SQL to GCP BigQuery SQL and need to maintain ansi nulls setting to ensure same results. Is there a way to make Google BigQuery return true when null = null is compared (i.e. 'null = null is true'…
pmo511
  • 569
  • 3
  • 9
0
votes
0 answers

SQL Server ANSI_NULLS confusion if it's set or not

So while I understand what it's for and why I want it on. I have a customer database that has some how been created with it off. There seems to be lots of places to change this value, on the server, on the db in SSMS options My confusion is how do…
Chris Ward
  • 771
  • 1
  • 9
  • 23
0
votes
2 answers

How to find routines that are created with SET ANSI_NULLS OFF?

I have created a filtered non-clustered index in order to optimize particular set of queries, but I started to get the following errors from various sources: UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS'.…
gotqn
  • 42,737
  • 46
  • 157
  • 243
0
votes
0 answers

set ansi_nulls and quoted_identifier on dynamically

I have a lot of scripts to run on a new database for creating all the stored procedures we need. Problem: none of those scripts are setting ANSI_NULLS ON or QUOTED_IDENTIFIER ON and I need to set them on for every stored procedure Is there a way to…
DJPB
  • 5,429
  • 8
  • 30
  • 44
0
votes
0 answers

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS - Trigger dealing with linked server

Upon trying to insert into table via app, I receive following error message. Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. Neither ANSI_NULLS ON nor ANSI_WARNINGS ON did help... SET…
unknown
  • 461
  • 9
  • 23
0
votes
1 answer

SET ANSI_NULL is ON but NULL rows are still being returned

I have the following table with SET ANSI_NULL set to ON SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblStandard5columnCustomerDetails] ( [Id] [int] NOT NULL, [FName] [varchar](60) NULL, …
Data Engineer
  • 795
  • 16
  • 41
1
2