VistaDB 5.2 Released with Enhanced Scripting & SQL Server Compatibility

We’ve published a new release of VistaDB with significant improvements to the development experience, particularly when targeting both SQL Server and VistaDB.  It’s now much easier to script out schema and data from VistaDB and migrate databases from SQL Server to VistaDB that include stored procedures and user defined functions.

You can download this latest version immediately, or read more about it below.

Configuration Management for VistaDB

Previously VistaDB didn’t have a particularly good story if you wanted to track changes to your database along with your source code.  If you checked in a template database it was an opaque binary file so every change resulted in the entire file being viewed as changed.  If you scripted out the database you didn’t see changes to stored procedures or functions and you had to pick through the entire file to see what changes really happened.

With VistaDB 5.2 you can select the option to Export Schema as Scripts to get a directory tree with a separate file for each database object (table, view, stored procedure, or function).  As you make changes, just re-export on top of the previous copy and check it in: Only objects that were changed will have their scripts modified, making it much easier to review and merge changes (particularly with a DVCS like git).

Migrating Databases from SQL Server

Whether you’re using SQL Server as your authoritative master schema for new versions or doing a one time migration you’ll appreciate our new stored procedure and user function migration capabilities.  Historically, these were skipped when migrating because they frequently need to be revised to be compatible with both SQL Server and VistaDB.  Over time we’ve improved the general compatibility story and now you can see during the migration if your sprocs and functions will work as-is or need updates.  For example, here is a database with a stored procedure that isn’t compatible with VistaDB as is.  The problematic sproc is colored in red on the left.

The Test SQL button will show us the specific error message, and we can then fix the problem in the lower window and repeatedly test the updated sproc until it parses successfully.  In this example, the error is because the sproc was created with an incompatible setting for ANSI nulls.  VistaDB only supports SET ANSI_NULLS ON, not OFF, so we’ll need to correct that.  If the sproc was designed for non-standard null behavior then it might have to be updated further in order to work correctly now with standard ANSI treatment of nulls in VistaDB, but those corrections could be made later with a migrated database to test them in.  We also need to make sure its checkbox is checked if we mean for it to be included in the migration; those with incompatible settings may have been unchecked.

You can go down the list and fix each sproc or function which failed the parse check until you end up with all the necessary code ready for migration.

Combining this with our new feature for exporting schema as scripts and you can easily have a workflow where you continuously develop in SQL Server and migrate to VistaDB when you’re ready to publish.  You can then use your favorite comparison tool to see exactly what objects were changed and how they were changed.  Using this information it’s easy to create your own upgrade script.

Make Derived Tables Quickly with SELECT INTO

You can now create a table directly from the output of a query using SELECT INTO.  VistaDB will create the table with the necessary schema given the query’s output, run the query and insert all of the rows in the new table.  You can then subsequently add additional indexes and constraints if you’d like and work with it like any other table.

For more information on this language feature, see MSDN: INTO Clause (Transact-SQL Reference) including examples A and C on using the syntax.

Export Data to Excel (and CSV)

A frequent customer request has been to save query results to files from Data Builder, at least matching what SQL Server Management Studio can do.  We’ve gone a few steps better with commands to export results to Excel or CSV.  If you run queries that have multiple results or even run multiple queries with go statements you can export all the results from the latest execution into a single Excel workbook, with each having nicely formatted columns.  The CSV exporter follows the RFC 4180 CSV format and supports quoted values, multiline text, and allows null values (distinguished as an unquoted empty value, while all non-null values are quoted) for a high fidelity representation you can import into an external system.

Behind-the-Scenes Changes

Finally, this is the first release of VistaDB from our new git repository.  Customers with source code access have probably been noticing that all of our development is now visible, day by day, and this isn’t just a copy of the official TFS repository but instead the one true source for all changes.  With source code access you can even see our individual comments and pull requests as we complete features and merge them in to the develop branch.  If you’re interested in adding source code access to your license, contact us through support.