xSQL Documenter Help

• Introduction
• Upgrading from trial to full version
• Feedback and Support
• What does xSQL Documenter do?
• Requirements
• Supported Platforms
• Quick Start
• Command line arguments
• Advanced features
  • Descriptive comments
  • Editing Extended Properties
  • Exclusion Lists
  • xtypes
  • Data models
  • External Objects
  • Custom navigation hierarchies
  • XML Comments
  • Custom Comments
  • Sample config file
• Branding

Introduction

Upgrading from trial to full version

Feedback and Support

What does xSQL Documenter do?

• SQL Server 2000/2005/2008
• Oracle 9i and above
• DB2 8.2 and above*
• Sybase 12.0.0.4 and above
• Sybase SQL Anywhere 10.0 and above
• PostgreSQL 8.0 and above
• MySQL 5.0 and above
• Analysis Server 2005/2008
• Access 97 and above
• Informix IDS 10 and above
• VistaDB 3.0 and above
• ENEA Polyhedra 7.0 and above

• Extending the documentation to cover external objects, such at Java packages, COM, .NET assemblies, Web Services, scripts, etc. You must specify your external objects and how they depend on the internal database objects in separate XML files.
• Specify an exclusion list to exclude certain objects you specify from appearing in the documentation. This is useful if there are certain stored procedures that you don't want to expose to the consumers of the documentation. You might not want everyone reading the code for sp_GiveEmployeeBigFatRaise, for example.
• Custom navigation hierarchies in the chm navigation tree.
• Collecting various properties of the server the database resides on.
• Documents all SQL jobs on the server, if the user has permissions to execute msdb..sp_help_job and its relatives.
• Shows a call tree for sprocs and udfs that call other sprocs and udfs.
• Documents SQL Server 2005 objects such as Assemblies, Symmetric and Asymmetric Keys, Certificates, XML Schema Collections, and the like.

Requirements

• The .NET framework (version 2.0) must be installed on the machine where you run the xSQLDocumenter.exe. Get it here if you don’t have it: http://msdn2.microsoft.com/en-us/netframework/aa731542.aspx.
• To generate specs for Analysis Services specs, you must install Analysis Management Objects (AMO). AMO is the managed code library that wraps all of AS 2005 (it’s used heavily by SSMS, so if you have the SQL Server 2005 client tools installed, you already have it). You can get AMO here if you don’t have it: http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en (search for “AMO” on that page).
• You do not need to have the .NET framework installed on the server where your database resides.
• If you want to compile the output into chm files, you need the Microsoft HTML Help Workshop installed. You may already have it installed. xSQLDocumenter will detect if you have it installed or not. If you don't have it, it will ask you if you want to install it. If you say yes, it will download and install it. Alternatively you can get it yourself from here: http://go.microsoft.com/fwlink/?LinkId=14188.
• To generate documentation for databases on MySQL 5.0 and above, you must have MySQL Connector/.NET installed on the computer where xSQLDocumenter runs. You can get it here: http://dev.mysql.com/downloads/connector/net. After you install it, you need to copy MySql.Data.dll to the directory where xSQLDocumenter.exe is located.
• To generate documentation for Oracle schemas, you need to have an Oracle OLE DB driver installed on the computer where xSQLDocumenter runs. You can download a driver from Oracle here: http://www.oracle.com/technology/software/tech/windows/ole_db/index.html.
• To generate documentation for Sybase ASE databases, you need to install AseClient, a .NET data provider for Sybase ASE. This component is available in the Sybase ASE SDK, and also ships with ASE itself: https://sybase.subscribenet.com.

Supported Platforms

• SQL Server 2000/2005/2008
  
  Example1
  xSQLDocumenter.exe /c "server=myserver; database=pubs; user id=myuser; pwd=mypass" /o "c:\my db specs" /x 1 /t 120 /q 1
  
  In this example, the output would be placed in c:\my db specs\MYSERVER.pubs, parsing of XML comments is turned on, timeout is set at 120 sec, and quiet mode is turned on.
  
  Example2
  xSQLDocumenter.exe /c "server=myserver;database=mydb;user id=myuser;pwd=mypass:server=(local);database=pubs;Trusted_Connection=yes;" /n MyDocs
  
  In this example, documentation will be generated from two databases and placed into a single MyDocs.chm file.
  
  Example3
  xSQLDocumenter.exe /b "c:\my db files\pubs.xml,c:\my db files\northwind.xml" /o "c:\my db files\output" /n NorthwindAndPubs /r 0
  
  In this example, pubs.xml and northwind.xml will be parsed and a chm named NorthwindAndPubs.chm will be produced and placed in c:\my db files\output. No pie charts for table sizes will be drawn. No queries to any SQL servers are performed.
  
  
