Qualifying Object Names
- Do not qualify objects with the database name. Datical takes care of that.
- For a Single-Schema project, you do not have to qualify object names with the schema name. If not qualified, Datical uses the default schema for the user (dbo is the default).
- For a Multi-Schema project, you must qualify object names with the schema name so they get deployed to the correct schema during packaging.
- For a Multi-Database project, you must qualify object names with the schema name so they get deployed to the correct schema during packaging. You typically organized folders by database and schema. You may also use only the database folder level. See Multi-database Projects.
SQL Server SCM Packaging Patterns
Note that Datical packages non-transient files differently from transient files. Place scripts for Oracle objects in packaging folders as follows.
- Non-transient - In-place editing, packaging and no archiving of database changes so they can be managed similar to application code. This applies to the following object types:
- Procedures (when scripts are placed in the
procedurefolder) - Functions (when scripts are placed in the
functionfolder) - Views (when scripts are placed in the
viewfolder) - Triggers (when scripts are placed in the
triggerfolder)
- Procedures (when scripts are placed in the
- Transient - Database changes are archived after packaging (all other folders).
Objects and Packaging Folders
Use the specified folder for listed SQL Server database operations.
| Operation and Object Type | Packaging Folders (fixed folders) | Package Methods (flexible folders) | Archived | Notes |
|---|---|---|---|---|
| CREATE/ALTER/DROP DATABASE LINK | sql_direct | direct | Yes | |
| CREATE/ALTER/DROP FUNCTION | function | storedlogic | No | |
| CREATE/ALTER/DROP INDEX | | convert, ddl_direct, direct | Yes | |
| CREATE/ALTER/DROP PROCEDURE | procedure | storedlogic | No | |
| CREATE/ALTER/DROP QUEUE | sql_direct | direct | Yes | |
| CREATE/ALTER/DROP SEQUENCE | | convert, ddl_direct, direct | Yes | |
| CREATE/ALTER/DROP SYNONYM | | convert, ddl_direct, direct | Yes | For ALTER and CREATE OR REPLACE synonym only use ddl_direct or sql_direct/direct. (Do not use ddl/convert with ALTER and CREATE OR REPLACE synonym.) |
CREATE/ALTER/DROP TABLE | | convert, ddl_direct, direct | Yes | |
| CREATE/ALTER/DROP TRIGGER |
| storedlogic | No | |
| CREATE/ALTER/DROP TYPE | sql_direct | direct | Yes | |
| CREATE/ALTER/DROP VIEW |
| storedlogic | No | |
| CREATE/ALTER/DROP VIEW INDEX | | convert, ddl_direct, direct | Yes | |
| RENAME <DB_OBJECT> | sql_direct | direct | Yes |
| Operation | Packaging Folder (fixed folders) | Package Method (flexible folders) | Archived |
|---|---|---|---|
| INSERT, UPDATE, DELETE, SELECT | data_dml | data_dml | Yes |
| GRANT, REVOKE | sql_direct | direct | Yes |
Error Handling
Do not put error-handling statements in the SQL scripts.
For SQL Server databases, do not include IF EXISTS statements with an object definition.
Datical DB has its own error-handling that wraps SQL scripts. Specifying error-handling within the statements is not necessary.
Deployment Packager fails with an error if it encounters statements before an object definition.
See also these pages for overview of packaging workflows, guidelines for writing scripts, and which packaging methods or folders to use for which types of changes: