SaveToDB Framework Procedures
Procedure | Description |
---|---|
xls.usp_database_permissions | This procedure is an Excel form to manage database-level permissions. |
xls.usp_database_permissions_change | This procedure updates a database on cell changes of xls.usp_database_permissions. |
xls.usp_object_permissions | This procedure is an Excel form to manage object-level permissions. |
xls.usp_object_permissions_change | This procedure updates a database on cell changes of xls.usp_object_permissions. |
xls.usp_principal_permissions | This procedure is an Excel form to manage principal permissions. |
xls.usp_principal_permissions_change | This procedure updates a database on cell changes of xls.usp_principal_permissions. |
xls.usp_role_members | This procedure is an Excel form to manage role members. |
xls.usp_role_members_change | This procedure updates a database on cell changes of xls.usp_role_members. |
xls.usp_translations | This procedure is an Excel form to select and edit translations in all languages. |
xls.usp_translations_change | This procedure updates a database on cell changes of xls.usp_translations. |
xls.xl_actions_add_framework_developer | |
xls.xl_actions_add_framework_user | |
xls.xl_actions_generate_constraints | This procedure generates primary key, unique, and foreign key constraints. |
xls.xl_actions_generate_handlers | This procedure generates SaveToDB event handlers. |
xls.xl_actions_generate_procedures | This procedure generates edit procedures and cell change handlers. |
xls.xl_actions_remove_framework_developer | |
xls.xl_actions_remove_framework_user | |
xls.xl_actions_set_framework_10_mode | |
xls.xl_actions_set_framework_9_mode | |
xls.xl_actions_set_role_permissions | This procedure sets permissions for the xls_users, xls_developers, and xls_formats roles. |
xls.xl_delete_translation | This procedure deletes rows of xls.view_all_translations. |
xls.xl_export_settings | This procedure exports SaveToDB Framework settings. |
xls.xl_import_formats | This procedure imports SaveToDB Framework settings into xls.formats. |
xls.xl_import_handlers | This procedure imports SaveToDB Framework settings into xls.handlers. |
xls.xl_import_objects | This procedure imports SaveToDB Framework settings into xls.objects. |
xls.xl_import_translations | This procedure imports SaveToDB Framework settings into xls.translations. |
xls.xl_import_workbooks | This procedure imports SaveToDB Framework settings into xls.workbooks. |
xls.xl_parameter_values_principal | This procedure selects database principals for Excel ribbon parameters. |
xls.xl_parameter_values_schema | This procedure selects schemas for Excel ribbon parameters. |
xls.xl_update_table_format |
xls.usp_database_permissions
This procedure is an Excel form to manage database-level permissions.
Use this form to check and manage database-level permissions.
The form uses actual user permissions to show and change permissions.
So, you may grant execute permissions to business users to check permissions themselves.
xls.usp_database_permissions_change
This procedure updates a database on cell changes of xls.usp_database_permissions.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@column_name | nvarchar(128) | IN | The column name of the edited cell (expected a permission name) |
@cell_value | int | IN | Possible values and applied commands:
- NULL - REVOKE - G or GRANT - GRANT - G+ or GRANT+ - GRANT WITH GRANT OPTION - D or DENY - DENY - D+ or DENY+ - DENY CASCADE - R or REVOKE - REVOKE - R- or REVOKE- - REVOKE WITH GRANT OPTION FOR - R+ or REVOKE+ - REVOKE CASCADE |
@principal | nvarchar(128) | IN | Database principal (user or role) |
xls.usp_object_permissions
This procedure is an Excel form to manage object-level permissions.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@principal | nvarchar(128) | IN | Database principal (user or role) to filter data |
@schema | nvarchar(128) | IN | Database schema or object schema to filter data |
@type | nvarchar(128) | IN | Object type to filter data:
- schema - table - view - procedure - function |
@has_any | bit | IN | Value to filter data:
1 - an object has any permission 0 - an object has no any permision |
@has_direct | bit | IN | Value to filter data:
1 - an object has any direct permission 0 - an object has no any direct permision |
Use this form to check and manage object-level permissions.
The form uses actual user permissions to show and change permissions.
So, you may grant execute permissions to business users to check permissions themselves.
xls.usp_object_permissions_change
This procedure updates a database on cell changes of xls.usp_object_permissions.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@column_name | nvarchar(128) | IN | The column name of the edited cell (expected a permission name) |
@cell_value | int | IN | Possible values and applied commands:
- NULL - REVOKE - G or GRANT - GRANT - G+ or GRANT+ - GRANT WITH GRANT OPTION - D or DENY - DENY - D+ or DENY+ - DENY CASCADE - R or REVOKE - REVOKE - R- or REVOKE- - REVOKE WITH GRANT OPTION FOR - R+ or REVOKE+ - REVOKE CASCADE |
@principal | nvarchar(128) | IN | Database principal (user or role) |
@schema | nvarchar(128) | IN | Database schema or object schema |
@name | nvarchar(128) | IN | Database object name |
xls.usp_principal_permissions
This procedure is an Excel form to manage principal permissions.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@principal | nvarchar(128) | IN | Database principal (user or role) |
@name | nvarchar(128) | IN | Database principal (user or role) |
@has_any | bit | IN | Value to filter data:
1 - an object has any permission 0 - an object has no any permision |
Use this form to check and manage principal permissions.
The form uses actual user permissions to show and change permissions.
So, you may grant execute permissions to business users to check permissions themselves.
xls.usp_principal_permissions_change
This procedure updates a database on cell changes of xls.usp_principal_permissions.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@column_name | nvarchar(128) | IN | The column name of the edited cell (expected a permission name) |
@cell_value | int | IN | Possible values and applied commands:
- NULL - REVOKE - G or GRANT - GRANT - G+ or GRANT+ - GRANT WITH GRANT OPTION - D or DENY - DENY - D+ or DENY+ - DENY CASCADE - R or REVOKE - REVOKE - R- or REVOKE- - REVOKE WITH GRANT OPTION FOR - R+ or REVOKE+ - REVOKE CASCADE |
@principal | nvarchar(128) | IN | Database principal (user or role) |
@name | nvarchar(128) | IN | Database principal (user or role) |
@type | nvarchar(128) | IN | Principal type:
- sql_user - windows_user - application_role - database_role |
xls.usp_role_members
This procedure is an Excel form to manage role members.
Use this form to add business users to the xls_users and xls_formats roles easily.
The form uses actual user permissions to show and change permissions.
So, you may grant execute permissions to business users to check permissions themselves.
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 xls.usp_role_members.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@column_name | nvarchar(128) | IN | The column name of the edited cell (expected a role name) |
@cell_number_value | int | IN | 1 - Add the user to the role
0 - Remove the user from the role |
@name | nvarchar(128) | IN | User or role name (value of the name column) |
@data_language | char(2) | IN | The 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.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@field | nvarchar(128) | IN | The source field of the xls.translations table: TRANSLATED_NAME, TRANSLATED_DESC, or TRANSLATED_COMMENT. |
@schema | nvarchar(128) | IN | xls.translations.TABLE_SCHEMA |
@is_complete | bit | IN |
Editing data requires the SaveToDB Enterprise edition.
xls.usp_translations_change
This procedure updates a database on cell changes of xls.usp_translations.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@column_name | nvarchar(128) | IN | The column name of the edited cell (expected xls.translations.LANGUAGE_NAME). |
@cell_value | nvarchar(max) | IN | A new value of the edited cell used to update for the source field. |
@TABLE_SCHEMA | nvarchar(128) | IN | xls.translations.TABLE_SCHEMA |
@TABLE_NAME | nvarchar(128) | IN | xls.translations.TABLE_NAME |
@COLUMN | nvarchar(128) | IN | xls.translations.COLUMN_NAME |
@field | nvarchar(128) | IN | The source field of the xls.translations table: TRANSLATED_NAME, TRANSLATED_DESC, or TRANSLATED_COMMENT. |
xls.xl_actions_add_framework_developer
Parameter | DataType | Mode | Comment |
---|---|---|---|
@name | nvarchar(128) | IN |
xls.xl_actions_add_framework_user
Parameter | DataType | Mode | Comment |
---|---|---|---|
@name | nvarchar(128) | IN | |
@allow_updating_formats | bit | IN |
xls.xl_actions_generate_constraints
This procedure generates primary key, unique, and foreign key constraints.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@Drop | bit | IN | 1 - Generate DROP commands
0 - Do not generate DROP commands |
@Create | bit | IN | 1 - Generate CREATE commands
0 - Do not generate CREATE commands |
@ConstraintType | tinyint | IN | 1 - Primary key constraint
2 - Unique constraint 3 - Index 4 - Foreign key constraint |
@SCHEMA | nvarchar(128) | IN | Table schema |
@TABLE | nvarchar(128) | IN | Table name |
@COLUMN | nvarchar(128) | IN | Column name |
@REFERENTIAL_SCHEMA | nvarchar(128) | IN | Referenced table schema |
@REFERENTIAL_NAME | nvarchar(128) | IN | Referenced table name |
@REFERENTIAL_COLUMN | nvarchar(128) | IN | Referenced column name |
@ON_UPDATE | nvarchar(128) | IN | CASCADE, SET NULL, SET DEFAULT, OR NULL (NO_ACTION) |
@ON_DELETE | nvarchar(128) | IN | CASCADE, SET NULL, SET DEFAULT, OR NULL (NO_ACTION) |
@CONSTRAINT | nvarchar(128) | IN | Constraint name |
@ExecuteScript | bit | IN | 1 - Execute the script
0 - Do not execute the script (default) |
@DataLanguage | char(2) | IN | The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter. |
@SelectCommands | bit | IN | 1 - Select generated commands (default if @PrintCommands IS NULL)
0 - Do not select generated commands (default if @PrintCommands IS NOT NULL) |
@PrintCommands | bit | IN | 1 - 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.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@BaseTableSchema | nvarchar(128) | IN | Base object schema |
@BaseTableName | nvarchar(128) | IN | Base object name |
@TargetObjectSchema | nvarchar(128) | IN | Target object schema |
@TargetObjectName | nvarchar(128) | IN | Target object name |
@HandlerType | int | IN | 1 - ValidationList
2 - ParameterValues 3 - ParameterValues with the NULL value 4 - ContextMenu 5 - DoubleClick 6 - SelectionChange 7 - Actions |
@GenerateTargetAsView | bit | IN | 1 - Generate the target object as a view
0 - Generate the target object as a procedure |
@ExecuteScript | bit | IN | 1 - Execute the script
0 - Do not execute the script (default) |
@RecreateProceduresIfExist | bit | IN | 1 - Execute DROP first if the target object exists
0 - Do not execute DROP commands |
@DataLanguage | char(2) | IN | The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter. |
@SelectCommands | bit | IN | 1 - Select generated commands (default if @PrintCommands IS NULL)
0 - Do not select generated commands (default if @PrintCommands IS NOT NULL) |
@PrintCommands | bit | IN | 1 - Print generated commands (using the PRINT command)
0 - Do not print generated commands (default) |
@MySqlStyle | bit | IN | 1 - Generate in MySQL style like @column_name
2 - Generate in SQL Server style like @ColumnName |
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.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@BaseTableSchema | nvarchar(128) | IN | Base object schema |
@BaseTableName | nvarchar(128) | IN | Base object name |
@SelectObjectSchema | nvarchar(128) | IN | Target schema of generated objects |
@SelectObjectName | nvarchar(128) | IN | Target name of the select object.
Edit procedures and change handlers get the same name with the standard suffixes. |
@GenerateSelectObject | bit | IN | 1 - Generate a view or procedure to select data
0 - Do not generate a select object |
@GenerateEditProcedures | bit | IN | 1 - Generate procedures for INSERT, UPDATE, and DELETE operations
0 - Do not generate edit procedures |
@GenerateChangeHandler | bit | IN | 1 - Generate a procedure to process cell change events
0 - Do not generate change handler |
@GenerateSelectAsView | bit | IN | 1 - Generate the target SELECT object as a view
0 - Generate the target SELECT object as a procedure |
@ExecuteScript | bit | IN | 1 - Execute the script
0 - Do not execute the script (default) |
@RecreateProceduresIfExist | bit | IN | 1 - Execute DROP first if the target object exists
0 - Do not execute DROP commands |
@DataLanguage | char(2) | IN | The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter. |
@SelectCommands | bit | IN | 1 - Select generated commands (default if @PrintCommands IS NULL)
0 - Do not select generated commands (default if @PrintCommands IS NOT NULL) |
@PrintCommands | bit | IN | 1 - Print generated commands (using the PRINT command)
0 - Do not print generated commands (default) |
@MySqlStyle | bit | IN | 1 - Generate in MySQL style like @column_name
2 - Generate in SQL Server style like @ColumnName |
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 procedures.
Developers may also edit the body to solve specific tasks.
xls.xl_actions_remove_framework_developer
Parameter | DataType | Mode | Comment |
---|---|---|---|
@name | nvarchar(128) | IN |
xls.xl_actions_remove_framework_user
Parameter | DataType | Mode | Comment |
---|---|---|---|
@name | nvarchar(128) | IN |
xls.xl_actions_set_framework_10_mode
xls.xl_actions_set_framework_9_mode
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.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@TABLE_SCHEMA | nvarchar(128) | IN | xls.translations.TABLE_SCHEMA |
@TABLE_NAME | nvarchar(128) | IN | xls.translations.TABLE_NAME |
@COLUMN_NAME | nvarchar(255) | IN | xls.translations.COLUMN_NAME |
@LANGUAGE_NAME | char(2) | IN | xls.translations.LANGUAGE_NAME |
See the configuration in the xls.objects table.
xls.xl_export_settings
This procedure exports SaveToDB Framework settings.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@part | tinyint | IN | NULL - all
1 - xls.objects 2 - xls.handlers 3 - xls.translations 4 - xls.formats 5 - xls.workbooks |
@as_exec_import | bit | IN | 0 - export as INSERT commands
1 - export as EXEC insert procedure commands |
@sort_by_names | bit | IN | 1 - sort by names
0 - sort by id (default) |
@schema | nvarchar(128) | IN | Target schema to export settings or NULL to export all settings |
@language | char(2) | IN | xls.translations.LANGUAGE_NAME |
@use_go | bit | IN | 1 - Add the GO command
0 - Do not add the GO command |
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.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@TABLE_SCHEMA | nvarchar(128) | IN | xls.formats.TABLE_SCHEMA |
@TABLE_NAME | nvarchar(128) | IN | xls.formats.TABLE_NAME |
@TABLE_EXCEL_FORMAT_XML | xml | IN | xls.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.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@TABLE_SCHEMA | nvarchar(20) | IN | xls.handlers.TABLE_SCHEMA |
@TABLE_NAME | nvarchar(128) | IN | xls.handlers.TABLE_NAME |
@COLUMN_NAME | nvarchar(128) | IN | xls.handlers.COLUMN_NAME |
@EVENT_NAME | varchar(25) | IN | xls.handlers.EVENT_NAME |
@HANDLER_SCHEMA | nvarchar(20) | IN | xls.handlers.HANDLER_SCHEMA |
@HANDLER_NAME | nvarchar(128) | IN | xls.handlers.HANDLER_NAME |
@HANDLER_TYPE | nvarchar(25) | IN | xls.handlers.HANDLER_TYPE |
@HANDLER_CODE | nvarchar(max) | IN | xls.handlers.HANDLER_CODE |
@TARGET_WORKSHEET | nvarchar(128) | IN | xls.handlers.TARGET_WORKSHEET |
@MENU_ORDER | int | IN | xls.handlers.MENU_ORDER |
@EDIT_PARAMETERS | bit | IN | xls.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.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@TABLE_SCHEMA | nvarchar(128) | IN | xls.objects.TABLE_SCHEMA |
@TABLE_NAME | nvarchar(128) | IN | xls.objects.TABLE_NAME |
@TABLE_TYPE | nvarchar(128) | IN | xls.objects.TABLE_TYPE |
@TABLE_CODE | nvarchar(max) | IN | xls.objects.TABLE_CODE |
@INSERT_OBJECT | nvarchar(max) | IN | xls.objects.INSERT_OBJECT |
@UPDATE_OBJECT | nvarchar(max) | IN | xls.objects.UPDATE_OBJECT |
@DELETE_OBJECT | nvarchar(max) | IN | xls.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.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@TABLE_SCHEMA | nvarchar(128) | IN | xls.translations.TABLE_SCHEMA |
@TABLE_NAME | nvarchar(128) | IN | xls.translations.TABLE_NAME |
@COLUMN_NAME | nvarchar(128) | IN | xls.translations.COLUMN_NAME |
@LANGUAGE_NAME | char(2) | IN | xls.translations.LANGUAGE_NAME |
@TRANSLATED_NAME | nvarchar(128) | IN | xls.translations.TRANSLATED_NAME |
@TRANSLATED_DESC | nvarchar(1024) | IN | xls.translations.TRANSLATED_DESC |
@TRANSLATED_COMMENT | nvarchar(2000) | IN | xls.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.
Parameter | DataType | Mode | Comment |
---|---|---|---|
@NAME | nvarchar(128) | IN | xls.workbooks.NAME |
@TEMPLATE | nvarchar(255) | IN | xls.workbooks.TEMPLATE |
@DEFINITION | nvarchar(max) | IN | xls.workbooks.DEFINITION |
@TABLE_SCHEMA | nvarchar(128) | IN | xls.workbooks.TABLE_SCHEMA |
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_parameter_values_principal
This procedure selects database principals for Excel ribbon parameters.
Underlying table: sys.database_principals
xls.xl_parameter_values_schema
This procedure selects schemas for Excel ribbon parameters.
Underlying tables: sys.schemas and sys.database_principals
xls.xl_update_table_format
Parameter | DataType | Mode | Comment |
---|---|---|---|
@schema | nvarchar(128) | IN | |
@name | nvarchar(128) | IN | |
@excelFormatXML | xml | IN |
In this article
- xls.usp_database_permissions
- xls.usp_database_permissions_change
- xls.usp_object_permissions
- xls.usp_object_permissions_change
- xls.usp_principal_permissions
- xls.usp_principal_permissions_change
- xls.usp_role_members
- xls.usp_role_members_change
- xls.usp_translations
- xls.usp_translations_change
- xls.xl_actions_add_framework_developer
- xls.xl_actions_add_framework_user
- xls.xl_actions_generate_constraints
- xls.xl_actions_generate_handlers
- xls.xl_actions_generate_procedures
- xls.xl_actions_remove_framework_developer
- xls.xl_actions_remove_framework_user
- xls.xl_actions_set_framework_10_mode
- xls.xl_actions_set_framework_9_mode
- xls.xl_actions_set_role_permissions
- xls.xl_delete_translation
- xls.xl_export_settings
- xls.xl_import_formats
- xls.xl_import_handlers
- xls.xl_import_objects
- xls.xl_import_translations
- xls.xl_import_workbooks
- xls.xl_parameter_values_principal
- xls.xl_parameter_values_schema
- xls.xl_update_table_format