Измените настройки ANSI_NULLS для всех Хранимых процедур в Базе данных
У нас есть некоторые проблемы с установкой ANSI_NULLS и вычисляемыми столбцами, и у нас есть тонна хранимых процедур, которые имеют
SET ANSI_NULLS OFF
Мы хотим изменить их всех на
SET ANSI_NULLS ON
Есть ли простой способ сделать это, или должен я извлекать весь SPS к сценарию, изменять его и выполнять его снова, чтобы отбросить и воссоздать весь СПА
4 ответа
Вы должны скриптировать все процедуры, и повторно создать их с помощью ANSI_NALLS.
Если у меня было много , чтобы сделать, я могу добавить функцию в мое клиентское приложение.
псевдокод:
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: как генерировать сценарии объектов без DMO / SMO?
, но обычно я просто буду использовать Enterprise Manager , начиная с вершины хранимого списка процедур:
- return
- Ctrl + Главная
- Ctrl + v
- Нажмите ОК
- вниз
- GOTO 1
, где мой буфер обмена содержит:
SET ANSI_NULLS ON
GO
, если вы несчастны достаточно, чтобы застрять с SSMS, то вы зольете с этим POS, IIRC. TWSS.
Решение, которое мы используем, было опубликовано IAN , и теперь у нас есть автоматическая процедура для решения проблемы.
Вот окончательный код, который мы используем для воссоздания всех SPS из базы данных:
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; }
}
}
, безусловно, самый простой способ состоит в том, чтобы скрипт S'Procs, запустить и заменить команду, затем снова запустите определения PREC.
Просто хотел, чтобы здесь было предупреждение. Я не могу себе представить, почему вы установили ansi_nulls для ВСЕХ ваших SP, но если какой-либо из них каким-либо образом рассчитывал на сравнения с NULL (и может быть много разных вариантов) ваши результаты будут отличаться, когда вы измените этот параметр. Я рекомендую провести тщательное регрессионное тестирование в безопасной среде.