Contents Diagrams Roles Schemas Tables Views Procedures Functions

SaveToDB Framework
for Microsoft SQL Server

Version 9.0, September 10, 2018

Contents

Introduction

SaveToDB Framework

SaveToDB Framework is a server-side part of the SaveToDB add-in.

It allows configuring Excel application features in a database.

The features include:

- Configuring target tables or procedures used to save data back to a database;
- Configuring cell change handlers to check and save data on user input;
- Configuring actions for the Actions and context menus;
- Configuring drill-down queries for double-click and selection change events;
- Configuring validation lists;
- Configuring ribbon parameters and their value lists;
- Configuring Excel formulas selected from views and stored procedures;
- Configuring translation of object names, columns and parameters within Excel;
- Configuring saving and loading Excel table formats from a database;
- Configuring end-user workbooks for Application Workbooks wizard.

These are the basic features of the SaveToDB Framework 8.
You may use SaveToDB Framework 8 as a run-time for production databases.

SaveToDB Framework 9 includes additional features for database developers:

- Generating views and procedures to select data;
- Generating stored procedures for INSERT, UPDATE, and DELETE operations;
- Generating cell change handlers;
- Generating handlers for the Actions and context menus, double-click and selection change events;
- Generating views and procedures to select values for validation lists and parameters;
- Generating primary key, unique, and foreign key constraints;
- Managing role members.

These features save a lot of time for developers.
For example, creating a view and edit procedures to wrap a table takes a minute.

Also, generated objects include the most useful built-in parameters like @column_name or @cell_value and the required configuration to use the objects.
So, this saves a lot of time on finding the required SaveToDB add-in features.

SaveToDB Framework 9 is an application itself.
It configures the framework features using the framework tables.
So, you find good examples for all used features.

You may install, update, and remove SaveToDB Framework using SaveToDB Application Installer wizard.

You may generate a workbook to edit configuration tables using the SaveToDB Application Workbooks wizard.

We also recommend installing the Database Help Framework.

The Database Help Framework is a great tool to learn and document databases.
This documentation is generated using this framework.

Also, we recommend installing the Change Tracking Framework.

The Change Tracking Framework is a great tool integrated with Microsoft Excel to track and revert data changes.

If you are looking for a planning application, take a look at Planning Application for Microsoft and SQL Server.

This is a ready-to-use application. You may also use it to learn design patterns of applications for Microsoft Excel and databases.

We are making a lot for developers.

We love to hear your feedback. Feel free to contact us.

Quick Start

You may install and uninstall the framework using the Application Installer wizard at any time.

1. Open Microsoft Excel, select the Database tab of the SaveToDB add-in.
2. Run Wizards, Application Installer.
3. Connect to your Microsoft SQL Server database and install SaveToDB Framework 9.
4. Create a new workbook and connect to any table in your database using the Connection Wizard.
5. Click the Actions menu and try any feature.

Configuration Workbook

You may create a workbook to edit the configuration tables at any time.

1. Run Wizards, Application Workbooks.
2. Connect to your Microsoft SQL Server database.
3. Generate the savetodb9_configuration.xlsx workbook.

Change History

Version 9.0, September 10, 2018

New Features:

Diagrams

DiagramDescription
Diagram X01SaveToDB Framework Tables

Diagram X01. SaveToDB Framework Tables

SaveToDB Framework Tables

SaveToDB Framework allows customizing Excel applications using database configuration tables.

The xls.objects table contains the configuration how to save data changes back to a database.
For example, you may specify the target table for any view or stored procedure.
Also, you may supply stored procedures or SQL codes for INSERT, UPDATE, and DELETE operations.

The xls.objects table also is a source of the xls.queries view that is used in the SaveToDB Connection Wizard and Query List.
You may add new objects based on SQL codes, HTTP queries, or text files.
Add SQL codes, URLs, and text files into the TABLE_CODE field.

The xls.handlers table contains the handler configuration.
You may use handlers to configure drill-down queries, cell change handlers, validation lists, parameter values, column formulas, and much more.

The xls.translations table contains translations of database objects, columns, and parameters.
The SaveToDB add-in uses this table to translate names within Microsoft Excel depending on the selected data language in the SaveToDB Options dialog box.
So, you may translate database names to business names in any language.

The xls.formats table contains Excel table formats including sets of table views.
The SaveToDB add-in applies these formats in the first connection to database objects.
Users can save and reload table formats using SaveToDB Table Format Wizard or Save Table Format and Load Table Format menu items.

The SaveToDB add-in uses the xls.workbooks table in the Application Workbooks wizard.
You may add workbook definitions and templates for your applications.

These tables are the required run-time framework for Excel applications.
These tables are common for SaveToDB Framework 8 and SaveToDB Framework 9.
You may install SaveToDB Framework 8.11 in production databases, if you do not need advanced features of the SaveToDB Framework 9.

Roles

RoleDescription
xls_developersThe role includes permissions for Excel application developers.
xls_formatsThe role includes permissions for saving Excel table formats.
xls_usersThe role includes permissions for Excel application users.

xls_developers

The role includes permissions for Excel application developers.

Assign this role to developers and advanced users who can customize Excel applications.

Members of this role have permissions to read an write the configuration of the SaveToDB Framework used to configure SaveToDB add-in features.

See actual database permissions in the xls.xl_actions_set_role_permissions procedure.

xls_formats

The role includes permissions for saving Excel table formats.

Assign this role to end-users of Excel applications to allow saving Excel table formats into the xls.formats table.

Users may use SaveToDB Table Format Wizard or the Save Table Format menu item to save formats.

Members of the xls_developers role have this permission by default.
See actual database permissions in the xls.xl_actions_set_role_permissions procedure.