• Analysis Server 2005/2008
  
  You can document any Analysis Server 2005/2008 database using xSQLDocumenter. To do it, use the /C command line argument. For example:
  
  xSQLDocumenter.exe /C "Data Source=SUMMER; Initial Catalog=AmoAdventureWorks" /o c:\analysis_server_specs
  
  You can also combine the spec for a SQL Server database and a Analysis Server database into a single chm:
  
  xSQLDocumenter.exe /C "Data Source=SUMMER; Initial Catalog=AmoAdventureWorks" /c server=SUMMER;database=AdventureWorks;trusted_connection=yes; /o c:\analysis_server_specs
  
  
• MySQL
  
  You can document any MySQL 5.0 and above database using xSQLDocumenter. To do it, use the /my command line argument. xSQLDocumenter uses MySQL Connector/NET for connecting to MySQL, which you can download here: http://dev.mysql.com/downloads/connector/net. Note that MySql.Data.dll must reside in the same directory as xSQLDocumenter.exe. MySql.Data.dll is the assembly that implements the MySQL Connector/NET library. Once you install Connector/NET, just copy MySql.Data.dll to where xSQLDocumenter.exe resides.
  
  Example: xSQLDocumenter.exe /my "server=localhost; database=sakila; uid=someuser; pwd=somepass" /o c:\mysql_specs
  
  
• Oracle
  
  You can document any Oracle 9i and above schema using xSQLDocumenter. To do it, use the /O command line arg, passing an OLEDB connection string. All objects in the schema for the user that you connect as will be documented. For example:
  
  xSQLDocumenter.exe /O "Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=OE;Password=OE" /o c:\oracle_specs
  
  By default when documenting Oracle databases, only the objects in the default schema of the user specified in the connection string are documented. If you want to objects in a schema other than the default schema of a given user, you can use the /OS flag. In the following example, all objects in the SYS and SYSTEM schemas will be documented. In the case of SYS, the credentials of PowerUser1 are used, and for SYSTEM, the credentials of PowerUser2 are used.
  
  xSQLDocumenter.exe /O "Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=PowerUser1;Password=asdf1234:Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=PowerUser2;Password=qwerty7890" /OS SYS:SYSTEM /o c:\oracle_specs /n sys_and_system
  
  
• DB2
  
  You can use xSQLDocumenter to document any DB2 database. It has been thoroughly tested on DB2 8.2, but may work on earlier versions as well.
  
  To document a DB2 database, use the /DB2, /DB2U, and /DB2P command line arguments, described in the command line arguments section. xSQLDocumenter uses OLEDB to connect to DB2, so you must specify an OLEDB connection string in the /DB2 command line argument.
  
  For example:
  
  xSQLDocumenter.exe /DB2 "Provider=IBMDADB2;Database=SAMPLE;Hostname= SUMMER;Protocol=TCPIP;Port=50000;Uid=username;Pwd=password" /DB2U username /DB2P password
  
  Of course you can generate docs for databases in other DBMS in the same chm by using other switches. This command line would generate a DB2 and SQL Server spec in the same chm, with 20 sample rows from each table and view included in the docs:
  
  xSQLDocumenter.exe /c "server=SUMMER;database=AdventureWorks;trusted_connection=yes;" /DB2 "Provider=IBMDADB2;Database=SAMPLE;Hostname=SUMMER;Protocol=TCPIP;Port=50000; Uid=username;Pwd=password" /DB2U username /DB2P password /R 20
  
  
• PostgreSQL
  
  You can document any PostgreSQL 8.0 and above database using xSQLDocumenter. To do it, use the /PG command line argument. xSQLDocumenter uses ODBC for connecting to PostgreSQL, so you need to install an ODBC driver for PostgreSQL on the client machine where xSQLDocumenter runs. You should already have one installed if you have the PostgreSQL client tools installed.
  
  Below is a sample of how to document a PostgreSQL database. The /PG flag contains a colon-separated list of connection strings, and the /PGDUMP flag specifies the path to pg_dump.exe, which is used to extract DDL for the objects in your database. If you don’t pass /PGDUMP, no DDL will appear in your docs.
  
  xSQLDocumenter.exe /PG "Driver={PostgreSQL UNICODE};Server=localhost;Port=5432; Database=dellstore;Uid=postgres;Pwd=password;" /PGDUMP "C:\Program Files\PostgreSQL\8.2\bin\pg_dump.exe" /o c:\postgresql_specs
  
  
