SaveToDB Framework 7 for Microsoft SQL Server

SaveToDB Framework 7 for Microsoft SQL Server

SaveToDB Framework for Microsoft SQL Server

SaveToDB Framework for Microsoft SQL Server contains ready-to-use solutions for application development using Microsoft Excel and Microsoft SQL Server.

The framework can be easily installed and uninstalled. The framework uses the dbo01, xls01, and etl01 schemas.

The framework can be installed from the SaveToDB add-in in a couple of minutes. Also, you can generate an Excel workbook to customize SaveToDB features using Microsoft Excel.

See SaveToDB Framework Installer and Configuration Workbook Generator.

 

After installation you can customize required features:

Installing and Uninstalling SaveToDB Framework for SQL Server

Installation Methods

SaveToDB Framework supports four methods to install or remove the framework:

  1. Installation using the SaveToDB Framework Installer integrated into the SaveToDB add-in.
  2. Installation using the standalone SaveToDB Framework Installer.
  3. Installation using source codes.
  4. Installation using batch files.

The first two methods allow installing or removing the framework in databases on all supported platforms:
Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

See SaveToDB Framework Installer.

The source code is located in the "SaveToDB Framework for SQL Server" folder:

  • framework-install-en.sql
  • framework-remove-en.sql

The installation using batch files is described below.

Installation Remarks

Database administrator permission is required to install or uninstall the framework.

Add Excel application end users to the SaveToDB_users role and Excel application developers to the SaveToDB_developers role after the framework setup.

Only framework objects are removed while framework uninstall. However, this operation removes all configuration data. Be careful.

Install and Uninstall Using Batch Files

The source code and batch files are located in the "SaveToDB Framework for SQL Server" folder.

Installing SaveToDB Framework

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run 2-install-savetodb-framework.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

The "setup" connection user must have database administrator permissions.

Instead of clearing the setup password on step 3, you may protect gsqlcmd.exe.config:

select the "User protected" option in gConnectionManager.

Uninstalling SaveToDB Framework

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run framework-remove.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Verifying Framework Objects

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run framework-list.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Schemas and Roles

SaveToDB Framework for Microsoft SQL Server has the following schemas:

SchemaDescription
xls01The schema contains the objects that should be available for Excel application end users.
dbo01The schema contains the objects that hold data and should be available for Excel application developers.
etl01The schema contains ETL (Extract, Transform, Load) objects.

SaveToDB Framework for Microsoft SQL Server creates the following roles:

RoleDescription
SaveToDB_usersThe role is for Excel application end users.
SaveToDB_developersThe role is for Excel application developers.

The SaveToDB_users role permissions are enough to read the SaveToDB configurations.

The SaveToDB_developers role permissions are enough to read and update the SaveToDB configurations.

Query Lists

Preface

SaveToDB allows changing queries using the ribbon Query List.

It is a useful feature as application workbooks can contain fewer worksheets. Also, users can easily connect to new database objects after query list refresh.

The query lists can contain database objects, custom SQL codes, HTTP and text file queries.

The query list configuration views are also used to configure base tables, custom stored procedures, or custom SQL codes to save changes of data loaded from views, stored procedures, SQL codes, HTTP and text file queries.

SaveToDB Framework for Microsoft SQL Server creates database objects to configure these advanced features.

See Configuring Query List, Configuring Saving Changes.

Configuration Data Table

The dbo01.QueryList table is used as an additional data source for the dbo01.viewQueryList configuration view.

CREATE TABLE [dbo01].[QueryList](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NOT NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [TABLE_TYPE] [nvarchar](128) NOT NULL,
    [TABLE_CODE] [nvarchar](max),
    [INSERT_PROCEDURE] [nvarchar](max),
    [UPDATE_PROCEDURE] [nvarchar](max),
    [DELETE_PROCEDURE] [nvarchar](max),
    [PROCEDURE_TYPE] [nvarchar](128),
 CONSTRAINT [PK_QueryList_dbo01] PRIMARY KEY CLUSTERED
(
    [ID]
)
)

The table is used to:

  • Define SQL codes for query lists.
    Specify an SQL code in the TABLE_CODE field and the CODE type in the TABLE_TYPE field.
  • Define HTTP queries for query lists.
    Specify an HTTP query in the TABLE_CODE field and the HTTP type in the TABLE_TYPE field.
  • Define text file queries for query lists.
    Specify a text file query in the TABLE_CODE field and the TEXT type in the TABLE_TYPE field
  • Define base tables to save changes of data loaded from views, stored procedures, or SQL codes, or to merge data loaded from any data source including HTTP and text file queries.
    To have the possibility to save changes, specify the table in all INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields.
    To have the possibility to merge data, specify the table in the INSERT_PROCEDURE field only.
  • Define custom stored procedures or SQL codes to save changes of data loaded from views, stored procedures, or SQL codes, or to merge data loaded from any data source including HTTP and text file queries.
    All the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields must be specified for the save mode, and only the INSERT_PROCEDURE field is specified for the merge mode.

See Configuring Query List, Configuring Saving Changes.

Query List View for Developers

The dbo01.viewQueryList view selects all objects available to the user, including SaveToDB Framework objects.

This view is suitable for developer query lists as developers can connect to any object.

Query List View for Users

The xls01.viewQueryList view selects all objects available to the user, excluding SaveToDB Framework and specially named objects.

This view can be used for creating derived views for different business areas.

For example:

CREATE VIEW [xls30].[viewQueryList]
AS
SELECT
    t.TABLE_SCHEMA
    , t.TABLE_NAME
    , t.TABLE_TYPE
    , t.TABLE_CODE
    , t.INSERT_PROCEDURE
    , t.UPDATE_PROCEDURE
    , t.DELETE_PROCEDURE
    , t.PROCEDURE_TYPE
FROM
    xls01.viewQueryList t
WHERE
    t.TABLE_SCHEMA IN ('dbo30', 'xls30', 'xls01')
    AND NOT t.TABLE_NAME IN ('viewCashFlowData')

The example view selects database objects in the certain schemas and hides the service viewCashFlowData view.

Change Saving Configuration

Preface

SaveToDB supports multiple methods to save data changes back to a database.

See Configuring Saving Changes.

The most powerful method to save data changes is using custom INSERT, UPDATE, and DELETE procedures.

This topic describes the procedure that allows generating custom stored procedures to save changes in Microsoft SQL Server databases.

Procedure to Generate Custom Edit Procedures

The etl01.uspGenerateEditProcedures procedure allows generating custom SELECT, INSERT, UPDATE, and DELETE procedures.

Procedure declaration:

CREATE PROCEDURE [etl01].[uspGenerateEditProcedures]
    @TableSchema nvarchar(128) = NULL
    , @TableName nvarchar(128) = NULL
    , @TargetSchema nvarchar(128) = NULL
    , @ExecuteScript bit = 0
    , @RecreateProceduresIfExist bit = 0

The procedure uses the following names of generated procedures:

<object>_select- the SELECT procedure
<object>_insert- the INSERT procedure
<object>_update- the UPDATE procedure
<object>_delete- the DELETE procedure

Important!

If a table contains a foreign key, then the master key table should have a unique key (not index) for a field with a user-friendly name that used for editing in Microsoft Excel.

Accordingly, the procedure uses this user-friendly name in view instead of the foreign key field.

For example:

The dbo60.Item table contains a foreign key for CategoryID from the dbo60.Category table.

The dbo60.Category table also contains the Name field that is user-friendly.

Accordingly, we can create a unique key for the Name field, and the generation procedure will use the CategoryName field instead of CategoryID and generate the code to transform names to identifiers.

There is an example of using unique keys:

CREATE TABLE [dbo60].[Category](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL
 CONSTRAINT [PK_Category_dbo60] PRIMARY KEY CLUSTERED
(
    [ID] ASC
),
 CONSTRAINT [IX_Category_dbo60] UNIQUE NONCLUSTERED
(
    [Name] ASC
)
)
GO

CREATE TABLE [dbo60].[Item](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CategoryID] [int] NULL,
    [Name] [nvarchar](255) NULL,
 CONSTRAINT [PK_Item_dbo60] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
)
GO

ALTER TABLE [dbo60].[Item]  WITH CHECK ADD  CONSTRAINT [FK_Item_Category_dbo60] FOREIGN KEY([CategoryID])
REFERENCES [dbo60].[Category] ([ID])
ON UPDATE CASCADE
ON DELETE SET NULL
GO

Event Handlers

Preface

SaveToDB allows using stored procedures, SQL codes, and HTTP queries for handling the following Microsoft Excel events:

  • Change
  • DoubleClick
  • SelectionChange

Using event handling developers can implement tasks like database data changes, drill-down queries, or master-detail interfaces.

Also, developers can customize the Microsoft Excel context menu and SaveToDB Actions menu to run:

  • Views
  • Stored procedures
  • SQL codes
  • HTTP queries
  • Text file queries
  • Macros
  • Windows Shell and CMD commands

See Configuring Event Handlers.

Configuration Data Table

The dbo01.EventHandlers table contains configuration data selected via the xls01.viewEventHandlers configuration view.

CREATE TABLE [dbo01].[EventHandlers](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NOT NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [COLUMN_NAME] [nvarchar](128),
    [EVENT_NAME] [varchar](50) NOT NULL,
    [HANDLER_SCHEMA] [nvarchar](128),
    [HANDLER_NAME] [nvarchar](128) NOT NULL,
    [HANDLER_TYPE] [nvarchar](128),
    [HANDLER_CODE] [nvarchar](max),
    [TARGET_WORKSHEET] [nvarchar](128),
    [MENU_ORDER] [int],
    [EDIT_PARAMETERS] [bit],
    [IS_ACTIVE] [bit],
 CONSTRAINT [PK_EventHandlers_dbo01] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
)

ALTER TABLE [dbo01].[EventHandlers] ADD  CONSTRAINT [DF_EventHandlers_IS_ACTIVE]  DEFAULT ((1)) FOR [IS_ACTIVE]

See field description in the Configuring Event Handlers topic.

The IS_ACTIVE field is used to turn handlers on or off.

The HANDLER_CODE field can contain SQL codes, HTTP and text queries, macro codes, or Windows Shell and CMD commands.

The recommended name format for event handling objects:

  • uspExcelEvent_<Event>_<Schema>_<Name>
  • uspExcelEvent_<Event>_<Schema>_<Name>_<Column>
  • viewExcelEvent_<Event>_<Schema>_<Name>
  • viewExcelEvent_<Event>_<Schema>_<Name>_<Column>

where <Schema>_<Name> is a base object name, <Column> is a base object column, and <Event> has the following event names:

  • Actions
  • Change
  • DoubleClick
  • ContextMenu
  • SelectionChange

Such objects are excluded from the Query List views.

Configuration View

The xls01.viewEventHandlers view is used by the SaveToDB add-in to get the event handler configuration data.

The view selects the data from the dbo01.EventHandlers table.

CREATE VIEW [xls01].[viewEventHandlers]
AS
SELECT
    t.ID
    , t.TABLE_SCHEMA
    , t.TABLE_NAME
    , t.COLUMN_NAME
    , t.EVENT_NAME
    , t.HANDLER_SCHEMA
    , t.HANDLER_NAME
    , t.HANDLER_TYPE
    , t.HANDLER_CODE
    , t.TARGET_WORKSHEET
    , t.MENU_ORDER
    , t.EDIT_PARAMETERS
FROM
    [dbo01].[EventHandlers] t
WHERE
    t.IS_ACTIVE = 1

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Service Procedures

etl01.uspGenerateGenegicHandler

The procedure generates a template for generic event handlers.

etl01.uspGenerateChangeHandler

The procedure generates a template for event handlers used to update a database on the cell change event.

Data Translation

Preface

SaveToDB allows translating names of database objects, object fields, and stored procedure parameters within Microsoft Excel.

It is a useful feature as the names can be changed in configuration tables without changes of underlying database objects.

In addition, the translation data are used in ScreenTips and table header comments.

See Configuring Data Translation.

Object Translation Table

The dbo01.ObjectTranslation table contains database object translation data selected via the xls01.viewObjectTranslation configuration view.

CREATE TABLE [dbo01].[ObjectTranslation](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NOT NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [LANGUAGE_NAME] [char](2) NOT NULL,
    [TRANSLATED_NAME] [nvarchar](128) NULL,
    [TRANSLATED_DESC] [nvarchar](255) NULL,
    [TRANSLATED_COMMENT] [nvarchar](4000) NULL,
 CONSTRAINT [PK_ObjectTranslation_dbo01] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
)

CREATE UNIQUE NONCLUSTERED INDEX [IX_ObjectTranslation_Schema_Name_Language_dbo01] ON [dbo01].[ObjectTranslation]
(
    [TABLE_SCHEMA] ASC,
    [TABLE_NAME] ASC,
    [LANGUAGE_NAME] ASC
)

See field description in the Configuring Data Translation topic.

The size of the TRANSLATED_* fields can be changed.

Object Translation Configuration View

The xls01.viewObjectTranslation view is used by the SaveToDB add-in to get the object translation data.

The view selects the data from the dbo01.ObjectTranslation table.

CREATE VIEW [xls01].[viewObjectTranslation]
AS
    SELECT
        t.ID
        , t.TABLE_SCHEMA
        , t.TABLE_NAME
        , t.LANGUAGE_NAME
        , t.TRANSLATED_NAME
        , t.TRANSLATED_DESC
        , t.TRANSLATED_COMMENT
    FROM
        [dbo01].[ObjectTranslation] t

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Column Translation Table

The dbo01.ColumnTranslation table contains field and parameter translation data selected via the xls01.viewColumnTranslation configuration view.

CREATE TABLE [dbo01].[ColumnTranslation](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NULL,
    [TABLE_NAME] [nvarchar](128) NULL,
    [COLUMN_NAME] [nvarchar](128) NOT NULL,
    [LANGUAGE_NAME] [char](2) NOT NULL,
    [TRANSLATED_NAME] [nvarchar](128) NULL,
    [TRANSLATED_DESC] [nvarchar](1024) NULL,
 CONSTRAINT [PK_ColumnTranslation_dbo01] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
)

CREATE UNIQUE NONCLUSTERED INDEX [IX_ColumnTranslation_Schema_Name_Column_Language_dbo01] ON [dbo01].[ColumnTranslation]
(
    [TABLE_SCHEMA] ASC,
    [TABLE_NAME] ASC,
    [COLUMN_NAME] ASC,
    [LANGUAGE_NAME] ASC
)

See field description in the Configuring Data Translation topic.

Column Translation Configuration View

The xls01.viewColumnTranslation view is used by the SaveToDB add-in to get the column translation data.

The view selects the data from the dbo01.viewColumnTranslation table.

CREATE VIEW [xls01].[viewColumnTranslation]
AS
    SELECT
        t.ID
        , t.TABLE_SCHEMA
        , t.TABLE_NAME
        , t.COLUMN_NAME
        , t.LANGUAGE_NAME
        , t.TRANSLATED_NAME
        , t.TRANSLATED_DESC
    FROM
        [dbo01].[ColumnTranslation] t

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Object Description View

The xls01.viewObjectDescription view selects translation data from the dbo01.ObjectTranslation table for objects available to users for connection.

This view can be used as an Excel application home page as a user can read object descriptions and connect to any object using the ribbon Query List.

Service Stored Procedures

etl01.uspUpdateColumnTranslation
The procedure adds data into dbo01.viewColumnTranslation for fields of table and views.
etl01.uspUpdateColumnTranslation_Group
The procedure adds data into dbo01.viewColumnTranslation for groups of fields.
etl01.uspUpdateObjectTranslation
The procedure adds data into dbo01.ObjectTranslation for the specified language and schema.
etl01.uspUpdateObjectTranslationAbsent
The procedure adds data into dbo01.ObjectTranslation for new objects.

Parameter Values

Preface

SaveToDB allows using views, stored procedures, or SQL codes to populate ribbon parameter values.

The ribbon parameters are used to change parameters of stored procedures, SQL codes, HTTP and text file queries, or WHERE fields for tables and views.

See Configuring Parameter Values.

Configuration Data Table

The dbo01.ParameterValues table contains configuration data selected via the xls01.viewParameterValues configuration view.

CREATE TABLE [dbo01].[ParameterValues](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NOT NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [PARAMETER_NAME] [nvarchar](128) NOT NULL,
    [SELECT_SCHEMA] [nvarchar](128),
    [SELECT_NAME] [nvarchar](128) NOT NULL,
    [SELECT_TYPE] [nvarchar](128),
    [SELECT_CODE] [nvarchar](max),
 CONSTRAINT [PK_ParameterValues_dbo01] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
)

CREATE UNIQUE NONCLUSTERED INDEX [IX_ParameterValues_Schema_Name_Parameter_dbo01] ON [dbo01].[ParameterValues]
(
    [TABLE_SCHEMA],
    [TABLE_NAME],
    [PARAMETER_NAME]
)

See field description in the Configuring Parameter Values topic.

The recommended name format for objects used in the SELECT_NAME field:

  • viewParameterValues_<PARAMETER_NAME>
  • uspParameterValues_<PARAMETER_NAME>

Such objects are excluded from the Query List views.

The SELECT_CODE field can contain SQL codes. In this case, the SELECT_TYPE field must contain the CODE type.

Configuration View

The xls01.viewParameterValues view is used by the SaveToDB add-in to get the configuration data.

The view selects the data from the dbo01.ParameterValues table.

CREATE VIEW [xls01].[viewParameterValues]
AS
SELECT
    t.ID
    , t.TABLE_SCHEMA
    , t.TABLE_NAME
    , t.PARAMETER_NAME
    , t.SELECT_SCHEMA
    , t.SELECT_NAME
    , t.SELECT_TYPE
    , t.SELECT_CODE
FROM
    [dbo01].[ParameterValues] t

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Table Formats

Preface

SaveToDB allows storing and distributing Microsoft Excel table formats of database objects using a database.

Database objects are formatted in Microsoft Excel when the objects are connected for the first time, or when required using the Table Format Wizard.

Table formats include the following elements:

  • Cell format.
  • Conditional formatting.
  • Formula columns.
  • Table totals.
  • Applied auto-filters.
  • Print page setup.
  • Window properties.
  • Column visibility.
  • Custom table views.

Use the Table Format Wizard to save, clear, or apply table formats.

See also Configuring Table Formats.

Configuration Data Table

The dbo01.TableFormats table contains Microsoft Excel table formats of database objects.

CREATE TABLE [dbo01].[TableFormats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NOT NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [TABLE_EXCEL_FORMAT_XML] [xml] NULL,
 CONSTRAINT [PK_TableFormat_dbo01] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
)

CREATE UNIQUE NONCLUSTERED INDEX [IX_TableFormats_Schema_Name_dbo01] ON [dbo01].[TableFormats]
(
    [TABLE_NAME] ASC,
    [TABLE_SCHEMA] ASC
)

The dbo01.uspUpdateTableFormat procedure is used to update the data.

Configuration View

The xls01.viewTableFormats view is used by the SaveToDB add-in to get the table formats.

The view selects table formats from the dbo01.TableFormats table.

CREATE VIEW [xls01].[viewTableFormats]
AS
    SELECT
        t.ID
        , t.TABLE_SCHEMA
        , t.TABLE_NAME
        , t.TABLE_EXCEL_FORMAT_XML
    FROM
        [dbo01].[TableFormats] t

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Configuration Procedure to Save Table Formats

The dbo01.uspUpdateTableFormat procedure is used by the SaveToDB add-in to update the dbo01.TableFormats table.

Procedure declaration:

CREATE PROCEDURE [dbo01].[uspUpdateTableFormat]
    @Schema nvarchar(128) = NULL
    , @Name nvarchar(128) = NULL
    , @ExcelFormatXML xml = NULL

Version History

Version 7.2, March 20, 2017

[+] New types of event handlers: ConvertFormulas and DoNotConvertFormulas.
[*] Added validation lists for the QueryList, EventHandlers, and ParameterValues tables and derived views.
[*] The EventHandlers.HANDLER_NAME field may have NULL values.

Version 7.1, February 3, 2017

[*] New VALUES and RANGE types in the SELECT_TYPE field.
[*] New VALUES and RANGE types in the HANDLER_TYPE field.

Version 7.0, January 17, 2017

[*] Updated batch files and readme.txt.
SQL codes have no changes.

Version 6.10, November 2, 2016

[-] Fixed dbo01.viewQueryList query for working with databases that contain dots in names.

Version 6.9, June 21, 2016

[+] A new event handler type: ProtectRows.
[*] Improved performance of dbo01.viewQueryList and xls01.viewQueryList.
Updated filters to exclude SaveToDB Framework objects.
[*] xls01.viewObjectDescription is not shown via xls01.viewQueryList.
You may add it to custom query lists yourself.

Version 6.8, May 27, 2016

[-] Fixed issues related to different collations in the master and current databases.

Version 6.7, April 14, 2016

[+] The framework includes updated validation lists of the EVENT_NAME column of the event handler configuration tables.
Use savetodb-framework-62-upgrade-to-67.sql to update the existing frameworks.
Use Table Format Wizard to reload validation lists of existing Excel configuration tables.
Also, you may create new configuration workbooks using Configuration Workbook Generator.

Version 6.2, December 23, 2015

[+] New event handler types: DoNotSelect, DoNotSave, DoNotChange, Formula, FormulaValue, and ValidationList.
[*] Improved performance of the dbo01.viewQueryList, xls01.viewQueryList and derived views.
[+] New SaveToDB Frameworks in MySQL (lowercase) and Oracle (uppercase) styles.
You may use these frameworks if other objects in your database have MySQL or Oracle name conventions.
[!] Additional features of the SaveToDB Framework 5.0 have been moved to separate packages:
SaveToDB Framework Administrator Tools
SaveToDB Framework Generator Tools
SaveToDB Framework Handler Tools
SaveToDB Framework Type Tools
You may find the packages in the SaveToDB Framework for SQL Server folder of the SaveToDB SDK.
This repackaging makes the core SaveToDB Framework clearer.
[*] dbo01.viewQueryList excludes objects like '%_change'.
[*] xls01.viewQueryList excludes objects like 'viewValidationList_%', 'uspValidationList_%', 'xl_%', 'view_xl_%', 'usp_xl_%', and 'ufn_xl_%'.
You may use these conventions to name validation list objects and other technical objects for Excel applications.
[*] You may use the code in the SaveToDB Framework Upgrade 5.0 to 6.2 folder to upgrade existing SaveToDB Frameworks 5.0.

Version 5.2, May 12, 2015

[*] Batch files have been updated.
[*] Table functions are shown in the dbo01.viewQueryList and xls01.viewQueryList views
as SaveToDB 5.2 supports connecting to table functions.
Functions with the ufnExcelEvent_ and ufnParameterValues_ prefixes are not shown.
[*] Procedures with the _merge suffixes are not shown in the dbo01.viewQueryList and xls01.viewQueryList views.

Version 5.0, April 13, 2015

[*] The TABLE_CODE and PROCEDURE_TYPE fields have been added to the dbo01.QueryList table and viewQueryList views.
[*] Types of the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE have been changed to nvarchar(max) to hold large SQL codes.
[*] The HANDLER_CODE field has been added to the dbo01.EventHandlers table and the xls01.viewEventHandlers view.
[*] The SELECT_CODE field has been added to the dbo01.ParameterValues table and the xls01.viewParameterValues view.
[*] The SPECIFIC_SCHEMA and SPECIFIC_NAME fields have been renamed to TABLE_SCHEMA and TABLE_NAME in the dbo01.ParameterValues table and the xls01.viewParameterValues view.
[*] Types of the TABLE_SCHEMA and HANDLER_SCHEMA fields have been changed to nvarchar(128).
[+] Objects for managing user roles and object permissions have been added.
Just connect to the viewQueryListTools objects using SaveToDB.
[+] An SQL code to upgrade SaveToDB Frameworks 3.x-4.x has been added.
You may find the code in the "SaveToDB Framework Upgrade to 50" folder of the SaveToDB SDK.

Version 4.8, August 27, 2014

[*] SaveToDB Framework for SQL Server 2000 has been released.

Version 4.7, July 15, 2014

[*] The batch files have been updated for using the gsqlcmd.exe command line utility instead of sqlcmd.exe.
As a result, the framework operations can be executed on a machine without Microsoft SQL Server components installed.

Version 4.0, December 13, 2013

[*]The lengths of the INSERT_PROCEDURE, UPDATE_PROCEDURE and DELETE_PROCEDURE fields of the dbo01.QueryList table have been increased to 2000 characters.
These fields can contain SQL codes to save data changes. You may increase the field length if needed.
[*]The dbo01.viewQueryList view has been changed to select objects as SQL codes, HTTP and text file queries (CODE, HTTP, and TEXT).
[*]Unique indexes to dbo01.ParameterValues, dbo01.ObjectTranslation, dbo01.ColumnTranslation, and dbo01.TableFormats have been added.
[*]Actions menu links to online help for framework tables and views have been added.

Version 3.0, June 10, 2013

[!]Framework object names have been changed to unify naming. See the name changes below.
[+]SaveToDB Framework can be installed or uninstalled using the SaveToDB Framework Installer wizard within Microsoft Excel or using standalone SaveToDB Framework Installer.
[+]SaveToDB Framework tables can be edited using SaveToDB Framework configuration workbooks that are generated using SaveToDB wizard.
[*]The event handler configuration table and view have new fields to configure new SaveToDB 3.0 features.
[*]The etl01.uspGenerateChangeHandler and etl01.uspGenerateGenegicHandler procedures have been added to generate event handler templates.
[*]Some dbo01.ObjectTranslation and dbo01.ColumnTranslation field sizes have been increased.
[*]The dbo01.ViewQueryList view has been added. The view does not exclude framework objects.
The view is useful for Excel application developers.
[*]The dbo01.QueryList table has been added to configure SQL codes and HTTP queries of Query Lists and to configure tables used to save data changes of views and stored procedures by built-in SaveToDB procedures.
The table is an additional data source for the dbo01.ViewQueryList and xls01.ViewQueryList views.
[!]The xls01.viewParameterValues view is redesigned.
The view selects data from the new dbo01.ParameterValues table.
So, all the framework configuration views use data from underlying configuration tables.
[*]Framework objects are documented in translation tables.
So, the objects are well documented in Microsoft Excel.
[*]The xls01.viewObjectDescription view has been added to select object descriptions for users.
The view is suitable for using as a home page of Excel applications.
[*]The etl01.uspUpdateObjectTranslationAbsent procedure has been added to update the dbo01.ObjectTranslation table with new objects.
[*]The documentation has been moved from the readme.htm and *.sql files to this help file.
[+]The auto-generated framework-setup-en.sql file has been added for manual install using SSMS.
[+]Import-export utilities have been added. See the import-export folder.
[-]Installation errors for Microsoft SQL Server 2005 have been fixed.