This role was added in SaveToDB Framework 8.11.

xls_users

The role includes permissions for Excel application users.

Assign this role to end-users of Excel applications.

Members of this role have permissions to read the configuration of the SaveToDB Framework used to configure SaveToDB add-in features.

See actual database permissions in the xls.xl_actions_set_role_permissions procedure.

Schemas

SchemaDescription
xlsThe schema contains SaveToDB Framework tables, views, and procedures.

xls

The schema contains SaveToDB Framework tables, views, and procedures.

Members of the xls_users role have permissions to select the configuration.

Members of the xls_developers role have permissions to update the configuration.

Tables

TableDescription
xls.formatsThis SaveToDB Framework table contains Excel table formats.
xls.handlersThis SaveToDB Framework table contains the configuration of Excel event handlers.
xls.objectsThis SaveToDB Framework table contains the configuration of saving changes.
xls.translationsThis SaveToDB Framework table contains translations of database objects.
xls.workbooksThis SaveToDB Framework table contains workbook configuration for SaveToDB Application Workbooks wizard.

xls.formats

This SaveToDB Framework table contains Excel table formats.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(128)Object schema
TABLE_NAMEnvarchar(128)Object name
TABLE_EXCEL_FORMAT_XMLxmlExcel table format in XML.

The SaveToDB add-in packs and unpacks this format internally.

The SaveToDB add-in loads Excel table formats for connected objects from this table.

Users may save and load Excel table formats using the Table Format Wizard for selected tables or using the Save Table Format and Load Table Format menu items for the active table.

To allow a user to save table formats, grant INSERT, UPDATE, and DELETE permissions on this table or add the user to the xls_formats role.

See also Configuring Table Formats.

xls.handlers

This SaveToDB Framework table contains the configuration of Excel event handlers.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(20)The schema of the object that is being configured.
TABLE_NAMEnvarchar(128)The name of the object that is being configured.
COLUMN_NAMEnvarchar(128)Database object column or parameter name.

Specify the column to apply the handler for the specified column only.
EVENT_NAMEvarchar(25)Event name.

See details in the table description.

This field has a validation list in Microsoft Excel.
HANDLER_SCHEMAnvarchar(20)Handler schema.

Use the database object schema for existing database objects and any schema for non-database handlers.
HANDLER_NAMEnvarchar(128)Handler name.

Use the database object name for existing database objects and any name for non-database handlers.
HANDLER_TYPEvarchar(25)Handler type.

See details in the table description.

This field has a validation list in Microsoft Excel.
HANDLER_CODEnvarchar(max)The field must contain the following values depending on the handler type:

CODE - SQL code
HTTP - HTTP or HTTPs link
TEXT - source text file name
RANGE - Excel range name or address
VALUES - Comma or semicolon-separated list of values

The field can contain a comma or semicolon-separated list of fields for tables and views
used to specify selected columns and aliases for validation lists of the COLUMN_NAME columns.
Examples:
- State
- StateID, State
- [State ID] AS id;[State] AS name
- StateID, State, CountryID
- StateID, State, Country
- State, Country

In the last three cases, the CountryID or Country can be used as a filter column of the dynamic validation list.
In this case, the column CountryID or Country must exist in the selected columns of the configured object.
TARGET_WORKSHEETnvarchar(128)The field allows specifying the handler output target or its behavior.

See complete description in the documentation.

Here is specification:
<Sheet name> | _new [ _TopWindow | _LeftWindow | _RightWindow | _BottomWindow ]
<Sheet names separated by a comma>
_self
_none, _browser
_popup | _taskpane [ _transpose | _transposeauto]
_reload
_saveas [<File name>[;CodePage=<Code page>][;Format=CSV][;Separator=;/,/Tab]]

Use a worksheet name to output the drill-down query to the target worksheet.
Use _popup to output the query to the popup window.
Use _taskpane to output the query to the task pane.
Use _browser to output the query into the default web browser.
Use _reload to reload data and configuration after finishing the handler query.
MENU_ORDERintHandler order in the Actions menu or in the Excel context menu.
EDIT_PARAMETERSbitNULL - default (Actions = 1, ContextMenu = 0);
1 - launch the Edit Parameters dialog box before the query;
0 - do not launch the Edit Parameters dialog box before the query.

This table allows customizing the SaveToDB add-in features.

TABLE_SCHEMA and TABLE_NAME define the object that is being configured.

COLUMN_NAME defines a column or parameter name of the object. If the value is NULL, then the configuration is applied to the entire table.

EVENT_NAME defines the configured feature. See the description below.

HANDLER_SCHEMA and HANDLER_NAME define the object that is used as a handler.
Use the database object schema and name for existing database objects and any schema and name for non-database handlers.
Note that you may translate handler names using the xls.translations table.

HANDLER_TYPE defines the handler type.
The field must contain TABLE, VIEW, FUNCTION, or PROCEDURE for database objects and special values described below for non-database objects.

The HANDLER_CODE field can contain the code specific to the handler type.
See the field comments.

The TARGET_WORKSHEET field can contain the output target.
See the field comments.

The MENU_ORDER can contain the handler order in the Actions menu at the ribbon or in the context menu.

The EDIT_PARAMETERS field can contain 1 or 0 to show or suppress the Edit Parameters dialog box for the Actions and ContextMenu handlers.

See also Configuring Event Handlers.

Event Names

The EVENT_NAME field has a validation list of available values (use Options, Show List Editor to show this task pane):

SaveToDB Framework - handlers - EVENT_NAME

The Actions handler adds an item to the Actions menu at the SaveToDB ribbon; the ContextMenu handler adds an item to the Actions menu in the Excel context menu.

