SMO из SQL CLR
SMO (в прошлом SQL-DMO) - это SQL Server Management Objects, .Netовская библиотека для управления объектами SQL Server, очень удобная тем, что ее структура классов повторяет иерархию объектов, как они видятся в SQL Server Management Studio, и позволяющая запрограммировать в ней практически любой клик.
Вполне очевидно, что полезность этой библиотеки многократно возросла, если бы написанные с ее помощью административные задачи можно было оформлять не только как клиентские приложения, но и в виде логики на стороне SQL Server. Скажу больше, на мой взгляд, она прямо-таки напрашивается выполняться на серверной стороне. Но не все вещи, которые нам представляются очевидными, реализованы в жизни, иначе она была бы неправдоподобно хороша.
У меня было очень сложное клиентское приложение
using System;
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
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SMO.Server srv = new SMO.Server();
Debug.WriteLine(srv.Name);
}
}
}
Скрипт 1
которое я мечтал оформить в виде CLRной хранимой процедуры:
using System;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
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
public class Class1
{
[SqlFunction]
public static void Main()
{
SMO.Server srv = new SMO.Server();
SqlContext.Pipe.Send(srv.Name);
}
}
Скрипт 2
Ее нужно делать в виде обычной библиотеки классов, потому что из SQL Serverного проекта ссылаемые dllи не видны - см. пост "Использование COMовских dll в SQL CLR". Чучелом или тушкой - на SQL Server 2005 она не пролезала никак. Стоило сказать CREATE ASSEMBLY, как выдавалась ругань:
Assembly 'ля-ля-ля' references assembly 'microsoft.sqlserver.batchparser, version=9.0.242.0, culture=neutral, publickeytoken=...', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
Как написано здесь - https://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/270dc01a-aff4-4707-bd7e-aca81b1399d3 - SMO не поддерживается внутри SQL Server. Обычно, что можно сделать в сценарии, когда какая-нибудь системная dll не поддерживается, это вручную зарегистрировать ее на базе. Однако в случае SMO это работать не будет. Точка.
Можно сказать, что препятствие носило фатальный характер, потому что, как обойти его, не знал никто ни до выхода SQL Server 2005, ни после. От отчаяния на коннекте появился следующий пост: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126386, на который примерно через 9 месяцев разродился ответ, что We have selected your request for consideration in the next version of SQL Server. Недавно предоставился повод проверить, насколько успешным было рассмотрение. Я попробовал продеплоить сборку Скрипт 2 на SQL Server 2008.
if object_id('Main', 'PC') is not null drop proc Main
if exists(select 1 from sys.assemblies where name = 'MyAssembly1') drop assembly MyAssembly1
create assembly MyAssembly1 from 'C:\Temp\ClassLibrary1\bin\Debug\SMOTest.dll' with permission_set = unsafe
go
create proc Main as external name MyAssembly1.Class1.Main
go
Скрипт 3
Вы не поверите, таки Command(s) completed successfully. Если бы она при этом еще работала, было бы совсем счастье.
exec Main
Скрипт 4
Счаз. Полного счастья не бывает.
Msg 6522, Level 16, State 1, Procedure Main, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "Main":
System.Exception: This functionality is disabled in the SQLCLR. It is recommended that you execute from your client application.
System.Exception:
at Microsoft.SqlServer.Management.Common.ConnectionManager..ctor()
at Microsoft.SqlServer.Management.Common.ServerConnection..ctor()
at Microsoft.SqlServer.Management.Smo.ExecutionManager..ctor(String name)
at Microsoft.SqlServer.Management.Smo.Server..ctor()
at Class1.Main()
Если закомментарить обращение к SMO, все работает на ура:
public class Class1
{
[SqlFunction]
public static void Main()
{
//SMO.Server srv = new SMO.Server();
SqlContext.Pipe.Send("ля-ля-ля");
}
}
Скрипт 5
Обычно, когда ей нужны подкрепляющие assemblies, она не стесняется про них спрашивать прямым текстом. Например, когда тягаем LINQ, к нему требуются c:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll и C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.Runtime.Serialization.dll. Она об этом так и пишет:
Msg 10301, Level 16, State 1, Line 1
Assembly 'System.Data.Linq' references assembly 'system.runtime.serialization, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(failed to retrieve text for this error. Reason: 15105)). Please load the referenced assembly into the current database and retry your request.
Здесь - молчок. Ни гу-гу ни про batchparser, ни про Microsoft.SqlServer.ConnectionInfo с Microsoft.SqlServer.Management.Sdk.Sfc. Можно предположить один из двух выводов. Первое - они рассмотрели и не нашли нужным, потому что рассмотреть в положительную сторону никто не обещал. Тогда слова про disabled functionality относятся ко всем SMO, и можно, стало быть расслабиться. Второе - слова относятся к моей неуклюжей попытке установления соединения. Возможно, там надо было написать что-то вроде строки с context connection=true, и тогда SMO нынче все-таки может работать внутри CLR на стороне SQL Server. Если вам известен ответ, напишите его в комментах, за что вам выразит глубочайшую признательность не токмо худый и смиренный аз, но и все читающее сообщество.
Во всяком случае на create assembly она уже не ругается, что наглядно подтверждает возросшую функциональность, устойчивость и масштабируемость 2008-го. Пойду посмотрю, может, в R2 уже и exec работает.