SMO. CaptureSQL

Коль скоро речь зашла о о скриптовании в SMO, стоит отметить еще одну возможность на эту тему под названием CaptureSQL.

Однажды я нарвался на ошибку при создании в SMO CLRной табличной функции. Это приблизительно функция Dir из поста "Как перелOжить файловую папку в базу"\Скрипт 6.

Рис.1

 

Обычно Inner Exception вносит ясность, информативно сообщая об истинных причинах недовольства. В данном случае UDF просит установить ей свойство TableVariableName. Я, помнится, еще удивился. Табличная переменная имеет смысл для T-SQLной табличной функции:

create function tvf() returns @t table (fld int) as begin insert @t values (1), (2), (3) return end

Здесь мы явно указываем, что функция будет CLRной: f.ImplementationType = Microsoft.SqlServer.Management.Smo.ImplementationType.SqlClr; а CLRной она, вроде как, без надобности. Ну ладно, раз просит, ей виднее. Недолго думая, забабахал ей строчку

f.TableVariableName = "@t";

Скрипт 1

На, подавись ты. Она и подавилась:

Рис.2

Причем, пребывая в состоянии подавленности, она перестала выдавать осмысленные сообщения об ошибках даже в Inner Exception. Наиболее разумные вещи - это An exception occurred while executing a Transact-SQL statement or batch и Incorrect syntax near the keyword 'EXTERNAL'. Хотелось бы понять, что именно в синтаксисе ей не по нутру.

Читатели, которые рискнули потратить время, пролистав пост "Программно сгенерить трассу профайлера. Ч. 1, 2", естественно, воспользуются для этой цели профайлером. Событие User Error Message выводит, по сути, закладку Messages в SSMS. Событие Exception показывает стандартную информацию о номере и строгости ошибки. Событие SQL:BatchStarting содержит вызвавший ее T-SQLный скрипт:

Рис.3

Однако существует увидеть образовавшийся в результате SQLный скрипт средствами SMO, не выходя за пределы Visual Studio. Для этого в код на Рис.2 достаточно добавить две строчки: в начале

srv.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;

Скрипт 2

которая означает, что все SQL-команды, которые SMO будет генерить в результате наших над ним измывательств, он не будет отправлять на сервер, а будет собирать их у себя в виде коллекции строк, которые затем можно посмотреть:

foreach (string s in srv.ConnectionContext.CapturedSql.Text) Debug.WriteLine(s);

Скрипт 3

Рис.4

В случае длинного SQL-скрипта его можно перенести в SSMS и там пройти построчно дебаггером. В данном случае очевидно, что ошибка синтаксиса происходит из-за пресловутой табличной переменной @t, которую все-таки не надо было указывать в случае CLRной функции. Стоит заменить в Скрипте 1 "@t" на пустую строку, все начинает прекрасно работать:

using System;

using System.Collections;

using System.Diagnostics;

using SMO = Microsoft.SqlServer.Management.Smo; //C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

//Также требует references на

//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll

//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll

using System.IO;

using System.Data;

class Program

{

    static void Main(string[] args)

    {

        CreateFunctionDir();

    }

    public static void CreateFunctionDir()

    {

        SMO.Server srv = new Microsoft.SqlServer.Management.Smo.Server();

        srv.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteAndCaptureSql;

        SMO.Database db = srv.Databases["TestFS"];

        SMO.SqlAssembly asm = db.Assemblies["MyAssembly"];

        SMO.UserDefinedFunction f = new Microsoft.SqlServer.Management.Smo.UserDefinedFunction();

        f.Parent = db; f.Name = "Dir";

        f.TextMode = false;

        f.ImplementationType = Microsoft.SqlServer.Management.Smo.ImplementationType.SqlClr;

        f.AssemblyName = asm.Name;

        f.ClassName = "FileSystem";

    f.MethodName = "Dir_InitMethod";

        SMO.UserDefinedFunctionParameter p1 = new Microsoft.SqlServer.Management.Smo.UserDefinedFunctionParameter(f, "@folder", SMO.DataType.NVarChar(266));

        f.Parameters.Add(p1);

        p1 = new Microsoft.SqlServer.Management.Smo.UserDefinedFunctionParameter(f, "@shallowTraversal", SMO.DataType.Bit);

        f.Parameters.Add(p1);

        f.FunctionType = Microsoft.SqlServer.Management.Smo.UserDefinedFunctionType.Table;

        SMO.Column c = new Microsoft.SqlServer.Management.Smo.Column(f, "ID", SMO.DataType.HierarchyId);

        f.Columns.Add(c);

        c = new Microsoft.SqlServer.Management.Smo.Column(f, "FullName", SMO.DataType.NVarChar(266));

        f.Columns.Add(c);

        c = new Microsoft.SqlServer.Management.Smo.Column(f, "DateCreated", SMO.DataType.DateTime2(3));

        f.Columns.Add(c);

        c = new Microsoft.SqlServer.Management.Smo.Column(f, "DateModified", SMO.DataType.DateTime2(3));

        f.Columns.Add(c);

        c = new Microsoft.SqlServer.Management.Smo.Column(f, "LastAccessed", SMO.DataType.DateTime2(3));

        f.Columns.Add(c);

        c = new Microsoft.SqlServer.Management.Smo.Column(f, "Properties", SMO.DataType.Xml(""));

        f.Columns.Add(c);

        c = new Microsoft.SqlServer.Management.Smo.Column(f, "Size", SMO.DataType.BigInt);

        f.Columns.Add(c);

        c = new Microsoft.SqlServer.Management.Smo.Column(f, "IsDir", SMO.DataType.Bit);

        f.Columns.Add(c);

        f.TableVariableName = "";

        f.Create();

     foreach (string s in srv.ConnectionContext.CapturedSql.Text) Debug.WriteLine(s);

    }

}

