Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Issue with ANSI_NULLS

Author  Topic 

shobhaaradhya
Starting Member

37 Posts

Posted - 2013-01-21 : 01:01:07
Hi,

I have a function ex : Getthedetails. I am executing this function in 2 ways.

1) select * from dbo.Getthedetails

which returns few records

2) Copy the entire script onto a query window and executed like normal query.

which returns few records

Here one of the column from the above recordset is resulting from first execution is different than the second execution though its executing same set of queries.

But when I set ANSI_NULLS off on the second execution the values are matching with the first execution.

Have one more issue also. After publishing the database, on one of the subscriber db the same function is resulting the similar issue. ie

on publisher select * from dbo.Getthedetails is resulting valid values
on subscriber select * from dbo.Getthedetails is resulting invalid values


Any idea what might be going wrong?

How can I resolve this ANSI_NULLS issue

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-21 : 01:18:24
is function code containing an explicit ANSI_NULLS set statement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

shobhaaradhya
Starting Member

37 Posts

Posted - 2013-01-21 : 04:41:12
No the function code does not contain and explicit ANSI_NULLS set statement.

Can I alter the function to set ANSI_NULLS OFF.

I am using sql server 2000.

Can I remove the existing function from the publisher and recreate the function. without dropping the subscriber.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-21 : 07:34:11
When you create a create a function, by default it sets ANSI_NULLS to ON (or OFF) depending on the database setting. If you script the function you will see that the script has an explicit statement setting it.

So if your database is set to ANSI_NULLS ON (which is the default) even if you had not explicitly set it so during creating of your function, it would be created with ANSI_NULLS ON. You can script the function to alter, change the setting to OFF and run it to set ANSI_NULLS to OFF.

ANSI_NULLS OFF is a deprecated feature, so my preference would be to fix the code so that it works with ANSI_NULLS ON.
Go to Top of Page
   

- Advertisement -