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
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.
Issue with ANSI_NULLS
Posted - 01/21/2013 : 01:01:07
