Для создания серверных приложений, основанных на SQL Server, необходимо программировать бизнес-логику,
используя язык запросов T-SQL. В силу его ограниченной функциональности, часто создание такой логики отнимает
много времени и усилий на отладку. Хотя уже давно в MS SQL при программировании хранимых процедур и
различных пользовательских функций есть возможность применять языки более высокого уровня. Я предпочитаю
делать это
На этой странице вы найдете несколько полезных примеров с пошаговой инструкцией, которые продемонстрируют использование этого инструмента.
Очень часто для выполнения политики безопасности команда xp_cmdshell отключена. Kак быть, если xp_cmdshell реально нужна приложению? Если разобраться детально, то все-таки нужна не xp_cmdshell, а возможность вызова из SQL Server команды ОС. Предлагаю вам вариант CLR-ной процедуры, которая будет делать ровно то же самое.
В Visual Studio создаем проект обычной библиотеки классов и пишем (язык c#):
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Diagnostics; namespace IT_INF_ClassLibraryCmd { public class Class1 { //Тег, определяющий, что данная функция возвращает таблицу [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "txt nvarchar(4000)")] public static System.Collections.IEnumerable FCmd(SqlString commandToRun, SqlString args) { Process p = new Process(); p.StartInfo.UseShellExecute = false; p.StartInfo.RedirectStandardOutput = true; p.StartInfo.FileName = commandToRun.Value; p.StartInfo.Arguments = args.Value; p.Start(); string value = p.StandardOutput.ReadToEnd(); string[] spl = value.Split('\n'); p.WaitForExit(); return spl.ToArray(); } //Функция заполнения таблицы public static void FillRow(Object obj, out string stringElement) { stringElement = obj.ToString();//Возвращает в таблицу строку } } }
Стандартной процедурой компилируем и получаем IT_INF_ClassLibraryCmd.dll
Размещаем dll-файл на SQL-сервере в любую папку, например,
Для того, чтобы пользователь мог зарегистрировать сборку, сначала добавим права на стороне SQL-сервера. В качестве примера взята база данных IT_INF:
USE [master] GO ALTER DATABASE IT_INF SET TRUSTWORTHY ON GO
Так как используется функция Split('\n'), то необходимо дополнительно зарегистрировать на MS SQL сервере системную сборку:
CREATE ASSEMBLY [System.Core] AUTHORIZATION [dbo] FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll' WITH PERMISSION_SET = UNSAFE GO
Теперь регистрируем нашу сборку на стороне SQL-сервера:
USE IT_INF GO create assembly IT_INF_CmdAssembly from 'C:\Program Files\Microsoft SQL Server\90\Tools\Bin\IT_INF\IT_INF_ClassLibraryCmd.dll' with permission_set = unsafe GO
Осталось только создать функцию, которая будет работать с методом класса. Выполняем на стороне SQL-сервера:
USE [IT_INF] GO CREATE FUNCTION FCmd ( @cmd nvarchar(max), @args nvarchar(max) ) RETURNS table (txt nvarchar(4000)) AS EXTERNAL NAME IT_INF_CmdAssembly.[IT_INF_ClassLibraryCmd.Class1].FCmd
Примеры использоваиня:
/* Стандартный вызов */ USE [IT_INF] GO select * FROM dbo.FCmd('ping', '-n 2 192.168.150.8') GO /* или с сохранением результата во временную таблицу */ USE [IT_INF] GO if object_id('tempdb..#t') is not null drop table #t create table #t (txt varchar(4000)) insert into #t select * FROM dbo.FCmd('ping', '-n 2 192.168.150.8') select * from #t
Инструмент T-SQL имеет достаточно функций, чтобы работать со строками. Но на мой взгляд, ему не хватает работы с регулярными выражениями. Об использовании регулярных выражений можно почитать на нашем сайте http://www.tool-bar.ru/regex.html. Предлагаю вам вариант CLR-ной процедуры, которая расширит возможность работы со строками на стороне MS SQL сервера.
В Visual Studio создаем проект обычной библиотеки классов и пишем (язык c#):
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Data.SqlTypes; namespace FnRegEx { public class Class1 { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString FRegExReplace(String inputSting, String pattern, String replacement) { try { return Regex.Replace(inputSting, pattern, replacement, RegexOptions.IgnoreCase); } catch { return ""; } } //********************************************************************** public class RegExMatchResult { // Содержит результаты поиска public RegExMatchResult(int id, string substr, int firstIndex, int length) { this.id = id; this.substr = substr; this.firstIndex = firstIndex; this.length = length; } public int id; // Порядковый номер в наборе public string substr; // найденная подстрока public int firstIndex; // индекс вхождения подстроки в строке public int length; // Длина найденной подстроки } //Тег, определяющий, что данная функция возвращает таблицу [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRowMatchResult", TableDefinition = "id int, substr nvarchar(4000), FirstIndex int, Length int")] public static System.Collections.IEnumerable FRegExFind(String inputSting, String pattern) { int i = 0; ArrayList resultCollection = new ArrayList(); Regex rgx = new Regex(pattern); foreach (Match match in rgx.Matches(inputSting)) { // Console.WriteLine("Found '{0}' at position {1}", match.Value, match.Index); resultCollection.Add(new RegExMatchResult(i, match.Value, match.Index, match.Value.Length)); i++; } return resultCollection; } //Функция заполнения таблицы public static void FillRowMatchResult(Object obj, out int id, out string substr, out int firstIndex, out int length) { //Возвращает в таблицу строку RegExMatchResult M = (RegExMatchResult)obj; id = M.id; substr = M.substr; firstIndex = M.firstIndex; length = M.length; } } }
Стандартной процедурой компилируем и получаем IT_INF_ClassLibraryCmd.dll
Размещаем dll-файл на SQL-сервере в любую папку, например,
Для того, чтобы пользователь мог зарегистрировать сборку, сначала добавим права на стороне SQL-сервера. В качестве примера взята база данных IT_INF:
USE [master] GO ALTER DATABASE IT_INF SET TRUSTWORTHY ON GO
Теперь регистрируем нашу сборку на стороне SQL-сервера:
USE IT_INF GO create assembly IT_INF_FnRegExAssembly from 'C:\Program Files\Microsoft SQL Server\90\Tools\Bin\IT_INF\FnRegEx.dll' with permission_set = unsafe GO
Регистрируем две функции на SQL Server. Отчилие в том, что вторая функция будет возвращать набор данных (Record Set)
CREATE FUNCTION FnRegExReplace ( @inputSting nvarchar(max), @pattern nvarchar(max), @replacement nvarchar(max) ) RETURNS nvarchar(4000) AS EXTERNAL NAME IT_INF_FnRegExAssembly.[FnRegEx.Class1].FRegExReplace ---------- CREATE FUNCTION FRegExFind ( @inputSting nvarchar(max), @pattern nvarchar(max) ) RETURNS table (id int, substr nvarchar(4000), FirstIndex int, Length int) AS EXTERNAL NAME IT_INF_FnRegExAssembly.[FnRegEx.Class1].FRegExFind
Примеры использоваиня:
DECLARE @str VarChar(1000), @pat varChar(1000) SET @str = 'IF.011_IF.084_0904.1111.20131021.xml IF.011_IF.084_0904.2222.20131021.xml' /* Строка с данными */ SET @pat = 'IF\.084.+?xml' /* регулярное выражение */ -- 1 select @str as s ,dbo.FnRegExReplace (@str, @pat, '@') as res -- 2 select @str as s, * from dbo.FRegExFind(@str, @pat)
Рекомендации
Если позволяет количество оперативной памяти, установленной на сервере, то рекомендую увеличить объем
памяти, отводимый внутри процесса SQL-сервера, но за пределами пула памяти самого SQL-сервера.
Установить параметр -g на 512.
Например,
Сcылки на статьи:
Ошибки
Нет ничего в мире совершенного. Поэтому иногда на SQL-сервере мы получаем сообщение. Эта ошибка вызывается какой-то странностью в работе кеша ASP.NET:
Невозможно загрузить файл или сборку "System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" или один из зависимых от них компонентов. Исключение из HRESULT: 0x80FC02BD
Как решение можно перегрузить SQL-сервер или просто очистить кеш — удалить временные файлы ASP.NET из папок: