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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Issue with ANSI_NULLS
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

37 Posts

Posted - 01/21/2013 :  01:01:07  Show Profile  Reply with Quote

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

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 01/21/2013 :  01:18:24  Show Profile  Reply with Quote
is function code containing an explicit ANSI_NULLS set statement?

SQL Server MVP

Go to Top of Page

Starting Member

37 Posts

Posted - 01/21/2013 :  04:41:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 01/21/2013 :  07:34:11  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000