This article is about using the DOS Batch script facility of the Windows command line, together with SQLCMD to write the contents of each table in a database to the local filesystem. It shows how to use temporary stored procedures to advantage.

Just to make it a bit harder, I’m doing it in extended JSON (MongoDB format), but I’ve included access to files with procedures for doing it in ordinary JSON or array-in-array JSON. It will also out put JSON Schema files. With tweaking, it will do XML or tab-delimited output as well.

I originally did this because I had to write a utility for copying a SQL Server database to MongoDB. The MongoImport utility didn’t want to play nicely with PowerShell, which is any Windows developer’s preferred way of scripting. I therefore went back to my roots and did it as an old-fashioned batch file, command file, call it what you will. Sadly, I have considerable ‘previous’ with writing DOS and Windows Batch files, as it was, and still is,  the equivalent to Duct Tape, Cable-ties and superglue for working with Windows servers. In looking around to find out if anyone had done a similar job, I was surprised to find very little, and even read some  comments on forums that it couldn’t be done: So I wrote this article.

In this script, I’m combining three slightly unconventional techniques. Dos Batch Scripts, SQLCMD scripting and the use of Temporary Procedures in SQL Server.

Dos Batch scripts

Batch files are written in the scripting language for Microsoft command line shell. It has evolved from the batch script language of MS-DOS, COMMAND.EXE, and CMD.EXE. They typically have either the .BAT or .CMD extension. Batch files were supposed to be superseded by PowerShell. The problem with Batch Files is that the format has been evolved over time by MSDOS/Windows developers who have little in the way of an overall vision of how a scripting language should work, so now it is a mess of switches, cryptic punctuation, strange conventions and hacks. If you have any sense that I’m exaggerating, just read the excellent documentation built into the command shell. If you don’t break into a sweat, you’re not reading it properly. To read the full story, type, in the command shell the names of all the commands followed by /?

Against all expectations, DOS scripting has thrived. On the Rosetta Code site, DOS Batch File Scripts compete with other languages for solving computer tasks and problems. The old MSDOS Batch news groups are still active. There are plenty of DOS script archives, and a package manager for Windows Batch scripts written, of course, as a batch file. Plenty of grey-muzzle ops people have built up expertise with DOS shell batches and see no reason to change, or to convert their myriad scripts. Stack Overflow is full of helpful Q&A pieces. Someone has even written Adventure games, RPGs and 3-D modelling in DOS Batch scripts: not because it is wise, but because it is challenging and invigorating to do so

SQLCMD

The SQLCMD command-line executable is a wonderful tool, but you really need to use it frequently in order to remember all the important switches, Command-line Options, scripting variables and sqlcmd commands. The most important advice that MSDN gives is to do as much as possible in a single SQLCMD session, and make full use of the sqlcmd commands, many of which work in SSMSs SQLCMD mode. You will need the MSDN instructions on SQLCMD with you as you work.

Temporary procedures

I’ve mentioned these in a previous article. I use the technique by loading all or any of the procedures I need at the start of the sqlcmd script and the procedures remain throughout the script because everything is run in the same connection. No teardown is needed because the temporary procedures are deleted along with temporary tables when the connection is closed.

The Script.

The script stores the credentials in plain text in the user area, relying on NTFS security to prevent them being disclosed. This is not that good, but a lot better that embedding UserIds or passwords in a script.

The script contacts the database to get a list of tables in the database you wish to use, and gets a list of the schemas and tablenames as a tablespec. With this list, it creates a SQLCMD script that writes out the results for each table to a different file in the directory you designate. It then executes this script.

The script writes  a few preliminaries to the SQL File that will eventually be executed. The source for this procedure is here 

then, for each table it does this ( Person.PhoneNumberType in this case) which writes out to the SQL File which is then executed, the instructions in SQL and SQLCMD commands to write  the JSON representation of the tables contents to a file

The procedure #SaveExtendedJSONDataFromTable does most of the work. It creates a JSON document containing the data from the table, written in the MongoDB dialect of JSON called Extended JSON. if you want to export ordinary JSON, use SaveJsonDataFromTable.sql instead. When this script is executed by SQLCMD as the final part of the DOS batch script, it ends up with all the files written out into the directory you have chosen.

Here is the script. Firstly, you need to change the parameters at the start of the script to specify your work directory, the name you want for your SQLCMD file, the name of your error file, the path to your preliminary SQL file (with procedures or initialization stuff) and the name of the database you want to execute the script against. Then you need to create your credentials file along with the name of your server. You do this by executing the commented-out section at the beginning, filling in the credentials and executing just that batch

Now we are ready to try it out. Such is the slight perversity of DOS batch files that it may not entirely run first time. Fortunately, your error log will tell you quite a bit, and if you read the attempt made at the SQLCMD file, this should indicate where things have broken. To debug, first delete the @echo off line so you can see how the batch ran. There are more hints here.

Once a script like this is bedded down, it seems to run consistently. It runs fast, and the amount of code seems less than in PowerShell. However, for most purposes, I think I’ll stick with PowerShell. Note that the Invoke-SQLCMD cmdlet doesn’t implement the :OUT SQLCMD command so you would be stuck with executing the SQLCMD.exe command. I was spurred into writing this code because I had to use a batch for other things that didn’t work in PowerShell so my time wasn’t wasted!