MS SQL Data Wizard

User's guide

Copyright © 2002-2016 SQL Maestro Group

 I MS SQL Data Wizard Help

Table of Contents
Foreword 0

I Welcome to MS SQL Data Wizard! 1

1 System...................................................................................................................................
Requirements 2
2 Installation
................................................................................................................................... 3
3 How can
...................................................................................................................................
I purchase MS SQL Data Wizard? 4
4 License...................................................................................................................................
Agreement 5
5 About SQL
...................................................................................................................................
Maestro Group 7
6 What's...................................................................................................................................
new 10

II Getting Started 11
1 How to...................................................................................................................................
connect to Microsoft SQL 13
2 Templates
................................................................................................................................... 14
3 Command
...................................................................................................................................
line options 15

III Data Import 16

1 Selecting
...................................................................................................................................
source format 17
2 Selecting
...................................................................................................................................
objects to import to 19
3 Adding
...................................................................................................................................
SQL queries 20
4 Setting...................................................................................................................................
files to import from 21
5 Setting...................................................................................................................................
the accordance between source and target columns 23
Map builder .......................................................................................................................................................... 24
Data formats .......................................................................................................................................................... 25
6 Customizing
...................................................................................................................................
common options 27

IV Data Export 29
1 Selecting
...................................................................................................................................
format of the result files 30
2 Adding
...................................................................................................................................
objects to export data from 31
3 Customizing
...................................................................................................................................
target columns properties 34
4 Setting...................................................................................................................................
general export options 35
Adjusting data..........................................................................................................................................................
formats 35
Setting format-specific
..........................................................................................................................................................
options 36

V SQL Dump 40
1 Selecting
...................................................................................................................................
source objects 41
2 Setting...................................................................................................................................
destination file names 42
3 Customizing
...................................................................................................................................
common dump options 43

VI Blob Import 44

© 2016 SQL Maestro Group

 Contents II

1 Selecting
...................................................................................................................................
tables for import 45
2 Setting...................................................................................................................................
files for import 46

VII Blob Export 48

1 Selecting
...................................................................................................................................
tables with BLOB data 49
2 Setting...................................................................................................................................
file name templates 50

VIII Task Scheduler 52

1 Using ...................................................................................................................................
MS SQL Data Wizard Agent 55

IX Options 56
1 Application
................................................................................................................................... 57
Preferences .......................................................................................................................................................... 57
2 Editors
...................................................................................................................................
& Viewers 59
General .......................................................................................................................................................... 59
Display .......................................................................................................................................................... 60
SQL highlight .......................................................................................................................................................... 61
XML highlight.......................................................................................................................................................... 62
PHP highlight .......................................................................................................................................................... 63
Code Insight .......................................................................................................................................................... 64
Code Folding .......................................................................................................................................................... 65
3 Appearance
................................................................................................................................... 67
Bars and menus.......................................................................................................................................................... 67
Trees and lists
.......................................................................................................................................................... 68
Edit controls .......................................................................................................................................................... 69
Check boxes .......................................................................................................................................................... 70
Buttons .......................................................................................................................................................... 71
Page controls.......................................................................................................................................................... 72
Group boxes .......................................................................................................................................................... 73
Splitters .......................................................................................................................................................... 74

Index 76

© 2016 SQL Maestro Group

II
 1 MS SQL Data Wizard Help

1 Welcome to MS SQL Data Wizard!

MS SQL Data Wizard is a powerful Windows GUI utility for managing your Microsoft SQL
data. It provides you with a number of easy-to-use tools for performing the required
data manipulation easily and quickly.

Key features:
· Data export to as many as 18 file formats
· Data import from Excel, CSV, text files and more
· Generating SQL dumps for selected tables
· Export BLOB data to files and load BLOB data from files
· Flexible Task Scheduler
· The Agent application to execute tasks in background mode
· Powerful command-line interface

© 2016 SQL Maestro Group

 Welcome to MS SQL Data Wizard! 2

1.1 System Requirements

Client environment
· Pentium PC or higher;
· Windows NT4/2000/XP/Vista/Windows 7/Windows 8/Windows 10;
· 512 MB RAM (1 GB recommended);
· 25 MB of free hard disk space;
· SVGA-compatible video adapter.

Server environment

· Microsoft SQL server versions up to SQL Server 2014.

© 2016 SQL Maestro Group

 3 MS SQL Data Wizard Help

1.2 Installation
To install MS SQL Data Wizard on your PC:

· download the MS SQL Data Wizard distribution package from the download page at
our site;
· run setup.exe from the local folder and follow the instructions of the installation
wizard;
· find the MS SQL Data Wizard shortcut in the corresponding program group of the
Windows Start menu after the installation is completed.

© 2016 SQL Maestro Group

 Welcome to MS SQL Data Wizard! 4

1.3 How can I purchase MS SQL Data Wizard?

Thank you for your interest in purchasing MS SQL Data Wizard!

You can select licensing options and register MS SQL Data Wizard at its on-line order
page. It is possible to purchase on-line, by fax, mail, toll-free phone call, or place a
purchase order. We send the software activation key by email within 24 hours after
completion of the order process. If you have not received the activation key within this
period, please contact our sales department.

All our products and bundles are shipped with 12 months of free upgrades (minor and
major ones) or with 36 months of free upgrades for a quite small additional fee. After this
period you may renew your license for the next 12(36) months with a 50% discount.

MS SQL Data Wizard has a free 30-day trial. Upon purchasing the product you confirm
that you have tested it and you are completely satisfied with its current version.

To obtain technical support, please visit the appropriate section on our website or
contact us by email to support@sqlmaestro.com.

© 2016 SQL Maestro Group

 5 MS SQL Data Wizard Help

1.4 License Agreement

Notice to users: carefully read the following legal agreement. The use of the software
provided with this agreement (the "SOFTWARE") constitutes your acceptance of these
terms. If you do not agree to the terms of this agreement, do not install and/or use this
software. The use of this software is conditioned upon the user's compliance with the
terms of this agreement.

· License grant. SQL Maestro Group grants you a license to use one copy of the
version of this SOFTWARE on any single hardware product for as many licenses as you
purchase. "You" means a company, an entity or an individual. "Use" means storing,
loading, installing, executing or displaying the SOFTWARE. You may not modify the
SOFTWARE or disable any licensing or control features of the SOFTWARE except as an
intended part of the SOFTWARE's programming features. This license is not
transferable to any other company, entity or individual. You may not publish any
registration information (serial numbers, registration keys, etc.) or pass it to any other
company, entity or individual.

· Ownership. The SOFTWARE is owned and copyrighted by SQL Maestro Group. Your
license confers no title or ownership of the SOFTWARE and should not be construed as
a sale of any rights for the SOFTWARE.

· Copyright. The SOFTWARE is protected by the United States copyright law and
international treaty provisions. You acknowledge that no title to the intellectual
property in the SOFTWARE is transferred to you. You further acknowledge that title
and full ownership rights to the SOFTWARE will remain the exclusive property of SQL
Maestro Group and you will not acquire any rights to the SOFTWARE except as
expressly set forth in this license. You agree that any copies of the SOFTWARE will
contain the same proprietary notices which appear on and in the SOFTWARE.

· License and distribution. An unregistered copy of the SOFTWARE ("UNREGISTERED

SOFTWARE") may be used for evaluation purposes. The UNREGISTERED SOFTWARE
may be freely copied and distributed to other users for their evaluation. If you offer
this UNREGISTERED SOFTWARE installation package for download, then you agree to:

· replace existing version of the UNREGISTERED SOFTWARE installation package with the
new package immediately after a new version of the SOFTWARE is released by SQL
Maestro Group, or
· delete an obsolete version of the UNREGISTERED SOFTWARE installation package
immediately upon written email notice by SQL Maestro Group.

A registered copy of the SOFTWARE ("REGISTERED SOFTWARE") allows you to use the
SOFTWARE only on a single computer and only by a single user at a time. If you wish to
use the SOFTWARE for more than one user, you will need a separate license for each
individual user. You are allowed to make one copy of the REGISTERED SOFTWARE for
back-up purposes.

· Reverse engineering. You affirm that you will not attempt to reverse compile,
modify, translate, or disassemble the SOFTWARE in whole or in part.

· Unauthorized use. You may not use, copy, rent, lease, sell, modify, decompile,
disassemble, otherwise reverse engineer, or transfer the SOFTWARE except as
provided in this agreement. Any such unauthorized use shall result in immediate and

© 2016 SQL Maestro Group

 Welcome to MS SQL Data Wizard! 6

automatic termination of this license.

· No other warranties. SQL Maestro Group does not warrant that the SOFTWARE is
error-free. SQL Maestro Group disclaims all other warranties with respect to the
SOFTWARE, either express or implied, including but not limited to implied warranties of
merchantability, fitness for a particular purpose and noninfringement of third party
rights. Some jurisdictions do not allow the exclusion of implied warranties or limitations
on how long an implied warranty may last, or the exclusion or limitation of incidental or
consequential damages, so the above given limitations or exclusions may not apply to
you. This warranty gives you specific legal rights and you may also have other rights
which vary from jurisdiction to jurisdiction.

· Limited warranty. This SOFTWARE is provided on an "AS IS" basis. SQL Maestro
Group disclaims all warranties relating to this SOFTWARE, whether expressed or
implied, including but not limited to any implied warranties of merchantability or fitness
for a particular purpose. Neither SQL Maestro Group nor anyone else who has been
involved in the creation, production, or delivery of this SOFTWARE shall be liable for
any indirect, consequential, or incidental damages arising out of the use or inability to
use such SOFTWARE, even if SQL Maestro Group has been advised of the possibility of
such damages or claims. The person using the SOFTWARE bears all risk as to the
quality and performance of the SOFTWARE.

Some jurisdictions do not allow limitation or exclusion of incidental or consequential

damages, so the above given limitations or exclusion may not apply to you to the extent
that liability is by law incapable of exclusion or restriction.

· Severability. In the event of invalidity of any provision of this license, the parties
agree that such invalidity shall not affect the validity of the remaining portions of this
license.

· No liability for consequential damages. In no event shall SQL Maestro Group or its
suppliers be liable to you for any consequential, special, incidental or indirect damages
of any kind arising out of the delivery, performance or use of the SOFTWARE, even if
SQL Maestro Group has been advised of the possibility of such damages. In no event
will SQL Maestro Group's liability for any claim, whether in contract, tort or any other
theory of liability, exceed the license fee paid by you, if any.

· Entire agreement. This is the entire agreement between you and SQL Maestro Group
which supersedes any prior agreement or understanding, whether written or oral,
relating to the subject matter of this license.

· Reserved rights. All rights not expressly granted here are reserved to SQL Maestro
Group.

© 2016 SQL Maestro Group

 7 MS SQL Data Wizard Help

1.5 About SQL Maestro Group

SQL Maestro Group is a privately-held company producing high-quality software for
database administrators and developers. The united team of eminently qualified
developers is pleased to create new software products for commercial, academic and
government customers worldwide. We do our best to design and develop products that
remove complexity, improve productivity, compress time frames, and increase database
performance and availability. We are glad to realize that our products take usual chores
upon themselves, so that our customers could have more time left for their creative work.

The company was founded in 2002 as an essential partner for every business that is
trying to harness the explosive growth in corporate data. SQL Maestro Group employs an
international team concentrating their efforts on cutting-edge DBA tools development.

The slogan of our company is The Shortest Path to SQL. It is aimed to denote that we
set to create easy-to-use products meant for those who appreciate comfort, friendly
program interface and support when working with SQL servers.

· We are pleased to facilitate your job.

· We aim at being of considerable assistance to our clients.

· We feel contented doing our beloved work.

At present, our company offers a series of Windows GUI admin tools for SQL
management, control and development of the following servers: MySQL, Microsoft SQL
Server, PostgreSQL, Oracle, SQL Anywhere, DB2, SQLite, Firebird, and MaxDB. We
also produce universal tools to be used for administering any database engine accessible
via ODBC driver or OLE DB provider. Such products may be the clear-cut decision for
those who constantly work with several database servers.

SQL Maestro is the premier Windows GUI admin

tool for database development, management, and
control.

It provides you with the ability to perform all the

necessary database operations such as creating,
editing, copying, extracting and dropping database
objects; moreover, you can build queries visually,
execute queries and SQL scripts, view and edit
data including BLOBs, represent data as diagrams,
export and import data to/from most popular file
formats, manage users and their privileges (if
possible), and use a lot of other tools designed for
making your work with your server comfortable and
efficient.

© 2016 SQL Maestro Group

 Welcome to MS SQL Data Wizard! 8

SQL PHP Generator is a powerful tool for creating

database-driven web applications visually. It allows
you to generate high-quality PHP scripts for
working with tables, views and queries through the
web. You needn't have any programming
background to use it.

SQL Data Wizard is a high-capacity Windows GUI

utility for managing your data.

It provides you with a number of easy-to-use

wizards for performing the required data
manipulation easily and quickly. The tool allows you
to export data from Microsoft SQL tables and
queries to most popular formats, import data into
the tables, generate SQL dump of selected tables,
and export/import BLOB fields from/to files.
SQL Code Factory is a premier GUI tool aimed at
the SQL queries and scripts development.

It allows you to manage SQL queries and scripts

using such useful features as code folding, code
completion and syntax highlighting, build query
visually, execute several queries at a time, execute
scripts from files, view and edit result data with
filtering, sorting and grouping abilities, export data
to as many as 14 file formats including Excel, RTF
and HTML, import data from Excel, CSV, XML and
text files, view and edit BLOBs in various way, build
diagrams based on Oracle data, and much more.
Database Converter is a user friendly tool to
migrate any local or remote ADO-compatible
database to Microsoft SQL.

Such tools transfer database schema and data and

are equipped with native support for the most
popular database servers.

Data Sync is a powerful and easy-to-use tool for

database contents comparison and
synchronization.

Such tools can be useful for database

administrators, developers and testers that need a
quick, easy and reliable way to compare and
synchronize their data.

© 2016 SQL Maestro Group

9 MS SQL Data Wizard Help

The software products are constantly optimized for the latest server versions support.

You can use the following contact information if necessary:

Our web-site www.sqlmaestro.com

Postal address: SQL Maestro Group

140 Broadway, Suite 706
New York City, New York 10005
United States

Thank you for your interest to our company!

© 2016 SQL Maestro Group

 Welcome to MS SQL Data Wizard! 10

1.6 What's new

Please find out the latest MS SQL Data Wizard news at http://www.sqlmaestro.com/
products/mssql/datawizard/news/

© 2016 SQL Maestro Group

 11 MS SQL Data Wizard Help

2 Getting Started
The Home page contains a set of links to run all the wizards included into the software.
Each wizard may be run in 3 ways:

1. Run in a usual way

In this way you need to customize all the process parameters step by step. To
invoke a wizard, use the wizard's link at the Home page or the wizard's button on
the Ribbon toolbar.

2. Run with template

On running a wizard in this way, you'll get all the session parameters loaded from
a template 14 file and ready for manual editing. To run a wizard with a template,
specify a template to load and use the Run with template link at the Home page.
The template file must be created during a previous wizard session.

3. Run in background mode

This way is useful if you already have a template which does not require further
customizing. To supply a wizard with a ll the session parameters from a template 14

file and execute it in background mode, specify a template file at the Home page
and click Run in background mode. Besides, you can specify a time to execute the
task within the Task Scheduler 52 .

© 2016 SQL Maestro Group

 Getting Started 12

To learn more about the wizards, follow the links:

· Data Export 29
· Data Import 16

· SQL Dump 40

· Blob Import 44

· Blob Export 48

© 2016 SQL Maestro Group

 13 MS SQL Data Wizard Help

2.1 How to connect to Microsoft SQL

MS SQL Data Wizard allows you to connect to Microsoft SQL servers using Windows and
SQL Server authentication modes.

Provider
The application allows you to connect to SQL Server using any of SQL Server Native
Clients installed on your computer. To choose a client you want to use, select the
appropriate item in the Provider combobox. The table below shows the correspondence
between the value selected in this combobox and SQL Server client to be used.

Value SQL Server client

Introduced with
SQLOLE Microsoft OLE DB Provider for SQL Comes with
DB Server (default value) Windows
SQLNCL SQL Server Native Client SQL Server 2005
I
SQLNCL SQL Server 2008 [R2] Native Client SQL Server 2008
I10 [R2]
SQLNCL SQL Server 2012 Native Client SQL Server 2012
I11 (recommended)

We would recommend you to install and use SQL Server 2012 Native Client as it (and
only it) supports SQL Server Express LocalDB. Also it provides the best support for data
types implemented in the recent versions of SQL Server.

Windows Authentication (more preferable)

Microsoft Windows Authentication mode allows a user to connect through a Windows
user account.

SQL Server Authentication

When a user connects with a specified Login name and Password from a non-trusted
connection, SQL Server performs the authentication itself by checking to see if a SQL
Server login account has been set up and if the specified password matches the one
previously recorded.

© 2016 SQL Maestro Group

 Getting Started 14

2.2 Templates
MS SQL Data Wizard allows you to save and restore all options set during a wizard
session. You need not to specify all session parameters each time you work with a
wizard anew; instead you can load all settings from a template and change them if
necessary. To create a template, walk through the wizard and click More > Save
Template at the last step. All the settings you have made will be saved to a file. To
restore previously saved settings from a template, click More > Load Template at the
first wizard step.

Templates are very useful when working with MS SQL Data Wizard. If you will close any
wizard without saving a template, all carefully adjusted settings will be lost. To set the
same options next time, you'll need to repeat the process step by step again while with
a template all the session parameters can be restored in a few mouse clicks.

© 2016 SQL Maestro Group

 15 MS SQL Data Wizard Help

2.3 Command line options

MS SQL Data Wizard supports a number of command line options that make it possible to
fully automate the data management operations.

The MS SQL Data Wizard command line syntax is as follows:

mydatawizard[.exe] <task> <template_file_name> [<log_file_name>]

mydatawizard[. The MS SQL Data Wizard program file.

exe]
<task> An operation to execute. It can be one of the following:
import (for Data Importing tasks);
export (for Data Exporting tasks);
sqldump (for SQL Dump tasks);
blobimport (for BLOB Import tasks);
blobexport (for BLOB Export tasks).
<template_file_na
The template 14 with all the task's settings.
me>
<log_file_name> The name of text file to store log information (optionally).

Examples.
The examples below assume that you are entering the command lines in the MS SQL
Data Wizard program directory. Don't forget to enclose all paths and filenames
containing spaces in quotes.

· mydatawizard export "C:\Templates\Export\MySQL\northwind.extx" "C:\Logs

\Export_to_MySQL_NBA_db.log"
· pgdatawizard.exe import "D:\Templates\Import\PostgreSQL\northwind.imtx"
· mydatawizard.exe sqldump "C:\Templates\SQLDump\MySQL\sakila.sdmpx" "C:
\Logs\SQLDump\MySQL\sakila.log"

© 2016 SQL Maestro Group

 Data Import 16

3 Data Import
Data Import wizard provides you with a graphical user interface to import data from the
most popular files formats into existing Microsoft SQL tables. It allows you to adjust data
formats, empty target tables, execute custom SQL scripts, etc. To run the wizard, use
the Run Data Import Wizard link at Home page 11 .

The following formats are supported:

· Microsoft Office Excel 95-2003

· Microsoft Office Excel 2007
· Microsoft Office Access
· Microsoft Office Access 2007
· Delimiter-separated values (CSV, DSV, TSV);
· Text files (Fixed-width columns)
· DBF
· XML
· ODBC data source (any data source accessible via ODBC driver or OLE DB
provider such as SQL Server, Oracle, MySQL, MS Access, Text files, MS Excel,
etc).

To import data,
· Set connection properties 13 of the database to import data to;
· Select the format 17 to use for the input data;
· Select the tables 19 where you want to import the data;
· Set source file options 21 ;
· Map source file columns and target table fields 23 ;
· Specify other import options 23 .

See also: Templates 14

© 2016 SQL Maestro Group

 17 MS SQL Data Wizard Help

3.1 Selecting source format

Select the format of data to be imported and set the default options to be applied to all
the source files and target tables during the import process. These options can be
customized later for each file and table separately. It is useful to set here the options
that correspond to the most of files and tables. For example, if the most of source files
contain column headers, check the appropriate checkbox to skip the first row for all the
files and uncheck this option only for files that do not contain headers.

Specify the Default directory for source files and complete the Encoding, XPath, Data
location, Delimiter, and Quote fields if necessary. Turn ON the Empty targets before
importing option to delete all the records from target tables before the import starts.

In case you are importing from an ODBC source specify the Connection string for the
source database. The connection string must contain the information that the ODBC
driver or OLE DB provider need to know to be able to establish a connection to the
database or the data file. To learn more about ODBC drivers, OLE DB providers and other
such stuff, read our brief guide to connection strings. Select the type of quotes used by
the server as Quote characters identifiers to escape improper symbols and blanks in
object names.

© 2016 SQL Maestro Group

 Data Import 18

© 2016 SQL Maestro Group

 19 MS SQL Data Wizard Help

3.2 Selecting objects to import to

To specify tables where you want to import data, click the Add... button and pick the
tables in the Select objects dialog. To choose several tables at a time, select them
using Ctrl and Shift keys, then click Mark selected.

© 2016 SQL Maestro Group

 Data Import 20

3.3 Adding SQL queries

This step is available if ODBC data source has been selected as the source format. To
import data from SQL queries:

· add necessary SQL queries;

· specify them as data sources for the Microsoft SQL tables on the next step.

Add an SQL query using the corresponding button and specify a file to load the query
from or place the query text in the corresponding window.

© 2016 SQL Maestro Group

 21 MS SQL Data Wizard Help

3.4 Setting files to import from

For each selected table 19 specify the input file (table, data source, SQL query 20 )
containing the data to be imported in the table and adjust such import options as the
Encoding, Data location, Delimiter, etc. for each file separately if necessary (by default,
the options specified earlier 17 are applied for all the files).

Import data from several files at a time

To import data from multiple files with the same structure, set the mask of the file
names to the corresponding field. To see the list of matching files, use with the button
on the right.

Example: To import data from the following tables:

D:\Data\Excel\country1.xls
D:\Data\Excel\country2.xls
D:\Data\Excel\country3.xls
D:\Data\Excel\country4.xls
use the mask D:\Data\Excel\country*.xls.

Additional import options due to source format

For MS Excel files, specify a data sheet containing the data to be imported to the
selected Microsoft SQL table. Check the Header box to skip the first row of the file and
uncheck it otherwise.

For MS Access files, specify a data table containing the data to be imported to the
selected Microsoft SQL table. Set the password for the source database if necessary.

For CSV, DSV, and TSV files, select the file encoding, the delimiter, and the quote.
Check the Header box to skip the first row of the file and uncheck otherwise.

© 2016 SQL Maestro Group

 Data Import 22

For TXT and DBF files, select the source file encoding. For text files, check the Header
box to skip the first row of the file or uncheck it otherwise.

For .XML files, define the XPath to the data to be imported to the selected Microsoft
SQL table and select whether data are stored in Attributes or Subnodes.

Example 2: To import data from the following .xml file, use

XPath=/Employees/Employee and
Data location=Subnodes

<?xml version="1.0" encoding="utf-8"?>

<Employees>
<Employee>
<ID>1</ID>
<FirstName>Klaus</FirstName>
<LastName>Salchner</LastName>
<PhoneNumber>410-727-5112</PhoneNumber>
</Employee>
<Employee>
<ID>2</ID>
<FirstName>Peter</FirstName>
<LastName>Pan</LastName>
<PhoneNumber>604-111-1111</PhoneNumber>
</Employee>
</Employees>

Example 3: To import data from the .xml file below, use

XPath=DATAPACKET/Data/Item and
Data location=Attributes

<?xml version="1.0"?>
<DATAPACKET Version="2.0">
<Data>
<Item ID="1" FirstName="Klaus" LastName="Salchner" PhoneNumber="410-727-5112" />
<Item ID="2" FirstName="Peter" LastName="Pan" PhoneNumber="604-111-1111" />
</Data>
</DATAPACKET>

© 2016 SQL Maestro Group

 23 MS SQL Data Wizard Help

3.5 Setting the accordance between source and target columns

The wizard provides you with several ways to map input data to the target tables
columns.

· You can map columns automatically by order with the Auto Fill and Auto fill all
maps buttons.
· You can do it manually for each table using the drop-down list of Source column
fields. To set the accordance in this way, select a target object in the list first.
The object columns appear in the Target field tab. Now select data to be imported
to the columns.
· To map columns visually, open Map builder 24 with the Build map link.

It's useful to save a specified map to a file for further using it in the next wizard
sessions. To save a map, use the More... button and follow the Save map link.

To see the 100 first rows of input file or output table, use the More... button and follow
the View source data or Preview results links respectively.

You can also specify Replacements to be applied to the selected column before the
import and data format masks 25 used for the input file.

To empty tables where you want to import data before the importation, check the
Empty target box. The default value of the boxes has been set earlier.

To exclude the first file row, use the File contains column header checkbox.

© 2016 SQL Maestro Group

 Data Import 24

3.5.1 Map builder

To specify the accordance between source and target columns visually, use popup menu
of the upper row to map source file columns to target table fields.

© 2016 SQL Maestro Group

 25 MS SQL Data Wizard Help

For text files define columns bounds first. To add a bound, double-click near the column
data in the builder area. To map a column to a target table field, select the field in the
Target field list and then click between the bounds.

3.5.2 Data formats

Use the window fields to indicate format masks of the source data imported to the
selected table. It allows the application to import data correctly.

The components of the date time format mask are represented at the window. Compose

© 2016 SQL Maestro Group

 Data Import 26

your date, time, and date time format mask of this components and separators. The
following table contains some types of input fields and suggests masks to import them.

To import these input data correctly Use these format masks

June 29 mmm dd
Jun 29, 2009 mmmm dd, yyyy
Tue Jun 14 16:50:49 ddd mmm dd hh:nn:ss
01/15/09 08:26 AM mm/dd/yy h:nn ampm

You can also set decimal and thousand separators, and custom NULL,TRUE and FALSE
values. If you have several values to be imported to NULL(TRUE, FALSE) value, use
semicolons to separate them.

© 2016 SQL Maestro Group

 27 MS SQL Data Wizard Help

3.6 Customizing common options

All data sources and target tables are specified on previous steps, it's time to define the
import process.

Logging
Use this option to be informed about all the actions occurred during the import. The log
file name may contain current timestamp with the %ts:TIMESTAMP_FORMAT% string.
Examples of valid log file names:
dbname_import_%ts:yyyy_mm_dd%.log
import_%ts:yyyy_mm_dd_hh_mm%_mysql.log
%ts:yyyy_mm_dd_hh_mm_ss%.log

Email notification
To send a log file as an email attachment after each import process or in case of an
occurred error, specify settings of email(s) to be sent: parameters of your SMTP server,
email addresses, subject and body text.

Scripts
There are many cases where the import process is necessary to correct with additional
scripts. So to disable table indexes before the importing, specify the corresponding
scripts to be executed before and after the process.

The typical example of usage of the Before each table and After each table scripts is
the import data to autoincrement columns of several tables. In this case it's neseccary
to set the corresponding scripts:
SET IDENTITY_INSERT %table_name% ON
and

© 2016 SQL Maestro Group

 Data Import 28

SET IDENTITY_INSERT %table_name% OFF

to be executed before and after import data to each table correspondingly.

Import mode
If the Update existing records option is turned ON, the records will be either updated or
inserted: an UPDATE will be performed when a target row exists in the table and an
INSERT is performed when the target row does not exist.

Reorder
MS SQL Data Wizard arranges target tables on import by dependencies to inserted
records in the correct order. To reorder target tables manually, follow the corresponding
button.

© 2016 SQL Maestro Group

 29 MS SQL Data Wizard Help

4 Data Export
Data Export wizard is a tool to save data from Microsoft SQL tables, views, and queries
to the most popular formats. It allows you to fully customize output files including
header and footer, fonts, colors, and data formats. To run the wizard, use the Run Data
Export Wizard link at Home page 11 .

The following formats are available:

· Microsoft Office Excel 97-2003
· Microsoft Office Excel 2007
· CSV, TSV, and DSV
· HTML
· XML
· Text files (fixed-width columns)
· Microsoft Office Word 97-2003
· Microsoft Office Word 2007
· Microsoft Office Access;
· OpenDocument Spreadsheet
· OpenDocument Text
· DBF
· PDF
· RTF
· DIF
· SYLK
· LaTeX
· JSON.

To export your data,

· Set connection properties 13 for the source Microsoft SQL database;
· Select the format 30 to use for the output;
· Select objects 31 you want to export data from;
· Select columns 34 you want to include into result files;
· Specify other export options 35 .

See also: Templates 14

© 2016 SQL Maestro Group

 Data Export 30

4.1 Selecting format of the result files

Select the output file format, the default directory for the result files and the default
encoding if necessary.

© 2016 SQL Maestro Group

 31 MS SQL Data Wizard Help

4.2 Adding objects to export data from

To specify source database objects you want to export data from, click the Add...
button and pick the objects in the Select objects dialog. To choose several objects at a
time, select them using Ctrl and Shift keys, then click Mark selected.

To use a custom SQL query as a data source, use the Create query button and enter
the query name and a SELECT statement. It is also possible to load a query from an .sql
file.

© 2016 SQL Maestro Group

 Data Export 32

Rules for creating queries used by Data Export

All queries must satisfy the simple requirement: the following query must be correct.

select * from
(
QUERY_TEXT_YOU_ENTERED
) an_alias

This happens because the software uses similar queries for internal needs. In case such
SQL expression is not valid, the wizard marks the query as invalid and displays its name
in red.

To meet this requirement, make sure that all the columns in the result dataset have
unique aliases. For example, the following query works fine itself, but returns a dataset
with two columns named id:

SELECT
table1.*,
table2.*
FROM table1, table2
WHERE table1.id = table2.id;

This is the reason the wizard marks this query as invalid. To solve the problem, provide
these columns with unique aliases:

SELECT
table1.id as table1_id,
table2.id as table2_id
FROM table1, table2

© 2016 SQL Maestro Group

33 MS SQL Data Wizard Help

WHERE table1.id = table2.id;

© 2016 SQL Maestro Group

 Data Export 34

4.3 Customizing target columns properties

Set the options of output files. The Objects tab allows you to edit names of result files
(tables in case of exporting to MS Access) and their headers and footers. By default,
the names of output files are set according to the tables/views names and queries titles.
To change a file name, select the corresponding item at the Objects tab, press F2 and
enter the new name.

To specify the result file's header and footer, double click the corresponding button and
complete fields of the Header and Footer window.

To exclude a column from the exported data:

· Select the object for which you want to exclude the column;
· Clear the checkbox beside the column.

To rename a target file column, double click the corresponding caption or select the
caption and use F2, and edit it manually.

© 2016 SQL Maestro Group

 35 MS SQL Data Wizard Help

4.4 Setting general export options

Use this step to specify options to be applied to all exported data:

· Set format masks 35 to be applied to the data.

· Customize format specific options 36 of the selected file format.
· Select the number of records to be exported from each table: a fixed number or
all records.
· Specify actions to be executed after the export. To open the result files in the
associated program (MS Excel, Notepad, default browser, etc), check the Open
files box. To send the result files to the default printer, use the Print files
checkbox.

4.4.1 Adjusting data formats

This step allows you to customize formats applied to exported data. Edit the format
masks to adjust the result format in the way you need.

© 2016 SQL Maestro Group

 Data Export 36

4.4.2 Setting format-specific options

Each format supposes corresponding additional export options. Use the wizard option to
adjust export properties depending on the target file format you have selected earlier.
The following formats are at your disposal: Microsoft Excel 36 , Microsoft Excel 2007, CSV
38 , Text 38 , HTML 37 , XML 38 , Microsoft Word, Microsoft Word 2007, Microsoft Access,

OpenDocument Spreadsheet, OpenDocument Text, DBF, PDF, RTF, DIF, SYLK, and
LaTeX.

Microsoft Excel
The Data Format tab contains general options, which allow you to adjust the format for
each kind of Excel cells. This means that you can specify such parameters as font,
borders, filling color and method, etc. for each entity (such as data field, header, footer,
caption, data, hyperlink and so on) separately. Also it is possible to create styles to
make target Excel file be striped by columns or rows (the Styles tab).

The Extensions tab provides a possibility to add hyperlinks and notes to any cell of
target file. Click the Plus button to add a new hyperlink or note to target Excel sheet
and adjust its parameters. Click the Minus button to delete added hyperlink or note.

The Advanced tab allows you to define page header, page footer and title for target
Excel sheet.

© 2016 SQL Maestro Group

37 MS SQL Data Wizard Help

HTML
The Preview tab allows you to select the style of HTML file from a number of built-in
templates provided by the Templates combo box. You can choose any of these
templates, customize it by clicking on objects in the preview panel, and save it as a
custom template using the Save template button. Use the Load template button to load
previously saved custom templates from hard disk.

The Basic tab allows you to specify basic parameters of target HTML file, such as its
title, cascade style sheet options, etc.

The Multi-file tab provides you with a possibility to split target HTML file into several
separated files. This tab allows you to specify the record count for a single file, set an
option to generate an index HTML file, and add an ability of navigation between each
other to each of exported files.

The Advanced tab contains such HTML options as default font, background, cell padding
and spacing, etc.

© 2016 SQL Maestro Group

 Data Export 38

Text files
Set the Calculate column width options on if you want each column of target file to be
adjusted to the maximum number of characters in it. The Spacing option specifies the
number of spaces between columns in the target file.

CSV files
You can specify column separator and optional values quote character for the target file
on this step.

XML documents
Specify XML document encoding in the Encoding edit box and set the Standalone option
on if you wish the target document to be standalone.

© 2016 SQL Maestro Group

39 MS SQL Data Wizard Help

© 2016 SQL Maestro Group

 SQL Dump 40

5 SQL Dump
Along with such data management abilities as data import and export, MS SQL Data
Wizard allows you to dump data stored in Microsoft SQL tables and views. As the result
of the SQL Dump wizard activity you'll get a bunch of .sql files (each table or view is
dumped to a separate file) containing SQL statements to create a table and populate it.
To run the wizard, use the corresponding link at the Home page 11 .

To get SQL dump, complete the following steps:

· Set connection properties 13 of the database with tables to be dumped;
· Select objects to dump 41 ;
· Set destination files 42 ;
· Specify common SQL dump options 43 .

See also: Templates 14

© 2016 SQL Maestro Group

 41 MS SQL Data Wizard Help

5.1 Selecting source objects

To specify tables and views you want to dump, click the Add... button and pick the
tables in the Select objects dialog. To choose several tables at a time, select them
using Ctrl and Shift keys, then click Mark selected.

© 2016 SQL Maestro Group

 SQL Dump 42

5.2 Setting destination file names

The wizard creates a separate dump file for each selected table. By default, the file
names are %source_table_name%.sql, but you can modify names of files to be created
as well as include dumped tables in SQL scripts with another names, and also exclude
some fields or rename them.

© 2016 SQL Maestro Group

 43 MS SQL Data Wizard Help

5.3 Customizing common dump options

Use multi-row INSERT statements/Use separate single-row statements
Use these options to define type of INSERT statements to be generated. To create a
smaller dump file and speed up inserts when the file is reloaded, select the multi-row
inserts and specify the commits' frequency.

Statement syntax
This allows you to specify the SQL syntax the result script to be written on. This feature
allows you to restore the dump contents to a table in a different database server such
as MySQL, PostgreSQL, SQL Server, Oracle, Firebird, or SQLite.

Add the "Create table" statement to the top of the dump

By default the dump file contains only INSERT statements. To include the SQL definition
of the source table, check the corresponding box.

Output
Specify the output directory for the result files and the files encoding.

© 2016 SQL Maestro Group

 Blob Import 44

6 Blob Import
The wizard allows you to load BLOB data from external files to Microsoft SQL tables. For
this purpose the file names must contain the information on the record they need to be
placed to: the files need to be named in the same manner and include content of one or
several table columns that can uniquely identify each row.

To import BLOB files into Microsoft SQL tables, complete the following steps:
· Set connection properties 13 of the database you want to import BLOBs;

· Select the tables 19 where you want to import data;

· Specify BLOB files 46 to be imported.

© 2016 SQL Maestro Group

 45 MS SQL Data Wizard Help

6.1 Selecting tables for import

To specify tables where you want to import data, click the Add... button and pick the
tables in the Select objects dialog. To choose several tables at a time, select them
using Ctrl and Shift keys, then click Mark selected.

© 2016 SQL Maestro Group

 Blob Import 46

6.2 Setting files for import

To load BLOB files, specify the file name template using filed name tags (i.e. %id%, %
user%, where 'id' and 'user' are the fact table columns).

Example:
Suppose we have a table 'employee' with Non-Blob data as follows:
Id User
1 Max
2 July
To import D:\Data\1.jpg and D:\Data\2.jpg to a BLOB column of the table, specify D:
\Data as directory name and %Id%.jpg as file name template.

You can define the default root directory with %root_dir%, %table_name%, and %
field_name% tags.

© 2016 SQL Maestro Group

47 MS SQL Data Wizard Help

© 2016 SQL Maestro Group

 Blob Export 48

7 Blob Export
To export BLOB data stored in Microsoft SQL tables to external files, complete the
following steps:
· Set connection properties 13 of the database you want to export BLOB data from;

· Select tables 49 storing BLOB data to be exported;

· Specify file name templates 50 the BLOB data will be unloaded to.

© 2016 SQL Maestro Group

 49 MS SQL Data Wizard Help

7.1 Selecting tables with BLOB data

To specify tables with BLOB data to be exported, click the Add... button and pick the
tables in the Select objects dialog. To choose several tables at a time, select them
using Ctrl and Shift keys, then click Mark selected.

© 2016 SQL Maestro Group

 Blob Export 50

7.2 Setting file name templates

BLOB data from Microsoft SQL tables will be unloaded to external files with names
generated according to the specified template in a specified directory. The template can
contain values of table columns, e.g. %first_name%_%last_name%.png, Image_%id%.
jpg, etc.

You can define the default root directory with %root_dir%, %table_name%, and %
field_name% tags.

© 2016 SQL Maestro Group

51 MS SQL Data Wizard Help

© 2016 SQL Maestro Group

 Task Scheduler 52

8 Task Scheduler
MS SQL Data Wizard allows you to schedule data manipulation tasks. To import/export
data, generate ASP.NET scripts, or convert Microsoft SQL data and structure in
background mode at a preset time, you need to:

· Create a template 14 with a scenario of the process;

· Add a new task to the Task Scheduler.

Task Scheduler contains the list of scheduled tasks with the time and the result of the
last task execution, and the time of the next one. To open the page, use Task
Scheduler button at the Ribbon toolbar or the Show Task Scheduler link at the Home 11
page.

Adding a task

To add a task to the scheduler, use the Add Task button at the Ribbon toolbar or open
the Task Scheduler page and click Add Task at the Navigation bar or at the area's
popup menu. The Add Scheduled Task window will appear. Fill the window fields and
click OK.

Scheduled tool
Select the wizard for the task execution from the drop-down list.

Specify the names of template and log files.

Set the time for the task to be executed. To execute the task minutely, hourly, daily,
weekly or monthly, use the Each box and its drop-down list. To cease the task
executing at a specified date or after a certain number of the process repetitions, use
the Until and the For drop-down lists.

Select the Task type. Three options are available: Application schedule, System
schedule and Both types schedule. An application scheduled task is executed by the MS
SQL Data Wizard Agent itself i.e. it requires a user logon while system tasks can be
executed by the Windows scheduler service even without user logon.

© 2016 SQL Maestro Group

53 MS SQL Data Wizard Help

Editing an existing task

To edit the selected task, use the Edit Task item of the Navigation bar or double
click the task. The Edit Scheduled Task window will appear. The window
parameters are similar as the parameters of the Add Scheduled Task window.

Disabling/Enabling a scheduled task

To enable/disable scheduled tasks without deleting them, use corresponding links

at the Navigation bar.

Executing an existing scheduled task

If necessary, you can run the configured task any time, irrespective of the
specified time. To execute the selected task, use the Execute task item of the

© 2016 SQL Maestro Group

 Task Scheduler 54

popup menu or the Execute Task link of the Navigation bar

© 2016 SQL Maestro Group

 55 MS SQL Data Wizard Help

8.1 Using MS SQL Data Wizard Agent

MS SQL Data Wizard Agent allows you to execute application scheduled tasks in
background mode. To execute such a task, the Agent application must be run, in other
words, it is necessary to have a user to be logged on (system scheduled tasks do not
have such a limitation). See Task Scheduler 52 to learn more on MS SQL Data Wizard
tasks.

To enable the agent at each Windows startup, open Options 56 > Preferences and
check Run agent at startup.

© 2016 SQL Maestro Group

 Options 56

9 Options
MS SQL Data Wizard allows you to customize the way it works within the Options dialog.
To open the dialog, use the More button and select Options at the drop-down list.

The window allows you to customize the options grouped by the following sections:

· Application 57
General MS SQL Data Wizard options: environment style, confirmations, window
restrictions.

· Editors & Viewers 59

Customizing of all the SQL editors.

· Appearance 67
Customizing program interface - bars, trees, menus, etc.

It is a good idea to check through these settings before you start working with MS SQL
Data Wizard. You may be surprised at all the things you can adjust and configure!

© 2016 SQL Maestro Group

 57 MS SQL Data Wizard Help

9.1 Application
The Application section allows you to customize common rules of MS SQL Data Wizard
behavior. The section consists of several tab; follow the links to find out more about
each of them.

· Preferences 57

9.1.1 Preferences
Agent options
The option allows you to launch MS SQL Data Wizard Agent at each Windows startup for
executing tasks configured in Task Scheduler 52 , in background mode.

Tool bar and menu style

MS SQL Data Wizard allows you to select between Ribbon and Classic toolbars for your
convenience. A possibility to select color scheme is implemented for Ribbon toolbar.

You can also cancel/allow the confirmation dialogs with the Confirmations options.

© 2016 SQL Maestro Group

 Options 58

© 2016 SQL Maestro Group

 59 MS SQL Data Wizard Help

9.2 Editors & Viewers

The Editors & Viewers section allows you to set the parameters of viewing and editing
the SQL statements within MS SQL Data Wizard.

· General 59

· Display 60

· SQL highlight 61

· PHP highlight 63

· XML highlight 62

· Code Insight 64

· Code Folding 65

9.2.1 General
If the Auto indent option is checked, each new indention is the same as the previous
when editing SQL text.

Insert mode
If this option is checked, insert symbols mode is default on.

Use syntax highlight

Enables syntax highlight in the object editor window.

Always show links

If this option is checked, hyperlinks are displayed in the editor window. To open a link
click it with the Ctrl button pressed.

Show line numbers

If this option is checked, line numbers are displayed in the SQL text editor window.

Use smart tabs

With this option on the number of tab stops is calculated automatically, depending on
the previous line tab.

Tab Stops
Defines the tab length, used when editing text.

Undo Limit
Defines the maximum number of changes possible to be undone.

© 2016 SQL Maestro Group

 Options 60

9.2.2 Display
You can disable/enable the right text margin and the gutter of the editor area, set the
position of the right text margin as Right margin, and the Gutter width.

Use the Editor font and Font size to define the font used in all program editors and
viewers. The panel below displays the sample of the selected font.

© 2016 SQL Maestro Group

 61 MS SQL Data Wizard Help

9.2.3 SQL highlight

Use the SQL highlight item to customize syntax highlight in all SQL editors and viewers.
Select the text element from the list, e.g. comment or SQL keyword and adjust its
foreground color, background color and text attributes according to your preferences.

© 2016 SQL Maestro Group

 Options 62

9.2.4 XML highlight

Use the XML highlight item to customize XML syntax highlight for the text representation
of BLOBs. Select the text element from the list, e.g. attribute or attribute value and
adjust its foreground color, background color and text attributes according to your
wishes.

© 2016 SQL Maestro Group

 63 MS SQL Data Wizard Help

9.2.5 PHP highlight

Select the text element from the list (e.g. Keyword, Comment, Identifier), and adjust its
foreground color, background color and text attributes according to your wishes.

© 2016 SQL Maestro Group

 Options 64

9.2.6 Code Insight

You can disable/enable the code completion with the corresponding option and also set
the time is appears as Delay, and case of words inserted automatically.

© 2016 SQL Maestro Group

 65 MS SQL Data Wizard Help

9.2.7 Code Folding

The Code Folding item group makes it possible both to view the whole text and to divide
it into logical parts (regions). Each part can be collapsed and extended. In extended
mode the whole text is displayed (set by default), in collapsed mode the text is hidden
behind one text line denoting the first line of the collapsed region.

© 2016 SQL Maestro Group

 Options 66

You can enable/disable code folding in SQL editors and viewers and customize the colors
of its items.

© 2016 SQL Maestro Group

 67 MS SQL Data Wizard Help

9.3 Appearance
The Appearance section allows you to customize the application interface style to your
preferences.

Use the Scheme name box to select the interface scheme you prefer: Office XP style,
Windows XP native style , etc. You can create your own interface schemes by
customizing any visual options (Bars and menus, Trees and lists, Edit controls, Check
boxes, Buttons, etc.) and clicking the Save As button. All the customized options are
displayed on the sample panel.

· Bars and menus 67

· Trees and lists 68

· Edit controls 69

· Check boxes 70

· Buttons 71

· Page controls 72

· Group boxes 73

· Splitters 74

9.3.1 Bars and menus

Use the Bars and menus item to customize MS SQL Data Wizard toolbars style and
menus animation.

The item allows you to select Bar style and menu animation from the corresponding
drop-down lists and to enable or disable such options as sunken border, F10 key for
opening menu, viewing full menus after delay, flat close buttons, gray-scale images.

© 2016 SQL Maestro Group

 Options 68

9.3.2 Trees and lists

Use the Trees and lists item to select various tree view options. Use the item to select
standard, flat or ultraflat styles, check or uncheck the hide selection, hide focus
rectangle and native style options.

© 2016 SQL Maestro Group

 69 MS SQL Data Wizard Help

9.3.3 Edit controls

Use the Edit controls item to customize the appearance of different MS SQL Data Wizard
edit controls. The tab allows you to select the edit controls border style, button style
and transparency, enable/disable hot tracks, shadows, native style and customize
edges. It is also possible to define samples for the text edit, button edit and combo box
controls.

© 2016 SQL Maestro Group

 Options 70

9.3.4 Check boxes

The Check boxes item allows you to customize the appearance of check boxes and radio
buttons. The tab allows you to customize the appearance of check boxes: set border
style, enable/disable hot tracks, shadows, native style. It is also possible to define
samples for check boxes and radio buttons.

© 2016 SQL Maestro Group

 71 MS SQL Data Wizard Help

9.3.5 Buttons
Use the Buttons item to customize MS SQL Data Wizard buttons. The tab allows you to
adjust the appearance of buttons and define sample buttons as well.

© 2016 SQL Maestro Group

 Options 72

9.3.6 Page controls

The Page controls item allows you to customize the style of all MS SQL Data Wizard
page controls. The tab allows you to select tab styles, enable/disable hot track, multi-
line pages and native style.

© 2016 SQL Maestro Group

 73 MS SQL Data Wizard Help

9.3.7 Group boxes

Use the Group boxes item to customize all MS SQL Data Wizard group boxes according to
your preferences. Use tab to apply styles for group boxes, enable/disable native style
and define samples.

© 2016 SQL Maestro Group

 Options 74

9.3.8 Splitters
Use the Splitters item to customize all MS SQL Data Wizard splitters according to your
preferences. Use the tab to select hot zone style (Windows XP task bar, Media Player 8,
Media Player 9, Simple or none) and specify the Hot zone drags a splitter option.

© 2016 SQL Maestro Group

75 MS SQL Data Wizard Help

© 2016 SQL Maestro Group

 Index 76

Index -I-
Import Data Wizard
-A- Adding SQL queries 20
Customizing common options 27
Appearance Options Data Format 25
Bar and menus 67 Map builder 24
Buttons 71 Overview 16
Check boxes 70 Selecting files to import from 21
Edit controls 69 Selecting objects to import to 19
Group boxes 73 Selecting source format 17
Page controls 72 Setting fields correspondence 23
Splitters 74 Installation instructions 3
Trees and lists 68

-L-
-B- License Agreement 5
BLOB Export Wizard 48
Selecting tables for export 49
Setting file name templates 50 -M-
BLOB Import Wizard 44 MS SQL Data Wizard
Selecting tables for import 45 Agent 55
Setting files for import 46 Command line options 15
Connection options 13

-E- Getting Started

Installation 3
11

Editor & Viewer Options License agreement 5

Code Folding 65 Overview 1
Code Insight 64 Registration 4
Display 60 System requirements 2
General 59 Task scheduler 52
PHP highlight 63 Templates 14
SQL highlight 61

EULA
XML highlight 62
5
-O-
Export Data Wizard Options 56
Adjusting data formats 35 Appearance 67
Overview 29 Application 57
Selecting fields 34 Application preferences 57
Selecting file format 30 Editor & Viewers 59
Selecting objects to export data from 31
Setting format-specific options 36
-P-
Purchase MS SQL Data Wizard 4

© 2016 SQL Maestro Group

 77 MS SQL Data Wizard Help

-R-
Registration 4

-S-
SQL Dump Wizard
Customizing common options 43
Overview 40
Source objects 41
Target files 42
System requirements 2

© 2016 SQL Maestro Group