Data Source Type:
|Any SQL Based Database|
This article covers how to use BuildSQLSchema to connect faster to a subset of tables of an SQL based data source. For more information, see the article on the new Connection Editor here.
AutoTag includes the program BuildSqlSchema.exe. You can use this program to create a schema file by pointing it at a database. You can then connect to this file as a data source.
When do I use this Feature?
There are a few cases where using BuildSQLSchema is a better way to connect to a database:
- 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 joins. AutoTag knows a path to join two tables, but it has no way of determing the best path.
- The database does not have complete metadata; it may be missing descriptions or PK:FK joins.
High level outline of the process
1. First, build the schema.xml file.
- The first part of the process is to build your schema.xml file.
- Then, delete from the schema file any tables, views, stored procedures, etc. that you do not wish to work with.
2. Next, connect to this schema file as a datasource.
a. Once built, you want to use it as a data source.
3. Finally, you are connected to a sub-set of a larger database and it was much faster than connecting to the database as a whole.
Connecting Your Template to a Schema File as a Data Source
Follow these steps in order to create and connect your template to a Schema file.
Step 1 – Create your 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.
Under Schema File enter 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 change to allow you to enter the appropriate information.
You may optionally test the connection. 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.
Once complete, click Close and your schema file is built and ready to use.
Your schema file contains all of the tables in the database you’ve selected to build the file against. If you wish to omit some of those, now is a good time to delete them from the schema file.
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. Once on the AutoTag Manager tab, click on the upper half of the Data Sourcesbutton. This will open the data source Connection Editor.
Note that if you click on the bottom half, you will get a dropdown menu which lists data sources that are already connected; this may be blank.
Next, enter the information for the Schema file you just created.
1. Click on the Sql Schema datasource option in the New tab.
2. Create a name for your data source.
3. Type in or browse to the schema file you created.
4. Click Connect, and then Test your connection. If all parameters and credentials are correct and a connection is made, you will receive a successful notification. Clicking Close will save your information.