ANSI_NULLS on stored procedures from VS2005 DBPro project
ajryan
Posts: 12
Hi,
We have a VS2005 Edition for Database Professionals project containing several stored procedures. We build a database snapshot from the project that is used at deployment. The issue we're having is that ANSI_NULLS is being set ON for all stored procedures.
In the DBPro project the ANSI_NULLS property is set False for each stored procedure. We are not using the IgnoreQuotedIdentifiersAndAnsiNullSettings - the only option used is IgnoreCollactions. When I examine the script generated by Work.BuildFromDifferences, the only ANSI_NULLS setting is at the top of the entire script.
I think I may know what the issue is -- DBPro does not put ANSI_NULLS settings at the top of the stored procedure create scripts - their first lines all read CREATE PROC... The ANSI_NULLS setting is in the DBProj project file.
Does anyone have a recommendation for the best way to disable ANSI_NULLS for these stored procedures?
Should SQL Compare support the AnsiNulls property of the stored procs that is in the .DBProj file?
Thanks,
Aidan
We have a VS2005 Edition for Database Professionals project containing several stored procedures. We build a database snapshot from the project that is used at deployment. The issue we're having is that ANSI_NULLS is being set ON for all stored procedures.
In the DBPro project the ANSI_NULLS property is set False for each stored procedure. We are not using the IgnoreQuotedIdentifiersAndAnsiNullSettings - the only option used is IgnoreCollactions. When I examine the script generated by Work.BuildFromDifferences, the only ANSI_NULLS setting is at the top of the entire script.
I think I may know what the issue is -- DBPro does not put ANSI_NULLS settings at the top of the stored procedure create scripts - their first lines all read CREATE PROC... The ANSI_NULLS setting is in the DBProj project file.
Does anyone have a recommendation for the best way to disable ANSI_NULLS for these stored procedures?
Should SQL Compare support the AnsiNulls property of the stored procs that is in the .DBProj file?
Thanks,
Aidan
Comments
Just wanted to share the approach we worked out in case anyone else runs into a similar issue -- instead of registering the database from the DBPRo scripts folder, we are having the DBPro project deploy itself and then registering the live deployed database. That way the MS tool ensures that the ANSI_NULLS settings are enacted on the deployed procs, making the property visible to SQL Compare.
Aidan