SQL Server 2000 - 如何恢复连接级别设置的先前状态
我在我的 T-SQL 代码库中使用 DBDeploy.NET 进行变更控制管理。 DBDeploy 的工作原理是由开发人员维护一组编号的更改脚本。当触发部署（通过 NAnt）时，DBDeploy 会查看更改日志表并确定需要执行哪些补丁以使实例保持最新状态。
我遇到的问题是设置创建索引视图所需的设置。 QUOTED_IDENTIFIER、ANSI_NULLS 和 ARITHABORT 都需要打开。是的，我可以轻松地将这些 SET 语句放在创建/更改索引视图的更改脚本的顶部。但这些设置是连接级别的。如果稍后我从头开始构建一个新实例怎么办？当我在新实例上运行 DBDeploy 时，这些设置将渗透到所有后续更改脚本，因为所有更改脚本都有效地连接到最终的 SQL 脚本中，以便在单个连接上执行。更糟糕的是像 QUOTED_IDENTIFIER 这样的解析时选项，它也将应用于之前的所有更改脚本。那么：
- 我使用的是 SQL Server 2000。我对连接级别设置的解释是否正确？即，使用 GO 将脚本分成批次不会限制这些 SET 选项的范围。那么以后的版本呢，连接级别设置已重命名为批处理级别？
- 有没有办法取消SET？据我了解，连接级设置是三元的——即ON、OFF和默认值，其中默认值是根据SQL语句的内容、实例设置、数据库设置和持久用户设置来解释的。如果我将某项设置为“开”，我无法撤消它，只需将其设置为“关”即可撤消它，因为它会屏蔽默认值（如果这是之前的设置）。
- 有没有办法在设置之前保存连接级别设置的状态，以便我可以在设置后手动恢复它？
替代方案很糟糕：
- 我可以在动态 SQL 中包装索引视图的每个创建/更改语句 - SS2K 上有 4000/8000 个字符的限制。这将很好地限制 SET 语句的范围。
- 我可以制定一项政策，修复要在项目级别使用的 SET 选项，并要求所有开发人员将这些 SET 选项放置在每个更改脚本的顶部以强制执行它，因为直到部署时间才确切知道哪些更改脚本将被应用。
- 我可以修补 DBDeploy 本身，以便始终为每个更改脚本使用新连接，但这需要重新设计它处理撤消更改脚本的方式。
那么可以做什么，我应该做什么？
I'm using DBDeploy.NET for change control management in my T-SQL codebase. DBDeploy works by the developer maintaining a numbered set of change scripts. When a deploy is triggered (via NAnt), DBDeploy looks at a changelog table and determines which patches need to be executed to bring the instance up to date.
The issue I have is with setting the required settings necessary to create an indexed view. QUOTED_IDENTIFIER, ANSI_NULLS, and ARITHABORT all need to be on. Yes, I can easily put these SET statements at the top of the change script that creates/alters the indexed view. But these settings are connection level. What if later I'm building a new instance from scratch? When I run DBDeploy on the new instance, these settings will bleed through to all subsequent change scripts, since all change scripts are effectively concatenated into a final SQL script to be executed on a single connection. What's worse are parse-time options like QUOTED_IDENTIFIER, which would be applied to all change scripts prior as well. So:
- I'm on SQL Server 2000. Is my interpretation of connection-level settings correct? I.e. using GO to break the script into batches does nothing to limit the scope of these SET options. What about later versions, where connection-level settings have been renamed batch-level?
- Is there any way to unSET the SET? As I understand it, connection-level settings are trinary - i.e. ON, OFF, and default, where default is interpreted based on the content of the SQL statement, instance settings, database settings, and persisted user settings. If I SET something to ON, I can't undo it simply undo it by setting it to OFF, because it would mask default, if that's what the setting was before.
- Is there any way to save the state of the connection-level setting before setting it, so I can manually restore it after?
The alternatives suck:
- I can wrap each create/alter statement for indexed views in dynamic SQL - with it's 4000/8000 char limitation on SS2K. That would limit the scope of SET statements quite well.
- I can institute a policy of fixing the SET options to be used at the project level, and requiring all devs to place those SET options at the top of each change script to enforce it, since there's no telling until deploy time precisely which change scripts will be applied.
- I can patch DBDeploy itself to always use a new connection for each change script, but that would require redesigning the way it handles undoing change scripts.
So what can be done, and what should I do?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不幸的是，我不相信有办法获取连接中当前的 SET 状态。
我不使用 DBDeploy.NET，但这听起来确实像是该工具的限制。 DBDeploy.NET 应在项目元数据（如 Visual Studio DB 项目中）中定义 SET 谓词对于随后部署的任何数据库的默认值。
为了避免脚本之间的“出血”，它应该在连接到最终脚本的每个脚本之前使用这些项目级默认值自动添加 SET 语句。
采用这种确定性方法意味着开发人员可以清楚地了解脚本执行时每个 SET 将处于什么状态。否则，脚本将受到 SQL 客户端连接或服务器实例中存在的各种默认值的影响。
Unfortunately I don't believe there's a way to source the current SET status in your connection.
I don't use DBDeploy.NET, but this does sound distinctly like a limitation with the tool. DBDeploy.NET should define within the project metadata (like in Visual Studio DB projects) what the SET predicate defaults should be for any database that it subsequently deploys.
To avoid "bleeding" between scripts, it should automatically add SET statements using those project-level defaults before each script it concatenates into the final script.
Having this kind of deterministic approach means the developers can clear about what state each of these SETs will be in when their script executes. Otherwise, the scripts will be subject to the whims of the various defaults that are present in the SQL client connection or server instance.