Data dictionary properties can be set in a variety of ways. Advantage Data Architect provides a graphical interface for changing these properties, however, sometimes they need to be changed programmatically. This can be done using an API, AdsDDSetDatabaseProperty or using a system procedure.
The sp_ModifyDatabase system procedure sets one database property at a time. To use the system procedure you specify the property using its keyword (i.e. VERSION_MAJOR) and then specify the value (i.e. 2). The example script below changes the database version and adds the date of the update to the comments field.
-- Script to UPDATE the database properties
DECLARE @sComment STRING;
-- Get the current comment
@sComment = (SELECT [Comment] FROM system.dictionary);
-- Add the upgrade info to the comment
@sComment = @sComment + '-- Upgraded to version 2.0 ';
-- Add the current timestamp to the comment
@sComment = @sComment + CONVERT(NOW(), SQL_CHAR);
-- Update the dictionary properties
EXECUTE PROCEDURE sp_ModifyDatabase('VERSION_MAJOR', '2');
EXECUTE PROCEDURE sp_ModifyDatabase('VERSION_MINOR', '0');
EXECUTE PROCEDURE sp_ModifyDatabase('COMMENT', @sComment);
No comments:
Post a Comment