Скрипт 4

Это была краткая иллюстрация возможности SMO по захвату SQL. Я пытался измыслить практические сценарии ее использования и не преуспел. Профайлер, как мы видели, позволяет делать все то же самое, плюс он обладает более широким спектром возможностей, т.к. не ограничен одной текущей сессией и T-SQLными событиями. Можно представить ситуацию, когда на предприятии нет выделенного DBA, и администратору приходится отвечать за все: за сервера баз данных, за почтовые сервера, за AD и т.д. Он использует PowerShell как универсальный рабочий инструмент автоматизации своих действий, из которого обращается к каждому из серверов в соответствии с его объектной моделью или WMI и иными административными интерфейсами. Например, SMO в случае SQL Server. Даже если предположить, что ему вдруг понадобилось превратить SMOшный скрипт в SQL, почему не использовать профайлер для перехвата действий, которые вызывает SMO на стороне сервера? Права ALTER TRACE у него по-любому должны иметься. Может быть, отталкиваться от обратной ситуации, когда разработчик не обладает сколь-либо серьезными административными привилегиями на SQL Server, и ему требуется проконтролировать, как в действительности отзовется код, написанный им с помощью SMO, на стороне сервера. С другой стороны, SMO, скорее, административный интерфейс, нежели доступ к данным общего назначения, так что разработчик будет, вероятно, использовать ADO.NET. Словом, если уважаемые читатели располагают убедительным примером, когда профайлер неприменим, а CaptureSQL выручает, давайте обсудим в комментариях.

В SMO имеется возможность скриптования, полезность которой очевидна. Она реализована в классе Microsoft.SqlServer.Management.Smo.Scripter, бегло рассмотренном в предыдущем посте. Речь идет не о захвате того, что происходит на лету, что позволяет делать и профайлер, а об автоматизации кнопки Generate SQL Script, т.е. генерации скриптов ранее созданных объектов. Совершенно замечательной возможностью скриптера является построение дерева зависимостей между объектами. Выполнение этой операции вручную довольно затруднительно.

Sys.sql_expression_dependencies (она же sql_dependencies, она же sysdepends, а также sp_depends, которая лазит по последней) не является исчерпывающим справочником отношений между объектами, т.к. отслеживает весьма ограниченное число типов зависимостей - см. BOL, https://msdn.microsoft.com/ru-ru/library/ms345449.aspx. Кстати, статья примечательна по двум причинам. Во-первых, творческий подход переводчика, переведшего вьюшку как просмотр. Действительно, традиционный перевод как представление уже прискучил. Единственно, ему нужно было определиться и остановиться на каком-нибудь одном варианте, а не терзаться творческими муками в пределах одной статьи. Во-вторых, DMV не отслеживает важных в данном примере зависимостей foreign key и принадлежностей CLRных модулей сборкам:

select object_name(referencing_id), referencing_class_desc, c1.name, referenced_entity_name, c2.name from sys.sql_expression_dependencies d

left join sys.columns c1 on d.referencing_id = c1.object_id and d.referencing_minor_id = c1.column_id

left join sys.columns c2 on d.referenced_id = c2.object_id and d.referenced_minor_id = c2.column_id

(No column name)	referencing_class_desc	name	referenced_entity_name	name
TestFTS	OBJECT_OR_COLUMN	type	GetFileExtension	NULL
TestFTS	OBJECT_OR_COLUMN	type	TestFTS	FullName

Скрипт 5

