UDF Limit Reached

Owned by Julie Doyle-Whitelock (Deactivated)
Last updated: Mar 29, 2017
14 min read

Problem

A hard limit of 255 columns has been introduced to the job_udf1 table.  This is to prevent issues arising from surpassing that limit in certain MSSQL environments.

If that limit is reached, a message in Framework Administration 6.3 will advise and request contact with a system administrator to resolve.

Note : This will only apply in for FWADM 6.3.9052 & FWECM 6.3.9058 onwards.  For v7.0, this will be applied in the Framework ECM Upgrade (HS#119716).

Solution

A script must be run to create and activate job_udf2 and job_udf3 tables.  This will allow for further Job UDFs to be created and supported.

------------------------------------------------------------------------------------------------------------------
-- HS #119716
-- Title: FWUPGECM0700 - Change - UDF table limit extension (HS#117900)
--
-- Usage: eXecute on the destimation ECM database in SSMS
--
-- Author: Adam Golding
-- Date: 6/2/2017
------------------------------------------------------------------------------------------------------------------


-- structural

-- UDF job 2
CREATE TABLE [dbo].[udf_job2](
[l_udfjob2_id] [int] NOT NULL,
[l_job_id] [int] NOT NULL,
[l_user_created_id] [int] NOT NULL,
[l_user_modified_id] [int] NOT NULL,
[l_user_owner_id] [int] NOT NULL,
[d_created] [datetime] NULL,
[t_created] [datetime] NULL,
[d_modified] [datetime] NULL,
[t_modified] [datetime] NULL,
[f_concurrency] [smallint] NOT NULL,
[l_cont_id] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[l_udfjob2_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[udf_job2] ADD DEFAULT ((0)) FOR [l_udfjob2_id]
GO

ALTER TABLE [dbo].[udf_job2] ADD DEFAULT ((0)) FOR [l_job_id]
GO

ALTER TABLE [dbo].[udf_job2] ADD DEFAULT ((0)) FOR [l_user_created_id]
GO

ALTER TABLE [dbo].[udf_job2] ADD DEFAULT ((0)) FOR [l_user_modified_id]
GO

ALTER TABLE [dbo].[udf_job2] ADD DEFAULT ((0)) FOR [l_user_owner_id]
GO

ALTER TABLE [dbo].[udf_job2] ADD DEFAULT ((0)) FOR [f_concurrency]
GO

ALTER TABLE [dbo].[udf_job2] ADD DEFAULT ((0)) FOR [l_cont_id]
GO

-- insert udf_job2 zero record
INSERT INTO [dbo].[udf_job2]
([l_udfjob2_id]
,[l_job_id]
,[l_user_created_id]
,[l_user_modified_id]
,[l_user_owner_id]
,[d_created]
,[t_created]
,[d_modified]
,[t_modified]
,[f_concurrency]
,[l_cont_id])
VALUES
(0--<l_udfjob2_id, int,>
,0--<l_job_id, int,>
,0--<l_user_created_id, int,>
,0--<l_user_modified_id, int,>
,0--<l_user_owner_id, int,>
,convert(varchar(10), getdate(), 120) --<d_created, datetime,>
,convert(varchar(20), getdate(), 120) --<t_created, datetime,>
,convert(varchar(10), getdate(), 120) --<d_modified, datetime,>
,convert(varchar(20), getdate(), 120) --<t_modified, datetime,>
,0 --<f_concurrency, smallint,>
,0--<l_cont_id, int,>
)
GO


-- UDF job 3

CREATE TABLE [dbo].[udf_job3](
[l_udfjob3_id] [int] NOT NULL,
[l_job_id] [int] NOT NULL,
[l_user_created_id] [int] NOT NULL,
[l_user_modified_id] [int] NOT NULL,
[l_user_owner_id] [int] NOT NULL,
[d_created] [datetime] NULL,
[t_created] [datetime] NULL,
[d_modified] [datetime] NULL,
[t_modified] [datetime] NULL,
[f_concurrency] [smallint] NOT NULL,
[l_cont_id] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[l_udfjob3_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[udf_job3] ADD DEFAULT ((0)) FOR [l_udfjob3_id]
GO

ALTER TABLE [dbo].[udf_job3] ADD DEFAULT ((0)) FOR [l_job_id]
GO

ALTER TABLE [dbo].[udf_job3] ADD DEFAULT ((0)) FOR [l_user_created_id]
GO

ALTER TABLE [dbo].[udf_job3] ADD DEFAULT ((0)) FOR [l_user_modified_id]
GO

ALTER TABLE [dbo].[udf_job3] ADD DEFAULT ((0)) FOR [l_user_owner_id]
GO

ALTER TABLE [dbo].[udf_job3] ADD DEFAULT ((0)) FOR [f_concurrency]
GO

ALTER TABLE [dbo].[udf_job3] ADD DEFAULT ((0)) FOR [l_cont_id]
GO

-- insert udf_job3 zero record
INSERT INTO [dbo].[udf_job3]
([l_udfjob3_id]
,[l_job_id]
,[l_user_created_id]
,[l_user_modified_id]
,[l_user_owner_id]
,[d_created]
,[t_created]
,[d_modified]
,[t_modified]
,[f_concurrency]
,[l_cont_id])
VALUES
(0--<l_udfjob3_id, int,>
,0--<l_job_id, int,>
,0--<l_user_created_id, int,>
,0--<l_user_modified_id, int,>
,0--<l_user_owner_id, int,>
,convert(varchar(10), getdate(), 120) --<d_created, datetime,>
,convert(varchar(20), getdate(), 120) --<t_created, datetime,>
,convert(varchar(10), getdate(), 120) --<d_modified, datetime,>
,convert(varchar(20), getdate(), 120) --<t_modified, datetime,>
,0 --<f_concurrency, smallint,>
,0--<l_cont_id, int,>
)
GO


-- rms and sys_key data

INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9776, -400, -9, 4, 0, 0, -1, N'l_udfJob3_id', N'l_udfJob3_id', N'', N'', 0, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9775, -400, -4, 8, 0, 0, 0, N't_modified', N't_modified', N'', N'', 8, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9774, -400, -3, 8, 0, 0, 0, N'd_modified', N'd_modified', N'', N'', 7, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9773, -400, -4, 8, 0, 0, 0, N't_created', N't_created', N'', N'', 6, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9772, -400, -3, 8, 0, 0, 0, N'd_created', N'd_created', N'', N'', 5, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9771, -400, -9, 4, 0, 0, 0, N'l_user_owner_id', N'l_user_owner_id', N'', N'', 4, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9770, -400, -9, 4, 0, 0, 0, N'l_user_modified_id', N'l_user_modified_id', N'', N'', 3, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9769, -400, -9, 4, 0, 0, 0, N'l_user_created_id', N'l_user_created_id', N'', N'', 2, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9768, -400, -9, 4, 0, 0, 0, N'l_job_id', N'l_job_id', N'', N'', 1, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9767, -400, -8, 2, 0, 0, 0, N'f_concurrency', N'f_concurrency', N'', N'', 9, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9766, -399, -9, 4, 0, 0, -1, N'l_udfJob2_id', N'l_udfJob2_id', N'', N'', 0, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9765, -399, -4, 8, 0, 0, 0, N't_modified', N't_modified', N'', N'', 8, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9764, -399, -3, 8, 0, 0, 0, N'd_modified', N'd_modified', N'', N'', 7, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9763, -399, -4, 8, 0, 0, 0, N't_created', N't_created', N'', N'', 6, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9762, -399, -3, 8, 0, 0, 0, N'd_created', N'd_created', N'', N'', 5, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9761, -399, -9, 4, 0, 0, 0, N'l_user_owner_id', N'l_user_owner_id', N'', N'', 4, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9760, -399, -9, 4, 0, 0, 0, N'l_user_modified_id', N'l_user_modified_id', N'', N'', 3, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9759, -399, -9, 4, 0, 0, 0, N'l_user_created_id', N'l_user_created_id', N'', N'', 2, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9758, -399, -9, 4, 0, 0, 0, N'l_job_id', N'l_job_id', N'', N'', 1, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_field] ([l_rms_field_id], [l_rms_table_id], [l_rms_dataType_id], [l_size], [f_hasPermission], [f_inReporting], [f_primary_key], [s_name_phy], [s_name_logical], [s_name_const], [s_default_value], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-9757, -399, -8, 2, 0, 0, 0, N'f_concurrency', N'f_concurrency', N'', N'', 9, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42636, -1080, -9766, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'l_udfJob2_id', N'l_udfJob2_id', N'', N'', N'', 0, 0, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42637, -1080, -9758, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'l_job_id', N'l_job_id', N'', N'', N'', 1, 1, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42638, -1080, -9759, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'l_user_created_id', N'l_user_created_id', N'', N'', N'', 2, 2, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42639, -1080, -9760, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'l_user_modified_id', N'l_user_modified_id', N'', N'', N'', 3, 3, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42640, -1080, -9761, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'l_user_owner_id', N'l_user_owner_id', N'', N'', N'', 4, 4, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42641, -1080, -9762, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'd_created', N'd_created', N'', N'', N'', 5, 5, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42642, -1080, -9763, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N't_created', N't_created', N'', N'', N'', 6, 6, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42643, -1080, -9764, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'd_modified', N'd_modified', N'', N'', N'', 7, 7, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42644, -1080, -9765, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N't_modified', N't_modified', N'', N'', N'', 8, 8, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42645, -1080, -9757, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'f_concurrency', N'f_concurrency', N'', N'', N'', 9, 9, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42646, -1081, -9767, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'f_concurrency', N'f_concurrency', N'', N'', N'', 9, 9, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42647, -1081, -9768, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'l_job_id', N'l_job_id', N'', N'', N'', 1, 1, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42648, -1081, -9769, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'l_user_created_id', N'l_user_created_id', N'', N'', N'', 2, 2, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42649, -1081, -9770, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'l_user_modified_id', N'l_user_modified_id', N'', N'', N'', 3, 3, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42650, -1081, -9771, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'l_user_owner_id', N'l_user_owner_id', N'', N'', N'', 4, 4, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42651, -1081, -9772, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'd_created', N'd_created', N'', N'', N'', 5, 5, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42652, -1081, -9773, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N't_created', N't_created', N'', N'', N'', 6, 6, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42653, -1081, -9774, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'd_modified', N'd_modified', N'', N'', N'', 7, 7, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42654, -1081, -9775, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N't_modified', N't_modified', N'', N'', N'', 8, 8, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_fieldCtx] ([l_rms_fieldCtx_id], [l_rms_tableCtx_id], [l_rms_field_id], [l_rms_fieldGrp_id], [l_mme_view_id], [f_hasCustProps], [f_hasPermission], [f_inReporting], [f_rptFieldCrit], [f_rptGroup], [f_rptFormula], [f_rptSort], [f_rptMultiPrint], [f_mme], [f_mmeFieldCrit], [l_list_popupType], [l_list_popupCtx], [s_name], [s_name_ref], [s_name_mme], [s_name_mmeCriteria], [s_default_value], [l_order], [l_order_fldGrp], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [f_regAllowEdit], [f_inRegister], [f_inRegisterCrit]) VALUES (42655, -1081, -9776, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'l_udfJob3_id', N'l_udfJob3_id', N'', N'', N'', 0, 0, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0)
GO
INSERT [dbo].[rms_table] ([l_rms_table_id], [l_rms_field_prim_id], [s_name_phy], [s_name_logical], [s_name_const], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-400, -9776, N'udf_job3', N'udf_job3', N'udf_job3', 0, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_table] ([l_rms_table_id], [l_rms_field_prim_id], [s_name_phy], [s_name_logical], [s_name_const], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes]) VALUES (-399, -9766, N'udf_job2', N'udf_job2', N'udf_job2', 0, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL)
GO
INSERT [dbo].[rms_tableCtx] ([l_rms_tableCtx_id], [l_rms_table_id], [s_name], [s_name_ref], [s_name_rpt], [s_name_dsv], [f_hasFields], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [l_parent_tableCtx_id], [l_parent_fieldCtx_id], [l_rms_fieldCtx_id], [l_rms_dsvGrp_id]) VALUES (-1081, -400, N'User Defined Fields (job3)', N'User Defined Fields (job3)', N'udf_job3', N'job:udf_job3', 0, 170, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, -3, 10328, 42647, 1)
GO
INSERT [dbo].[rms_tableCtx] ([l_rms_tableCtx_id], [l_rms_table_id], [s_name], [s_name_ref], [s_name_rpt], [s_name_dsv], [f_hasFields], [l_order], [s_notes_derived], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency], [m_notes], [l_parent_tableCtx_id], [l_parent_fieldCtx_id], [l_rms_fieldCtx_id], [l_rms_dsvGrp_id]) VALUES (-1080, -399, N'User Defined Fields (job2)', N'User Defined Fields (job2)', N'udf_job2', N'job:udf_job2', 0, 169, N'', 0, 0, 0, NULL, NULL, NULL, NULL, 0, NULL, -3, 10328, 42637, 1)
GO
INSERT [dbo].[sys_key] ([l_sys_key_id], [l_sys_keyType_id], [l_sys_rmsTable_id], [l_sys_rmsField_id], [s_name], [s_name_table], [s_name_pkidFld], [f_negative_key], [f_high_volume], [l_bump], [l_lastValue], [s_lastValue_string], [f_site_specific], [l_reorder_level], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency]) VALUES (630, 0, 0, 0, N'udf_job2', N'udf_job2', N'l_udfjob2_id', 0, 0, 1, 0, N'', 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, 0)
GO
INSERT [dbo].[sys_key] ([l_sys_key_id], [l_sys_keyType_id], [l_sys_rmsTable_id], [l_sys_rmsField_id], [s_name], [s_name_table], [s_name_pkidFld], [f_negative_key], [f_high_volume], [l_bump], [l_lastValue], [s_lastValue_string], [f_site_specific], [l_reorder_level], [l_user_created_id], [l_user_modified_id], [l_user_owner_id], [d_created], [t_created], [d_modified], [t_modified], [f_concurrency]) VALUES (631, 0, 0, 0, N'udf_job3', N'udf_job3', N'l_udfjob3_id', 0, 0, 1, 0, N'', 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, 0)
GO

DECLARE @MAXJOB2PKID INT
SELECT @MAXJOB2PKID = MAX(ISNULL(l_udfJob2_id, 0)) FROM udf_job2;

-- job2 from jobs
WITH CTE_JOB AS
(SELECT ROW_NUMBER() OVER(ORDER BY l_job_id) as RowNum, l_job_id FROM job)

-- insert records for existing jobs
INSERT INTO [dbo].[udf_job2]
([l_udfJob2_id]
,[l_job_id]
,[l_user_created_id]
,[l_user_modified_id]
,[l_user_owner_id]
,[d_created]
,[t_created]
,[d_modified]
,[t_modified]
,[f_concurrency])
--VALUES
SELECT
ISNULL(@MAXJOB2PKID, 0) + RowNum --<l_udfJob2_id, int,>
,l_job_id --<l_job_id, int,>
,-3 --<l_user_created_id, int,>
,-3 --<l_user_modified_id, int,>
,0 --<l_user_owner_id, int,>
,convert(varchar(10), getdate(), 120) --<d_created, datetime,>
,convert(varchar(20), getdate(), 120) --<t_created, datetime,>
,convert(varchar(10), getdate(), 120) --<d_modified, datetime,>
,convert(varchar(20), getdate(), 120) --<t_modified, datetime,>
,0 --<f_concurrency, smallint,>
FROM CTE_JOB
WHERE CTE_JOB.l_job_id NOT IN
(SELECT l_job_id FROM udf_job2)

GO

DECLARE @MAXJOB3PKID INT
SELECT @MAXJOB3PKID = MAX(ISNULL(l_udfJob3_id, 0)) FROM udf_job3;

-- job3 from jobs
WITH CTE_JOB AS
(SELECT ROW_NUMBER() OVER(ORDER BY l_job_id) as RowNum, l_job_id FROM job)

-- insert records for existing jobs
INSERT INTO [dbo].[udf_job3]
([l_udfJob3_id]
,[l_job_id]
,[l_user_created_id]
,[l_user_modified_id]
,[l_user_owner_id]
,[d_created]
,[t_created]
,[d_modified]
,[t_modified]
,[f_concurrency])
--VALUES
SELECT
ISNULL(@MAXJOB3PKID, 0) + RowNum --<l_udfJob1_id, int,>
,l_job_id --<l_job_id, int,>
,-3 --<l_user_created_id, int,>
,-3 --<l_user_modified_id, int,>
,0 --<l_user_owner_id, int,>
,convert(varchar(10), getdate(), 120) --<d_created, datetime,>
,convert(varchar(20), getdate(), 120) --<t_created, datetime,>
,convert(varchar(10), getdate(), 120) --<d_modified, datetime,>
,convert(varchar(20), getdate(), 120) --<t_modified, datetime,>
,0 --<f_concurrency, smallint,>
FROM CTE_JOB
WHERE CTE_JOB.l_job_id NOT IN
(SELECT l_job_id FROM udf_job3)


GO

DECLARE @MAXJOB2PKID INT
SELECT @MAXJOB2PKID = MAX(ISNULL(l_udfJob2_id, 0)) FROM udf_job2;


-- job2 from conts
WITH CTE_JOBCONT2 AS
(SELECT ROW_NUMBER() OVER(ORDER BY l_job_id) as RowNum, l_cont_id FROM udf_job1 WHERE l_cont_id <> 0)

-- insert records for existing jobs
INSERT INTO [dbo].[udf_job2]
([l_udfJob2_id]
,[l_cont_id]
,[l_user_created_id]
,[l_user_modified_id]
,[l_user_owner_id]
,[d_created]
,[t_created]
,[d_modified]
,[t_modified]
,[f_concurrency])
--VALUES
SELECT
ISNULL(@MAXJOB2PKID, 0) + RowNum --<l_udfJob2_id, int,>
,l_cont_id --<l_cont_id, int,>
,-3 --<l_user_created_id, int,>
,-3 --<l_user_modified_id, int,>
,0 --<l_user_owner_id, int,>
,convert(varchar(10), getdate(), 120) --<d_created, datetime,>
,convert(varchar(20), getdate(), 120) --<t_created, datetime,>
,convert(varchar(10), getdate(), 120) --<d_modified, datetime,>
,convert(varchar(20), getdate(), 120) --<t_modified, datetime,>
,0 --<f_concurrency, smallint,>
FROM CTE_JOBCONT2
WHERE CTE_JOBCONT2.l_cont_id NOT IN
(SELECT l_cont_id FROM udf_job2)

GO

DECLARE @MAXJOB3PKID INT
SELECT @MAXJOB3PKID = MAX(ISNULL(l_udfJob3_id, 0)) FROM udf_job3;

-- job3 from jobs
WITH CTE_JOBCONT3 AS
(SELECT ROW_NUMBER() OVER(ORDER BY l_job_id) as RowNum, l_cont_id FROM udf_job1 WHERE l_cont_id <> 0)

-- insert records for existing jobs
INSERT INTO [dbo].[udf_job3]
([l_udfJob3_id]
,[l_cont_id]
,[l_user_created_id]
,[l_user_modified_id]
,[l_user_owner_id]
,[d_created]
,[t_created]
,[d_modified]
,[t_modified]
,[f_concurrency])
--VALUES
SELECT
ISNULL(@MAXJOB3PKID, 0) + RowNum --<l_udfJob1_id, int,>
,l_cont_id --<l_job_id, int,>
,-3 --<l_user_created_id, int,>
,-3 --<l_user_modified_id, int,>
,0 --<l_user_owner_id, int,>
,convert(varchar(10), getdate(), 120) --<d_created, datetime,>
,convert(varchar(20), getdate(), 120) --<t_created, datetime,>
,convert(varchar(10), getdate(), 120) --<d_modified, datetime,>
,convert(varchar(20), getdate(), 120) --<t_modified, datetime,>
,0 --<f_concurrency, smallint,>
FROM CTE_JOBCONT3
WHERE CTE_JOBCONT3.l_cont_id NOT IN
(SELECT l_cont_id FROM udf_job3)


GO

-- set udf_job2 sys_key
DECLARE @UDFJOB2SYSKEY INT
SELECT @UDFJOB2SYSKEY = MAX(ISNULL(l_udfJob2_id, 0)) FROM udf_job2;

UPDATE sys_key SET l_lastValue = ISNULL(@UDFJOB2SYSKEY, 0) WHERE l_sys_key_id = 630

-- set udf_job3 sys_key
DECLARE @UDFJOB3SYSKEY INT
SELECT @UDFJOB3SYSKEY = MAX(ISNULL(l_udfJob3_id, 0)) FROM udf_job3;

UPDATE sys_key SET l_lastValue = ISNULL(@UDFJOB3SYSKEY, 0) WHERE l_sys_key_id = 631