New Stuff
Resources for Your Developer Toolbox
Marnie Hutcheson
Contents
A few months ago I had a large SQL Server™ asset management database into which I had to import data from text files in daily batches. I needed to tweak the tables, add some queries, and generate new reports. To be sure that I had identified all dependencies, I asked the vendor for the schema. They sent a PDF that measured 6' x 8'. I couldn't print it in any usable fashion, and trying to follow the connections on the screen was a frustrating exercise to say the least, so it wasn't much help. I ended up doing everything by trial and error using the tools that come with SQL Server. I really wish I had known about this bunch of tools then! I could have saved weeks of debugging.
Understand Your Database Schemas
EasyStruct
4.5 from Vishwak Solutions gives you a complete picture of the structure of any database that supports OLE DB. You can use it to document the structure of a database or to answer specific questions, such as "What are the dependencies for this query?" This makes it very handy for modifying or migrating a database. It displays dependencies, stored procedures, and triggers. You can also view statistics and object properties like privileges and creation dates.
You can use the "Execute Query" feature to run a stored procedure within EasyStruct, so you can view, insert, update, and delete data. You can view the output in grid format or save it in HTML format.
EasyStruct 4.5 will let you generate a complete SQL script for recreating an existing database. It also supports XML and XML Schema natively. It allows you to define rules governing the relations between elements and attributes using a standard XML syntax that can be parsed and managed using other XML applications.
You select the objects you want to work with, and EasyStruct 4.5 lets you control the number of database objects in a single page. The reports include Entity Relationship diagrams using a new .NET Framework-based Diagram Application.
Price: $299 per single-user license.
Fast Data Integration
Extract, transform, and load (ETL) processes allow companies to move data from multiple sources into their own databases, transforming it (reformatting and cleaning it) along the way. Visual Importer Professional 7.0 by DBSoftware Laboratory combines ETL and design capabilities in one environment, simplifying the creation and deployment of complex data warehouses and marts.
Visual Importer Professional is a data integration tool for the high-speed transfer of data from text files and ODBC source databases into SQL Server, Oracle, or ODBC target databases. Its capabilities range from simple record transfer within a single system to supporting cross-system data extraction and loading that can be transformed by calculations including data formatting, concatenations, and loading cross or pivot tables.
There is a comprehensive error log, rejected records file, and a mapping editor to help you keep track of events. Visual Importer Professional 7.0 runs on Windows® 98 through Windows Server™ 2003.
Price: $150 per single-user license.
Build Complete SQL Scripts
Superior SQL Builder 1.5 by Red Earth Technologies is a SQL script builder that lets you build complete SQL scripts (not just single queries) without typing any code. Instead of building scripts using tokens that you replace with text values, Superior SQL Builder 1.5 uses soft programmed Script Templates that support multiple data types. Each data type can be configured at design time and run time, and exposes functionality that can be used to build highly customizable scripts.
The data you provide to the Script Templates via Inputs is exposed via Outputs. The data outputs of a Script Template can be used as Inputs into another Script Template, so the changes that you make in one Script Template "flow" downstream into other Script Templates.
You can build any type of SQL statement. Superior SQL Builder 1.5 virtually eliminates syntax, logic, and typographical errors when developing SQL, because instead of typing or cutting and pasting SQL, you drag and drop Superior SQL Builder Script Templates onto the work area and then connect them in order to define your data flow.
There are lots of templates to choose from. You fill in forms to configure the elements of the Script Templates to suit your solution. You select your database server type and when you are ready, you just click a button and your SQL script is generated. The Script Templates can work together to convert the entire project to the appropriate database dialect for SQL Server, Microsoft® Access, MySQL, and Oracle.
Superior SQL Builder runs on Windows 98 and later.
Price: $349 per license.
Add Tools to Your SQL Editor
DTM SQL editor by DTM soft is an integrated SQL environment that starts with a SQL editor and lets you add on more tools as you need them. The editor uses a step-by-step approach to let you edit SQL scripts and build SQL statements.
You don't have to dig into the actual SQL, but the editor does feature syntax highlighting, multilevel undo and redo features, and integrated search and replace tools. You can also use macros to automate entering frequently used fragments.
The add-on tools include a Connections Manager, database Schema Viewer, a View Execution History option, and a lot of other handy tools that are all available from the customizable user interface. The clipboard and data export tools export to plain text, HTML, XML, and Microsoft Office formats. In addition, all of your result queries can be saved as SQL statements.
DTM SQL comes in several options that range from the Standard version that gives you the SQL editor and connection to a single database, all the way to the Enterprise edition that lets you add on all the tools and work with large projects having multiple data connections.
Price: DTM SQL Enterprise is $220 per single-user license. Additional modules are available for purchase separately.
A Data Replication Solution
ClusterReplica MSSQL Edition 2.0 by XLink TechnologyInc. is a turnkey replication and failover solution that is specifically designed for small businesses running SQL Server. It binds two servers running Windows 2000, Windows XP, or Windows Server 2003 into one cluster system with automatic failover capability. This is a critical feature if you are trying to support 24?7 availability and disaster recovery.
ClusterReplica MSSQL Edition version 2.0 adds enhancements to network communications that improve connections and client service on the clustered server. The Snapshot technology has been improved so that ClusterReplica can handle real-time, open file data replication for SQL database files and Windows registry files. Other improvements help reduce the incidence of corrupted files during replication and insure successful failover for SQL Server instances.
XLink has taken pains to make this product as easy as possible to set up and use. They claim that the cluster replication will be automatically configured when ClusterReplica is installed with both primary and secondary stations up and running—no IT experts required. The manuals and instructions support this goal with lots of clear instruction presented in simple steps.
Price: $799 per single-cluster license.
Manage Data from Multiple Sources
Aqua Data Studio 4.0 by AquaFold Inc gives you an integrated database environment with a single consistent interface to all major relational databases. Aqua Data Studio lets you administer and manage data from multiple data sources simultaneously, so you can tackle multiple tasks simultaneously from one application.
The Query Analyzer lets you create database scripts with specific relational database management system (RDBMS) syntax highlighting, auto-completion data retrieval, and custom results formatting. The new export tool gives you a wizard for extracting and exporting data from any relational database into different data sources, including SQL script with INSERT statements so that you can transfer data directly to other databases. You can visually manage the security of any relational database's users, roles, and object permissions.
Aqua Data Studio supports Windows, Macintosh OS X, Solaris, and the Linux operating system.
Price: $149 per single commercial user license. Free for educational institutions and personal use.
A CD-Based Training Suite
SQL Server 2000 Grand Slam Multimedia Training Suite by SMI Corp. consists of 30 CDs that teach you techniques for administering and programming SQL Server 2000.
Here is the breakdown of the Grand Slam CD set: Practical SQL Server 2000 Administration (21 CDs), Data Warehouse Architecture with OLAP Cubes (5 CDs), Crash Course in SQL Server 2000 (3 CDs), and 120 Minutes to SQL Server Stardom (1 CD).
The video portion of the training lets you view the computer screen user interfaces used to administer, design, and program SQL Server as though you were looking over the instructor's shoulder. All you have to do is follow the mouse and the cursor as the instructor talks you through the short and practical lessons.
The material is aimed at beginner and intermediate students. Many of these courses are available individually.
Price: $895.
All prices were confirmed at press time and are subject to change.
Send your New Stuff to newstuff@microsoft.com.
Marnie Hutcheson is president of Internet Development Associates (Ideva), a firm in Ocala, Florida, that specializes in Internet and intranet Web application design and development. She has published technical papers and books on various computing topics. You can reach her at
marnie@ideva.com.