You may execute any query, to get or update data, from any data source.
The ContextMenu handlers always have active row values in parameters.
The Actions handlers have no active row values if the user clicks the action with the selection outside of the table.

The DoubleClick handler are executed on the double-click event.
The SelectionChange handlers are executed on the selection change event.
See possible targets in the TARGET_WORKSHEET field.
For example, you may update task pane data on the selection change.

The Change handler is executed on the change event.
You may update the underlying database or raise an exception to prevent changes.
This powerful feature is available in the SaveToDB Enterprise and Personal editions.
Note that you may create change handlers adding the _change suffix, and the add-in adds such handlers to the underlying objects automatically.

The ConvertFormulas handler defines replacing column values to Excel formulas.
So, you may define Excel formulas in the views and stored procedures.
For example, like '=[@Amount]*[@Price]'.
You may add the formula into Excel first, and then use its text in the view or procedure.

The SaveToDB add-in detects such formulas automatically testing the first row.
Add the ConvertFormulas handler if the first row can be empty.

Use the DoNotConvertFormulas handler to prevent converting text to formulas in a column.

Use the DoNotAddValidation handler to prevent automatic adding Excel validation lists detected on foreign key constraints.
You may disable this feature for the entire table or the specific column.
Note that the SaveToDB add-in does not add auto-detected validation lists if the object already has any ValidationList handler.
You may use the Help, Workbook Information dialog box to check the generated handlers and copy the configuration into the xls.handlers table.

Use the DoNotAddChangeHandler to prevent adding the change handlers detected by the _change suffix.

Use the ProtectRows handler to prevent adding or deleting rows in Excel table.
Usually, this handler is used together with the Change handler.

The Formula handler allows specifying a column Excel formula in the HANDLER_CODE field (starting =).
The add-in replaces the column values with the specified formula.
This is especially useful for tables as views or stored procedures can select the column formulas using the code.
The add-in saves initial formula values on the hidden SaveToDB_LoadedID worksheet and updates rows with changed formula values.

The FormulaValue handler allows calculating cell values on row changes using the formula specified in the HANDLER_CODE field (starting =).
Unlike the previous handler, this handler inserts the calculated values, not formulas.
You may use any Excel formula or SaveToDB built-in formulas: =DomainUsername and =Username.

Use the ParameterValues handler to specify a query to select ribbon parameter values.

You may use tables, views, stored procedures, or custom SQL code.
The queries may return one or two columns.
If the query returns two columns, the add-in shows the values of the second column but passes into underlying queries the values of the first column.
For example, a query selects id and name. The add-in shows name values but passes the id values.
If you use tables or views to select values, you may specify output columns separated by commas or semicolon in the HANDLER_CODE format.
You may also use + and - prefixes to sort the selected values by the specified column.

Note that you may use ParameterValues queries with parameters.
In this case, the add-in reloads parameters values if the previous parameter at the ribbon with the same name has been changed.
For example, the first ribbon parameter has the @country name and the second has @state.
The second parameter query has the @country parameter.
So, when a user changes the country at the ribbon, the add-in reloads the values of the start parameter.

You may specify parameters for queries from tables and views using the third field with the @ prefix (in the HANDLER_CODE field).
Example: id, +state, @country_id

Use the ValidationList handler to specify a query to select values for Excel validation lists.
The ValidationList handlers have the same configuration capabilities as the ParameterValues handlers.

The queries may select one, two, or three columns.
If the query returns a single column, the add-in adds a validation list with loaded values.
If the query returns two columns, the add-in replaces underlying column values with names.
Queries that return two columns may have parameters. In this case, the add-in reloads values on ribbon parameter value changes.
If the query returns three columns, the add-in creates dynamic validation lists using named formulas.
For example, the ValidationList query of the state_id query returns the id, state, and country_id columns.
If the underlying table contains the country_id column, the add-in creates a validation formula for the state_id column.
When a user selects a new country in the country_id column, Excel will show only states of the selected country.
It is important that the filter column must exist in the underlying table with the same name.
You may use the AS keyword and the alias to change the name of the validation list column.
For example: id, +state, @country_id AS country

Use server-specific brackets to add fields with spaces or other special characters.
Example: id, +state, @country_id AS [country id]

The SelectionList handlers are similar to the ValidationList handlers.
However, the add-in does not create Excel validation lists.
Users may select values from the List Editor (Options, Show List Editor).

The SyncParameter handlers allow linking parameters of several queries.
For example, a worksheet has two queries: usp_order_header and usp_order_details.
Both queries have the @order_id parameter.
By default, the add-in reloads only the query with a changed parameter.
So, when a user changes @order_id in usp_order_header, the add-in reloads only the header.
To change this behavior, add SyncParameter handlers for both queries with the order_id column name.

Use the DefaultListObject handler to specify the main object on the worksheet.
The add-in shows the ribbon parameters of this object if the active cell is outside of any table.

SaveToDB 8 has a specific mode to edit tables that implement many-to-many relations.
For example, an employee_states table can contain two foreign key fields: employee_id and state_id.
So, you may specify the ManyToMany handler for both fields of the employee_states table (view, or procedure).

See Wizards, Online Examples, Developer Guides, Developer Guide 8.8 - Many-to-many.xlsx for details.

The SaveToDB add-in detects many-to-many relations automatically in most cases.
To disable this special mode, use the DoNotAddManyToMany handler.

Handler Types

The HANDLER_TYPE field has a validation list of available values (use Options, Show List Editor to show this task pane):

SaveToDB Framework - handlers - HANDLER_TYPE

