SQL Script Service Request Requirements

Review the SQL Script Requirements for Deltek Vantagepoint cloud databases. This list is not exhaustive, but it does address the most common missed requirements that result in SQL script requests being returned for correction.

The use of "SP_" as a prefix for customer stored procedures is not allowed.

This requirement does the following:

• Distinguishes between system and stored processes and non-system stored processes.
• Avoids performance issues caused by checking for equivalent objects in primary and local, leading to extra metadata overhead.
• Avoids ambiguity or collisions if a name is chosen that already exists in the system catalog.

Scripts should be Unicode compliant because all cloud databases have Unicode enabled.

SQL Scripts accessing Deltek tables (including custom grid tables created from the application) must use NOLOCK hint while reading data from it.

This requirement does the following:

• Helps you avoid running into blocking or deadlock issues when there is a conflict and a Deltek process is updating the data at the same time.
• Helps the custom procedure complete quickly in cases where transaction isolation levels are incorrectly defined or not set.

Any SQL or custom script, which changes Deltek Hub tables, must include logic to disable the audit trail trigger at the beginning of the script and then re-enable the audit trail trigger at the end of the script.

Hub tables include the Vantagepoint Hubs, grids within those Hubs, and any user-defined hub or grid.

This requirement does the following:

• Avoids performance issues when the custom process is conducting bulk deletes and reloads of User Defined tables containing large data sets, which can lead to blocking or long-running query issues and replication issues.
• Checks to see if auditing is enabled on the table and, if so, disables the audit trigger before the operation and re-enables it after. Take care not to just disable and then re-enable because the trigger may already be disabled; in this case, it should not be enabled.
• If you are deleting all the rows in the table first, consider using the truncate command instead of delete. Truncate is faster and is not logged by SQL Server, which helps keep the SQL Server log from becoming large and helps the overall health of the system.

Sample Code: How to Disable/Enable the Audit Trail in your Stored Procedure

/* SAMPLE FOR STORED PROCEDURE - START Here */
CREATE OR ALTER PROCEDURE [dbo].[spABC_SPNameHere]
AS
-- START: DISABLE AUDITING FOR THIS SESSION
DECLARE @company nvarchar(14) = (SELECT [dbo].[GetActiveCompany]() )
DECLARE @auditingEnabled varchar(1)
DECLARE @username nvarchar(32) = (SELECT [dbo].[FW_GetUsername]())
DECLARE @culture varchar(10) = (SELECT dbo.FW_GetActiveCultureName())
DECLARE @auditDetail varchar(1) = (SELECT [dbo].[GetVisionAuditingDetail] ())
DECLARE @auditSource nvarChar(3) = (SELECT [dbo].[GetVisionAuditSource]() )
DECLARE @auditTime datetime = (SELECT [dbo].[GetVisionAuditTime] () )
if @auditTime = '1900-01-01 00:00:00.000'
begin
		SET @auditingEnabled = 'N'
end
else
begin
		SET @auditingEnabled = 'Y'
end
if @auditingEnabled = 'Y'
begin
		exec setContextInfo
		@StrCompany = @company,
		@StrUserName = @username,
		@StrCultureName = @culture,
		@StrAuditingEnabled ='N'
End
-- END: DISABLE AUDITING FOR THIS SESSION
/* ---CODE START IN HERE ---*/
DML code HERE
Sample:
UDPATE TableHere SET FIELD1 = 'UPDATED FIELD' WHERE <ConditionsHere>
DELETE TableHere 'UPDATED FIELD' WHERE <ConditionsHere>
INSERT TableHere (Field1, Field2) VALUE (Value1, Value2)
/* ---CODE END IN HERE ---*/
-- START: RE-ENABLE AUDITING FOR THIS SESSION
IF @auditingEnabled = 'Y'
begin
		exec setContextInfo
		@StrCompany = @company,
		@StrUserName = @username,
		@StrCultureName = @culture,
		@StrAuditingEnabled ='Y',
		@StrAuditSource = @auditSource,
		@strAuditingDetail = @auditDetail
End
-- END: RE-ENABLE AUDITING FOR THIS SESSION
End
/* SAMPLE FOR STORED PROCEDURE - END Here */

Sample Code: How to Disable/Enable the Audit Trail for Regular DML Statements:

/* SAMPLE FOR NORMAL SCRIPT - START Here */
-- START: DISABLE AUDITING FOR THIS SESSION
	DECLARE @company nvarchar(14) = (SELECT [dbo].[GetActiveCompany]() )
	DECLARE @auditingEnabled varchar(1)
	DECLARE @username nvarchar(32) = (SELECT [dbo].[FW_GetUsername]())
	DECLARE @culture varchar(10) = (SELECT dbo.FW_GetActiveCultureName())
	DECLARE @auditDetail varchar(1) = (SELECT [dbo].[GetVisionAuditingDetail]
())
	DECLARE @auditSource nvarChar(3) = (SELECT [dbo].[GetVisionAuditSource]()
)
	DECLARE @auditTime datetime = (SELECT [dbo].[GetVisionAuditTime] () )
	if @auditTime = '1900-01-01 00:00:00.000'
	begin
						SET @auditingEnabled = 'N'
	end
	else
	begin
						SET @auditingEnabled = 'Y'
	end
	if @auditingEnabled = 'Y'
	begin
						exec setContextInfo
								@StrCompany = @company,
								@StrUserName = @username,
								@StrCultureName = @culture,
								@StrAuditingEnabled ='N'
		End
-- END: DISABLE AUDITING FOR THIS SESSION

/* ---CODE START IN HERE ---*/
DML code HERE
Sample:
UDPATE TableHere SET FIELD1 = 'UPDATED FIELD' WHERE <ConditionsHere>
DELETE TableHere 'UPDATED FIELD' WHERE <ConditionsHere>
INSERT TableHere (Field1, Field2) VALUE (Value1, Value2)
/* ---CODE END IN HERE ---*/

-- START: RE-ENABLE AUDITING FOR THIS SESSION
	IF @auditingEnabled = 'Y'
	begin
						exec setContextInfo
								@StrCompany = @company,
								@StrUserName = @username,
								@StrCultureName = @culture,
								@StrAuditingEnabled ='Y',
								@StrAuditSource = @auditSource,
								@strAuditingDetail = @auditDetail
		End
-- END: RE-ENABLE AUDITING FOR THIS SESSION

Restricted SQL Objects

You are not permitted to create the following custom SQL objects via SQL scripts in Basic or Flex Cloud environments:

• Custom Triggers
• Custom Tables
• Custom Views
• Custom Indexes

If you attempt to create any of these object types in a Basic or Flex Cloud environment, the SQL script will be rejected.

SQL Scripts that deploy encrypted objects in the Deltek Cloud are not allowed and will be rejected.