更改ANSI_NULLS在数据库设置为所有存储过程
我们有一些问题ANSI_NULLS设置和计算列和我们有一吨的有更改ANSI_NULLS在数据库设置为所有存储过程
SET ANSI_NULLS OFF
存储过程,我们希望所有这些改变
SET ANSI_NULLS ON
是否有一个简单的方法来做到这一点还是必须提取所有的SP到一个脚本,改变它,再运行它删除并重新创建所有水疗
您必须脚本编写所有过程,然后使用ANSI_NULLS重新创建它们。
如果我有很多要做,我可能会添加一个函数到我的客户端应用程序。
伪代码:
procedure FixAllStoredProcedureAnsiNullness(connection)
{
Strings spNames = GetStoredProcedureNames(connection);
foreach spName in spNames
{
String sql = GetStoredProcedureSQL(connection, spName);
//turn on option for remainder of connection
connection.ExecuteNoRecords("SET ANSI_NULLS ON");
BeginTransaction(connection);
try
connection.ExecuteNoRecords("DROP PROCEDURE "+spName);
connection.ExecuteNoRecords(sql);
CommitTranasction(connection);
except
RollbackTransaction(connection);
raise;
end;
}
}
我对如何让一个存储过程的SQL编程上SQL Server: How to generate object scripts without DMO/SMO?
代码,但通常我只会用企业管理,开始于顶部存储过程列表:
- 返回
- 按Ctrl +首页
- 按Ctrl +V
- 单击确定
- 向下
- 转到1
凡我剪贴板包含:
SET ANSI_NULLS ON
GO
如果你不幸被SSMS卡住了,那么你就是那个POS,IIRC的SOL。 TWSS。
到目前为止电子最简单的方法是脚本s'procs,运行查找和替换命令,然后再次运行proc定义。
当你有数百个存储过程时,这不是最简单的方法。 选择OBJECT_NAME(OBJECT_ID)的名称,定义代码 从sys.all_sql_modules 其中OBJECTPROPERTY([OBJECT_ID],“IsProcedure: – Suncat2000 2017-06-02 14:29:13
我们使用的解决方案是张贴伊恩现在我们有一个自动化的程序来解决这个问题。
这里是我们用来重建所有从数据库中SP的最终代码:
public static class AnsiNullsManager
{
public static void ReCreateAllStoredProcedures(SqlConnection connection, bool ansiNullsOn)
{
var sql =
@"select object_name(sys.all_sql_modules.object_id) as Name, definition as Code
from sys.all_sql_modules inner join sys.objects ON
sys.all_sql_modules.object_id = sys.objects.object_id
where objectproperty(sys.all_sql_modules.object_id, 'IsProcedure') = 1 AND is_ms_shipped = 0 and uses_ansi_nulls = " +
(ansiNullsOn ? "0" : "1") +
"ORDER BY Name ";
if (connection.State == ConnectionState.Closed)
connection.Open();
var sps = new List<SpObject>();
var cmd = connection.CreateCommand();
cmd.CommandText = sql;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
sps.Add(new SpObject(reader.GetString(0), reader.GetString(1)));
}
}
var cmdSetAnsiNulls = connection.CreateCommand();
cmdSetAnsiNulls.CommandText = "SET ANSI_NULLS " + (ansiNullsOn ? "ON" : "OFF") + ";";
cmdSetAnsiNulls.ExecuteNonQuery();
foreach (var sp in sps)
{
var trans = connection.BeginTransaction();
try
{
var cmdDrop = connection.CreateCommand();
cmdDrop.CommandText = "DROP PROCEDURE " + sp.Name;
cmdDrop.Transaction = trans;
cmdDrop.ExecuteNonQuery();
var cmdReCreate = connection.CreateCommand();
cmdReCreate.CommandText = sp.Code;
cmdReCreate.Transaction = trans;
cmdReCreate.ExecuteNonQuery();
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
throw;
}
}
}
private class SpObject
{
public SpObject(string name, string code)
{
Name = name;
Code = code;
}
public string Name { get; private set; }
public string Code { get; private set; }
}
}
只是想抛出一个警告在那里。我无法想象为什么你的ansi_nulls会为你所有的SP启动,但是如果它们中的任何一个都以任何方式将NULL与NULL进行比较(并且可能会有很多不同的方式),当你改变时你的结果会不同该设置。我建议在安全的环境中进行一些严格的回归测试。
@TG:感谢您的警告,所有这些存储过程都是自动生成的CRUD操作。我们拥有所有SP,因为我们在模板sql中保留了SET ANSI_NULLS OFF。程序非常简单,我们不会涉及这个问题。 – MarcosMeli 2010-01-29 05:25:43
我明白了 - 这很有道理。如果您的代码生成器具有该ansi_nulls设置,那么当然它们都会以这种方式结束。那么可能任何手动创建的SP都没有关闭ansi_nulls。如果任何生成的SP在可空列上加入JOIN,我想只有潜在的“gottcha”才会出现。但事实并非如此。继续 :) – 2010-01-29 13:32:44
由于一吨,我会用这个SQL来获取所有失败的SP及其代码走那条路')= 1 and uses_ansi_nulls = 0 – MarcosMeli 2010-01-27 14:37:59
我刚刚添加了C#代码来重新创建所有sps :)我们无法手动完成,因为我们有8个数据库,每个数据库都有近500个,所以感谢您指出如何自动化过程。 – MarcosMeli 2010-01-27 15:24:53
编辑的伪代码:在连接上设置ansi_nulls选项一次 - 因为批处理中必须单独使用“CREATE PROC”。 – 2010-01-27 18:51:34