September 4, 2003 at 3:32 am
Hi, I've just found an odd occurance on a project I'm working on and was wondering if anyone could shed some light on it.
I have two databases (1 development, 1 live) on the same physical SQL Server 2000/Win 2000 Server machine. The two databases are identical in terms of structure and data.
I had various SP's and UDF's which needed to compare local variables to NULL. I did this using IF (@local != NULL) which worked fine on the development database, but when these SP's/UDF's were deployed onto the live database they no longer worked. After much cursing and pulling-out-of-hair, I found that changing the comparison code to IF (@local IS NOT NULL) fixed the issue.
But why? How can two databases on the same physical server treat comparisons so differently. Is there a setting somewhere that controls how NULL values are handled?
September 4, 2003 at 3:59 am
Have you checked if both db's have the same setting for 'ANSI NULL default'?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 4, 2003 at 4:07 am
Dear,
Yes, indeed, there is a default setting at database level (ANSI_NULLS, not to be confused with ANSI_NULL_DEFAULT)
If you do :
sp_dboption @dbname = 'your_db1', @optname = 'ANSI_NULLS'
sp_dboption @dbname = 'your_db2', @optname = 'ANSI_NULLS'
you should get different values.
HTH.
CVM.
September 4, 2003 at 4:10 am
Yes, all the settings on the 'Options' tab of the database 'Properties' dialog box are identical.
ANSI NULL default is turned off... although I should say that I have no idea what this setting does!
September 4, 2003 at 4:15 am
I've tried running 'sp_dboption', but both databases have the value 'off'.
Thanks for the idea though.
September 4, 2003 at 4:57 am
quote:
I've tried running 'sp_dboption', but both databases have the value 'off'.
so I think you should post the statement you fire at both db's that gives you different results
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 4, 2003 at 5:02 am
If you have not already done so take a look here at the following article.
http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp
But there are factors such as Connection explicitly turning off or that the server itself has the propety for new connections turned off on one but not the other (open EM, right click the server, choose properties, choose the connections tab, and look at the default connection settings of each server). The server connection settings will in many cases override the DB level settings.
September 4, 2003 at 5:05 am
Below is one example where this occured. This is the fixed version that actually returns the same results for both databases, but replace all the 'IS NOT NULL' statements with '!=NULL' and you get different results...
CREATE FUNCTION UDF_Requirement_Status (@RequirementId AS int)
RETURNS varchar(20) AS
BEGIN
DECLARE @SignoffId int
DECLARE @DateSignedOff datetime
DECLARE @DateArchived datetime
DECLARE @DateReplaced datetime
DECLARE @ReplacedById int
DECLARE @Result varchar (20)
SELECT
@SignoffId=SignoffId,
@DateSignedOff=DateSignedOff,
@DateArchived=DateArchived,
@DateReplaced=DateReplaced,
@ReplacedById=ReplacedById
FROM tbl_Requirement
WHERE RequirementId = @RequirementId
IF (@DateArchived IS NOT NULL)
SET @Result='Archived'
ELSE IF (@DateReplaced IS NOT NULL AND @ReplacedById != 0)
SET @Result='Replaced'
ELSE IF (@DateReplaced IS NOT NULL AND @ReplacedById = 0)
SET @Result='Retracted'
ELSE IF (@DateSignedOff IS NOT NULL)
SET @Result='Live'
ELSE IF (@SignoffId = -1)
SET @Result='Awaiting Signoff'
ELSE
SET @Result='Draft'
RETURN @Result
END
September 4, 2003 at 7:14 am
Hi McDavis,
quote:
If you have not already done so take a look here at the following article.http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp
But there are factors such as Connection explicitly turning off or that the server itself has the propety for new connections turned off on one but not the other (open EM, right click the server, choose properties, choose the connections tab, and look at the default connection settings of each server). The server connection settings will in many cases override the DB level settings.
did this help you?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 4, 2003 at 7:30 am
It was useful background info, but I don't think it highlighted the problem in this instance.
The article mentioned the difference between variables that had only been DECLAREd and those that had been given an explicit value using SET... but in this instance all of my local variables had been explicitly set from my SELECT statement.
It then mentioned the differences encountered when SET ANSI_NULLS was run, but I've already determined that both databases have the ANSI_NULLS setting turned off, so doing a straight = or != should work fine.
Finally, as both databases are on the same server (and use the same ASP.NET connection string - albeit using a different name) it can't be put down to differences in connection either.
It doesn't really matter, I've managed to work around the issue by using NOT IS NULL, I was just curious of where the difference could have been.
All the responses have been most helpful anyway, Thanks.
September 4, 2003 at 8:34 am
If I think of anything else I will pass along. However what si the difference in the connection and are you doing a straight connection or thru ODBC perchance?
September 4, 2003 at 8:45 am
The connection is being made through the use of the ADO.NET Managed Provider for SQL Server, and is using the following connection strings:
dev:
server=Pisang;Trusted_Connection=false;database=eP_Development;User Id=xxxx;Password=xxxx;
live:
server=Pisang;Trusted_Connection=false;database=eP;User Id=xxxx;Password=xxxx;
So apart from the database name, the connections are identical. It's also worth noting that I get the same odd behavior when connecting to the databases directly via Enterprise Manager.
September 4, 2003 at 11:06 am
Just a question-what VERSION are each of the SQL Servers???? Could it be an issue with different service packs?
-SQLBill
September 4, 2003 at 12:08 pm
quote:
Just a question-what VERSION are each of the SQL Servers???? Could it be an issue with different service packs?-SQLBill
Don't think it is that since he wrote
quote:
Finally, as both databases are on the same server (and use the same ASP.NET connection string - albeit using a different name) it can't be put down to differences in connection either.
Unless they are different instances on the same server machine.
September 4, 2003 at 12:35 pm
Antares,
I agree that it's probably not the problem. But I had trouble with my databases until I realized that loading SP3 on one computer did not update ALL instances on that computer. Especially since one instance is a virtual server.
-SQLBill
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply