SMO. CaptureSQL



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


 


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


 



image001


Рис.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


 


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


 


image003



Рис.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ный скрипт:


 


image005



Рис.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


 


image007



Рис.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, http://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 - http://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


 


image009



Рис.5


 


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


Comments (0)

Skip to main content