Use the TABLE, VIEW, FUNCTION, and PROCEDURE types for database objects.

Use the CODE type for handlers based on SQL codes placed in the HANDLER_CODE field.
Use this type, for example, to execute stored procedures with predefined parameters.

Use the HTTP and TEXT types for HTTP and text file queries.
Place the URLs and file names in the HANDLER_CODE field.

Use the MACRO type to run macros specified in the HANDLER_CODE field.
Here is an example of the HANDLER_CODE code: AppMacros.xlsm!SayHello {FirstName}, {LastName}
The add-in populates parameters in the curly brackets using the active row values.

Use the CMD type to execute Windows and CMD commands specified in the HANDLER_CODE field.

Use the VALUES type for ParameterValues and ValidationList handlers with comma- or semicolon-separated lists in the HANDLER_CODE field.

Use the RANGE type for ParameterValues and ValidationList handlers with range names or addresses in the HANDLER_CODE field.

Use the REFRESH type for the Actions handlers to refresh comma- or semicolon-separated lists of worksheets specified in the HANDLER_CODE field.

Use the MENUSEPARATOR type to add menu separators to the Actions menu at the ribbon or in the context menu.
Specify the handler names like MenuSeparator20 or MenuSeparator30 to satisfy the unique constraint.
Do not forget to specify the MENU_ORDER values.

Use the PDF and REPORT types to generate PDF files or Excel report workbooks using the Actions menu.
You may specify the required worksheets in the HANDLER_CODE field.

xls.objects

This SaveToDB Framework table contains the configuration of saving changes.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(128)Object schema
TABLE_NAMEnvarchar(128)Object name
TABLE_TYPEnvarchar(128)Object type: TABLE, VIEW, PROCEDURE, CODE, HTTP, TEXT, or HIDDEN.

Use TABLE, VIEW, and PROCEDURE for existing database objects.
Use CODE for SQL queries.
Use HTTP for HTTP or HTTPs links.
Use TEXT for queries from CSV, JSON, XML, or plain text files.
Use HIDDEN to hide an object from the SaveToDB Query List.

This field has a validation list in Microsoft Excel.
TABLE_CODEnvarchar(max)Object code for non-database objects depending on the TABLE_TYPE value:

CODE - SQL code
HTTP - HTTP or HTTPs link
TEXT - source text file name
INSERT_OBJECTnvarchar(max)Table, procedure, or SQL code used to insert new rows.

If UPDATE_OBJECT and DELETE_OBJECT are empty, the add-in merges data using the INSERT_OBJECT (updates all existing rows first and then inserts all new rows).
UPDATE_OBJECTnvarchar(max)Table, procedure, or SQL code used to update existing rows.
DELETE_OBJECTnvarchar(max)Table, procedure, or SQL code used to delete rows.

Use this table to specify the target table to save changes or the target procedures that perform INSERT, UPDATE, and DELETE operations.

Here is a simple configuration that defines the target table to save changes of the data selected by a stored procedure:

Simple Target Table

See also Configuring Query Lists.

xls.translations

This SaveToDB Framework table contains translations of database objects.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(128)Object schema
TABLE_NAMEnvarchar(128)Object name
COLUMN_NAMEnvarchar(128)Column or parameter name.

The field must contain NULL for translation of database objects.
LANGUAGE_NAMEchar(2)Two characters of a language code like 'en' or 'fr'.

Users can select the data language using the SaveToDB Options dialog box.
TRANSLATED_NAMEnvarchar(128)Translated name of an object, column, or parameter.

The add-in uses these values to translate labels and headers in UI.

You may output the value into an Excel spreadsheet cell using the 'SaveToDB_Name' named cell.
TRANSLATED_DESCnvarchar(1024)Object, column, or parameter description.

The add-in uses these values as extended descriptions and column header comments.

You may output the value into an Excel spreadsheet cell using the 'SaveToDB_Desc' named cell.
TRANSLATED_COMMENTnvarchar(2000)Object comment.

The add-in does not use these values directly.

You may output the value into an Excel spreadsheet cell using the 'SaveToDB_Comment' named cell.

The SaveToDB add-in applies this translation within Microsoft Excel.

It loads the translation automatically depending on the selected Data Language in the SaveToDB Options dialog box.

You may use the xls.usp_translations procedure to edit translation for multiple languages in a pivot table and the xls.view_translations view to check and edit the translation of all database objects.

See also Configuring Data Translation.

xls.workbooks

This SaveToDB Framework table contains workbook configuration for SaveToDB Application Workbooks wizard.

ColumnDataTypeNullComment
IDintIdentity
NAMEnvarchar(128)Workbook name
TEMPLATEnvarchar(255)Template workbook path or URL.

The template workbook is not necessary.
The add-in can create a workbook using the definition only.
However, you may supply the workbook to have special features.

You may use Wizards, Developer Wizard, Prepare Workbook for Distribution to remove sensitive data and passwords from the workbook.
DEFINITIONnvarchar(max)Workbook definition.

Use the Workbook Definition tab in the Help, Workbook Information dialog box to get the definition of any opened workbook.

The SaveToDB add-in allows generating workbooks using the Application Workbooks wizard.

The add-in loads the workbook configuration from this table.

Use the Workbook Definition tab in the Help, Workbook Information dialog box to get the definition of any opened workbook.

See also Configuring Application Workbooks.

Views

ViewDescription
xls.queriesThis view selects database objects for the SaveToDB Connection Wizard and SaveToDB Query List.
xls.view_all_translationsThis view is an Excel form for editing translation for all objects, columns, and parameters.
xls.view_developer_handlersThis view selects the handler configuration for generating views, procedures, and constraints.
xls.view_foreign_keysThis view is an Excel form for managing foreign key constraints.
xls.view_primary_keysThis view is an Excel form for managing primary key constraints.
xls.view_unique_keysThis view is an Excel form for managing unique constraints.

