{"id":83297,"date":"2019-02-12T17:35:09","date_gmt":"2019-02-12T17:35:09","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=83297"},"modified":"2019-02-14T10:02:27","modified_gmt":"2019-02-14T10:02:27","slug":"using-batch-scripts-and-sqlcmd-to-write-out-a-databases-data","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/using-batch-scripts-and-sqlcmd-to-write-out-a-databases-data\/","title":{"rendered":"Using Batch Scripts, and SQLCMD to Write Out a Database’s Data."},"content":{"rendered":"

This article is about using the DOS Batch script facility of the Windows command line<\/a>, 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<\/a>.<\/p>\n

Just to make it a bit harder, I\u2019m doing it in extended JSON (MongoDB format), but I\u2019ve 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.<\/p>\n

I originally did this because I had to write a utility for copying a SQL Server database to MongoDB. The MongoImport utility didn\u2019t want to play nicely with PowerShell, which is any Windows developer\u2019s 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 \u2018previous\u2019 with writing DOS and Windows Batch files, as it was, and still is,\u00a0 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\u00a0 comments on forums that it couldn’t be done: So I wrote this article.<\/p>\n

In this script, I\u2019m combining three slightly unconventional techniques. Dos Batch Scripts, SQLCMD scripting and the use of Temporary Procedures in SQL Server.<\/p>\n

Dos Batch scripts<\/h2>\n

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\u2019m exaggerating, just read the excellent documentation built into the command shell. If you don\u2019t break into a sweat, you\u2019re not reading it properly. To read the full story, type, in the command shell the names of all the commands followed by \/?<\/p>\n

Against all expectations, DOS scripting has thrived. On the Rosetta Code site<\/a>, DOS Batch File Scripts compete with other languages for solving computer tasks and problems. The old MSDOS Batch news groups<\/a> are still active. There are plenty of DOS script archives, and a package manager for Windows Batch scripts<\/a> 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<\/p>\n

SQLCMD<\/h2>\n

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<\/code> commands, many of which work in SSMSs SQLCMD mode. You will need the MSDN instructions on SQLCMD<\/a> with you as you work.<\/p>\n

Temporary procedures<\/h2>\n

I\u2019ve 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<\/code> 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.<\/p>\n

The Script.<\/h2>\n

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.<\/p>\n

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.<\/p>\n

The script writes\u00a0 a few preliminaries to the SQL File that will eventually be executed. The source for this procedure is here<\/a>\u00a0<\/p>\n

--specify the name of the error file\r\n:Error \"C:\\Users\\phil\\RunBatch\\error.log\"\r\n--Execute preliminary script to add the temp procedure(s)\r\n:r MyPath\\JSONSQLServerRoutines\\SaveExtendedJsonDataFromTable.sql\r\nUSE AdventureWorks2016\r\n:XML on\r\nset nocount on<\/pre>\n
 <\/p>\n
then, for each table it does this ( Person.PhoneNumberType<\/strong> in this case) which writes out to the SQL File which is then executed, the instructions in SQL and SQLCMD commands to write\u00a0 the JSON representation of the tables contents to a file<\/p>\n
GO\r\n:Out MyDirectoryPath\\AdventureWorks2016\\Person.PhoneNumberType.json\r\nDECLARE @Json NVARCHAR(MAX)\r\nEXECUTE #SaveExtendedJsonDataFromTable @TableSpec='Person.PhoneNumberType',@JSONData=@json OUTPUT\r\nSelect @json<\/pre>\n
The procedure #SaveExtendedJSONDataFromTable<\/a>\u00a0does 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\u00a0SaveJsonDataFromTable.sql<\/a>\u00a0instead. 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.<\/p>\n
<\/p>\n
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<\/p>\n
 <\/p>\n
echo -S Bluebottle\/Fly -U Queen -P NotAPassword9>%userProfile%\\MyServerSqlCmd.txt \r\nREM Test out that it was properly saved\r\n\r\nSet \/p TheServer=<%userProfile%\\MyServerSqlCmd.txt \r\necho %TheServer%<\/pre>\n
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<\/a>.<\/p>\n
@echo off\r\nVERIFY errors 2>nul\r\n   SETLOCAL ENABLEEXTENSIONS\r\n   IF ERRORLEVEL 1 echo Unable to enable extensions\r\nREM before you start, you need to write out your SQLCMD credentials to a file in your user\r\nrem area using code like this, if you use SQL Server Credentials ...\r\nRem echo -S MyServer -U MyUserName -P MyPassword>%userProfile%\\MyServerSqlCmd.txt\r\nREM ... or like this if you use windows security\r\nREM echo -S MyServer  >%userProfile%\\MyServerSqlCmd.txt\r\nREM\r\nREM read in your SQLCMD command and credentials\r\nSet \/p TheServer=<%userProfile%\\MyServerSqlCmd.txt\r\nREM Specify your work directory. I chose 'RunBatch' in my user area\r\nSet workpath=%userProfile%\\RunBatch\r\nREM specify the name of your SQLCMD file\r\nSet TheSQLCMDFileToExecute=%workpath%\\SQLCMDFile.sql\r\nREM Specify what preliminary file you need to set up \r\nSet PreliminarySQL=S:\\work\\Github\\JSONSQLServerRoutines\\SaveExtendedJsonDataFromTable.sql\r\nREM choose the name of your error file\r\nSet Errorfile=%workpath%\\error.log\r\nREM and specify the name of your database that you want to use\r\nSet Database=AdventureWorks2016\r\nREM check whether the workpath directory exists\r\nif not exist \"%workpath%\\\" (md %workpath%) \r\nif ERRORLEVEL 1 (\r\n  echo An error creating \"%workpath%\" directory occurred \r\n  goto bombsite)\r\nREM check whether the database directory within the workpath directory exists\r\nif not exist \"%workpath%\\%Database%\\\" (md %workpath%\\%Database%) \r\nif ERRORLEVEL 1 (\r\n\techo An error creating \"%workpath%\\%Database%\" occurred \r\ngoto bombsite)\r\nrem Write out the header to the SQLCMD file to execute\r\n(\r\necho --specify the name of the error file\r\necho :Error \"%Errorfile%\"\r\necho --Execute prelimiary\r\necho :r %PreliminarySQL%\r\necho USE %Database%\r\necho :XML on\r\necho set nocount on\r\n) >%TheSQLCMDFileToExecute%\r\nRem Create the query that brings you the list of tables\r\nSet QUERY=\"SET NOCOUNT ON; SELECT Object_Schema_Name(object_id)+'.'+name AS The_Tables FROM sys.tables WHERE is_ms_shipped=0;\"\r\nREM Execute the query and create the entire SQL Command file that will be executed\r\nfor \/F usebackq %%i in (`sqlcmd %TheServer% -d %Database% -h -1  -f 65001  -Q %QUERY%`) do (\r\nrem for every tablespec in the list append the following text ....\r\n        if ERRORLEVEL 1 (\r\n\t  echo An error accessing  %Database%  to get the list of tables occurred \r\n          goto bombsite)\r\n\t(\r\n\techo :Out %workpath%\\%Database%\\%%i.json\r\n\techo DECLARE @Json NVARCHAR^(MAX^)\r\n        echo EXECUTE #SaveExtendedJsonDataFromTable @TableSpec=^'%%i^',@JSONData=@json OUTPUT\r\n        echo Select @json\r\n\techo GO\r\n\t) >>%TheSQLCMDFileToExecute% \t\t\r\n     ) \r\nRem Now that is done, we just execute the file\r\nsqlcmd %TheServer% -d %Database% -f 65001 -y 0 -i %TheSQLCMDFileToExecute%\r\nif ERRORLEVEL 1 (\r\n  echo An error running the script %TheSQLCMDFileToExecute% on %TheServer% occurred \r\n  goto bombsite)\r\ngoto end \r\n:bombsite\r\nEcho We bombed!\r\ncolor 0C\r\nENDLOCAL\r\nExit \/b 1\r\n:end\r\nEcho Yes! We got here at last!!\r\nENDLOCAL\r\nExit \/b 0\r\n<\/pre>\n
 <\/p>\n
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\u2019ll stick with PowerShell. Note that the Invoke-SQLCMD cmdlet doesn\u2019t 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\u2019t work in PowerShell so my time wasn\u2019t wasted!<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019m doing it in extended JSON……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6813],"class_list":["post-83297","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83297","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=83297"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83297\/revisions"}],"predecessor-version":[{"id":83306,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83297\/revisions\/83306"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=83297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=83297"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=83297"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=83297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}