May 9, 2005 at 9:06 am
table with column città (in some cases città is NULL)
If i run select statement :
select * from qrbanca where codabi = '03069' and citta like '%'
I see only record where citta is NOT NULL. IS there any possibility to view also the record with NULL values with the same statement (maybe some db option)????
regards
May 9, 2005 at 9:20 am
select * from qrbanca where codabi = '03069'
in this case will return all records (nulls or not)
I might add that citta like '%' filters absolutely nothing (besides nulls in this case). Is there something more to the stored proc?
If there's more you might try to use this statement :
where codabi = '03069' (and citta like '%' + @SomeVar or citta is null)
May 9, 2005 at 9:42 am
we have migrated the database from sqlserver 7 to 2000.
Is there any db option that i miss?
The same query on SQL 7 returns 1200 rows (also record with null)...in sql 2000 only 9 records (no null values)
the query is run by application (i agree with you that like '%' doesn't filter nothing).
May 9, 2005 at 9:43 am
we have migrated the database from sqlserver 7 to 2000.
Is there any db option that i miss?
The same query on SQL 7 returns 1200 rows (also record with null)...in sql 2000 only 9 records (no null values)
the query is run by application (i agree with you that like '%' doesn't filter nothing).
May 9, 2005 at 9:52 am
I'll give this to you but keep in mind that you could break the whole app by changing this setting.. but since it seems to be already the case it might be worth a try
[edited]
Forget that solution.. won't work
May 9, 2005 at 10:03 am
I'm not sure how to change the db settings so I'll let someone else answer this one.
this solution however shows you how to change the setting on the connection
SET ANSI_NULLS ON
Select * from dbo.SysObjects where null = null
--returns nothing
SET ANSI_NULLS OFF
Select * from dbo.SysObjects where null = null
--returns everything
May 9, 2005 at 10:03 am
anyway in both sqlserver (7 and 2000) the option is OFF
May 9, 2005 at 10:07 am
Maybe there's another way, but I think the safest way would be to recheck every statement to make sure it returns the correct result (not just this one as something else may have broken in the application). And the recode accordingly. The very best option would be to move all the queries to the server and convert them to stored procs. But I guess that this is out of the question for now.
May 9, 2005 at 12:07 pm
how are you connecting to the database from the application? if you are using ODBC, you might want to check the Use ANSI nulls, paddings, and warnings check box. Its checked by default
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply