This article covers how to use the BuildSQLSchema tool to connect to a subset of tables of a SQL database. Even though you are creating an XML Schema file, your queries will still use SQL.
AutoTag includes the program BuildSqlSchema.exe in the AutoTag Manager Tab. You can use this program to create a schema file by pointing it at a database, and then connect to this file as a data source in the Data Source Connection Editor.
When do I use this tool?
There are a few cases where using BuildSQLSchema is a better way to connect to a database:
- If you have a template that is taking a very long time to generate, you can use this tool to create a pared down version to decrease the run time for that template.
- If you only want to show a subset of the tables and/or columns in the database.
- Make sure to delete the ones your report designers will not use from the schema file before using it. (This does not stop designers from using those tables/columns in a select, it merely does not display them in the tag editor.)
- Hand code the PK:FK (Primary Key:Foreign Key) Joins. AutoTag knows a path to join two tables, but it has no way of determining the best path.
- The database does not have complete metadata; it may be missing descriptions or PK:FK joins.
- You can also use this tool if you need to provide your template and sample data to our team to test, or for you to teach your users with a smaller data set.
What you will need to establish this connection
- A SQL database (MS SQL, MySQL, Oracle, PostgreSQL, etc.) to which you have an authorized login
High level outline of the process
1. First, build the schema.xml file, then delete from the schema file any tables, views, stored procedures, etc. that you do not need.
2. Next, connect to this schema file as a data source using the Connection Editor in AutoTag.
Connecting Your Template to a Schema File as a Data Source
Step 1 – Create Schema.xml file.
First, from the AutoTag Manager tab click on the Build SQL Schema button.
This will bring up the Build SQL Schema dialog box below.
Create a name and location for the file you are going to create. In the Schema File field, type or paste the full path and name for the schema file you wish to build. (It is OK if the file does not exist, this procedure will create it.)
Then, select the database vendor from the Vendor drop down list. For this example we will use SQL Server Database.
Once you select the vendor, the dialog box will take on the familiar Datasource Connector look. Enter the appropriate information, then hit Test to make sure that the connection works. Click Build Schema to build the schema file.
You will see the SQL Schema Status window (shown below) letting you know when the process is complete.
Your schema file contains all of the tables in your database. Once complete, click Close and your schema file is built and ready to use.
Pare down your file
If you want remove tables, views, columns, etc., simply open the XML file with a product like Notepad++ and delete them directly from the .xml file. Make sure to save your changes.
Step 2 – Connect to your SQL Schema file as a data source
Now, connect to the SQL Schema file you’ve just created. From MS Word, Excel or PowerPoint, navigate to the AutoTag Manager tab in the ribbon.
Once in the AutoTag Manager tab, click on the Data Source button. This will open the DataSources dialog box. Click Add to bring up the Datasource Connector.
The Datasource Connector appears. Select SQL Schema Datasource as shown below for versions 14 and older, or 15 and newer.
Add a Name for this connection.
Finally, fill in the full path to the Schema.xml file you’ve created and Test the Connection.
Once the connection succeeds, click Add, and then Close or OK.
Step 3 - Use your data!
Once your template is connected to your SQL Schema file, build your tags as you would normally do when using a SQL data source.