SaveToDB Framework for Microsoft SQL Server object name changes:

SaveToDB Framework 2.0SaveToDB Framework 3.0
dbo01.Translation_Objectsdbo01.ObjectTranslation
dbo01.Translation_Columnsdbo01.ColumnTranslation
dbo01.ExcelEventHandlersdbo01.EventHandlers
dbo01.ExcelTableFormatsdbo01.TableFormats
xls01.viewTranslation_Columnsxls01.viewObjectTranslation
xls01.viewTranslation_Objectsxls01.viewColumnTranslation
xls01.viewExcelEventHandlersxls01.viewEventHandlers
xls01.viewParameterValuesQueriesxls01.viewParameterValues
xls01.viewExcelTableFormatsxls01.viewTableFormats
dbo01.uspUpdateExcelTableFormatdbo01.uspUpdateTableFormat

System Requirements

Supported Versions of SaveToDB:

  • SaveToDB 5.0 and higher

Supported Versions of Microsoft SQL Server:

  • Microsoft SQL Server 2000 *
  • Microsoft SQL Server 2005, 2008, 2008 R2
  • Microsoft SQL Server 2012, 2014, 2016 including Express LocalDB
  • Microsoft Azure SQL Database

SaveToDB Framework for Microsoft SQL Server 2000 and SaveToDB Framework for Microsoft SQL Server are different frameworks.

SaveToDB Examples for Microsoft SQL Server

The SaveToDB SDK includes examples for Microsoft SQL Server.

The example databases are deployed in Microsoft Azure SQL Database.
So you may test the examples using Microsoft Excel with installed SaveToDB add-in only.

Also, the SDK contains folders with complete source codes for on-premises deployment.

Developer Guide 5.3

This guide shows how to develop Microsoft Excel applications using the SaveToDB add-in with Microsoft SQL Server.

This is a complete guide. Try it first.

Developer Guide 6.2

This guide shows how to use new features of SaveToDB 6.2.

Developer Guide 6.5

This guide shows how to use new features of SaveToDB 6.5. Definitely, try it.

Northwind

The example shows new SaveToDB 5.0 features:

  • Cursors and forms fields.
  • Implementing master-detail interfaces.
  • Showing images.
  • Pivot table views.

Budget Pivot

The example shows new SaveToDB 5.0 features:

  • Using pivot tables as edit forms.
  • Pivot table views.

Employee-City

The example shows how to configure saving changes into normalized tables.

Azure Catalog

This is a complete example. The example shows the following features:

  • Using views and stored procedures with parameters for working with a large amount of hierarchy data.
  • Populating parameter values using views and stored procedures.
  • Populating parameter values depend on other parameter values.
  • Saving data changes using handling cell change events using stored procedures.
  • Protecting Excel table values and formulas using Change event handlers.
  • Configuring the ribbon Actions menu to run context views, stored procedures, and HTTP queries.
  • Configuring the Microsoft Excel context menu to run context views, stored procedures, and HTTP queries.
  • Defining Microsoft Excel formulas in views and stored procedures.
  • Configuring query output targets like a sheet, the default web browser, a data window, or an internal browser.
  • Generating HTML documents within a database, including ready-to-print official documents.
  • Configuring the ribbon Query List using database views.
  • Configuring table and view fields to customize WHERE filters using the ribbon.
  • Saving table formats of closed queries and applying saved formats when the queries are reopened.
  • Distributing Excel table formats of database objects from a database.
  • Translating database object names and descriptions to any language within Microsoft Excel using database translation views.
  • Translating database object fields and parameters to any language within Microsoft Excel using database translation views.

Azure Calendar

This example application shows how to use SQL Server stored procedures as Excel event handlers for handling the following events:

  • Cell Change
  • DoubleClick
  • SelectionChange

Also, the example shows how to customize Microsoft Excel context menu and SaveToDB Actions menu,

Azure Monthly Cash Flow

This example is a simple application of monthly cash flow.

The application contains one database table and several stored procedures.

However, take a look at this example as it shows how easily to get the real multi-user work in Microsoft Excel.

The example shows core SaveToDB features available from-the-box, without any coding:

  • Saving data changes of database tables back to a database.
  • Changing queries using the ribbon Query List.
  • Changing WHERE filters of the table and view queries using the ribbon parameters.
  • Changing stored procedure parameters using the ribbon parameters.

Azure Monthly Cash Flow + VBA

This example is a simple application of monthly cash flow.

The example shows how to work with normalized tables and to use the SaveToDB add-in as a VBA library.

Azure Budget