xls.queries

This view selects database objects for the SaveToDB Connection Wizard and SaveToDB Query List.

Source tables: xls.objects

ColumnDataTypeComment
TABLE_SCHEMAnvarchar(128)sys.schemas.name, xls.objects.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)sys.objects.name, xls.objects.TABLE_NAME
TABLE_TYPEnvarchar(128)sys.objects.type, xls.objects.TABLE_TYPE
TABLE_CODEnvarchar(max)xls.objects.TABLE_CODE
INSERT_PROCEDUREnvarchar(max)xls.objects.INSERT_OBJECT
UPDATE_PROCEDUREnvarchar(max)xls.objects.UPDATE_OBJECT
DELETE_PROCEDUREnvarchar(max)xls.objects.DELETE_OBJECT
PROCEDURE_TYPEnvarchar(50)Empty column. Used to be compatible with previous SaveToDB versions.

The previous version may have values:

TABLE - the add-in saves data into the specified table
UPDATABLE VIEW - the add-in saves data into the view itself
PROCEDURE - the add-in saves data using the specified procedures
MERGE - the add-in inserts new records and updates existing records

The native Excel connection wizard shows tables and views only.

The SaveToDB connection wizard shows stored procedures also.

The SaveToDB add-in hides procedures that do not return results.

It does this in several ways.
For example, it excludes procedures with the _change, _insert, _update, and _delete suffixes.

The second task is to hide technical objects like views or procedures that select ribbon parameter values or Excel validation lists.

The add-in excludes such objects using naming conventions.
For example, the add-in does not show objects with the xl_, view_xl_, and usp_xl_ prefixes.

You may find the complete rules in the view code.

The add-in uses these rules in the default built-in query list.

You may use this view as a base for derived views to filter the objects you need.

For example, you may create query lists specific to different business areas.

This view selects database objects from the sys.objects table and non-database objects configured in the xls.objects table.

Unlike SaveToDB 7, SaveToDB 8 reads the xls.objects table directly.
So, you do not need to select the saving configuration for database objects.

See also Configuring Query Lists.

xls.view_all_translations

This view is an Excel form for editing translation for all objects, columns, and parameters.

Source tables: xls.translations

ColumnDataTypeComment
SECTIONint1 - tables
2 - routines
3 - columns and parameters on schema level
4 - columns of tables and views
5 - function columns of returned tables
6 - parameters
SORT_ORDERbigintSort order within a section
TRANSLATION_TYPEvarchar(9)object, schema, column, or parameter
TABLE_TYPEnvarchar(20)table, view, function, or procedure
TABLE_SCHEMAnvarchar(128)xls.translations.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)xls.translations.TABLE_NAME
COLUMN_NAMEnvarchar(128)xls.translations.COLUMN_NAME
LANGUAGE_NAMEvarchar(2)xls.translations.LANGUAGE_NAME
TRANSLATED_NAMEnvarchar(128)xls.translations.TRANSLATED_NAME
TRANSLATED_DESCnvarchar(1024)xls.translations.TRANSLATED_DESC

Unlike the xls.translation table, the view selects all objects suitable for translation.

Use it to check and edit translations.

xls.view_developer_handlers

This view selects the handler configuration for generating views, procedures, and constraints.

ColumnDataTypeComment
TABLE_SCHEMAnvarchar(128)Configured object schema
TABLE_NAMEnvarchar(128)Configured object name
COLUMN_NAMEnvarchar(128)Column or parameter name of the configured object
EVENT_NAMEvarchar(7)Actions
HANDLER_SCHEMAvarchar(3)xls
HANDLER_NAMEvarchar(37)Handler object name
HANDLER_TYPEvarchar(13)CODE or MENUSEPARATOR
HANDLER_CODEnvarchar(max)SQL code of the handler object
TARGET_WORKSHEETnvarchar(128)NULL
MENU_ORDERintHandler order in the Actions menu (200-220)
EDIT_PARAMETERSbit1 - Always suggest the Edit Parameters dialog box

This view adds items to the Actions menu for every database object to generate edit procedures and handlers.

This view is accessible for the xls_developers role members only.

You may use DENY SELECT to disable this feature.

xls.view_foreign_keys

This view is an Excel form for managing foreign key constraints.

ColumnDataTypeComment
SORT_ORDERbigintSort order
SCHEMAnvarchar(128)Table schema
TABLEnvarchar(128)Table name
COLUMNnvarchar(128)Column name
POSITIONintOrdinal position
REFERENTIAL_SCHEMAnvarchar(128)Referenced table schema
REFERENTIAL_TABLEnvarchar(128)Referenced table name
REFERENTIAL_COLUMNnvarchar(128)Referenced column name
CONSTRAINTnvarchar(128)Constraint name
ON_UPDATEnvarchar(60)CASCADE, SET NULL, SET DEFAULT, OR NULL (NO_ACTION)
ON_DELETEnvarchar(60)CASCADE, SET NULL, SET DEFAULT, OR NULL (NO_ACTION)

Use this view to create or drop foreign key constraints using the context menu.

Use it after publishing tables using the SaveToDB Publish Wizard.

xls.view_primary_keys

This view is an Excel form for managing primary key constraints.

ColumnDataTypeComment
SORT_ORDERbigintSort order
SCHEMAnvarchar(128)Table schema
TABLEnvarchar(128)Table name
COLUMNnvarchar(128)Column name
POSITIONintOrdinal position
CONSTRAINTnvarchar(128)Constraint name
INDEX_POSITIONintColumn position in the index
IS_DESCENDINGbitNULL - ASC
1 - DESC

