0

We have around 1000 stored procedures with few having SET ANSI_NULLS ON and some OFF, depending on the scope.

Using VS2019 and importing all procedures into a new project, the ANSI setting is not captured.

How can I import all procedures with the setting?

Example procedure begin as :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE .....
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ak11
  • 1
  • 2
    FYI `SET ANSI_NULLS OFF` is to be deprecated, so why not just fix all your procedures to respect ANSI NULLs? Then you don't get a nasty surprise when it's removed. – Thom A Mar 12 '22 at 15:36
  • Unfortunately we have it right now and need a way tocapture them for version control, else we will have problems when deployments happen without it. – Ak11 Mar 12 '22 at 16:08

1 Answers1

1

The ANSI_NULLS and QUOTED_IDENTIFIER of imported objects are captured in the SQL Server database project, but not as T-SQL source code.

Select the project item in solution explorer to see the properties. The setting will show ON, OFF, or Project Default (if inherited from the database project setting).

Below is an example screenshot with QUOTED_IDENTIFIER OFF in a project with both options ON by default:

Stored Procedure Properties

I suggest you use ANSI_NULLS ON and QUOTED_IDENTIFIER ON for compatibility with SQL Server features that require the options ON (e.g. filtered indexes) and for better ISO SQL compatibility.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • thanks for the answer, the reason I ask this is we are looking to version control on github and to push all scripts there,we are importing it now. If we are not able to capture ANSI and QUOTED_IDENTIFIER on each proc in some way, it wont be there on github and eventual branches will miss and cause issues post deployment. please suggest some option...we have 1000 procs per db and 100+ dbs. – Ak11 Mar 12 '22 at 16:05
  • @AkshayPatil, when you publish the database project, the script will include the necessary SET options to honor the settings per the properties. You won't lose them. – Dan Guzman Mar 12 '22 at 16:08