This is an example application for budgeting that extends the Azure Monthly Cash Flow application. It adds a powerful feature:

  • Saving data changes of database views and stored procedures to a database using custom INSERT, UPDATE, and DELETE procedures.

The example shows how to use complex input forms to update normalized database tables.

Azure Stocks

This is an example application for stock analysts.

The example shows advanced SaveToDB features:

  • Saving and restoring table formats and Excel formula columns when the query is changed.
  • Saving data changes using stored procedures as cell change event handlers.
  • Changing report parameters using the ribbon parameters.
  • Updating named cells with changed parameter values and backward.
  • Drill-down database queries using the double-click.
  • Drill-down database queries using the context menu.

You may easily add these features to your existing Excel applications.

Azure Tasks

This is an example application for working with tasks.

The example shows advanced SaveToDB features:

  • Saving Excel formula values to a database.
  • Saving and restoring table formats and Excel formula columns when the query is changed.
  • Populating ribbon parameter values using views.
  • Customizing and changing table views in Microsoft Excel.

This is a useful example created for learning native Microsoft Excel formulas, conditional formatting, and working with table features.

Database Management

This is an example application for managing user and role permissions on Microsoft SQL Server and Microsoft Azure SQL Database.

You may use this application in your real work. This is a more comfortable way to control and change the rights than the use of SQL Server Management Studio.

The example shows how to use stored procedures as Excel event handlers to easily modify database data including database permissions.

Extended Properties Editor

This example shows how to view and edit database object extended properties using Microsoft Excel.

This example is simple, but it can be useful for developer teams.

The example shows how to use stored procedures as Excel change event handlers.

This example is not work in Microsoft Azure SQL Database as the extended properties are not supported.

Installing and Uninstalling Examples

Installing Application Examples for Microsoft SQL Server

Use the following steps to install the application examples for Microsoft SQL Server:

  1. Install example database objects.
  2. Change connection data in Microsoft Excel example workbooks.

Installing Examples

The source codes of examples for Microsoft SQL Server are located in the "source code" subfolders.

To install the example, open the subfolder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" and "master" connections.
  2. Run 2-edit-grants.cmd and edit example users if applicable.
  3. Run 3-install-savetodb-framework.cmd if SaveToDB Framework for Microsoft SQL Server is not installed yet.
  4. Run 4-install-application.cmd.
  5. Run 5-clear-credentials.cmd and clear the "setup" and "master" connection passwords.

You may select in the first step the target server and database, and the setup credentials.

The "setup" and "master" connection users must have database administrator permissions.

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-install.sql file to install the examples.

Changing Excel Workbook Connections

Examples contain preconfigured Excel workbooks.

You should change the connection server, database, username and the password in the workbooks if you have changed the default settings in the previous step.

You may use the Change Connection Wizard to modify all connections at once:

  1. Select the SaveToDB tab and run the Change Connection Wizard,
  2. Connect to the target database using the new target username and password.
  3. Select all the connections and click Finish.

Then you should refresh all the data tables. You may use the Reload, Reload All Workbook Tables menu item.

Default usernames and passwords:

ExampleDeveloper LoginDeveloper PasswordUser LoginUser Password
Azure Calendarcalendar10_developerDev_2011#_Xls4168calendar10_userUsr_2011#_Xls4168
Azure Catalogcatalog12_developerDev_2011#_Xls4168catalog12_userUsr_2011#_Xls4168
Azure Monthly Cash Flowpayments30_developerDev_2011#_Xls4168payments30_userUsr_2011#_Xls4168
Azure Budgetbudget40_developerDev_2011#_Xls4168budget40_userUsr_2011#_Xls4168
Azure Stocksstocks50_developerDev_2011#_Xls4168stocks50_userUsr_2011#_Xls4168
Azure Taskstasks60_developerDev_2011#_Xls4168tasks60_userUsr_2011#_Xls4168

The Database Management and Extended Properties Editor examples work with the current user permissions.

Uninstalling Application Examples for Microsoft SQL Server

To uninstall the application example, open the "source code" folder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" and "master" connections.
  2. Run application-remove.cmd.
  3. Run 5-clear-credentials.cmd and clear the "setup" and "master" connection passwords.

The "setup" and "master" connection users must have database administrator permissions.

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-remove.sql file.

Command Line Utility gsqlcmd

The batch files to install and remove the examples use the command line utility gsqlcmd.
gsqlcmd allows executing SQL scripts and exporting data against all SaveToDB supported database platforms including Microsoft SQL Server.

SaveToDB installs gsqlcmd and changes the PATH environment variable that allows using gsqlcmd anywhere.

SaveToDB SDK includes the gsqlcmd folder with the executables, and the utility is available from the SaveToDB SDK subfolders only.

Version History

Version 7.2, March 20, 2017

[+] Developer Guide 7.2.
[*] Updated SaveToDB Framework 7.2.

Version 7.1, February 3, 2017

[+] Developer Guide 7.1.

Version 7.0, January 17, 2017

[*] Updated SaveToDB Framework 7.0, batch files, and readme.txt.
[*] Updated example dates.

System Requirements

Supported Versions of SaveToDB:

  • SaveToDB 5.0 and higher

Supported Versions of SaveToDB Framework for Microsoft SQL Server:

  • SaveToDB Framework 5.0 for Microsoft SQL Server and higher