Показывается зависимость колонки type в таблице TestFTS, которая имеет дефолтное значение dbo.GetFileExtension(FullName) от функции GetFileExtension и от колонки FullName. В то же время не показывается зависимость для таблицы ttt от таблицы TestFTS

create table ttt (id int, fk HierarchyID references TestFTS(id))

которая не даст просто так удалить таблицу TestFTS:

drop table TestFTS

Msg 3726, Level 16, State 1, Line 1

Could not drop object 'TestFTS' because it is referenced by a FOREIGN KEY constraint.

Скрипт 6

Эти зависимости нужно искать в sys.foreign_keys:

select name, object_name(parent_object_id), object_name(referenced_object_id) from sys.foreign_keys

name	(No column name)	(No column name)
FK__ttt__fk__43F60EC8	ttt	TestFTS

Скрипт 7

В sys.sql_expression_dependencies не отражается также зависимость функции GetFileExtension() от сборки MyAssembly. Ее нужно смотреть в sys.assembly_modules:

select a.name, o.name, o.type_desc, am.assembly_class, am.assembly_method from sys.assembly_modules am join sys.assemblies a on am.assembly_id = a.assembly_id join sys.objects o on am.object_id = o.object_id

name	name	type_desc	assembly_class	assembly_method
MyAssembly	Dir	CLR_TABLE_VALUED_FUNCTION	FileSystem	Dir_InitMethod
MyAssembly	SplitPath	CLR_TABLE_VALUED_FUNCTION	FileSystem	SplitPath_InitMethod
MyAssembly	LoadDir	CLR_STORED_PROCEDURE	FileSystem	LoadDirWithFileContent
MyAssembly	GetFileExtension	CLR_SCALAR_FUNCTION	FileSystem	GetFileExtension
MyAssembly1	Main	CLR_STORED_PROCEDURE	Class1	Main

Скрипт 8

Из-за того, что зависимости невозможно получить из единого справочника, а приходится собирать по разным местам, самостоятельное построение дерева зависимостей превращается в нетривиальную задачу. Далее, предположим, это как-то удастся сделать, но это будут голые object_id без скриптов. В зависимости от типа объекта придется сочинять drop table или drop proc или drop function и т.д. В T-SQL нет команды drop object <object_id> (а, наверное, зря). Аналогично, в SMO нету общей коллекции объектов базы, есть по отдельности db.Tables, db.StoredProcedures, db.UserDefinedFunctions и т.д. В зависимости от типа объекта придется ветвить, из какой коллекции мы хотим его дропнуть. К тому же сборки, хоть и создаются внутри базы данных, не имеют object_id и не входят в sys.objects. Object_id имеют процедуры и функции, созданные на основе ее модулей. С использованием возможности SMO CaptureSQL можно получить SQLный скрипт манипуляций над объектами SQL Server, когда последовательность действий над ними известна, то есть известны как сами объекты, так и зависимости между ними. Нижеприведенный скрипт выполняет передеплоймент сборки в явном виде: он удаляет таблицу TestFTS, зависимую от функции GetFileExtension, т.к. использует ее для дефолтного значения одного из своих полей, удаляет саму эту функцию, а также функцию Dir и процедуру LoadDir, удаляет сборку MyAssembly, откуда они берутся, а затем пересоздает их в обратной последовательности и получает SQLный скрипт всего этого безобразия. Я не стал приводить создание таблиц, поскольку пример на таблицы имеется в документации.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

cls

$srv = new-object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList ""

$srv.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::ExecuteAndCaptureSql

$db = $srv.Databases["TestFS"]

#Удаление объектов

$o = $db.Tables["ttt", "dbo"][0]; if ($o -ne $null) { $o.Drop() } #при необходимости можно уточнить схему, но тогда возвращается не единичный объект, а коллекция

$o = $db.Tables["TestFTS"]; if ($o -ne $null) { $o.Drop() }

$o = $db.StoredProcedures["LoadDir"]; if ($o -ne $null) { $o.Drop() }

$o = $db.UserDefinedFunctions["Dir", "dbo"][0]; if ($o -ne $null) { $o.Drop() } #коллекция UserDefinedFunctions содержит все ф-ции - CLR, SQL, скалярные, табличные, ...

$o = $db.UserDefinedFunctions["GetFileExtension"]; if ($o -ne $null) { $o.Drop() }

$o = $db.UserDefinedFunctions["SplitPath"]; if ($o -ne $null) { $o.Drop() }

$o = $db.Assemblies["MyAssembly"]; if ($o -ne $null) { $o.Drop() }

#Создание сборки

$asm = new-object -TypeName "Microsoft.SqlServer.Management.Smo.SqlAssembly"

$asm.Name = "MyAssembly"

$asm.Parent = $db #иначе на след.строчке Exception setting "AssemblySecurityLevel": "You must set Parent property."