Use this view to check tables without primary keys.

Use the context menu to drop and create constraints.

xls.view_unique_keys

This view is an Excel form for managing unique constraints.

ColumnDataTypeComment
SORT_ORDERbigintSort order
SCHEMAnvarchar(128)Table schema
TABLEnvarchar(128)Table name
COLUMNnvarchar(128)Column name
POSITIONintOrdinal position
CONSTRAINTnvarchar(128)Constraint name
INDEX_POSITIONintColumn position in the index
IS_DESCENDINGbitNULL - ASC
1 - DESC
IS_INCLUDEDbitNULL - key column
1 - nonkey column

Use this view to create or drop unique constraints using the context menu.

The SaveToDB add-in uses fields with unique constraints as names in validation lists.
Check the referenced tables and add unique constraints for the desired fields.

Procedures

ProcedureDescription
xls.usp_role_membersThis procedure is an Excel form to manage role members.
xls.usp_role_members_changeThis procedure updates a database on cell changes of xls25.usp_role_members.
xls.usp_translationsThis procedure is an Excel form to select and edit translations in all languages.
xls.usp_translations_changeThis procedure updates a database on cell changes of xls.usp_translations.
xls.xl_actions_generate_constraintsThis procedure generates primary key, unique, and foreign key constraints.
xls.xl_actions_generate_handlersThis procedure generates SaveToDB event handlers.
xls.xl_actions_generate_proceduresThis procedure generates edit procedures and cell change handlers.
xls.xl_actions_set_role_permissionsThis procedure sets permissions for the xls_users, xls_developers, and xls_formats roles.
xls.xl_delete_translationThis procedure deletes rows of xls.view_all_translations.
xls.xl_export_settingsThis procedure exports SaveToDB Framework settings.
xls.xl_import_formatsThis procedure imports SaveToDB Framework settings into xls.formats.
xls.xl_import_handlersThis procedure imports SaveToDB Framework settings into xls.handlers.
xls.xl_import_objectsThis procedure imports SaveToDB Framework settings into xls.objects.
xls.xl_import_translationsThis procedure imports SaveToDB Framework settings into xls.translations.
xls.xl_import_workbooksThis procedure imports SaveToDB Framework settings into xls.workbooks.

xls.usp_role_members

This procedure is an Excel form to manage role members.

Use this form to easily add business users to the xls_users and xls_formats roles.

Note that you may generate a complete workbook for permission management using the Application Workbooks wizard.

xls.usp_role_members_change

This procedure updates a database on cell changes of xls25.usp_role_members.

ParameterDataTypeModeComment
@column_namenvarchar(128)INThe column name of the edited cell (expected role name).
@cell_number_valueintIN1 - Add the user to the role
0 - Remove the user from the role
@namenvarchar(128)INUser or role name (value of the name column)
@data_languagechar(2)INThe SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter.

xls.usp_translations

This procedure is an Excel form to select and edit translations in all languages.

ParameterDataTypeModeComment
@fieldnvarchar(128)INThe source field of the xls.translations table: TRANSLATED_NAME, TRANSLATED_DESC, or TRANSLATED_COMMENT.

Editing data requires the SaveToDB Enterprise edition.

xls.usp_translations_change

This procedure updates a database on cell changes of xls.usp_translations.

ParameterDataTypeModeComment
@column_namenvarchar(128)INThe column name of the edited cell (expected xls.translations.LANGUAGE_NAME).
@cell_valuenvarchar(max)INA new value of the edited cell used to update for the source field.
@TABLE_SCHEMAnvarchar(128)INxls.translations.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INxls.translations.TABLE_NAME
@COLUMNnvarchar(128)INxls.translations.COLUMN_NAME
@fieldnvarchar(128)INThe source field of the xls.translations table: TRANSLATED_NAME, TRANSLATED_DESC, or TRANSLATED_COMMENT.

xls.xl_actions_generate_constraints

This procedure generates primary key, unique, and foreign key constraints.

ParameterDataTypeModeComment
@DropbitIN1 - Generate DROP commands
0 - Do not generate DROP commands
@ALTERbitIN
@ConstraintTypetinyintIN1 - Primary key constraint
2 - Unique constraint
3 - Index
4 - Foreign key constraint
@SCHEMAnvarchar(128)INTable schema
@TABLEnvarchar(128)INTable name
@COLUMNnvarchar(128)INColumn name
@REFERENTIAL_SCHEMAnvarchar(128)INReferenced table schema
@REFERENTIAL_NAMEnvarchar(128)INReferenced table name
@REFERENTIAL_COLUMNnvarchar(128)INReferenced column name
@ON_UPDATEnvarchar(128)INCASCADE, SET NULL, SET DEFAULT, OR NULL (NO_ACTION)
@ON_DELETEnvarchar(128)INCASCADE, SET NULL, SET DEFAULT, OR NULL (NO_ACTION)
@CONSTRAINTnvarchar(128)INConstraint name
@ExecuteScriptbitIN1 - Execute the script
0 - Do not execute the script (default)
@DataLanguagechar(2)INThe SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter.
@SelectCommandsbitIN1 - Select generated commands (default if @PrintCommands IS NULL)
0 - Do not select generated commands (default if @PrintCommands IS NOT NULL)
@PrintCommandsbitIN1 - Print generated commands (using the PRINT command)
0 - Do not print generated commands (default)

Use the xls.view_primary_keys, xls.view_unique_keys, and xls.view_foreign_keys views to easily add or drop constraints.