• Sybase ASE
  
  You can document a Sybase ASE 12.0.0.4 and above database using xSQLDocumenter. To do it, use the /SY command line argument. xSQLDocumenter uses Sybase.Data.AseClient.dll for connecting to Sybase ASE, so you need to install it on the client machine where xSQLDocumenter runs. You should already have it installed if you have the Sybase client tools installed. If you don't have it installed, you may need to run the Sybase ASE installer again.
  
  Below is a sample of how to document a Sybase ASE database. The /SY flag contains a colon-separated list of connection strings, and the /DGPATH flag specifies the path to ddlgen.bat, which is used to extract DDL for the objects in your database. If you don't pass /DGPATH, no DDL for tables will appear in your docs. If you pass /DGPATH, you must also pass /DGUSER, /DGPASS, and /DGSERVER. These are the other arguments that are passed to ddlgen.bat which specify a user, password, and server to connect to.
  
  xSQLDocumenter.exe /SY "Data Source=SUMMER;Port=5000;Database=pubs3;Uid=sa;Pwd=" /DGPATH "C:\sybase\ASEP\bin\ddlgen.bat" /DGUSER sa /DGPASS abcd?1234! /DGSERVER summer:5000 /o c:\sybase_specs
  
  You may initially have some trouble getting xSQLDocumenter to connect to your Sybase ASE server using AseClient. If so, try this:
  • First, make sure you have a copy of Sybase.Data.AseClient.dll on your computer. Often it will be located here: %SYBASE%\DataAccess\ADONET\dll. You should also find sybdrvado115.dll, msvcp71.dll and msvcr71.dll in that same directory.
  • Copy sybdrvado115.dll, msvcp71.dll and msvcr71.dll to the C:\WINDOWS\system32 directory. You can find them in the same directory as Sybase.Data.AseClient.dll.
  • Copy Sybase.Data.AseClient.dll into the directory where xSQLDocumenter.exe is located.
  After following these steps, xSQLDocumenter should be able to document Sybase ASE databases.
  
  
• Sybase SQL Anywhere
  
  You can document a Sybase SQL Anywhere 10.0 and above database using xSQLDocumenter. To do it, use the /SA command line argument, or enter an ODBC connection string to a SQL Anywhere database in the GUI. If using the command line, you must pass an ODBC connection string in the /SA argument. Example:
  
  xSQLDocumenter.exe /SA "Uid=dba;Pwd=MyPassword;Dsn=SQL Anywhere 10 Demo;" /o c:\sql_anywhere_specs
  
  
• Informix
  
  You can document any Informix 9.4 and above database using xSQLDocumenter. To do it, use the /IFX command line argument.
  
  Below is a sample of how to document an Informix database. The /IFX flag contains a colon-separated list of connection strings. The /IFXDBS flag specifies the path to dbschema.exe on your system – it is used to generate DDL for the tables in your database. The /IFXCMD flag specifies the path to a batch file that defines various environment variables such as %INFORMIXSERVER%, etc. Without these environment variables being defined, dbschema.exe will not work properly. If you don't pass /IFXDBS or /IFXCMD, DDL for tables will not appear in your docs.
  
  xSQLDocumenter.exe /IFX "Provider=Ifxoledbc.2;Password=Zebra!01;User ID=informix; Data Source=stores_demo@summer; Persist Security Info=true;" /CLC "C:\Program Files\IBM\Informix\summer.cmd" /IFXDBS "C:\Program Files\IBM\Informix\bin\dbschema.exe" /o c:\informix_specs
  
  
• MS Access
  
  To generate documentation for MS Access databases from the command line, specify an OLEDB connection string to each mdb file. If documenting more than one mdb, separate the connection strings by commas:
  
  xSQLDocumenter.exe /o c:\access_specs /A "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\msaccess\AccWebFAQ.mdb,Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\msaccess\Sample.mdb"
  
  