$asm.AssemblySecurityLevel = [Microsoft.SqlServer.Management.Smo.AssemblySecurityLevel]::Unrestricted #лежит в microsoft.sqlserver.sqlenum.dll

$asm.Create("C:\Temp\LoadFolderToSQL\bin\Debug\ClassLibrary1.dll")

#Currently, only one file per assembly is supported - https://msdn.microsoft.com/ru-ru/library/microsoft.sqlserver.management.smo.sqlassembly.sqlassemblyfiles.aspx

#Создание CLRной хранимой процедуры

$sp = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.StoredProcedure" -ArgumentList $db, "LoadDir" #аргументы - БД, название процедуры; можно еще указать схему

$sp.TextMode = $false

$sp.ImplementationType = [Microsoft.SqlServer.Management.Smo.ImplementationType]::SqlClr

$sp.AssemblyName = $asm.Name

$sp.ClassName = "FileSystem" #public partial class ... в dll

$sp.MethodName = "LoadDirWithFileContent" #метод, как он называется в библиотеке классов

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.StoredProcedureParameter" -ArgumentList $sp, "@folder", $([Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(255))

$sp.Parameters.Add($o)

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.StoredProcedureParameter" -ArgumentList $sp, "@shallowTraversal", $([Microsoft.SqlServer.Management.Smo.DataType]::Bit)

$sp.Parameters.Add($o)

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.StoredProcedureParameter" -ArgumentList $sp, "@tblName", $([Microsoft.SqlServer.Management.Smo.DataType]::SysName)

$sp.Parameters.Add($o)

$sp.Create()

#Создание CLRной скалярной функции

$f = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.UserDefinedFunction"

$f.Parent = $db; $f.Name = "GetFileExtension"

$f.TextMode = $false

$f.ImplementationType = [Microsoft.SqlServer.Management.Smo.ImplementationType]::SqlClr

$f.AssemblyName = $asm.Name

$f.ClassName = "FileSystem"

$f.MethodName = "GetFileExtension"

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.UserDefinedFunctionParameter"

$o.Name = "@fileName"; $o.Parent = $f; #без этого не даст присвоить DataType параметру

$o.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(266)

$f.Parameters.Add($o)

$f.FunctionType = [Microsoft.SqlServer.Management.Smo.UserDefinedFunctionType]::Scalar

$f.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(5)

$f.Create()

#Создание CLRной TVF

$f = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.UserDefinedFunction" -ArgumentList $db, "Dir"

$f.TextMode = $false

$f.ImplementationType = [Microsoft.SqlServer.Management.Smo.ImplementationType]::SqlClr

$f.AssemblyName = $asm.Name

$f.ClassName = "FileSystem"

$f.MethodName = "Dir_InitMethod"

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.UserDefinedFunctionParameter" -ArgumentList $f, "@folder", $([Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(266))

$f.Parameters.Add($o)

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.UserDefinedFunctionParameter" -ArgumentList $f, "@shallowTraversal", $([Microsoft.SqlServer.Management.Smo.DataType]::Bit)

$f.Parameters.Add($o)

$f.FunctionType = [Microsoft.SqlServer.Management.Smo.UserDefinedFunctionType]::Table

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "ID", $([Microsoft.SqlServer.Management.Smo.DataType]::HierarchyId)

$f.Columns.Add($o)

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "FullName", $([Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(266))

$f.Columns.Add($o)

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "DateCreated", $([Microsoft.SqlServer.Management.Smo.DataType]::DateTime2(3))

$f.Columns.Add($o)

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "DateModified", $([Microsoft.SqlServer.Management.Smo.DataType]::DateTime2(3))

$f.Columns.Add($o)

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "LastAccessed", $([Microsoft.SqlServer.Management.Smo.DataType]::DateTime2(3))

$f.Columns.Add($o)

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "Properties", $([Microsoft.SqlServer.Management.Smo.DataType]::Xml(""))

$f.Columns.Add($o)

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "Size", $([Microsoft.SqlServer.Management.Smo.DataType]::BigInt)

$f.Columns.Add($o)

$o = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Column" -ArgumentList $f, "IsDir", $([Microsoft.SqlServer.Management.Smo.DataType]::Bit)

$f.Columns.Add($o)

$f.TableVariableName = ""

$f.Create()

$srv.ConnectionContext.CapturedSql.Text | % { Write-Host $_}

Скрипт 9

Рис.5

В случае, когда дерево зависимостей от объекта априорно неизвестно, следует прибегнуть к Microsoft.SqlServer.Management.Smo.DependencyТree, DependencyCollection и кодогенерации DDL средствами скриптера, как показывалось в предыдущем посте.