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
--specify the name of the error file :Error "C:\Users\andre\RunBatch\error.log" --Execute preliminary script to add the temp procedure(s) :r MyPath\JSONSQLServerRoutines\SaveExtendedJsonDataFromTable.sql USE AdventureWorks2016 :XML on set nocount on
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
GO :Out MyDirectoryPath\AdventureWorks2016\Person.PhoneNumberType.json DECLARE @Json NVARCHAR(MAX) EXECUTE #SaveExtendedJsonDataFromTable @TableSpec='Person.PhoneNumberType',@JSONData=@json OUTPUT Select @json
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
echo -S Bluebottle/Fly -U Queen -P NotAPassword9>%userProfile%\MyServerSqlCmd.txt REM Test out that it was properly saved Set /p TheServer=<%userProfile%\MyServerSqlCmd.txt echo %TheServer%
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.
@echo off
REM before you start, you need to write out your SQLCMD credentials to a file in your user
rem area using code like this, if you use SQL Server Credentials ...
Rem echo -S MyServer -U MyUserName -h -1 -P MyPassword>%userProfile%\MyServerSqlCmd.txt
REM ... or like this if you use windows security
REM echo -S MyServer -h -1 -W -f 65001 >%userProfile%\MyServerSqlCmd.txt
REM
REM read in your SQLCMD command and credentials
Set /p TheServer=<%userProfile%\MyServerSqlCmd.txt
REM Specify your work directory. I chose 'RunBatch' in my user area
Set workpath=%userProfile%\RunBatch
REM specify the name of your SQLCMD file
Set TheSQLCMDFileToExecute=%workpath%\SQLCMDFile.sql
REM Specify what preliminary file you need to set up
Set PreliminarySQL=MyCode\Github\JSONSQLServerRoutines\SaveExtendedJsonDataFromTable.sql
REM choose the name of your error file
Set Errorfile=%workpath%\error.log
REM and specify the name of your database that you want to use
Set Database=AdventureWorks2016
REM check whether the workpath directory exists
if not exist "%workpath%\" (md %workpath%)
if ERRORLEVEL 1 (
echo An error creating "%workpath%" directory occurred
goto bombsite)
REM check whether the database directory within the workpath directory exists
if not exist "%workpath%\%Database%\" (md %workpath%\%Database%)
if ERRORLEVEL 1 (
echo An error creating "%workpath%\%Database%" occurred
goto bombsite)
rem Write out the header to the SQLCMD file to execute
(
echo --specify the name of the error file
echo :Error "%Errorfile%"
echo --Execute prelimiary
echo :r %PreliminarySQL%
echo USE %Database%
echo :XML on
echo set nocount on
) >%TheSQLCMDFileToExecute%
Rem Create the query that brings you the list of tables
Set QUERY="SET NOCOUNT ON; SELECT Object_Schema_Name(object_id)+'.'+name AS The_Tables FROM sys.tables WHERE is_ms_shipped=0;"
REM Execute the query and create the entire SQL Command file that will be executed
for /F usebackq %%i in (`sqlcmd %TheServer% -d %Database% -Q %QUERY%`) do (
rem for every tablespec in the list append the following text ....
if ERRORLEVEL 1 (
echo An error accessing %Database% to get the list of tables occurred
goto bombsite)
(
echo :Out %workpath%\%Database%\%%i.json
echo DECLARE @Json NVARCHAR^(MAX^)
echo EXECUTE #SaveExtendedJsonDataFromTable @TableSpec=^'%%i^',@JSONData=@json OUTPUT
echo Select @json
echo GO
) >>%TheSQLCMDFileToExecute%
)
Rem Now that is done, we just execute the file
sqlcmd %TheServer% -d %Database% -y 0 -i %TheSQLCMDFileToExecute%
if ERRORLEVEL 1 (
echo An error running the script %TheSQLCMDFileToExecute% on %Database% occurred
goto bombsite)
goto end
:bombsite
Echo We bombed!
Exit /b 1
:end
Echo Yes! We got here at last!!
Exit /b 0
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!
The post Using Batch Scripts, and SQLCMD to Write Out a Database’s Data. appeared first on Simple Talk.
from Simple Talk http://bit.ly/2E8lXbD
via
No comments:
Post a Comment