These views allow executing the procedure using the context menu.

xls.xl_actions_generate_handlers

This procedure generates SaveToDB event handlers.

ParameterDataTypeModeComment
@BaseTableSchemanvarchar(128)INBase object schema
@BaseTableNamenvarchar(128)INBase object name
@TargetObjectSchemanvarchar(128)INTarget object schema
@TargetObjectNamenvarchar(128)INTarget object name
@HandlerTypeintIN1 - ValidationList
2 - ParameterValues
3 - ParameterValues with the NULL value
4 - ContextMenu
5 - DoubleClick
6 - SelectionChange
7 - Actions
@GenerateTargetAsViewbitIN1 - Generate the target object as a view
0 - Generate the target object as a procedure
@ExecuteScriptbitIN1 - Execute the script
0 - Do not execute the script (default)
@RecreateProceduresIfExistbitIN1 - Execute DROP first if the target object exists
0 - Do not execute DROP commands
@DataLanguagechar(2)INThe SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter.
@SelectCommandsbitIN1 - Select generated commands (default if @PrintCommands IS NULL)
0 - Do not select generated commands (default if @PrintCommands IS NOT NULL)
@PrintCommandsbitIN1 - Print generated commands (using the PRINT command)
0 - Do not print generated commands (default)

Members of the xls_developers role may execute this procedure using the Actions menu.

The xls.view_developer_handlers view configures the Actions menu for every database object.

This feature may save a lot of time as the procedure generates complete procedure declarations. Developers may just edit the body to solve specific tasks.

xls.xl_actions_generate_procedures

This procedure generates edit procedures and cell change handlers.

ParameterDataTypeModeComment
@BaseTableSchemanvarchar(128)INBase object schema
@BaseTableNamenvarchar(128)INBase object name
@SelectObjectSchemanvarchar(128)INTarget schema of generated objects
@SelectObjectNamenvarchar(128)INTarget name of the select object.
Edit procedures and change handlers get the same name with the standard suffixes.
@GenerateSelectObjectbitIN1 - Generate a view or procedure to select data
0 - Do not generate a select object
@GenerateEditProceduresbitIN1 - Generate procedures for INSERT, UPDATE, and DELETE operations
0 - Do not generate edit procedures
@GenerateChangeHandlerbitIN1 - Generate a procedure to process cell change events
0 - Do not generate change handler
@GenerateSelectAsViewbitIN1 - Generate the target SELECT object as a view
0 - Generate the target SELECT object as a procedure
@ExecuteScriptbitIN1 - Execute the script
0 - Do not execute the script (default)
@RecreateProceduresIfExistbitIN1 - Execute DROP first if the target object exists
0 - Do not execute DROP commands
@DataLanguagechar(2)INThe SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter.
@SelectCommandsbitIN1 - Select generated commands (default if @PrintCommands IS NULL)
0 - Do not select generated commands (default if @PrintCommands IS NOT NULL)
@PrintCommandsbitIN1 - Print generated commands (using the PRINT command)
0 - Do not print generated commands (default)

Members of the xls_developers role may execute this procedure using the Actions menu.

The xls.view_developer_handlers view configures the Actions menu for every database object.

This feature may save a lot of time as the procedure generates ready-to-use procedure.
Developers may also edit the body to solve specific tasks.

xls.xl_actions_set_role_permissions

This procedure sets permissions for the xls_users, xls_developers, and xls_formats roles.

The procedure grants access to the configuration tables to the xls_users role.
These tables are the required run-time objects shipped with SaveToDB Framework 8.11.

Members of the xls_developers role have full access on the xls schema, including all new objects of the SaveToDB Framework 9.

It also grants access on the xls.formats table to members of the xls_formats role.

xls.xl_delete_translation

This procedure deletes rows of xls.view_all_translations.

ParameterDataTypeModeComment
@TABLE_SCHEMAnvarchar(128)INxls.translations.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INxls.translations.TABLE_NAME
@COLUMN_NAMEnvarchar(255)INxls.translations.COLUMN_NAME
@LANGUAGE_NAMEchar(2)INxls.translations.LANGUAGE_NAME

See the configuration in the xls.objects table.

xls.xl_export_settings

This procedure exports SaveToDB Framework settings.

ParameterDataTypeModeComment
@parttinyintINNULL - all
1 - xls.objects
2 - xls.handlers
3 - xls.translations
4 - xls.formats
5 - xls.workbooks
@as_exec_importbitIN0 - export as INSERT commands
1 - export as EXEC insert procedure commands
@sort_by_namesbitIN1 - sort by names
0 - sort by id (default)
@schemanvarchar(128)INTarget schema to export settings or NULL to export all settings
@languagechar(2)INxls.translations.LANGUAGE_NAME

Exporting data using this procedure is a better choice.

It uses special import procedures to merge settings correctly.

xls.xl_import_formats

This procedure imports SaveToDB Framework settings into xls.formats.

ParameterDataTypeModeComment
@TABLE_SCHEMAnvarchar(128)INxls.formats.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INxls.formats.TABLE_NAME
@TABLE_EXCEL_FORMAT_XMLxmlINxls.formats.TABLE_EXCEL_FORMAT_XML

The procedure is used in the scripts generated by the xls.xl_export_settings procedure.

The procedure tries to update the record first. If the update fails, it inserts a new record.

xls.xl_import_handlers

This procedure imports SaveToDB Framework settings into xls.handlers.

