Build a Scripting Utility
Use the SQL-DMO library to generate SQL Server object scripts as Web services quickly and simplify database administration.
by Roman Rehak
Web Services in the Enterprise 2002
Technology Toolbox: VB.NET, SQL Server 2000, XML, Web Services, SQL-DMO
Developers and database administrators (DBAs) often need to migrate database objects from one database to another during database application development. They usually accomplish this task by generating SQL Server object scripts from SQL Server Enterprise Manager. Although its scripting interface has improved greatly in the SQL Server 2000 client tools, Enterprise Manager doesn't let you generate scripts for triggers without including scripts for the parent tables. Nor does it allow you to append new scripts to the scripts it has generated already.
I prefer the greater script-generating flexibility I can gain from creating my own scripting utility. I wrote a utility in VB6 that worked fine for me, but caused deployment issues when my colleagues ran it from computers without SQL Server client tools installed. I rewrote the scripting tool as a Web service with a WinForms front end, so now the only client-side requirement is the .NET Framework.
In this article, I'll show you how to create such a scripting utility easily and how to implement it as a Web service for greater flexibility and ease of deployment. The example code contains two projects—a Web service that does the scripting, and a WinForms application that lets you set scripting options and call the Web service. The Web service uses the SQL Distributed Management Objects (SQL-DMO) library to connect to SQL Server and generate scripts for views, triggers, and stored procedures. The scripting Web service works with both SQL Server 7.0 and SQL Server 2000. (To create a solution that works with SQL Server 6.5, use the same techniques with the version of SQL-DMO that comes with SQL Server 6.5.)
Back to top
|