Integration Points

The limitation of not being able to put a non-deterministic rand() call in a SQL User Defined Function forces usage of code like this in a SPROC to generate the SEQ Number that creates uniqueness in Deltek Grid.  If anyone has more info and / or a better way please comment.

Apparently you can get around this limitation and create a UDF see this post: http://www.dbtechie.com/sql-server/user-defined-functions-udf/random-thoughts-on-sql-server/

Here is what I put in my custom grid insert. I had to create the rand number in 2 parts otherwise it would use the exponent notation.

Results as 20 char Seq # ::  XXXXXX1110300245842

,  @ModUser + '1'        -- Username 6 chars
+ Convert(nvarchar(255),100000 + (100000*RAND()))
+ Convert(nvarchar(255),100000 + (100000*RAND()))

Whole SPROC used for custom grid (Username is ModUser):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create  Procedure [dbo].[LoadFunding]
(
        @WBS1						        varchar(30)
      , @Username					        varchar(6)
      , @custFundingRequestedAmount		decimal(19,5)
      , @custFundingNotes				        varchar(1000)
      , @custFund						varchar(255)
      , @custFundingReceivedAmount		decimal(19,5)

)
as
INSERT INTO [Projects_Funding]
           ([WBS1]
           ,[WBS2]
           ,[WBS3]
           ,[Seq]
           ,[custFundingRequestedAmount]
           ,[custFundingNotes]
           ,[custFund]
           ,[custFundingReceivedAmount]
		   )
     VALUES
			(
                          @WBS1
			,' '
			,' '
			,  @Username + '1'		-- Create SEQ# (20 chars i.e.  xxxxxx1262113234429)
					+ Convert(nvarchar(255),100000 + (100000*RAND()))
					+ Convert(nvarchar(255),100000 + (100000*RAND()))
			, @custFundingRequestedAmount
			, @custFundingNotes
			, @custFund
			, @custFundingReceivedAmount
			)