SaveToDB Framework Procedures

SaveToDB Framework Procedures

ProcedureDescription
xls.usp_database_permissionsThis procedure is an Excel form to manage database-level permissions.
xls.usp_database_permissions_changeThis procedure updates a database on cell changes of xls.usp_database_permissions.
xls.usp_object_permissionsThis procedure is an Excel form to manage object-level permissions.
xls.usp_object_permissions_changeThis procedure updates a database on cell changes of xls.usp_object_permissions.
xls.usp_principal_permissionsThis procedure is an Excel form to manage principal permissions.
xls.usp_principal_permissions_changeThis procedure updates a database on cell changes of xls.usp_principal_permissions.
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 xls.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_add_framework_developer
xls.xl_actions_add_framework_user
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_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_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.xl_parameter_values_principalThis procedure selects database principals for Excel ribbon parameters.
xls.xl_parameter_values_schemaThis 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.

ParameterDataTypeModeComment
@column_namenvarchar(128)INThe column name of the edited cell (expected a permission name)
@cell_valueintINPossible 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
@principalnvarchar(128)INDatabase principal (user or role)

xls.usp_object_permissions

This procedure is an Excel form to manage object-level permissions.

ParameterDataTypeModeComment
@principalnvarchar(128)INDatabase principal (user or role) to filter data
@schemanvarchar(128)INDatabase schema or object schema to filter data
@typenvarchar(128)INObject type to filter data:
- schema
- table
- view
- procedure
- function
@has_anybitINValue to filter data:
1 - an object has any permission
0 - an object has no any permision
@has_directbitINValue 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.

ParameterDataTypeModeComment
@column_namenvarchar(128)INThe column name of the edited cell (expected a permission name)
@cell_valueintINPossible 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
@principalnvarchar(128)INDatabase principal (user or role)
@schemanvarchar(128)INDatabase schema or object schema
@namenvarchar(128)INDatabase object name

xls.usp_principal_permissions

This procedure is an Excel form to manage principal permissions.

ParameterDataTypeModeComment
@principalnvarchar(128)INDatabase principal (user or role)
@namenvarchar(128)INDatabase principal (user or role)
@has_anybitINValue 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.

ParameterDataTypeModeComment
@column_namenvarchar(128)INThe column name of the edited cell (expected a permission name)
@cell_valueintINPossible 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
@principalnvarchar(128)INDatabase principal (user or role)
@namenvarchar(128)INDatabase principal (user or role)
@typenvarchar(128)INPrincipal 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.

ParameterDataTypeModeComment
@column_namenvarchar(128)INThe column name of the edited cell (expected a 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.
@schemanvarchar(128)INxls.translations.TABLE_SCHEMA
@is_completebitIN

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_add_framework_developer

ParameterDataTypeModeComment
@namenvarchar(128)IN

xls.xl_actions_add_framework_user

ParameterDataTypeModeComment
@namenvarchar(128)IN
@allow_updating_formatsbitIN

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
@CreatebitIN1 - Generate CREATE commands
0 - Do not generate CREATE commands
@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)
@MySqlStylebitIN1 - 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.

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)
@MySqlStylebitIN1 - 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

ParameterDataTypeModeComment
@namenvarchar(128)IN

xls.xl_actions_remove_framework_user

ParameterDataTypeModeComment
@namenvarchar(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.

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
@use_gobitIN1 - 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.

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
@TABLE_SCHEMAnvarchar(128)INxls.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

ParameterDataTypeModeComment
@schemanvarchar(128)IN
@namenvarchar(128)IN
@excelFormatXMLxmlIN