AbaPerls

This month’s cool, free tool is called AbaPerls, which was written by long-time SQL Server MVP Erland Sommarskog of Sweden. As its name implies, AbaPerls is a collection of command-line tools written in Perl. These tools aid in the development, configuration management, and implementation of SQL Server databases and schema objects by making automating and scripting these operations fast and easy.

Development Features

AbaPerls is a rich tool with features that fall in a lot of different areas. In the area of SQL development, AbaPerls includes a command-line tool called ABASQL. ABASQL lets you automate often difficult operations, such as interacting with source code libraries and running scripts automatically when the script depends on many variables that might change at the time of execution, than you could with tools such as Query Analyzer, sqlcmd.exe, or SQL Server Management Studio. AbaPerls also includes the following development features:

Database Management Features

AbaPerls facilitates the building and updating of databases using its DBBUILD tool. DBBuild integrates with Microsoft Visual SourceSafe and builds an empty database in the optimal object order using the projects and subprojects from ABASQL. It also automatically extracts errors and warnings into a log file for you. The DBUPDGEN tool lets you compare two SourceSafe versions of a project tree and generate an update script from the differences.

You can also configure the database build process to install and update subsets of the database together using an AbaPerls configuration file. For example, you can specify that all stored procedures and user-defined functions be encrypted before they’re stored, while other objects aren’t encrypted. You can view the configuration file using the LISTCONFIG tool.

AbaPerls also includes a data loading feature called INSFILGEN, which reads a Microsoft Excel workbook and creates all the INSERT statements needed to insert that data into a SQL Server database. In addition, AbaPerls includes the following Visual SourceSafe tools, which facilitate search and replace operations:

There are several other tools available in AbaPerls. DOBCP does a fast BCP load in or out of all the tables in a database. The TBLCNT tool counts the number of records in all your tables, which is useful in conjunction with DOBCP. TBLFIX is a rather limited tool that extracts all table and type definitions for SQL scripts and places the definitions into 1-3 files per table under the AbaPerls file structure. (For more information about the AbaPerls file structure, see the tool’s documentation at www.sommarskog.se/AbaPerls/doc/index.html.) SPFIX does the same thing for stored procedures. RUNSPS reads a comma-delimited file containing the names of stored procedures and all their parameters and executes them en masse.

System Requirements

Although some of AbaPerls features are a bit quirky because they were developed for an inhouse project with a developer close-at-hand, its usefulness is without question. AbaPerls works with SQL Server 2000 and later, and SourceSafe 2005 and Visual SourceSafe 6.0. AbaPerls runs on any OS that supports SQL Server 2000 and later. In addition, AbaPerls creates a SQL Server database for tracking and meta-objects, so you’ll need to designate a database for that purpose. You can download lots of other great articles and information by Sommarskog from www.sommarskog.se/index.html.