![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>Suite de l’article pr\u00e9c\u00e9dent<\/a>, o\u00f9 il \u00e9tait question de g\u00e9n\u00e9rer le DDL d’une base avec C# et SMO. Si on pousse l’id\u00e9e un peu plus loin, il serait s\u00e9duisant de pouvoir le faire directement dans une proc\u00e9dure stock\u00e9e, en s’interfa\u00e7ant avec SMO et sa m\u00e9thode Script(). La premi\u00e8re solution qui vient \u00e0 l’esprit, c’est \u00e9videmment SQLCLR.<\/p>\n Seulement voil\u00e0, on s’y casse un peu les dents car comme le dit Bob Beauchemin<\/a>, SMO n’est pas support\u00e9 dans SQLCLR. Donc \u00e0 priori, adieu la m\u00e9thode Script(), et retour au clic-droit …<\/p>\n Cet article propose une alternative qui utilise\u00a0 COM Automation<\/strong> (ou OLE Automation). C’est une fonctionnalit\u00e9 qui permet de charger un composant COM et d’utiliser ses m\u00e9thodes gr\u00e2ce \u00e0 des proc\u00e9dures stock\u00e9es \u00e9tendues sp_OA… C’est un peu l’anc\u00eatre de SQLCLR, mais avec moins de restrictions, donc plus de risques \u00e0 l’utilisation, c’est la raison pour laquelle cette technique est pas mal d\u00e9cri\u00e9e.<\/p>\n D’ailleurs, en th\u00e9orie utiliser du SMO avec OLE Automation n’est pas support\u00e9 par SQL Server<\/strong>, pour des raisons de s\u00e9curit\u00e9 du code notamment, parce que par d\u00e9faut la DLL externe que l’on souhaite utiliser est charg\u00e9e dans le process address space du moteur, ce qui augmente le risque d’\u00e9craser de la m\u00e9moire li\u00e9e \u00e0 SQL Server et de crasher le processus tout entier ou de provoquer des fuites m\u00e9moire. Il existe tout un tas de recommandations contre l’usage de COM Automation avec SQL Server, et\u00a0 beaucoup de cas ont \u00e9t\u00e9 ouverts au support \u00e0 cause de \u00e7a. Il faut \u00e0 chaque fois que c’est possible lui pr\u00e9f\u00e9rer l’utilisation de SQLCLR. Sauf que dans notre cas, SMO n’\u00e9tant\u00a0 pas utilisable dans du SQLCLR, il ne reste que peu d’options.<\/p>\n Une technique acceptable pour utiliser COM Automation sans mettre en p\u00e9ril l’instance est de charger le COM dans un processus s\u00e9par\u00e9 et d\u00e9di\u00e9, pour que tout probl\u00e8me de pointeur NULL ou de\u00a0 garbage collection dans le COM n’affecte pas SQL Server. Par exemple dans un web service accessible par Service Broker comme le propose Simon Sabin<\/a>, ou dans un serveur COM+ comme nous allons le voir ici. Donc en r\u00e9sum\u00e9:<\/p>\n COM est un truc \u00e9norme et je ne peux pas en parler dans un article. Il faudrait un bon millier de pages pour d\u00e9crire ce que c’est dans les d\u00e9tails et ce que \u00e7a fait, mais disons que l’id\u00e9e derri\u00e8re COM est qu’un objet puisse publier ses classes et ses m\u00e9thodes pour permettre \u00e0 des applications tierces de pouvoir les utiliser sans pour autant divulguer son code source. Les objets COM existent le plus souvent sous la forme de librairies dynamiques (DLL). Le principe est utilis\u00e9 massivement dans \u00e0 peu pr\u00e8s toutes les applications Windows MFC ou Console, y compris SQL Server.<\/p>\n Pour pouvoir \u00eatre r\u00e9utilis\u00e9, un objet COM doit poss\u00e9der les caract\u00e9ristiques suivantes:<\/strong> Par exemple pour scripter juste une table:<\/p>\n –> <\/strong>1:<\/strong> D\u00e9claration de l’interface publique, qui va publier aux applications externes l’unique m\u00e9thode de la classe CgetDDL :: ddlThis() Il faudra \u00e9galement modifier la propri\u00e9t\u00e9 ComVisible <\/strong>de l’assembly pour permettre d’y acc\u00e9der depuis l’ext\u00e9rieur. Dans assembly.cs:<\/p>\n Enfin, il faut signer la DLL avec un nom fort que l’on pourra g\u00e9n\u00e9rer avec VS:<\/p>\n Une fois la DLL compil\u00e9e, il va falloir l’enregistrer sur la machine et g\u00e9n\u00e9rer un fichier Type Library (*.tlb) que l’on va utiliser ensuite dans le serveur COM+. Pour enregistrer une dll et g\u00e9n\u00e9rer le tlb, on utilise l’utilitaire regasm <\/strong>qui est fournit avec tous les frameworks .NET (et qui doit se trouver qq part sous C:\\Windows\\Microsoft .NET\\Framework\\…):<\/p>\n A partir de l\u00e0, on va cr\u00e9er un nouveau serveur COM+ en utilisant le service de composants comexp.msc<\/strong><\/em>: dans Services des composants -> Ordinateurs -> Poste de travail -> Applications COM+<\/em>, on cr\u00e9\u00e9 une nouvelle application serveur vide, d\u00e9marr\u00e9e sous un compte windows qui serait reconnu comme sysadmin sur l’instance (par exemple l’admin local, ou tout autre compte windows sysadmin). On d\u00e9pose ensuite le fichier tlb dans les composants de la nouvelle application COM+, et on v\u00e9rifie si la m\u00e9thode est bien expos\u00e9e:<\/p>\n Enfin on d\u00e9marre le serveur COM+, et on v\u00e9rifie qu’un nouveau processus h\u00f4te de dll (dllhost.exe) est bien d\u00e9marr\u00e9 avec le bon compte. On va pouvoir s’int\u00e9resser \u00e0 la partie T-SQL.<\/p>\n Avant toute chose, pour pouvoir autoriser SQL Server \u00e0 utiliser Ole Automation, il faut l’activer dans les param\u00e8tres de configuration:<\/p>\n Ensuite, on cr\u00e9\u00e9 la proc\u00e9dure stock\u00e9e qui va charger le COM et utiliser la m\u00e9thode ddlThis():<\/p>\n La proc\u00e9dure r\u00e9cup\u00e8re le nom de la table, v\u00e9rifie si celle-ci existe dans la base courante, et charge le COM dans le dllhost.exe avec sp_OACreate<\/strong>. Le nom du COM object est celui d\u00e9clar\u00e9 dans le serveur COM+ . Un autre param\u00e8tre d’extr\u00eame importance est le dernier pass\u00e9 \u00e0 sp_OACreate\u00a0 (4). Il s’agit du contexte de chargement du COM, <\/strong>dans ce cas 4 indique \u00e0 SQL Server que le composant sera charg\u00e9 en dehors de son process address space et ne peut avoir acc\u00e8s aux ressources m\u00e9moire dans sqlservr.exe (cf MSDN<\/a>). C’est notre garde-fou et c’est indispensable, car par d\u00e9faut, la ddl aurait \u00e9t\u00e9 charg\u00e9e dans sqlservr.exe et aurait p\u00fb causer des d\u00e9g\u00e2ts, il est donc tr\u00e8s important de proc\u00e9der de cette mani\u00e8re.<\/p>\n Ensuite la proc\u00e9dure appelle sp_OAMethod<\/strong> pour invoquer la m\u00e9thode expos\u00e9e par l’interface du COM, et r\u00e9cup\u00e8re le DDL dans un nvarchar(4000). A la fin du processus, la r\u00e9f\u00e9rence vers l’objet est d\u00e9truite avec sp_OADestroy<\/strong>.<\/p>\n A l’appel de la proc\u00e9dure:<\/p>\n COM ou Ole Automation est une technique pour faire communiquer SQL Server avec des composants externes. Elle\u00a0 pr\u00e9sente des risques \u00e9lev\u00e9s de corruption m\u00e9moire lorsqu’on l’utilise avec les param\u00e8tres de contexte par d\u00e9faut, mais lorsqu’on manipule l’objet \u00e0 travers son propre conteneur, les risques sont limit\u00e9s.<\/p>\n Dans la mesure o\u00f9 SMO n’est pas support\u00e9 avec SQLCLR, elle repr\u00e9sente une alternative int\u00e9ressante pour pouvoir interfacer SQL Server avec des fonctions qui ne sont pas disponibles ailleurs.<\/p>\n A+. David B.<\/p>\nSQLCLR vs COM Automation:<\/h2>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2010\/08\/Sans-COM_usage5.jpg\" "\\"Sans")
<\/a><\/p>\nEcrire le COM object:<\/h2>\n
\n– Ses classes doivent \u00eatre publiques et impl\u00e9menter une interface publique.
\n– Chaque classe doit poss\u00e9der un constructeur par d\u00e9faut (vide).
\n– La DLL doit \u00eatre sign\u00e9e.
\n– La DLL doit \u00eatre enregistr\u00e9e sur la machine sur laquelle on va l’utiliser.<\/p>\nusing System;\r\nusing System.Collections.Generic;\r\nusing System.Collections.Specialized;\r\nusing System.Linq;\r\nusing System.Text;\r\nusing Microsoft.SqlServer.Management.Smo;\r\nusing Microsoft.SqlServer.Management.Common;\r\n\r\nnamespace getDDL\r\n{\r\n public interface IgetDDL \/\/ <- 1<\/span>\r\n {\r\n string ddlThis(string _srvname, string _dbname, int _objid);\r\n }\r\n public class cgetDDL : IgetDDL \/\/ <- 2<\/span>\r\n {\r\n string ddlstr;\r\n\r\n public cgetDDL() { } \/\/ <- 3<\/span>\r\n\r\n public string ddlThis(string _srvname, string _dbname, int _objid) \/\/ <- 4<\/span>\r\n {\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\n Server Srv = new Server(_srvname);\r\n Database Db = new Database();\r\n Db = Srv.Databases[_dbname];\r\n StringCollection _tablescr = Db.Tables.ItemById(_objid).Script();\r\n\r\n foreach (string _line in _tablescr)\r\n ddlstr += _line;\r\n<\/span><\/span> ddlstr += \"GO\"+'\\n';\r\n\r\n IndexCollection _indexes = _t.Indexes;\r\n foreach (Index _i in _indexes)\r\n {\r\n StringCollection _iscr = _i.Script();\r\n foreach (string _line in _iscr)\r\n ddlstr += _line + '\\n';\r\n }\r\n if (_indexes.Count != 0)\r\n ddlstr += \"GO\" + '\\n';\r\n\r\n CheckCollection _checks = _t.Checks;\r\n foreach (Check _c in _checks)\r\n {\r\n StringCollection _cscr = _c.Script();\r\n foreach (string _line in _cscr)\r\n ddlstr += _line + '\\n';\r\n }\r\n if (_checks.Count != 0)\r\n ddlstr += \"GO\" + '\\n';\r\n\r\n TriggerCollection _triggers = _t.Triggers;\r\n foreach (Trigger _trg in _triggers)\r\n {\r\n StringCollection _trgscr = _trg.Script();\r\n foreach (string _line in _trgscr)\r\n ddlstr += _line + '\\n';\r\n }\r\n if (_triggers.Count != 0)\r\n ddlstr += \"GO\" + '\\n';\r\n\r\n foreach (Column _col in _t.Columns)\r\n {\r\n if (_col.DefaultConstraint != null)\r\n {\r\n StringCollection _def = _col.DefaultConstraint.Script();\r\n foreach (string _line in _def)\r\n ddlstr += _line + '\\n';\r\n ddlstr += \"GO\" + '\\n';\r\n }\r\n }<\/span>\r\n\r\n return ddlstr;\r\n }\r\n }\r\n}<\/span><\/pre>\n
\n–><\/strong> 2:<\/strong> D\u00e9claration de la classe publique qui impl\u00e9mente l’interface.
\n–> 3: <\/strong>D\u00e9claration de son constructeur par d\u00e9faut.
\n–><\/strong> 4: <\/strong>D\u00e9claration de la m\u00e9thode, qui prend en arguments le nom de l’instance, le nom de la base et l’object_id de la table \u00e0 scripter. Pour le reste, cf l’article pr\u00e9c\u00e9dent<\/a>.<\/p>\n[assembly: ComVisible(true<\/span>)]<\/span><\/pre>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2010\/08\/sign_assembly.jpg\" "\\"sign_assembly\\"")
<\/a><\/p>\nChargement du COM dans un serveur COM+:<\/h2>\n
DOS >regasm \/codebase getDDL.dll \/tlb:getDDL.tlb\r\nMicrosoft (R) .NET Framework Assembly Registration Utility 2.0.50727.3053\r\nCopyright (C) Microsoft Corporation 1998-2004. Tous droits r\u00e9serv\u00e9s.<\/em>\r\n\r\nInscription des types r\u00e9ussie\r\nL'assembly a \u00e9t\u00e9 export\u00e9 vers 'C:\\DBA\\DEV\\C#\\getDDL\\bin\\Debug\\getDDL.tlb' et la biblioth\u00e8que de types a \u00e9t\u00e9 inscrite avec succ\u00e8s<\/span><\/pre>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2010\/08\/COMP.jpg\" "\\"COMP\\"")
<\/a><\/p>\nEcriture de la proc\u00e9dure stock\u00e9e:<\/h2>\n
sp_configure 'Ole Automation Procedures<\/span>',1;\r\nGO\r\nreconfigure;\r\nGO<\/span><\/pre>\ncreate procedure usp_getDDL (@tablename varchar(128))\r\nAS\r\nBEGIN\r\n DECLARE @hr int, @obj int;\r\n DECLARE @Source nvarchar(255), @Desc nvarchar(255);\r\n DECLARE @DDLTEXT nvarchar(4000);\r\n DECLARE @dbname sysname, @objid int, @srvname sysname;\r\n\r\n select @srvname = @@servername\r\n select @dbname= db_name()\r\n select @objid = object_id(@tablename)\r\n if (@objid is NULL)\r\n BEGIN\r\n print 'Table '+@tablename+' non trouvee dans la base courante'\r\n RETURN\r\n END\r\n\r\n execute @hr = sp_OACreate 'getDDL.cgetDDL<\/span>', @obj OUT, 4 --> context = 4 => <\/span><\/span><\/span>COM stock\u00e9 dans un dllhost.exe, pas dans sqlservr.exe<\/span>\r\n if (@hr <> 0)\r\n BEGIN\r\n EXEC sp_OAGetErrorInfo @obj,\r\n @Source OUT,\r\n @Desc OUT;\r\n SELECT HR = convert(varbinary(4),@hr),\r\n Source=@Source,\r\n Description=@Desc;\r\n RETURN\r\n END\r\n\r\n execute @hr = sp_OAMethod @obj, 'ddlThis<\/span>', @DDLTEXT OUT, @srvname, @dbname, @objid\r\n if (@hr <> 0)\r\n BEGIN\r\n EXEC sp_OAGetErrorInfo @obj,\r\n @Source OUT,\r\n @Desc OUT;\r\n SELECT HR = convert(varbinary(4),@hr),\r\n Source=@Source,\r\n Description=@Desc;\r\n RETURN\r\n END\r\n\r\n execute @hr = sp_OADestroy @obj\r\n\r\n select @DDLTEXT as 'DDL'\r\nEND<\/span><\/pre>\nDOS>sqlcmd -E\r\n1> use CAPDATA\r\n2> go\r\nLe contexte de la base de donn\u00e9es a chang\u00e9\u00a0; il est maintenant 'CAPDATA'.\r\n1> exec usp_getddl 'ANNONCE'\r\n2> go\r\n\r\nDDL\r\n------------------------------------------------------------------------------------------------------<\/em>\r\n\r\nSET ANSI_NULLS ON\r\nSET QUOTED_IDENTIFIER ON\r\nCREATE TABLE [dbo].[ANNONCE](\r\n [ID_ANNONCE] [int] NOT NULL,\r\n [ID_JOURNAL] [int] NOT NULL,\r\n [ID_PROPALOUER] [int] NOT NULL,\r\n [DATETIME_ANNONCE] [datetime] NOT NULL,\r\n [PRIX] [numeric](6, 2) NULL,\r\n [NB_PARUTION] [int] NULL\r\n) ON [PRIMARY]\r\n\r\nGO\r\nALTER TABLE [dbo].[ANNONCE] ADD\u00a0 CONSTRAINT [PK_ANNONCE] PRIMARY KEY CLUSTERED\r\n(\r\n [ID_ANNONCE] ASC\r\n)WITH (PAD_INDEX\u00a0 = OFF, STATISTICS_NORECOMPUTE\u00a0 = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,\r\nONLINE = OFF, ALLOW_ROW_LOCKS\u00a0 = ON, ALLOW_PAGE_LOCKS\u00a0 = ON) ON [PRIMARY]\r\nGO<\/span><\/pre>\nConclusion:<\/h2>\n