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.
| 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 records2) Copy the entire script onto a query window and executed like normal query. which returns few recordsHere 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 valueson subscriber select * from dbo.Getthedetails is resulting invalid valuesAny idea what might be going wrong?How can I resolve this ANSI_NULLS issueThanks 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
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. |
|
|
|
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. |
|
|
|
|
|
|
Issue with ANSI_NULLS