Supported Versions of Microsoft SQL Server:

  • Microsoft SQL Server 2008, 2008 R2
  • Microsoft SQL Server 2012, 2014, 2016 including Express LocalDB
  • Microsoft Azure SQL Database

SaveToDB Examples for Microsoft SQL Server Compact

SaveToDB SDK includes ready-to-use Microsoft Excel workbooks and Microsoft SQL Server Compact databases for a dozen of applications and examples.

The SDK includes complete source codes and batch files used to install and remove applications.

The examples for Microsoft SQL Server Compact show a complete set of SaveToDB features used for Excel application development.

Developer Guide 6.5

This guide shows how to develop Microsoft Excel applications using the SaveToDB add-in with Microsoft SQL Server Compact.

This is a complete guide. Try it first.

Application - Web Finance

The example application allows getting end-of-a-day stock data from Google Finance or Yahoo! Finance and saving loaded data into a local Microsoft SQL Server Compact database.

Application - AdWords Keyword Generator

This application allows generation keywords for Google AdWords using semantic model.

Example - Monthly Cash Flow

The example application allows getting money transactions in one place and building cash flow statements.
The application uses a local Microsoft SQL Server Compact database, so you may use it right after installation.

Example - Monthly Cash Flow Adv + VBA

The example application allows getting money transactions in one place and building cash flow statements.
In addition, the example shows how to work with normalized tables and to use SaveToDB as a VBA library.
The application uses a local Microsoft SQL Server Compact database, so you may use it right after installation.

Example - Northwind

The example shows new SaveToDB 5.0 features:

  • Cursors and forms fields.
  • Implementing master-detail interfaces.
  • Showing images.
  • Pivot table views.

Example - Budget Pivot

The example shows new SaveToDB 5.0 features:

  • Using pivot tables as edit forms.
  • Pivot table views.

Example - DDE Interactive Brokers

The example shows how to use and configure DDE formulas without using macros.
The example works with Trader Workstation (TWS), a powerful trading platform from Interactive Brokers.

Example - DDE Thinkorswim

The example shows how to use and configure DDE formulas without using macros.
The example works with Thinkorswim Desktop, a powerful trading platform from Thinkorswim, Division of TD Ameritrade.

Example - Loading Text

The example shows how to load data from text files in the CSV, XML, and HTML formats into a database.

Example - Saving Changes to Database

The example shows how to save data changes back to a database using different techniques.

Example - Saving Formula Values

The example shows how to save values calculated by Excel formulas to a database.

Example - Using Event Handlers

The example shows how to use database objects and codes for Excel event handling.
This is a simple and powerful feature. It allows saving changes on the cell change event, implementing master-detail interfaces, drill-down queries, etc.

Example - Using Macros

The example shows how to use macros with the SaveToDB add-in.
The SaveToDB add-in brings additional benefits for traditional Excel applications built using macros.

Installing and Uninstalling Examples

Installing Application Examples for Microsoft SQL Server Compact

Use the following steps to install the application examples for Microsoft SQL Server Compact:

  1. Install example database objects.
  2. Change connection data in Microsoft Excel example workbooks.

Installing Examples

The source codes of examples for Microsoft SQL Server Compact are located in the "source code" subfolders.

To install the example, open the subfolder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run 2-create-database.cmd if the database does not exist.
  3. Run 3-install-savetodb-framework.cmd if SaveToDB Framework for Microsoft SQL Server Compact is not installed yet.
  4. Run 4-install-application.cmd.
  5. Run 5-clear-credentials.cmd and clear the "setup" connection password.

You may create a Microsoft SQL Server Compact database in a visual mode in the first step.

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-install.sql file to install the examples.

Changing Excel Workbook Connections

Examples contain preconfigured Excel workbooks.

You should change the database files and passwords in the workbooks if you have changed the default settings in the previous step.

By default, all the examples use database files with empty passwords in the example folders.

You may use the Change Connection Wizard to modify all connections at once:

  1. Select the SaveToDB tab and run the Change Connection Wizard,
  2. Connect to the target database.
  3. Select all the connections and click Finish.

Then you should refresh all the data tables. You may use the Reload, Reload All Workbook Tables menu item.

Uninstalling Application Examples for Microsoft SQL Server Compact

To uninstall the application example, open the "source code" folder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run application-remove.cmd.
  3. Run 5-clear-credentials.cmd and clear the "setup" connection password.

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-remove.sql file.

Command Line Utility gsqlcmd

The batch files to install and remove the examples use the command line utility gsqlcmd.
gsqlcmd allows executing SQL scripts and exporting data against all SaveToDB supported database platforms including Microsoft SQL Server Compact.

SaveToDB installs gsqlcmd and changes the PATH environment variable that allows using gsqlcmd anywhere.

SaveToDB SDK includes the gsqlcmd folder with the executables, and the utility is available from the SaveToDB SDK subfolders only.

Version History

Version 7.2, March 20, 2017

[*] Updated SaveToDB Framework 7.2.

Version 7.0, January 17, 2017

[*] Updated SaveToDB Framework 7.0, batch files, and readme.txt.
[*] Updated example dates.

System Requirements

Supported Versions of SaveToDB:

  • SaveToDB 5.0 and higher

Supported Versions of SaveToDB Framework for Microsoft SQL Server Compact:

  • SaveToDB Framework 5.0 for Microsoft SQL Server Compact and higher

Supported Versions of Microsoft SQL Server Compact:

  • Microsoft SQL Server Compact 4.0