SQLHelp

Welcome to the SQLHelp SQL documentation tool from Pikauba software. This tool allows the SQL developer the ability to produce professional documentation that can be viewed using the Microsoft HTML Help System. By using the HTML Help format, SQLHelp can generate documentation in an industry standard form that should be familiar to any developer working with Microsoft tools.

The rich feature set allows the developer a simplified method for creating the documentation. The tag replacement capability permits "document as you go" methodologies.

The benefits of SQLHelp are numerous and range from; decoupling the SQL and object development, to facilitating coding standards adherence, to providing production quality documentation for SQL database administrators.

Important Note: Since this documentation is available for separate download from the SQLHelp product, it contains its own licensing agreement. If you are running this documentation from within SQLHelp itself, you can safely ignore this section, otherwise, please view the licensing agreement here.

© 2001-2004 Pikauba Software. All rights reserved.


SQLHelp - Benefits

The SQLHelp system offers numerous benefits to developers, database administrators, professional services firms, and those generally interested in documenting the queries running on their database.

SQLHelp is designed to document the stored procedures contained in batch scripts. To minimize the impact on a database, SQLHelp is intended to run offline from the actual database by parsing the batch scripts used to define the stored procedures in the system. If your application requires documenting procedures where no batch script currently exists, please contact Pikauba Software.

The use of the SQLHelp documentation tool, and its output offer several benefits including:

  • The ability to document procedures in raw form. Raw form documentation refers to the parsing of a procedure where no comment tags currently exist. In this mode, developers are given the ability to see what the external interface to the procedure is, but lacks any description of its purpose. This type of documentation is the default form generated when run against pre-existing SQL.
  • Isolation of non-SQL developers from any requirement to read the SQL of a procedure to determine what the required inputs and expected outputs might be.
  • The ability to provide rich descriptions of the purpose behind procedures, their parameters, and output.
  • The ability to provide database administrators with a listing of procedures, that return data, modify data, use cursors or are transactional. With this information, administrators can better evaluate database performance.
  • The ability to determine if SQL coding conventions are being followed. E.g. if a project must use transactions, but a data modification procedure exists that isn't transactional.
  • The ability to determine if multiple result sets are being used in a query.
  • The ability to provide examples of the intended use behind a procedure.
  • Providing a method to isolate the query development from the critical path for projects. In object oriented design an interface contract is defined between clients and servers. By allowing the database interface to be defined, actual database query development can occur in parallel with other aspects of a project.
  • The ability to define project specific groupings of procedures. This allows the consumer of the documentation the ability to see other procedures in the context of their peers.
  • Clearly indicates the procedure owner, if designated in the CREATE PROCEDURE declaration.


SQLHelp - Features

The SQLHelp system contains a large number of features to assist development staff in producing production quality SQL documentation. Providing the current development practice entails placing all SQL into stored procedures, SQLHelp can produce documentation of queries running on a database.

Documentation items include the ability to:

  • Read all parameters of a stored procedure, including the name, data type, direction of data transfer (input vs. output), and default values.
  • Produce color syntax highlighted output, ideal for printed reports.
  • Determine if the procedure modifies data in the database.
  • Determine if the procedure makes use of cursors.
  • Determine if the procedure makes use of transactions.
  • Determine if the procedure uses return values, and what those values might be.
  • Determine if the procedure returns data via one or more result sets, and what the columns present in the result sets might be. Up to 9 result sets can be determined.
  • Include errors raised via RAISERROR
  • Include descriptions defined in SQL Enterprise Manager
  • Define project specific copyright notices.
  • Define project specific project file descriptions.
  • Define stored procedure purpose.
  • Optionally, provide examples for the use of a given stored procedure.
  • Override the SQL parser for result sets and return values when those items might be dynamically generated, or be defined by complex queries.
  • Improved handling of result set column aliases built with delimited identifiers
  • Globally override the SQL parser allowing for all documentation to be generated prior to a single SQL command being developed.
  • Recurse file folders to locate all files in the SQL project
  • Save the selected file list that forms the project for future runs of SQLHelp
  • Include the source of the procedure in the compiled help. This allows users to view the SQL without having access to either the database or the source SQL.
  • Document SQL without having any connection to the database.
  • Define functional groupings for the SQL that is in the project.
  • Procedures executing other procedures are highlighted
  • Procedures executed within procedures can be documented as to the intent of execution
  • Ability to hyperlink between procedures that are called to determine hidden result sets


SQLHelp.ini

The SQLHelp.ini file is a simple text file that you should create in the same folder as the SQL batches. This file is used to contain project specific information. The SQLHelp.ini file is the only valid location for ##CO tags and ##PR tags.

The ##AT tag is also valid syntax for SQLHelp.ini. In addition to allowing the definition of new object groups, the ##AT tag, when used in SQLHelp.ini, can define the text displayed with the stock groupings of Cursors, Returns Data, Modifies Data, Executes Procedures, and Transactional


SQLHelp - Error Messages

Error Messages

In the course of SQLHelp operation, you may experience a number of popup messages boxes. These message boxes are intended to provide users with the information they need to resolve issues arising during SQLHelp execution. If you find an error message that does not guide you in the correct direction, please consult the troubleshooting guide, or send an email to our support staff at support@pikauba.com.

Configuration Warning

SQLHelp has been designed to run on Windows 98, Windows ME, Windows NT 4.0 SP6a and above, and Windows 2000, so the probability that you do not have a version of Internet Explorer installed on your system is fairly remote. If you have gone to the difficulty of removing all instances of Internet Explorer from your system, you will not be able to view compiled HTML Help files due to the HTML Help file viewers' dependency upon Internet Explorer.

Please note that SQLHelp does not require you to set your default browser to Internet Explorer, rather a version of Internet Explorer must be installed on your system.

Note also that the absence of Internet Explorer does not affect the systems' ability to correctly process you ASP files.

This error message may appear at SQLHelp system startup.

Which Version of IE?

We would generally recommend IE 4.01 SP1 or higher. This is due to various issues including support for DHTML, and resolution of various Y2K bugs that were present in previous versions.

SQLHelp Validation Error

Input folder validation

If you elect to enter a folder name for the input folder directly, rather than browsing for it, you may inadvertently enter a folder that does not exist. This warning indicates just that situation.

This condition is validated when the next button is pressed on step 2.

Output folder validation

If you elect to enter a folder name for the output folder directly, rather than just browsing for it, you may inadvertently or intentionally enter a folder that does not exist. This warning indicates just that situation. If you accidentally entered an incorrect folder, simply press No and update the folder name (perhaps to the extent of browsing for it). If you intentionally entered a folder name that does not exist, pressing Yes will instruct SQLHelp to attempt to create it. If no system error message occurs, the folder has been created.

This condition is validated when the next button is pressed on step 2.

Output Filename validation

If you enter a filename extension other than .chm, this warning will be generated. By default, the HTML Help viewer provided by Microsoft works only with .chm extensions. Even in the absence of the correct extension, SQLHelp will correctly launch the output file, if instructed to do so in the project options.

Note: If no extension is supplied, SQLHelp will assume .chm is intended and produce the appropriate output.

This condition is validated when the next button is pressed on step 2.

SQLHelp Evaluation Warning

While in evaluation mode (i.e. not yet purchased), SQLHelp has certain limitations. One of these limitations is the number of files it will process. All versions are limited to 10 files in evaluation mode. Pikauba Software feels that generating compiled help against a 10 file project provides sufficient data points with which to properly evaluate SQLHelp.

You will see this warning whenever you attempt to add more than 10 files to the project while running in evaluation mode.

In addition to the 10 file limit, there is an effective 10 procedure limit as well. In the event that your project contains more than 10 procedures, during evaluation, a similar dialog box will also be displayed.

Compilation Error - Missing Microsoft HTMLHelp Compiler

This error message will occur after all processing has occurred if your system does not have the requisite Microsoft HTML Help compiler installed. The current version of the HTML Help compiler can be obtained from Microsoft at: http://msdn.microsoft.com/library/default.asp?URL=/library/tools/htmlhelp/chm/hh1start.htm

The required files to generate the output have been created, so once the HTML Help compiler is installed, you have the option of either re-running SQLHelp, or simply running the .hhp file located in the output directory.

SQLHelp Internal Exception

If SQLHelp encounters an internal error it generally will present a message box similar to the one shown above. If you encounter such a box, the indicated file has encountered a processing error that will prevent its inclusion in the final output file. The reason is also indicated. Generally this information is helpful to Pikauba support personnel. If you elect to contact Pikauba support at mailto:support@pikauba.com, please include the error message and a detailed description of what your system configuration is.

You are presented with the option of continuing processing. If you elect to continue, the file which experienced the error will be skipped, but other files will be included. If you elect to not continue, SQLHelp will exit.


Trouble Shooting

Troubleshooting the System

This page is a collection of items that have been shown to come up in the installation or operation of the system.

Installation

1. The system is designed under Microsoft Visual Basic® system. In order to run properly, the system needs to have Service Pack 4 installed. If it is not installed, the DLLs will be updated. Running the Setup application will normally take care of this step automatically.

2. The system also needs to have the Windows Script Host installed. The current version can be obtained from Microsoft at: http://msdn.microsoft.com/scripting/

3. Under no situations should the files located in the installation directory be modified. These files are encrypted, and if modified will cause the system to perform poorly at best, or not at all at worst.

4. You need to have the HTML Help Workshop installed on your system for the application to compile an output file. If this is not installed, you may experience errors in the application during its final phase. The current version of the HTML Help compiler can be obtained from Microsoft at: http://msdn.microsoft.com/library/default.asp?URL=/library/tools/htmlhelp/chm/hh1start.htm

Compiled Output Errors

The majority of errors associated with the compiled HTML output can be traced to misreads of the SQL. Most of these can be solved using the ##OV tag. We are constantly working to improve the reliability of the SQL parser we use. If you run into problems, and are comfortable sending us some of your SQL that is producing errors, please send it to support@pikauba.com indicating where the error is being tripped. We recognize that if you send us SQL that it is ours only to use in debugging your situation, and for the betterment of SQLHelp for all. We will not make any use of SQL batches sent to use without express written consent from the submitter.

Common Issues

Error message box during compilation. This normally indicates the absence of the HTML Help compiler. The current version of the HTML Help compiler can be obtained from Microsoft at: http://msdn.microsoft.com/library/default.asp?URL=/library/tools/htmlhelp/chm/hh1start.htm

No output file is generated. Verify that the HTML Help Workshop from Microsoft® is installed.

Note that this situation may also manifest itself as a warning error box at the end of compilation of the project.

The project name is incorrect. The project name is determined by the destination file name chosen in step 2. If the name is incorrect, then verify you've chosen the correct name.

How do I remove the Pikauba copyright? If you are not seeing your organizations copyright notice (as defined in the ##CO tag) at the bottom of the pages then you are working with an unregistered version of the software. When you register the software, this notice is removed.

How do I remove the document preparation copyright? The Pikauba copyright notice stating that this document was prepared by a Pikauba software product can not be removed. If the presence of this copyright notice posses an issue for your organization, please contact Pikauba.

I do not see any project definition or copyright notices. Copyright and project definition information may only be present in the SQLHelp.ini files. If the file is not present, then no information can be obtained. Placing the ##PR and ##CO tags in any other location will cause them to be ignored.


Usage Guidelines

SQLHelp, like any other developer tool, is capable of considerably more than first glance would indicate. Here we present several hints to derive the most from SQLHelp.

Define the SQL Interfaces Early

Using the ##OV tag the intent behind the stored procedure can be defined before a single line of SQL is ever written.

  • ##OV allows you to define the parameters passed and document them with ##PD early.
  • ##OV allows you to define the column names of the result set (s) and document them with ##RS
  • ##OV allows you to define transactional procedures with the ##TR tag
  • ##OV allows you to define data modification procedures, before the SQL is written, with the ##MD tag
  • ##OV allows you to define procedures which use cursors, before the SQL is written, using the ##CU tag

Optionally, project global overrides van be defined in the user interface.

Remove ##OV as Procedures are Completed

As valuable as the ##OV is during interface design, it can be a hindrance to ensuring the procedures are correctly documented. Since ##OV actually prevents the SQL parser from parsing the batch, if there is a departure from the design, SQLHelp will not be able to locate it.

Optionally, the ##NOV tag can be used to remove the overrides on a per procedure basis.

Run SQLHelp Frequently

Running SQLHelp periodically as projects progress can highlight potential bugs before they manifest themselves. SQLHelp does this by noting those parameters that are not yet documented. If a parameter or column alias is not yet documented, this could indicate a departure from the original design, and likely indicates a situation where code has been developed that is potentially no longer in spec.

Provide Rich Descriptions

Since you never know who might need to use the interfaces being developed, or how far in the future this need might arise, the richer the documentation you provide, the greater the likelihood that ongoing maintenance will be successful.

Document each result set returned

If returning more than one result set, documenting each one independently allows the users of the documentation to better understand each result sets purpose.


SQLHelp - User Interface

The SQLHelp user interface uses a simple wizard philosophy. You enter the various elements of information the tool looks for, and it goes about its merry way to generate HTML Help representing the database procedures in your project.

The wizard pages are:

  • Licensing Like all software products, SQLHelp has a licensing agreement that grants you, the user, the ability to use the product, as long as you play nice.
  • Registration Since SQLHelp is distributed via the shareware and trialware channels, the version you have now probably has 90% of the feature set working. Registration gives you that lacking 10%, and is well worth the price. Registration can be made by going to Pikauba.com
  • Step 1 is literally the first step a registered user sees when running the program.
  • Step 2 prompts you to select the directory where the source files, and destination files should be located. You are also able to set various project level options that will govern this project.
  • Step 3 prompts you to select the files to form part of the project.
  • Step 4 displays the progress as the project is compiled.
  • Step 5 is displayed when processing has completed and offers the option of exiting SQLHelp or continuing with the same or different projects.

Additional user interface pages are:

  • Testing of components SQLHelp has the ability to test your system to verify that all the components it requires to function are installed and accessible.


SQLHelp - Licensing

When you first run SQLHelp, and each day that you run the shareware version, you will be presented with the Licensing Agreement. Written mostly in legalese, it simply states that you will use the shareware version of SQLHelp for evaluation only. If it meets your needs, great, register it. If it fails to meet your needs (and we hope you'll let us know if this is the case), fine, delete it.

Basically, we all will be pressing I Agree and going to the next screen, but it is important to read what's in there.


SQLHelp - Registration

After pressing "I Agree" on the licensing page, you'll be taken to the registration page. On this page you have the option of registering SQLHelp, or trying it out. If you do elect to try it out, you'll be taken through this process daily until you either determine the program is worth it's registration fee, or decide to delete it. Unlike some shareware programs, this period is not fixed in time or duration, thus the SQLHelp shareware version will not expire.

If you are looking to register SQLHelp, please click here.

The registration key and code were sent to via email during registration. If you are registering a domain license, please see your local technical operations department for this information, as part of the licensing is based on your NT domain login.
Please note that if you are registering an upgraded version, you do not need to get a new key and code (actually, you shouldn't even see this screen), unless you are outside the current major version.

Press Register to go to the registration page on the Pikauba Software site. Current pricing will be listed. Unlocking SQLHelp will eliminate the licensing and registration prompts, remove the file count lockout, and enable your copyright notice to be placed on the output.

Press Try Me! to enter shareware mode and go to step 1.


SQLHelp - Step 1 - Welcome

The entry point for the registered version of SQLHelp is Step 1.

On the welcome screen you have the opportunity to select a previous project, browse for a project file to load, or create a new project.

The recent project list will contain the ten most recently used projects, sorted alphabetically. By default the last project file is selected. After selecting a project file, press the Load button to continue.

If you wish to browse for previous project files, select the browse button. Once a project file is loaded, you will automatically be taken to step 2.

Project files will all have the .SQLHelp file extension and are located in the same folder as the source files for the project. These files are also registered with the system and can be double clicked to launch SQLHelp.

Press New to create a new project.

Pressing Unlock will take you to the registration page. This button will only be displayed if SQLHelp is not currently registered.

Pressing "?" will display this page.


SQLHelp - Step 2 - Select Folders

In step 2, you select directories for SQLHelp to parse the SQL batches from, and to place the compiled help output file in.

Once a project file is loaded, or a new project is requested, you are taken to step 2. Step 2 sets all the processing options for the project. These options are saved with the project file, and default to the values used with the most recent project.

If you have loaded a project file, and that project file has files listed to process, the Finish button will be active and pressing it will begin processing of the project.

Directories

The directories tab allows you to select the input and output folders for the project.

  • Specify source directory: Indicates that you wish to specify a source directory and optionally the specific files to include in the project, but have not defined any external file list. This is the default option and corresponds to SQLHelp behavior.
  • Source directory: Allows you to specify where the source files are located if no external file list is used. For new projects, this value is always blank.

You can either enter the value directly, or press . . . to browse for the folder. If you enter the folder directly it must already exist.

  • Use external file list: If selected indicates that an external file list is to be used for the list of SQL files to process. The location of the file list is specified in the File list edit box, or can be selected by pressing the . . . button to the right of the File list edit box.
  • External file list location: The location of the external file list. For new projects, this value is always blank.

    The file used for the external file list will need to have the files for the project listed with a carriage return line feed pair separating each file. The files need to have a fully qualified path to them (i.e. full drive letters or UNC path names) and will be processed in the order in which they appear in the list. If it becomes desirable to comment out a file, simply place two hyphens (--) in front of the file name.

    If an external file list is selected, you will not have the opportunity to specify a source directory or file extensions.
  • Output directory: Allows you to specify where the output compiled help file is stored. Generally, this should be a working directory as SQLHelp will generate various working files. This value is stored between runs and with new projects the most recent saved folder will be used as a default. A fully qualified path must be entered.

    If you enter the folder directly and it does not exist, you will be prompted to create it. Alternatively, you can press . . . and browse for a directory.

Note: You are strongly recommended to select an output folder that is not identical to the input folder. While there is no restriction on having the same folders, SQLHelp will generate at least one output file for each object contained in the source SQL which could result in a large number of temporary files.

  • Output filename: You may enter any filename you wish, but if the extension is not .chm, a warning message will be displayed. You are free to ignore the warning message, but will end up creating a file that the HTML Help viewer will not natively know how to open. If you do not give a file extension, .chm will be assumed.

Project Options

The project options tab lets you set the various options that define the project.

File Options

The file options tab lets you define the various file types used in the project.

SQL Options

The SQL options tab lets you define the various SQL attributes to process.

  • Press Next once the folders are selected.
  • Press Back to return to the welcome screen.
  • Pressing "?" will display this page.


SQLHelp - Step 2 - Set Project Options

In step 2, you select directories for SQLHelp to parse the SQL batches from, and to place the compiled help output file in. Additionally, you can specify the project options to use when compiling the output.

Project Options

The project options tab lets you set the various options that define the project.

  • Launch compiled help: instructs SQLHelp to launch the compiled help file after compilation has completed.

Note: If the file is open in the HTML Help viewer when compilation occurs, a warning message will be displayed. You will have the option of closing the open output file and continuing with the project.

  • Allow any user to modify output. When working under Windows Server 2003, the output file will be owned by the user running SQLHelp. Ownership implies that user account (and the Administrators security group) are the only users able to modify the output file. By selecting this checkbox, SQLHelp will allow the default behavior but will also make any user who is a member of the Users security group able to modify the output file.

    This option is only enabled under Windows NT based operating systems.
  • Override mode: Instructs SQLHelp to only use the tags present in each batch. Use this setting to define the database interface before any code has been developed.
  • Remove temporary files: Instructs SQLHelp to delete any intermediate files generated as a part of building the project. SQLHelp will produce at least one temporary file for each object being documented.
  • Modern MSDN layout produces an output CHM whose general format follows the current MSDN style. Previous versions of SQLHelp only produced "classic" MSDN layout formatted CHMs.
  • Case sensitive: When working with case sensitive databases, or databases that have collation settings that are sensitive to case, accents, or have a character set that is UNICODE, selecting this option instructs SQLHelp to treat all procedural elements as UNICODE data.

Note: It is very important to note that when running case sensitive mode, all of the annotation tags present for a given procedure must also be case sensitive. Additionally, if the See Also syntax is being used, it must be case sensitive.

  • Include source: Instructs SQLHelp to include all text found in each batch in the compiled output. Source code is accessed by clicking the "Source" button at the top to the generated HTML page.
  • Colorize source code: Instructs SQLHelp to apply Query Analyzer color syntax highlighting to the included source. This option is only valid if Include source is selected.

Note: Colorization is CPU intensive. If your computer has a CPU speed below 400 MHz, you are advised to only run SQLHelp with colorization selected for final documentation.

  • Spaces expand tabs: Provides SQLHelp with the number of spaces to insert in the output source code for each tab character found.
  • Show source filename: if selected, this option instructs SQLHelp to include the name of the file containing the given object.

Include Options

The include options tab lets you define the various file types used in the project.

Directories

The directories tab allows you to select the input and output folders for the project.

SQL Options

The SQL options tab lets you define the various SQL attributes to process.

  • Press Next once the folders are selected.
  • Press Back to return to the welcome screen.
  • Pressing "?" will display this page.


SQLHelp - Step 2 - Set Include Options

SQLHelp - Step 2 - Set File Options

In step 2, you select directories for SQLHelp to parse the SQL batches from, and to place the compiled help output file in. Additionally, you can specify the project options to use when compiling the output.

File Options

The include options tab lets you define the various file types to use when building the list of files for the project.

  • Included file types: This option box indicates the file types SQLHelp will work with. The supported default file types (or extensions) are: SQL, TSQ, SSQ, TXT, QUERY, SP, and DB. In addition to these predefined file types, SQLHelp supports the definition of a custom file extension.
  • Look in sub folders: If selected, this option will cause SQLHelp to recurse the specified input folder for files to process. The file types to search for are defined in the Include file types option boxes.
  • Include all files meeting these criteria indicates that SQLHelp will automatically include all files whose file extension matches the included file types in the source folder, and if recursion is turned on, all sub folders.
  • Select the specific files to include indicates that you want SQLHelp to display a list of files and have you specify which files to include in the project.

Project Options

The project options tab lets you set the various options that define the project.

Directories

The directories tab allows you to select the input and output folders for the project.

SQL Options

The SQL options tab lets you define the various SQL attributes to process.

  • Press Next once the folders are selected.
  • Press Back to return to the welcome screen.
  • Pressing "?" will display this page.


SQLHelp - Step 2 - Set SQL Options

In step 2 - SQL Options, you select the types of SQL attributes to process for any given procedure.

SQL Options

  • If RAISERROR is included, any object for which RAISERROR is valid will have error events listed.
  • Enterprise manager descriptions are an alternate method to provide descriptions for use in SQLHelp. Beginning with SQL Server 2000, Microsoft has provided a means for describing SQL object elements. Using SQL enterprise manager, developers can associate descriptions with the objects they are creating while in "design" mode of enterprise manager. These descriptions are stored internally as extended properties with a name of MS_Description. For more information on SQL Server 2000 extended properties, search the Books Online for "extended properties".

    If selected, this option indicates that the enterprise manager supplied description will be used if a SQLHelp tag is not present. If a corresponding SQLHelp tag is present, it will always be used regardless of this setting.

Project Options

The project options tab lets you set the various options that define the project.

Directories

The directories tab allows you to select the input and output folders for the project.

  • Press Next once the folders are selected.
  • Press Back to return to the welcome screen.
  • Pressing "?" will display this page.


SQLHelp - Step 3 - Select Files

In step 3, you select the files to include in your project.

Note: If you are working with an unregistered version of SQLHelp, the number of files you can select is limited. To bypass this limitation will require registration of the software. For more information on registration options, click here.

In addition to the limitation on the number of files that can be processed, the unregistered version of SQLHelp also places a limitation on the number of batches that can be processed in a given run. This limitation is the same as the number of files, the value of which is currently set to 10.

  • Click on a folder to select or deselect all files listed in that folder and below. After any given folder, SQLHelp will display the number of files below the folder matching your file mask, and the number of files currently selected for processing from that set. A check will be placed in the check-box for a folder only if all the files in the folder are included in the project.
  • Press Next once the files are selected.
  • Press Back to return to the folder selection screen.
  • Pressing "?" will display this page.


SQLHelp - Step 4 - Final

In step 4, you confirm the project details, and instruct the system to prepare the output.

The system will parse through the SQL batches in the source folder defined in step 2 looking for the various tags in the batches to produce the final output according to the project options. During this processing, the progress indicator will increase. The current file is listed above the upper progress indicator, and the batch progress is indicated below that.

If these indicators appear to stop for a small period of time, this could indicate either a large batch, or that final compilation is occurring. The length of time to allow for processing is entirely dependent upon the number of files, and the complexity of the batches in the files.

Once done, you will be presented with the options of re-running the project or exiting SQLHelp. If you see a message box indicating that compilation failed, this normally indicates the lack of the Microsoft HTML Help compiler. Please view the troubleshooting guide for more information.

Pressing "?" will display this page.


SQLHelp - Step 5 - Processing completed

SQLHelp - Step 5 - Processing Complete

When processing of the project has completed, step 5 will be displayed.

  • Exit SQLHelp If selected, when you press the close button SQLHelp will exit.
  • Select a different project or define a new project If selected, when you press continue, SQLHelp will go to Step 1.
  • Reload the current project and change project options If selected, when you press continue, SQLHelp will go to Step 2.
  • Rerun the current project with all current processing options If selected, when you press continue, SQLHelp will go to Step 4 and the project will re-run.

Pressing "?" will display this page.


Tag Reference

SQLHelp provides a rich set of commands for use in the source SQL to produce formatted, professional compiled help. Each of these tags can appear more than once in a batch, but must appear within the create procedure portion of the batch to be processed.

If the identical tag name is encountered, the system will append (concatenate) the value to the previous value. This allows for greater readability of the source file, and ease of documentation.

Tags may make use of any valid HTML syntax to create more detailed help.

Tag | Description
##AT | Provides an "Applies To" description. The "Applies To" section allows the developer the capability to group functional procedures in the final compiled output. Note the default organization of returns data, transactional, updates data, and cursors remains. This tag may appear in the SQLHelp.ini file or the batch as required.
##BD | Provides a batch description.
##CO | Provides a copyright description. May only be used in the SQLHelp.ini file.
##CU | When working in annotation override mode, denotes a procedure which uses cursors.
##EP | Defines the purpose of a called procedure
##EX | Provides an example description. The example must be a fully self contained example of the intended use of the procedure.
##MD | When working in annotation override mode, denotes a procedure which modifies data.
##NOV | Reverses any override directive in effect. If no override directive is in effect, the ##NOV tag has no effect.
##OV | Denotes an overridden procedure. Overridden procedures do not have their return values or result sets parsed. Only the values explicitly named are included in the compiled help.
##PD | Provides a parameter description.
##PR | Provides a project description. All ##PR tags in the project are combined to make the final title page. Note: ##PR tags may only appear in the SQLHelp.ini file.
##RD | Provides a return value description.
##RS | Provides a result set return column description.
##RSD | Provides a result set description.
##SA | Provides a "See Also" link description.
##TR | When working in annotation override mode, denotes a procedure which is transactional.


##AT Tag

Provides an "Applies To" description. The "Applies To" section allows the developer the capability to group functional procedures in the final compiled output. Note the default organization of returns data, transactional, updates data, and cursors remains.

If a tag is found at the batch level, it must have been defined at the SQLHelp.ini level or it will be ignored.

Syntax

Batch: -- ##AT Section

SQLHelp.ini: -- ##AT Section ^^ Comment

Where Section is the name of the applies to section the developer wishes to group procedures by,
and Comment is the documentation text to use

Example

-- ##AT Product Catalogue

--##AT Product Catalogue ^^ These procedures are designed to work with the automated update
--##AT Product Catalogue ^^ process of the product catalogue.

Sample Output

The following example shows the sample.chm file provided in the installation package. We have the default groupings of Modifies Data, Returns Data, Transactional, Examples and Executes Procedures along with the user defined grouping of UserProfile. Not shown is the Uncategorized grouping which contains those procedures which are neither transactional, use cursors or modify data.

All procedures that are associated with the grouping, either through the default logic, of expressly included by the AT tag are listed.


##BD Tag

The ##BD tag defines a batch. Batches are defined as SQL batches that contain a CREATE PROCEDURE statement. If the CREATE PROCEDURE is missing, the batch will not be processed.

Since the ##BD is not legal SQL syntax, it must appear between comment tags. The tag may be between multiline comment tags (/* ... */), but only the first line will be read. If the ##BD line is intended to be a multiline comment, a ##BD must appear on the line immediately before the text to form part of the comment. ##BD tags can contain valid HTML to assist in formatting.

Syntax

-- ##BD Comment

Where Comment is the description of the individual procedure

Example

-- ##BD The product update procedure (spUpdateProduct) modifies the product table,
-- ##BD and product availability table with the new details.

Sample Output

Taken from the sample.chm file, this detail shows the fully qualified procedure name, with the batch definition highlighted.


##CO Tag

The ##CO tag denotes a copyright. Copyrights will appear at the bottom of all compiled files. The ##CO may only appear in the SQLHelp.ini file.

Since the #CO is not legal SQL syntax, it must appear between comment tags. The tag may be between multiline comment tags (/* ... */), but only the first line will be read. If the ##CO line is intended to be a multiline comment, a ##CO must appear on the line immediately before the text to form part of the comment.

Note: When running in shareware mode, SQLHelp will ignore any ##CO tags present in the SQL.

Syntax

-- ##CO Copyright

Where Copyright is the text to use for the copyright notice to be placed at the base of each page.

Example

--##CO ©2001 MyCompany.com. All rights reserved.


##CU Tag

When running in annotation override mode, the ##CU tag defines a procedure which is intended to use cursors. Normally this tag will only be required if there are no cursor elements present in the batch, or if a misread of the presence of cursors in the batch occurs.

This tag is ignored when not running in annotation override mode.

Syntax

-- ##CU


##EP Tag

The ##EP is used to define the roll a called procedure plays in a specific stored procedure. It allows the user to document the rationale associated with calling a specific procedure.

Syntax

##EP ProcedureName ^^ Comment

Where ProcedureName is the name of the procedure as called, including all dotted components,
and Comment is the documentation text to use


##EX Tag

The ##EX tag defines an example for use with the given procedure. The example should be valid HTML as no parsing of the content will occur.

Since the ##EX is not legal SQL syntax, it must appear between comment tags. The tag may be between multiline comment tags (/* ... */), but only the first line will be read. If the ##EX line is intended to be a multiline comment, a ##EX must appear on the line immediately before the text to form part of the comment.

Multiple lines are appended to existing lines, thus all ##EX tags will appear in the order in which they were defined in the batch.

Syntax

-- ##EX ExampleText

Where ExampleText is the text to use for the example.

Example

-- ##EX <P>To use the update procedure, ensure that transactions are on in the calling
-- ##EX routine. The update procedure assumes it is part of a transaction and will not
-- ##EX create a new one.</P>
-- ##EX <br>
-- ##EX <P>Thus,
-- ##EX BEGIN TRANSACTION
-- ##EX EXEC UpdateProcedure
-- ##EX If @@Error = 0
-- ##EX COMMIT TRANSACTION
-- ##EX Else
-- ##EX ROLLBACK TRANSACTION
-- ##EX </P>


##MD Tag

When running in annotation override mode, the ##MD tag defines a procedure which is intended to modify data. Normally this tag will only be required if there are no directives present in the batch which modify data, or if a misread of the presence of data modification directives in the batch occurs.

This tag is ignored when not running in annotation override mode.

Syntax

-- ##MD


##OV Tag

If your procedure contains complex SQL, it may not be parsed correctly. It is recommended that you first try to parse the SQL using SQLHelp to see if any issues are present before using the override tag.

Common issues include:

  • Use of dynamic column names. A dynamic column name is one built from the result set. (e.g. SELECT 'string' + table.Name FROM table)
  • Use of multiple paths of execution. If a conditional expression returns a result set, the parser will assume it to be present under all situations. i.e. the parser does not evaluate the potential paths of execution. The net result is the appearance of additional result sets.
  • Selecting values into a return value. The return values parsed from the SQL procedure are static values. The parser makes no attempt to parse dynamic values.
  • Result sets returned as the result of an EXECUTE on additional procedures.

If the ##OV tag is present, neither the return values, nor the result sets will be parsed. If you use the ##OV tag, incorrect results may be obtained unless the ##RD, ##RS, ##MD, ##CU and ##TR tags are kept in sync with changes to the procedures.

You can also set the entire project as being overridden. This eliminates the need to enter ##OV on each batch during design time.

To reverse the effect of the override tag, either remove it from the batch, of use the ##NOV tag.

Syntax

-- ##OV


##PD Tag

The ##PD tag defines a parameter in the procedure call. The system will parse all the named parameters from the CREATE PROCEDURE definition for inclusion in the compiled help. Type information, input/output status, and default values are extracted. If no ##PD tag exists for a given parameter, there will be no description associated with it in the help, but the parameter will be named.

Since the ##PD is not legal SQL syntax, it must appear between comment tags. The tag may be between multiline comment tags (/* ... */), but only the first line will be read. If the ##PD line is intended to be a multiline comment, a ##PD must appear on the line immediately before the text to form part of the comment.

Syntax

##PD Parameter Name ^^ Comment

Where Parameter Name is the name of the parameter passed to the procedure,
and Comment is the documentation text to use

Example

-- ##PD Title ^^ The title of the book to be modified.

Sample Output

When SQLHelp parses the SQL forming the batch, it determines both the data type, and if the parameter is optional. If the parameter is required, this is indicated. If it is optional, the default value is supplied such that the user can know if the default is appropriate.


##PR Tag

The ##PR tag denotes a project definition. The project definition is included in the default page of the compiled help file. ##PR tags may only appear in the SQLHelp.ini file. Multiple lines are permitted and will be concatenated together to form the full project definition.

Since the ##PR is not legal SQL syntax, it must appear between comment tags. The tag may be between multiline comment tags (/* ... */), but only the first line will be read. If the ##PR line is intended to be a multiline comment, a ##PR must appear on the line immediately before the text to form part of the comment.

Syntax

-- ##PR Comment

Where Comment is the description of the database project

Example

-- ##PR This project forms the database procedures for the transaction processing
-- ##PR system for our new book store. Only those procedures relating to the sale
-- ##PR of merchandise are included. For other items, including web logs and historical
-- ##PR analysis, please refer to other projects.


##RS Tag

The system is capable of parsing out up to 9 result sets. The ##RS tag allows the developer the ability to document each of the named columns parsed out. The ##RS tag is immediately followed by a number indicating the result set the parameter belongs too.

Since the ##RS is not legal SQL syntax, it must appear between comment tags. The tag may be between multiline comment tags (/* ... */), but only the first line will be read. If the ##RS line is intended to be a multiline comment, a ##RS must appear on the line immediately before the text to form part of the comment.

The values returned from the ##RS tag can be overridden with the ##OV tag.

When defining multiple result sets from a given procedure, use the ##RSD tag to define the intent of each result set.

Note: When documenting the command SELECT * FROM Table, the column name is not * but rather Table.*

Note: Since a procedure might return more than one result set depending upon the path of execution in the procedure, or could return the same column alias template for multiple paths of execution, it is not uncommon to find the same result set described more than once.

Note: If a result set does not use named columns, SQLHelp will assign a name of Unnamed column #nnn, where nnn is the ordinal of the column in the result set.

Note: If a result set has column names which are duplicated, SQLHelp will assign an ordinal offset to the column name to provide for uniqueness. Thus if a named column of Products were repeated in the same result set, the second time it would appear as Products #nnn, where nnn represents the duplicate columns ordinal in the output result set.

Syntax

##RSNumber Column ^^ Comment

Where Number is the number of the result set as it appears in the batch,
Where Column is the name of the column,
and Comment is the documentation text to use

Examples

-- ##RS1 ProductName ^^ The product name.

-- ##RS2 SupplierName ^^ The supplier name.

-- ##RS2 Supplier Sku ^^ The SKU Number associated with the product as defined by the supplier.

-- ##RS3 Sales.* ^^ All columns from the sales table

Sample Output

During SQL parsing, SQLHelp attempts to determine all result sets returned from the procedure. If there is only one result set, the output file will contain a header labeled Output Recordset. If more than one result set was found, each one will be numbered as in this sample. Please note that the values are listed as found. Thus for this result set, the first column is labeled UserType.

Occasionally you might find the a description value that contains the phrase Dynamic Column. This normally will occur if the procedure returns different column aliases for the same absolute column position, or contains a column alias that is built from the concatenation of one or more strings. While perfectly legitimate, this SQL could pose difficulties for procedure consumers (via ADO or other access means) who expect specific column names to be present in the result set.


##RD Tag

The ##RD tag is used to define return values from the procedure. Return values are generally numeric in nature, but when dynamic values are defined (either as a result of @@IDENTITY or similar, or column data), the parameter name will be displayed.

Since the ##RD is not legal SQL syntax, it must appear between comment tags. The tag may be between multiline comment tags (/* ... */), but only the first line will be read. If the ##RD line is intended to be a multiline comment, a ##RD must appear on the line immediately before the text to form part of the comment.

The values returned from the ##RD tag can be overridden with the ##OV tag.

If a non-numerical value is found, the parser will attempt to determine its meaning. Thus you can define help for return values of @@IDENTITY.

Syntax

-- ##RD Value ^^ Comment

Where Value is the value returned from the procedure,
and Comment is the documentation text to use

Example

-- ##RD 0 ^^ Procedure executed with no errors
-- ##RD -1 ^^ Procedure was not able to update the sales table.

Sample Output

This sample illustrates a procedure with three numeric return values. With the documentation provided, the consumer of the procedure can develop their application accordingly.


##SA Tag

Provides a "See Also" link description. A "See Also" link is a link name describing a procedure that is part of the project. If the procedure name does not match a procedure in the project (either due to typographical errors or absence of the file), then clicking on the "See Also" link in the compiled help will generate an error.

Syntax

##SA Procedure Name

Where Procedure Name is the name of additional procedures in the project that have significance as a group, e.g. the Add, Update and Delete procedures for a given table,
and Comment is the documentation text to use

Example

-- ##SA spUpdateProductCatalogue


##TR Tag

When running in annotation override mode, the ##TR tag defines a procedure which is intended to be transactional. Normally this tag will only be required if there are no transactional elements present in the batch, or if a misread of the transactional nature of the batch occurs.

This tag is ignored when not running in annotation override mode.

Syntax

-- ##TR


Documentation Licensing Agreement

Please note that this is an abridged version of the licensing agreement associated with SQLHelp. This abridged version is present for those users who (a) downloaded the documentation separately from the product, or (b) are viewing the documentation prior to product use. This agreement follows a plain English format.

  1. RIGHTS TO USE

    By downloading this documentation, or viewing this documentation prior to actual use of the product (SQLHelp), you are agreeing that the procedures and techniques described in this documentation are proprietary to Pikauba Software. You have the right to use this documentation in the use of and evaluation of SQLHelp, but may not use this documentation for any other purpose, including:
  • Product reviews, without previously informing Pikauba of this intent. This restriction is in place to ensure that any reviews are conducted with the most recent public version of the product, and to ensure that any reviews are from product usage, not details present in documentation.
  • Design of competing products, or inclusion of feature concepts in products not directly competing with Pikauba Software products. This restriction is in place largely to protect any derivative works from being developed. If you believe that you might wish to make use of any concept presented here in a product, please contact mailto:sales@pikauba.com. In many cases a simple agreement can be reached allowing specified derivations to be conducted.
  • Extraction of product screen shots or other images for any use. If you are planning on writing a product review, or providing any level of image linkage related to this product, we have found that use of the most recent image is the most effective course of action. Since product development is ongoing, the images you may have obtained from this documentation may no longer be relevant. Please contact mailto:sales@pikauba.com if you wish to make use of product images and details can be arranged to ensure your content is as accurate as possible.
  2. WARRANTY

    Use of this documentation does not convey any warranty, or suitability to purpose of either the documentation or its associated product. This documentation was obtained free of any charge from Pikauba Software. In no event shall Pikauba Software be liable for any damages, including any loss of revenue, loss of profit, incidental, indirect or consequential damages resulting from the use of this documentation, or its associated product. Further details relating to limitation of liability can be found in the file License.txt which is included with the product.


Enterprise Manager Descriptions

Using Enterprise Manager Descriptions

Microsoft SQL Server 2000 introduced a method for providing descriptions for certain objects, or object attributes in the database. These descriptions are stored in the database as extended properties with a property name of MS_Description. Any object or object attribute which has a design time description edit box in SQL Enterprise Manager will have its value stored in the MS_Description extended property.

You can obtain more information on the use of extended properties in general by looking in the SQL Server Books Online (BOL) under the topic "Property Management" and "Using Extended Properties on Database Objects". It is important to note that while the BOL does describe the usage of extended properties, it does not describe the MS_Description property.

How SQLHelp uses Extended Properties

If you select the "Use enterprise manager descriptions" SQL option, SQLHelp will make use of the MS_Description extended property, if it finds one. This can provide for a common location to store your object descriptions, assuming you are using SQL Server 2000. If you are using a combination of both SQLHelp tags and extended properties, please note that if both are defined for a given object or object attribute, the descriptions from each will be used, with the SQLHelp tags appearing first.

Where Extended Properties Fail

As good as the new extended properties are for documenting SQL elements, they do have some current limitations. For example, there is no mechanism to describe the columns in a result set, nor return values. As such, any complete database documentation solution will need to have a combination of both SQLHelp tags and the MS_Description extended properties.

Additionally, if you are desiring to compose long descriptions for certain objects (e.g. a complete usage guideline for a critical stored procedure), extended properties may not be appropriate. This stems from the use of the SQL Server 2000 data type of sql_variant which has a maximum length of 8016 bytes (or approximately 4000 UNICODE characters).

Even with these limitations, use of the MS_Description extended property can go a long way towards creating self documented databases.


##NOV Tag

If you have defined a global override as described under the ##OV tag, you can reverse that tags effect on a page by page basis by including the ##NOV tag in the page.

Technically there is no reason to remove any existing ##OV tag, but from a readability perspective removal of the ##OV tag is recommended.

If there is no override directive in effect for a page containing the ##NOV tag, it will have no effect on the output file.

Syntax

'##NOV


##RSD Tag

The system is capable of parsing out up to 9 result sets. The ##RSD tag allows the developer the ability to document each of the result sets independently. The ##RSD tag is immediately followed by a number indicating the result set the comment pertains too.

Note: Since a procedure might return more than one result set depending upon the path of execution in the procedure, or could return the same column alias template for multiple paths of execution, it is not uncommon to find the same result set described more than once.

Since the ##RSD is not legal SQL syntax, it must appear between comment tags. The tag may be between multiline comment tags (/* ... */), but only the first line will be read. If the ##RSD line is intended to be a multiline comment, a ##RSD must appear on the line immediately before the text to form part of the comment.

When defining multiple result sets from a given procedure, use the ##RSD tag to define the intent of each result set.

Syntax

##RSDNumber Comment

Where Number is the sequential number of the result set associated,
and Comment is the documentation text to use

Examples

-- ##RSD1 The product details result set.

-- ##RSD2 The stock status for each supplier.

Sample Output

During SQL parsing, SQLHelp attempts to determine all result sets returned from the procedure. If there is only one result set, the output file will contain a header labeled Output Recordset. If more than one result set was found, each one will be numbered as in this sample. ##RSD will work equally well for each situation. In the case of a single result set, ##RSD1 is the key. In the case of multiple result sets, the developer should be aware that conditional execution could lead to differences in the expected number of result sets returned (e.g. if a SELECT is executed as part of each branch in an IF block).


SQLHelp - Select Input

In this dialog you select the location of the files for the project. The default extension is *.sql, but other types are supported, simply select a file of that type.

Note: SQLHelp will not recurse into folders by default. If you wish to recurse folders, you will need to select that option under File Options.

Press Save when done


SQLHelp - Select Output

In this dialog you select the location of the output files for the project. The output files consist of the project chm file, and all HTML files required to prepare a web site containing the documentation.

Important: You are required to enter a name for the chm file. This name, including case, will become the project name for this project.
Note that these values are saved for future runs of SQLHelp.

Press Save when done


SQLHelp - Test installed components

When SQLHelp starts up it will test your computer system to ensure that the components it needs to function are installed and present. Should a component be found to be missing, a window will appear with information regarding the component. If no components are missing, this window will not appear.

Windows NT and Higher Notes

When running on Windows NT and higher (that is Windows NT/2000/XP/2003), the installation of software can only be performed by local machine administrators (members of the Administrators local security group). If you are missing one or more components on your system, these components may only be installed by a local machine administrator.

Windows Server 2003 Notes

When running on Windows Server 2003 with the Internet Explorer Enhanced Security Settings in place, you may experience difficulties locating some of the missing components. Specifically:

  • If you are missing the Common Controls, you will need to add *.pikauba.com to your trusted sites list otherwise you will not be able to download the installation package.
  • If you are missing any other component, you will be redirected to a web page within the microsoft.com domain structure. If you have not added *.microsoft.com as a trusted site, you will receive many warnings of unsafe content and will not be able to download the missing components.

After the installation of SQLHelp is complete, you can safely remove both *.pikauba.com and *.microsoft.com from your trusted sites list if you so desire.

  • Locate For each missing component a locate button may be present. Press the locate button to be taken to a web site containing information on how to obtain the missing component.
  • Refresh If you have installed a missing component while SQLHelp was running, press Refresh to determine if there are additional requirements. If all the components are present, you'll be taken to Step 1.
  • Continue You always have the option of attempting to use SQLHelp to process your SQL files without the required component. Depending upon the missing piece, SQLHelp may still function in a limited fashion, or may not function at all
  • Cancel Press cancel to exit SQLHelp