ParameterDataTypeModeComment
@TABLE_SCHEMAnvarchar(20)INxls.handlers.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INxls.handlers.TABLE_NAME
@COLUMN_NAMEnvarchar(128)INxls.handlers.COLUMN_NAME
@EVENT_NAMEvarchar(25)INxls.handlers.EVENT_NAME
@HANDLER_SCHEMAnvarchar(20)INxls.handlers.HANDLER_SCHEMA
@HANDLER_NAMEnvarchar(128)INxls.handlers.HANDLER_NAME
@HANDLER_TYPEnvarchar(25)INxls.handlers.HANDLER_TYPE
@HANDLER_CODEnvarchar(max)INxls.handlers.HANDLER_CODE
@TARGET_WORKSHEETnvarchar(128)INxls.handlers.TARGET_WORKSHEET
@MENU_ORDERintINxls.handlers.MENU_ORDER
@EDIT_PARAMETERSbitINxls.handlers.EDIT_PARAMETERS

The procedure is used in the scripts generated by the xls.xl_export_settings procedure.

The procedure tries to update the record first. If the update fails, it inserts a new record.

xls.xl_import_objects

This procedure imports SaveToDB Framework settings into xls.objects.

ParameterDataTypeModeComment
@TABLE_SCHEMAnvarchar(128)INxls.objects.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INxls.objects.TABLE_NAME
@TABLE_TYPEnvarchar(128)INxls.objects.TABLE_TYPE
@TABLE_CODEnvarchar(max)INxls.objects.TABLE_CODE
@INSERT_OBJECTnvarchar(max)INxls.objects.INSERT_OBJECT
@UPDATE_OBJECTnvarchar(max)INxls.objects.UPDATE_OBJECT
@DELETE_OBJECTnvarchar(max)INxls.objects.DELETE_OBJECT

The procedure is used in the scripts generated by the xls.xl_export_settings procedure.

The procedure tries to update the record first. If the update fails, it inserts a new record.

xls.xl_import_translations

This procedure imports SaveToDB Framework settings into xls.translations.

ParameterDataTypeModeComment
@TABLE_SCHEMAnvarchar(128)INxls.translations.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INxls.translations.TABLE_NAME
@COLUMN_NAMEnvarchar(128)INxls.translations.COLUMN_NAME
@LANGUAGE_NAMEchar(2)INxls.translations.LANGUAGE_NAME
@TRANSLATED_NAMEnvarchar(128)INxls.translations.TRANSLATED_NAME
@TRANSLATED_DESCnvarchar(1024)INxls.translations.TRANSLATED_DESC
@TRANSLATED_COMMENTnvarchar(2000)INxls.translations.TRANSLATED_COMMENT

The procedure is used in the scripts generated by the xls.xl_export_settings procedure.

The procedure tries to update the record first. If the update fails, it inserts a new record.

xls.xl_import_workbooks

This procedure imports SaveToDB Framework settings into xls.workbooks.

ParameterDataTypeModeComment
@NAMEnvarchar(128)INxls.workbooks.NAME
@TEMPLATEnvarchar(255)INxls.workbooks.TEMPLATE
@DEFINITIONnvarchar(max)INxls.workbooks.DEFINITION

The procedure is used in the scripts generated by the xls.xl_export_settings procedure.

The procedure tries to update the record first. If the update fails, it inserts a new record.

Functions

FunctionDescription
xls.get_escaped_parameter_nameThis function returns an escaped parameter name.
xls.get_friendly_column_nameThis function returns a column friendly name.
xls.get_procedure_underlying_tableThis function returns the best procedure underlying table.
xls.get_translated_stringThis function returns a translated string.
xls.get_unescaped_parameter_nameThis function returns an unescaped parameter name.
xls.get_view_underlying_tableThis function returns the best view underlying table.

xls.get_escaped_parameter_name

This function returns an escaped parameter name.

ParameterDataTypeComment
Resultnvarchar(255)Escaped parameter name
@namenvarchar(128)Column name

The function converts special characters using the _xHHHH_ format, where HHHH is the hexadecimal character code.

For example, it converts a single quote to _x0027_.

You may use such parameter names in the procedures and handlers that get values from the Excel table columns.
The SaveToDB add-in uses the same conversion function to link columns and parameters.

xls.get_friendly_column_name

This function returns a column friendly name.

ParameterDataTypeComment
Resultnvarchar(255)Friendly column name
@namenvarchar(128)Column name

It returns the quoted column name if it is required. Otherwise, it returns the name as is.

xls.get_procedure_underlying_table

This function returns the best procedure underlying table.

ParameterDataTypeComment
Resultnvarchar(255)Name of the underlying table
@schemanvarchar(128)Procedure schema
@namenvarchar(128)Procedure name

The code generation procedures use this function to find the underlying table of the procedure used to generate INSERT, UPDATE, and DELETE operations.

The result can be wrong. In this case, specify the underlying table manually.

xls.get_translated_string

This function returns a translated string.

ParameterDataTypeComment
Resultnvarchar(128)The value from the xls.translations table
@stringnvarchar(128)String to translate
@data_languagechar(2)Target language

The function uses the xls.translations table to translate strings.

xls.get_unescaped_parameter_name

This function returns an unescaped parameter name.

ParameterDataTypeComment
Resultnvarchar(128)Unescaped column name
@namenvarchar(255)Escaped parameter name

The function converts escaped parameter names with the _xHHHH_ format into the regular column names.

For example, it converts the _x0027_ value to a single quote.

xls.get_view_underlying_table

This function returns the best view underlying table.

ParameterDataTypeComment
Resultnvarchar(255)Name of the underlying table
@schemanvarchar(128)View schema
@namenvarchar(128)View name

The code generation procedures use this function to find the underlying table of the view used to generate INSERT, UPDATE, and DELETE operations.

The result can be wrong. In this case, specify the underlying table manually.

^