• VistaDB
  
  You can document VistaDB 3.0 databases using xSQLDocumenter. To do it, use the /VDB command line argument. VistaDB is an in-process database engine implemented in managed code, available here: www.vistadb.net.
  
  Below is a sample of how to document a VistaDB database. The /VDB flag contains a list of VistaDBConnection connection strings, separated by the chart you specify in the /delim argument. If /delim is not passed, a comma is assumed.
  
  Finally, xSQLDocumenter requires that a copy of VistaDB.NET20.dll is located in the same directory as xSQLDocumenter.exe. This DLL implements the VistaDB engine and is located here in a default install of VistaDB: C:\Program Files\VistaDB 3.0\Frameworks\NET 2.0\Runtime.
  
  xSQLDocumenter.exe /VDB "Data Source=C:\Program Files\VistaDB 3.0\Data\DBDemos.vdb3; Open Mode=SharedReadOnly" /o c:\vistadb_specs
  
  
• Polyhedra
  
  Starting with xSQLDocumenter 3.7, you can document Polyhedra databases using xSQLDocumenter. To do it, use the /POLY command line argument. Polyhedra is an in-memory database for high-availability systems made by Enea Software.
  
  Below is a sample of how to document a Polyhedra database. The /POLY flag contains a list of OLEDB connection strings. The /CLC flag specifies the path to clc.exe on your system – it is used to generate DDL for the tables in your database. If you don't pass /CLC, DDL for tables will not appear in your docs.
  
  xSQLDocumenter.exe /POLY "Provider=PatOleDB.PatOleDB.1;Data Source=localhost:8001" /CLC "C:\Polyhedra\poly7.0\win32\i386\bin\clc.exe" /o c:\poly_specs

Quick Start

• Click on xSQLDocumenter.exe to launch the GUI. You can also run it from the command line passing command line arguments, see the command line arguments section.
• Enter the connection string(s) to the database(s) you want to document.
• Hit the Go! Button. As it does its work, it will print out progress. You can cancel while it's running by hitting the Cancel button.
• When it's finished, click on the “View .chm” button to see your documentation. By default the output is placed in a folder called “output” in the same directory as xSQLDocumenter.exe.

Command line arguments

Advanced features

• Under the <exclude> element, you can have any number of <server> elements.
• Under the <server> elements, you can have any number of <database> elements.
• Under the <database> elements, you can have any number of <object> elements each needs an xtype and name attribute to say what the type of object is, and what its name is.
• For objects that have related code, such as views, sprocs, and udfs, you can use the excludeCodeOnly=”true” attribute to say you want to keep the object in the documentation, but don’t include the code for it.
• On all elements, the “name” attribute is required.
• The values of the “name” attributes on all elements are case sensitive.
• The <exclude> element must appear as a child of the root <xSQLDocumenter> element in the config file.
• If you want to exclude the code for ALL objects in the spec, put excludeCodeOnly=”true” as an attribute on the top level <exclude> element.

• Under the <models> element, you can have any number of <model> elements.
• Each <model> element describes a particular data model diagram:
  • The "name" attribute is required and specifies the name of the data model.
  • The "iconsPerRow" attribute is optional and specifies the maximum number of icons (there is one icon for each object) will fit on a single row. In the diagram, icons are placed on a grid, so this value says how many icons could appear in a single row if all the positions in the row were occupied. If you don’t specify it, the default value is 8.
  • The "seed" attribute is an integer that is used to seed a random number generator that is used in the icon placement algorithm that xSQLDocumenter uses. The default value is 1. If you pass a different value, the positions of the icons will change. If a particular placement doesn't appeal to you, try changing the seed and see how things change.
  • The "allowOverlap" attribute specifies if you want to allow the lines in the diagram that represent links between objects to overlap or not. A value of 1 means allow, 0 means don't allow overlap. The default is 0.
  • The "horizontalSpace" attribute specifies how much space you want to leave between icons in the horizontal direction. Default is 80.
  • The "verticalSpace" attribute specifies how much space you want to leave between icons in the vertical direction. Default is 80.
  • The "maxLabelLength" attribute specifies how many characters of an icon's name you want to show as its label. The default value is 8. That means that if your objects name is "MyLongNamedObject" it would appear as "MyLongNa…" if you leave the default. However, when you hover over the icon with the mouse, you will see the full name of the object.
• Under each <model> element, you may have any number of <server> elements. This means you can have data model diagrams that incorporate database objects from different databases on different servers.
• Under each <server> element, you may have any number of <database> elements.
• Under each <database> element, you can have any number of <object> elements. The <object> element is used to specify any database object together with the xtype attribute, and also any external objects.
• The "name" attribute is required on all elements except for <models>
• The "xtype" attribute is required on <object> elements.
• The value of the "name" attribute is case sensitive in all cases.
• The <models> element must appear as a child of the root <xSQLDocumenter> element in the config file.
• The "type" attribute is required on the server element. Allowed values are:
  • SqlServer
  • AnalysisServer
  • Oracle
  • MySQL
  • PostgreSQL
  • DB2
  • Sybase
  • Access
  • Informix
  • VistaDB
  • Polyhedra

• First, you must declare each type of external object that you want to represent in the <definitions> element of the config file.
• Second, for each external object that you want to include, you must compose some html that you want to be displayed for that object, as well as define the dependencies of that object on all other objects.

• Under the <definitions> element, you may have any number of <object> elements.
• Each <object> element declares a type of external object.
  • The type of object is specified by the value of the "xtype" attribute. The value may be anything you want, except that they must all be different (case-insensitive), and that you can't use any that are already listed in the xtypes section.
  • If you use the xtype="MyType" then you cannot use xtype="mytype" for a different object because the xtype is used for naming htm files that xSQLDocumenter produces, and file names are case-insensitive in Windows! You wouldn"t want to do that anyway, because that would be confusing. So don't do it.
  • The "name" attribute is a human readable name for your external object type. It can be whatever you want.
  • The "plural" attribute is a human readable name for the plural form of whatever you put for the "name" attribute. Usually you just add an "s".
  • The "color" attribute is used to specify a color for icons that appear in diagrams (dependency graphs and data model diagrams). It is a hex RGB value, and needs a hash sign on the front, as in the examples above.
• The <definitions> element must appear as a child of the root <SqlSpec> element in the config file.

To note:
• The root element of an external object is <customObjects>
• Under <customObjects>, you may have any number of <object> elements.
  • Each <object> element must have an "xtype" attribute and a "name" attribute, both values case sensitive.
  • The value of the "xtype" attribute must match one of the xtypes in the <definitions> element in your config file.
  • The value of the "name" element can be anything you want.
  • The child elements of <object> make up some html, whatever you want. This will be used to make an html page describing the external object in the documentation.
• Under <customObjects>, you may have any number of <dependency> elements. Each one describes a dependency that your object has on some other object, or vice versa. This other object could be another external object, or it could be a database object.
  • The "objName" attribute contains the name of an object.
  • The "xtype" attribute contains the xtype of the object referred to by the "objName" attribute.
  • if xtype and objName identify an object in a database, an not an external object, you must further specify these attributes:
    • The "database" attribute specifies the name of the database that the object identified by objName lives in.
    • The "server" attribute specifies the name of the server that the database identified by the "database" attribute.
    • The "serverType" says what type of server it is. It can have these values:
      • SqlServer
      • Oracle
      • DB2
      • Sybase
      • MySQL
      • PostgreSQL
      • Access
      • AnalysisServer
  • The "dependentObjectName" attribute contains the name of an object that depends on the object referred to by "objName".
  • The "dependentObjectType" attribute contains the xtype of the object referred to by "dependentObjectName".

• Under <customContents>, you may have any number of <item> elements. Each of these <item> elements will become a root node in the navigation tree in the chm file.
• The "exclusive" attribute on the customContents node specifies whether or not to suppress the built-in table of contents produced by xSQLDocumenter. If you specify "true" in this attribute, the only nodes that will appear in your table of contents will be those items specified in the customContents block.
• Under any <item> element, you may have any number of <item> elements, and so on, forever, as deep as you like.
  • Each <item> element must have a "name" attribute, which is used to name the node in the navigation tree.
  • Each <item> element must have a "href" attribute, which is the location (relative or absolute) of an htm that will be displayed if the node corresponding to the current <item> is clicked on in the navigation tree in the chm.
    • If href is an empty string, then there will be no page displayed for that node.
    • If href is a relative path, then it is relative to the output directory you specified with the /o flag.
    • If href is an absolute path, well, then it's absolute and that's that.

• if the package body is not wrapped, it is parsed for xml comments and the package specification is ignored.
• if the package body is wrapped and the package specification is not wrapped, then the package specification is parsed for xml comments.
• if both body and specification are wrapped, neither is parsed.

• Define what objects make up your data model diagrams.
• Define the types and various properties of your external objects.
• Define an exclusion list to keep certain database objects from appearing in the documentation.
• Define a custom hierarchy of items to appear in the left hand navigation tree of the chm.
• Define a html snippet for branding your generated chm.
• Define a list of custom comments for table columns, indexes, constraints, or triggers, or for sproc and udf parameters.