KeHYpPXH

1--The following have already been updated into R1 templates, though not in TR templates
2--Procedure List:
3/****** Object:  StoredProcedure [dbo].[proc_FixCTE]    Script Date: 12/03/2013 08:08:16 ******/
4/****** Object:  StoredProcedure [dbo].[proc_Setup_Queues]    Script Date: 12/03/2013 08:08:16 ******/
5/****** Object:  StoredProcedure [dbo].[proc_restore_baselineSLC]    Script Date: 04/30/2013 17:59:09 ******/
6/****** Object:  StoredProcedure [dbo].[proc_sync_Extsite]    Script Date: 04/30/2013 17:59:09 ******/
7/****** Object:  StoredProcedure [dbo].[proc_Setup_EXTCustomerdbs]    Script Date: 04/30/2013 17:59:09 ******/
8/****** Object:  StoredProcedure [dbo].[proc_sync_ExtRBSusers]    Script Date: 04/30/2013 17:59:09 ******/
9/****** Object:  StoredProcedure [dbo].[proc_update_JMS_UES]    Script Date: 04/30/2013 17:59:09 ******/
10/****** Object:  StoredProcedure [dbo].[proc_update_int3rdparty]    Script Date: 04/30/2013 17:59:09 ******/
11/****** Object:  StoredProcedure [dbo].[proc_update_RBSTables]    Script Date: 04/30/2013 17:59:09 ******/
12/****** Object:  StoredProcedure [dbo].[proc_update_wkcTables]    Script Date: 04/30/2013 17:59:09 ******/
13/****** Object:  StoredProcedure [dbo].[proc_Sync_ExtWEBUsers]    Script Date: 05/01/2013 17:28:07 ******/
14/****** Object:  StoredProcedure [dbo].[proc_update_wkcTables]    Script Date: 04/30/2013 17:59:09 ******/
15/****** Object:  StoredProcedure [dbo].[proc_Fixuserobjects]    Script Date: 05/07/2013 14:46:43 ******/
16/****** Object:  StoredProcedure [dbo].[proc_sync_RBScontexts]    Script Date: 08/05/2013 10:44:36 ******/
17/****** Object:  StoredProcedure [dbo].[proc_Manage_RBStriggers]    Script Date: 06/07/2013 10:33:30 ******/
18/****** Object:  StoredProcedure [dbo].[proc_setup_devuser]    Script Date: 05/28/2013 14:20:07 ******/
19/****** Object:  StoredProcedure [dbo].[proc_kill_users]    Script Date: 03/31/2014 11:09:51 ******/
20/****** Object:  StoredProcedure [dbo].[proc_register_SQL_CLR_Objects] ******/
21/****** Object:  StoredProcedure [dbo].[proc_register_SQL_CLR_Objects_R2_2018] ******/
22/****** Object:  StoredProcedure [dbo].[proc_cleanup_RBSSite] ******/
23
24
25
26/*********************************************************************************************************************************************************************************************/
27USE [UTILITY]
28GO
29
30/****** Object:  Table [dbo].[DBFileExtGroups]    Script Date: 08/20/2014 14:57:51 ******/
31IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBFileExtGroups]') AND type in (N'U'))
32DROP TABLE [dbo].[DBFileExtGroups]
33GO
34
35/****** Object:  Table [dbo].[DBFileExtGroups]    Script Date: 08/20/2014 14:57:51 ******/
36SET ANSI_NULLS ON
37GO
38
39SET QUOTED_IDENTIFIER ON
40GO
41
42SET ANSI_PADDING ON
43GO
44
45CREATE TABLE [dbo].[DBFileExtGroups](
46	[dbname] [varchar](50) NOT NULL,
47	[LogicalFileName] [varchar](100) NOT NULL,
48	[PhysicalFileName] [varchar](100) NOT NULL,
49	[GroupNo] [varchar](10) NOT NULL
50) ON [PRIMARY]
51
52GO
53
54SET ANSI_PADDING OFF
55GO
56
57/*********************************************************************************************************************************************************************************************/
58
59/****** Object:  StoredProcedure [dbo].[proc_FixCTE]    Script Date: 12/03/2013 08:08:16 ******/
60
61USE [UTILITY]
62GO
63
64IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_FixCTE]') AND type in (N'P', N'PC'))
65DROP PROCEDURE [dbo].[proc_FixCTE]
66GO
67
68SET ANSI_NULLS ON
69GO
70SET QUOTED_IDENTIFIER ON
71GO
72CREATE PROCEDURE [dbo].[proc_FixCTE]
73 AS
74
75EXEC('ALTER AUTHORIZATION ON DATABASE::METADATA TO sa;
76      ALTER DATABASE [METADATA] SET TRUSTWORTHY ON')
77
78
79IF EXISTS (SELECT 1 FROM HRMS_SITE..SecLoginToType WHERE sntNameType='gateway')
80BEGIN
81	Update HRMS_SITE..SecLoginToType SET sntLoginName='context', sntSecDatabasePwd='4OsPxe==' where sntNameType='gateway'
82END
83ELSE
84BEGIN
85	Insert into HRMS_SITE..SecLoginToType(sntGroupHostId,sntIsNTLoginInd,sntIsRoleInd,sntLoginName,sntNameType,sntSecDatabasePwd) Values
86('all','N','N','context','gateway','4OsPxe==')
87END
88
89IF EXISTS (SELECT 1 FROM HRMS_SITE..SecLoginToType WHERE sntNameType='dpm')
90BEGIN
91	Update HRMS_SITE..SecLoginToType SET sntLoginName='dpmuser', sntSecDatabasePwd='8rDYLvs7+tc=' where sntNameType='dpm'
92END
93ELSE 
94BEGIN
95	Insert into HRMS_SITE..SecLoginToType(sntGroupHostId,sntIsNTLoginInd,sntIsRoleInd,sntLoginName,sntNameType,sntSecDatabasePwd) Values
96('all','N','N','dpmuser','dpm','8rDYLvs7+tc=')
97END
98
99IF EXISTS (SELECT 1 FROM HRMS_SITE..SecLoginToType WHERE sntNameType='SVR_PAYCALC')
100BEGIN
101	Update HRMS_SITE..SecLoginToType SET sntLoginName='XPT', sntSecDatabasePwd='4SvV5i==' where sntNameType='SVR_PAYCALC'
102END
103ELSE
104BEGIN
105	Insert into HRMS_SITE..SecLoginToType(sntGroupHostId,sntIsNTLoginInd,sntIsRoleInd,sntLoginName,sntNameType,sntSecDatabasePwd) Values
106('all','N','N','XPT','SVR_PAYCALC','4SvV5i==')
107END
108
109IF EXISTS (SELECT 1 FROM HRMS_SITE..SecLoginToType WHERE sntNameType='siteadmin')
110BEGIN
111	Update HRMS_SITE..SecLoginToType SET sntLoginName='SiteUser', sntSecDatabasePwd='8rDYLvs7+tc=' where sntNameType='siteadmin'
112END
113ELSE
114BEGIN
115	Insert into HRMS_SITE..SecLoginToType(sntGroupHostId,sntIsNTLoginInd,sntIsRoleInd,sntLoginName,sntNameType,sntSecDatabasePwd) Values
116('all','N','N','SiteUser','siteadmin','8rDYLvs7+tc=')
117END
118
119--XPTSetup Fix
120--EXEC HRMS_SITE.dbo.usg_sec_add_SQLUser @grouphostid = 'ALL', @nametype = 'SVR_PAYCALC', @username = 'XPT', @password = '4SvV5i=='
121--EXEC HRMS_GLOBALDATA.dbo.HRMS_DB_Setup_Permissions @p_NameType = 'SVR_PAYCALC', @p_CreateLogins = 1, @p_Password = 'calc'
122--FMS Fix
123UPDATE HRMS_SITE.dbo.RBSSiteValues SET stlValue='upapp' WHERE stlCode='HostServerName' and stlType like 'FileService.%'
124
125UPDATE HRMS_SITE.dbo.RBSSiteValues SET stlValue='' WHERE stlCode='SECFILEURI' and stlType='SITE'
126
127UPDATE HRMS_SITE.dbo.RBSSiteValues SET stlValue='\\upweb\reports\reports' WHERE stlCode='FILEURI' and stlType='SITE'
128
129--Payroll Fix
130UPDATE HRMS_SITE.dbo.RBSSiteValues SET stlValue='\\upweb\reports\FILEURIDDCOMBINED' WHERE stlCode='FILEURIDDCOMBINED' and stlType='PAYROLL'
131
132UPDATE HRMS_SITE.dbo.RBSSiteValues SET stlValue='\\upweb\reports\FILEURIDIRECTDEPOSIT' WHERE stlCode='FILEURIDIRECTDEPOSIT' and stlType='PAYROLL'
133
134--Disable UseContentDomain
135UPDATE HRMS_SITE.dbo.RBSSiteValues SET stlValue='N' WHERE stlCode='USECONTENTDOMAIN' and stlType='SITE'
136
137--Disable EmpCount
138UPDATE HRMS_SITE.dbo.RbsSiteValues SET stlValue='' WHERE stlCode='FTP\Address' and stlType='EMPCOUNT'
139
140--UES Fix
141UPDATE HRMS_SITE.dbo.RBSSiteValues SET stlValue='upapp' WHERE stlCode='ServiceHostInternal' and stlType='UES'
142UPDATE HRMS_SITE.dbo.RBSSiteValues SET stlValue='upapp' WHERE stlCode='ServiceHostHA' and stlType='UES'
143
144--Enable Direct Login
145UPDATE [HRMS_SITE].[dbo].[SSODirectLoginConfig] SET isDirectLoginEnabled = 1, CanAllUsersDirectLogin = 1
146
147--Eliminate any attempts to contact a supersite
148UPDATE HRMS_SITE.dbo.RBSSiteValues SET stlValue = '0' WHERE stlCode = 'SiteType'
149
150UPDATE HRMS_SITE.dbo.RbsSiteValues SET stlValue = '0' WHERE stlCode = 'OIDCEnabled'
151
152IF NOT EXISTS(SELECT 1 FROM HRMS_SITE.dbo.RbsSiteValues WHERE stlCode='EnableCertificateSec' and stlType='UES')
153BEGIN
154	INSERT INTO HRMS_SITE.dbo.RbsSiteValues (stlCode, stlType, stlValue) VALUES ('EnableCertificateSec','UES','1')
155END
156
157delete from HRMS_SITE.dbo.RBSSiteValues where stlType = 'PAYROLL.EVENTING' and stlCode like '%Rabbit%'
158
159INSERT INTO HRMS_SITE.dbo.RbsSiteValues (stlCode, stlType, stlValue)
160   SELECT 'RabbitMQUserName', 'PAYROLL.EVENTING', 'a39c5837-43f5-4882-952e-24a18ea2238c'
161   UNION
162   SELECT 'RabbitMQPassword', 'PAYROLL.EVENTING', 'n2cdm6oa9g17p3tqfp0ec9i1bn'
163   UNION
164   SELECT 'RabbitMQPort', 'PAYROLL.EVENTING', '5671'
165   UNION
166   SELECT 'RabbitMQHostName', 'PAYROLL.EVENTING', 'lb.paas.atl.ulti.io'
167   UNION
168   SELECT 'RabbitMQVirtualHost', 'PAYROLL.EVENTING', 'd9adaff3-4b66-462d-9a69-106ef6635bc6'
169
170
171DECLARE @envID VARCHAR(50)
172SELECT @envID = newID()
173
174UPDATE HRMS_SITE.dbo.RbsSiteValues
175  SET stlValue = @envID
176  WHERE stlcode = 'SITEID'
177
178IF EXISTS(SELECT * FROM MASTER.dbo.SYSDATABASES WHERE NAME = 'SUPERSITE')
179  BEGIN
180    IF EXISTS( SELECT * FROM SUPERSITE.dbo.SITES)
181         UPDATE SUPERSITE.dbo.SITES
182         SET SiteKey = @envID
183    ELSE
184         INSERT SUPERSITE.dbo.SITES(Description, ServiceHostInternal,SiteKey, SiteLocation, ThumbPrint, SiteType)
185         SELECT @@SERVERNAME,NULL,@envID,1,null,'p'
186  END    
187
188IF OBJECT_ID('MetaData.dbo.DiscoveredDictionaries') IS NULL
189  EXEC( 'CREATE TABLE MetaData.dbo.DiscoveredDictionaries(
190           ID INT NOT NULL DEFAULT(-1), 
191           DictionaryName VARCHAR(30) NULL, 
192           DatabaseName sysname, 
193           AdhocDatabaseTag CHAR(1) NOT NULL DEFAULT(''Z'')
194         )' )
195
196  DECLARE @id INT, @dictionary_name VARCHAR(30), @database_name sysname, @adhoc_database_tag CHAR(1), @sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX)
197
198  IF OBJECT_ID('tempdb..#ExpandedSQL') IS NOT NULL DROP TABLE #ExpandedSQL
199  CREATE TABLE #ExpandedSql(sql varchar(8000))
200
201  DELETE MetaData.dbo.DiscoveredDictionaries
202  
203  SELECT @id = MIN(ID) FROM MetaData.dbo.Dictionary WHERE ISNULL(DatabaseListSQL, '') <> ''
204  
205  WHILE @id IS NOT NULL
206  BEGIN
207    SELECT @dictionary_name = Name, @adhoc_database_tag = AdhocDatabaseTag, @sql = DatabaseListSQL FROM MetaData.dbo.Dictionary WHERE ID = @id 
208    
209    IF LEFT(@sql, 3) = '***'
210    BEGIN
211      TRUNCATE TABLE #ExpandedSql
212      
213      SET @sql = SUBSTRING(@sql, 4, 8000)
214      INSERT #ExpandedSql([sql]) EXEC sp_executesql @sql
215        
216      SET @sql2 = ''
217
218      SELECT @sql2 = @sql2 + 'INSERT INTO MetaData.dbo.DiscoveredDictionaries(DatabaseName) ' + [sql] + CHAR(13) + CHAR(10) 
219      FROM #ExpandedSQL
220
221      EXEC(@Sql2)
222    END
223    ELSE
224    BEGIN    
225      INSERT MetaData.dbo.DiscoveredDictionaries(DatabaseName) EXEC sp_executesql @sql
226    END
227    
228    UPDATE MetaData.dbo.DiscoveredDictionaries SET
229      ID = @id,
230      DictionaryName = @dictionary_name,
231      AdhocDatabaseTag = @adhoc_database_tag
232    WHERE DictionaryName IS NULL
233
234    SELECT @id = MIN(ID) FROM MetaData.dbo.Dictionary WHERE ISNULL(DatabaseListSQL, '') <> '' AND id > @id
235  END
236
237
238
239GO
240
241
242
243
244
245/*********************************************************************************************************************************************************************************************/
246
247/****** Object:  StoredProcedure [dbo].[proc_SetCompatibility]    Script Date: 12/06/2017 ******/
248
249USE [UTILITY]
250GO
251
252IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_SetCompatibility]') AND type in (N'P', N'PC'))
253DROP PROCEDURE [dbo].[proc_SetCompatibility]
254GO
255
256SET ANSI_NULLS ON
257GO
258SET QUOTED_IDENTIFIER ON
259GO
260CREATE PROCEDURE [dbo].[proc_SetCompatibility]
261 AS
262
263exec [master].[dbo].sp_MSForEachDB 'if ''?'' not in (''master'', ''tempdb'', ''model'', ''msdb'', ''ReportServer'',''ReportServerTempDB'',''UTILITY'')
264BEGIN
265	ALTER DATABASE ?
266	SET COMPATIBILITY_LEVEL = 120;
267
268	SELECT ''?'', compatibility_level  
269	FROM sys.databases WHERE name = ''?'';
270END
271'
272
273GO
274
275
276
277
278 
279/*********************************************************************************************************************************************************************************************/
280
281USE [UTILITY]
282GO
283/****** Object:  StoredProcedure [dbo].[proc_Setup_Queues]    Script Date: 12/03/2013 08:08:16 ******/
284SET ANSI_NULLS ON
285GO
286SET QUOTED_IDENTIFIER ON
287GO
288ALTER PROCEDURE [dbo].[proc_Setup_Queues]
289 AS
290DECLARE 
291	@PATHNAME VARCHAR(200), 
292	@FileURI varchar(100), 
293	@SQL nvarchar(200)
294
295IF EXISTS(Select * from master..sysDatabases where name = 'HRMS_DISPATCH')
296BEGIN
297	IF EXISTS(select * from HRMS_DISPATCH.dbo.sysobjects where name='Queues')
298	BEGIN 
299		IF EXISTS(SELECT * FROM HRMS_DISPATCH..QUEUES WHERE QUEUETYPE = 'Q')
300			UPDATE HRMS_DISPATCH.dbo.Queues
301			SET PathName = 'upapp\request'
302			WHERE QueueType IN ('Q')
303		ELSE
304			BEGIN
305			  INSERT HRMS_DISPATCH..QUEUES
306					(DESCRIPTION,LABEL,PATHNAME,QUEUETYPE)
307					 VALUES ('Request Queue','Primary','upapp\request','Q')
308			END
309
310		IF EXISTS(SELECT * FROM HRMS_DISPATCH..QUEUES WHERE QUEUETYPE = 'R')
311			UPDATE HRMS_DISPATCH.dbo.Queues
312			SET PathName = 'upapp\response'
313			WHERE QueueType = 'R'
314		ELSE
315			BEGIN
316			  INSERT HRMS_DISPATCH..QUEUES
317					(DESCRIPTION,LABEL,PATHNAME,QUEUETYPE)
318					 VALUES ('Response Queue','Secondary','upapp\response','R')
319			END
320	END
321	
322	SET @SQL = 'IF EXISTS(SELECT * FROM HRMS_SITE.dbo.SYSOBJECTS WHERE NAME = ''RBSSITEVALUES'' and TYPE = ''u'')
323	UPDATE HRMS_SITE..RbsSiteValues
324	SET stlValue = ''\\upweb\reports''
325	WHERE stlCode = ''FILEURI'''
326	
327	EXEC sp_executesql @SQL
328END
329GO
330
331/*********************************************************************************************************************************************************************************************/
332
333
334/****** Object:  StoredProcedure [dbo].[proc_update_RBSTables]    Script Date: 04/30/2013 17:59:09 ******/
335SET ANSI_NULLS ON
336GO
337SET QUOTED_IDENTIFIER ON
338GO
339ALTER PROCEDURE [dbo].[proc_update_RBSTables]
340@database varchar(50)
341as
342
343declare @statement varchar(800)
344declare @debug char(1)
345
346set @debug = 'Y'
347
348--Remove temp tables if any
349IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserRoles') > 0
350      DROP TABLE ##tempRBSUserRoles
351IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSQualList') > 0
352      DROP TABLE ##tempRBSQualList
353IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSQualifiers') > 0
354      DROP TABLE ##tempRBSQualifiers
355IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSExceptions') > 0
356      DROP TABLE ##tempRBSExceptions
357
358--Retrieve RBS tables into temp tables	  
359IF @debug = 'Y' PRINT 'Retrieving remote RBSUserRoles data...'
360SET @statement = 'SELECT * INTO ##tempRBSUserRoles FROM '
361       + @database + '.dbo.RBSUserRoles'
362EXEC(@statement)
363SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were retrieved for RBSUserRoles.'
364RAISERROR( @statement,0,1) WITH NOWAIT
365
366--create index
367CREATE NONCLUSTERED INDEX [IDX_TempRBSUserRoles] ON [dbo].[##tempRBSUserRoles] 
368(
369      [rurUserID] ASC,
370      [rureeid] ASC
371)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
372
373IF @debug = 'Y' PRINT 'Retrieving remote RBSQualList data...'
374SET @statement = 'SELECT * INTO ##tempRBSQualList FROM '
375      + @database + '.dbo.RBSQualList'
376EXEC(@statement)
377SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were retrieved for RBSQualList.'
378RAISERROR( @statement,0,1) WITH NOWAIT
379
380IF @debug = 'Y' PRINT 'Retrieving remote RBSQualifiers data...'
381SET @statement = 'SELECT qalFIELD,qalRoleID,qalUserID,qalValue INTO ##tempRBSQualifiers FROM '
382       + @database + '.dbo.RBSQualifiers'
383EXEC(@statement)
384SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were retrieved for RBSQualifiers.'
385RAISERROR( @statement,0,1) WITH NOWAIT
386
387IF @debug = 'Y' PRINT 'Retrieving remote RBSExceptions data...'
388SET @statement = 'SELECT rexCOID,RexEEID,rexExceptionType,rexRoleID,RexUserID INTO ##tempRBSExceptions FROM '
389       + @database + '.dbo.RBSExceptions'
390EXEC(@statement)
391SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were retrieved for RBSExceptions.'
392RAISERROR( @statement,0,1) WITH NOWAIT
393
394--Update temp tables
395SET @statement = 'Updating ##TempRBSUserRoles in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
396IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
397SET @statement = 
398'UPDATE dbo.##TempRBSUserRoles SET ruruserID = NewUserID '
399+ ' FROM UTILITY.dbo.UserIDMap '
400+ ' WHERE ruruserID = OldUserID'
401+ ' AND [Database] = ''' + @database + ''''
402EXEC(@statement)
403SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were updated in ##TempRBSUserRoles.'
404RAISERROR( @statement,0,1) WITH NOWAIT
405
406
407PRINT ''
408SET @statement = 'Updating ##TempRBSUserRoles in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
409IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
410SET @statement = 
411'UPDATE dbo.##TempRBSUserRoles SET rurUserCompanyID = NewUserCompanyID '
412+ ' FROM UTILITY.dbo.UserCompanyIDMap '
413+ ' WHERE rurUserCompanyID = OldUserCompanyID'
414EXEC(@statement)
415SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were updated in ##TempRBSUserRoles.'
416RAISERROR( @statement,0,1) WITH NOWAIT
417
418PRINT ''
419SET @statement = 'Updating ##TempRBSQualList in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
420IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
421SET @statement = 
422'UPDATE dbo.##TempRBSQualList SET userID = NewUserID '
423+ ' FROM UTILITY.dbo.UserIDMap'
424+ ' WHERE userID = OldUserID'
425+ ' AND [Database] = ''' + @database + ''''
426EXEC(@statement)
427SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were updated in ##TempRBSQualList.'
428RAISERROR( @statement,0,1) WITH NOWAIT
429
430SET @statement = 'Updating ##TempRBSExceptions in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
431IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
432SET @statement = 
433'UPDATE ##TempRBSExceptions SET rexuserID = NewUserID '
434+ ' FROM UTILITY.dbo.UserIDMap'
435+ ' WHERE rexuserID = OldUserID'
436+ ' AND [Database] = ''' + @database + ''''
437EXEC(@statement)
438SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were updated in ##TempRBSExceptions.'
439RAISERROR( @statement,0,1) WITH NOWAIT
440
441SET @statement = 'Updating ##TempRBSQualifiers in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
442IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
443SET @statement = 
444'UPDATE ##TempRBSQualifiers SET qaluserID = NewUserID '
445+ ' FROM UTILITY.dbo.UserIDMap'
446+ ' WHERE qaluserID = OldUserID '
447+ ' AND [Database] = ''' + @database + ''''
448EXEC(@statement)
449SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were updated in ##TempRBSQualifiers.'
450RAISERROR( @statement,0,1) WITH NOWAIT
451
452--Delete RBS tables in company
453SET @statement = 
454'UTILITY.dbo.proc_Manage_RBSTriggers ' + @database + ',0'
455EXEC(@statement)
456
457IF @debug = 'Y' PRINT 'Deleting Local RBSUserRoles,RBSQualList,RBSQualifiers,RBSExceptions data...'
458SET @statement = 
459'DELETE ' + @database + '.dbo.RBSQualList'
460+ ' DELETE '+ @database + '.dbo.RBSQualifiers'
461+ ' DELETE '+ @database + '.dbo.RBSExceptions'
462+ ' DELETE '+ @database + '.dbo.RBSUserRoles'
463EXEC(@statement)
464RAISERROR( 'Deleted data in Company RBS Tables',0,1) WITH NOWAIT
465
466--Update RBS tables in company
467SET @statement = 'Inserting RBSQualList in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
468IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
469SET @statement = 
470'INSERT '+ @database + '.dbo.RBSQualList([CoID],[DedGroupCode],[EEType],[EmplStatus],[ExcludeSelf],[FullTimeOrPartTime]
471      ,[JobCode],[Location],[MyEEID],[OECode],[Orglvl1],[Orglvl2],[Orglvl3],[Orglvl4],[Paygroup],[Project],[RoleID],[SalaryOrHourly]
472      ,[Shift],[SupervisorID],[TaxCalcGroupID],[UserID]) '
473+ ' SELECT [CoID],[DedGroupCode],[EEType],[EmplStatus],[ExcludeSelf],[FullTimeOrPartTime]
474      ,[JobCode],[Location],[MyEEID],[OECode],[Orglvl1],[Orglvl2],[Orglvl3],[Orglvl4],[Paygroup],[Project],[RoleID],[SalaryOrHourly]
475      ,[Shift],[SupervisorID],[TaxCalcGroupID],[UserID] FROM  ##tempRBSQualList '
476EXEC(@statement)
477SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were Inserted in RBSQualList.'
478RAISERROR( @statement,0,1) WITH NOWAIT
479
480SET @statement = 'Inserting RBSUserRoles in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
481IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
482SET @statement = 
483'INSERT '+  @database + '.dbo.RBSUserRoles(rurCascadeSupervisor,rurEEID,rurExcludeSelf,rurRoleID,rurUserCompanyID,rurUserID) '
484+ ' SELECT rurCascadeSupervisor,rurEEID,rurExcludeSelf,rurRoleID,rurUserCompanyID,rurUserID FROM  ##tempRBSUserRoles '
485+ ' JOIN '+  @database + '.dbo.EMPPERS on rureeid = eepeeid'
486+ ' JOIN '+  @database + '.dbo.RBSRoles on rolID = rurRoleID'
487EXEC(@statement)
488SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were Inserted in RBSUserRoles.'
489RAISERROR( @statement,0,1) WITH NOWAIT
490
491SET @statement = 'Inserting RBSExceptions in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
492IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
493SET @statement = 
494'INSERT '+ @database + '.dbo.RBSExceptions (rexCOID,RexEEID,rexExceptionType,rexRoleID,RexUserID) '
495+ 'SELECT rexCOID,RexEEID,rexExceptionType,rexRoleID,RexUserID FROM  ##tempRBSExceptions'
496+ ' JOIN '+ @database + '.dbo.RBSUSERRoles on ruruserid = rexuserid and rurRoleID = rexRoleID'
497EXEC(@statement)
498SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were Inserted in RBSExceptions.'
499RAISERROR( @statement,0,1) WITH NOWAIT
500
501
502SET @statement = 'Inserting RBSQualifiers in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
503IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
504SET @statement = 
505'INSERT '+ @database + '.dbo.RBSQualifiers (qalFIELD,qalRoleID,qalUserID,qalValue)'
506+ ' SELECT qalFIELD,qalRoleID,qalUserID,qalValue FROM ##tempRBSQualifiers'
507+ ' JOIN '+  @database + '.dbo.RBSUSERRoles on qaluserid = ruruserid and qalRoleID = rurRoleID'
508EXEC(@statement)
509SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were Inserted in RBSQualifiers.'
510RAISERROR( @statement,0,1) WITH NOWAIT
511
512SET @statement = 'Updating RptSvWeb in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
513IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT 
514SET @statement = 
515'UPDATE '+  @database + '.dbo.RptSvWeb' 
516+ ' SET rvwWebUserID = newuserid' 
517+ ' FROM UTILITY.dbo.UserIDMap' 
518+ ' WHERE rvwWebUserID = OldUserID' 
519+ ' AND [Database] = ''' + @database + '''' 
520EXEC(@statement) 
521SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were Updated in RptSvWeb.' 
522RAISERROR( @statement,0,1) WITH NOWAIT
523
524SET @statement = 
525'EXEC ' + 'UTILITY.dbo.proc_Manage_RBSTriggers ' + @database + ',1'
526EXEC(@statement)
527
528--Not sure if this field eepuserID in the table in this version
529SET @statement = 'Updating Emppers in the ' + UPPER(@Database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
530IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
531SET @statement = 
532'IF EXISTS(SELECT * FROM ' + @Database + '.dbo.sysobjects 
533    WHERE NAME = ''EMPPERS'' AND TYPE = ''u'')'
534+ 'EXEC(''UPDATE ' + @Database 
535+ '.dbo.Emppers SET eepuserID = ruruserid '
536+ ' FROM ' + @Database  + '.dbo.RBSUserRoles'
537+ ' WHERE eepEEID = rurEEID'')'
538EXEC(@statement)
539SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were Updated in Emppers.'
540RAISERROR( @statement,0,1) WITH NOWAIT
541
542SET @statement = 
543'IF NOT EXISTS(SELECT * FROM ' + @Database + '.dbo.sysobjects 
544    WHERE NAME = ''EMPPERS'' AND TYPE = ''u'')'
545+ 'EXEC('' UPDATE ' + @Database 
546+ '.dbo.IEmppers SET userID = ruruserid '
547+ ' FROM ' + @Database + '.dbo.RBSUserRoles'
548+ ' WHERE EEID = rurEEID'')' 
549EXEC(@statement)
550SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were Updated in Emppers.'
551RAISERROR( @statement,0,1) WITH NOWAIT
552GO
553
554
555/*********************************************************************************************************************************************************************************************/
556
557
558
559
560/****** Object:  StoredProcedure [dbo].[proc_update_int3rdparty]    Script Date: 04/30/2013 17:59:09 ******/
561SET ANSI_NULLS ON
562GO
563SET QUOTED_IDENTIFIER ON
564GO
565ALTER PROCEDURE [dbo].[proc_update_int3rdparty]
566@dbname varchar(50)
567as
568SET NOCOUNT ON
569EXEC ('IF EXISTS(SELECT 1 FROM ' + @dbname + '..SYSOBJECTS WHERE name = ''int3rdparty'')
570        BEGIN
571			UPDATE ' + @DBNAME + '.dbo.int3rdParty
572			  SET intValue = u.intvalue
573			 FROM ' + @DBNAME + '.dbo.int3rdParty i
574			 LEFT JOIN UTILITY.dbo.Uint3rdParty U ON u.intkey = i.intkey
575								   AND u.dbname = ''' + @dbname + '''
576			WHERE i.intLabel = u.intLabel
577			  AND i.intProduct = u.intProduct
578			  AND i.intValue <> u.intValue
579	      
580		   INSERT INTO ' + @DBNAME + '..int3rdparty 
581			   ([intCreateModDate]
582			   ,[intDesc]
583			   ,[intKey]
584			   ,[intLabel]
585			   ,[intProduct]
586			   ,[intValue])
587			SELECT
588				intCreateModDate
589			   ,intDesc
590			   ,intKey
591			   ,intLabel
592			   ,intProduct
593			   ,intValue
594			 FROM [UTILITY].[dbo].[Uint3rdParty]
595			WHERE NOT EXISTS (SELECT * FROM
596			' + @DBNAME + '..int3rdparty )
597			AND dbname = ''' + @DBNAME + '''
598		END	
599        --PRINT ''int3rdParty table not in this version of the Database''')
600GO
601
602
603/*********************************************************************************************************************************************************************************************/
604
605
606/****** Object:  StoredProcedure [dbo].[proc_update_JMS_UES]    Script Date: 04/30/2013 17:59:09 ******/
607SET ANSI_NULLS ON
608GO
609SET QUOTED_IDENTIFIER ON
610GO
611ALTER PROCEDURE [dbo].[proc_update_JMS_UES]
612AS
613/*BEGIN -- UPDATE JMS_UES TABLE INFO IN THE COMPANIES*/
614PRINT ''
615PRINT '***UPDATING UES JOBS IN COMPANY DATABASES***'
616		DECLARE UES_JOB CURSOR
617		READ_ONLY
618		FOR SELECT [DBname]
619			  FROM [UTILITY].[dbo].[vw_DBList] WHERE DBType = 'COMPANY'
620
621		DECLARE @dBname varchar(40)
622
623		OPEN UES_JOB
624
625		FETCH NEXT FROM UES_JOB INTO @dBname
626		WHILE (@@fetch_status <> -1)
627		BEGIN
628			IF (@@fetch_status <> -2)
629			BEGIN
630		    	PRINT 'UPDATING JmsPendingTransaction_UES ' + @dBname  
631		  exec('USE ' + @dBname + 
632		     ' IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = ''VW_RBSSITEVALUES'')' +
633		     ' BEGIN ' +
634		       ' if exists(select * from ' + @dbname + '.dbo.vw_rbsSiteValues (nolock) where stlCode=''SITETYPE'' and stlValue in(''1'',''2''))
635				   begin
636				    if exists(	
637						 select	jms.JobId
638						   from	  ' + @dbname + '..jmsJobStateInfo jms(nolock)
639						  where   jms.StateCode in (3,5)
640						 except
641						 select	distinct JobId
642						   from	' + @dbname + '..JmsPendingTransaction(nolock))
643						  begin
644						   exec	' + @dbname + '..HRMS_JmsPendingTransaction_UES
645							end
646				end
647				END')
648				             
649			END
650			
651			FETCH NEXT FROM UES_JOB INTO @dBname
652		END
653		CLOSE UES_JOB
654		DEALLOCATE UES_JOB
655		PRINT ''
656/*END ---UPDATE JMS_UES TABLE INFO IN THE COMPANIES*/
657GO
658
659
660/*********************************************************************************************************************************************************************************************/
661
662
663
664/****** Object:  StoredProcedure [dbo].[proc_sync_ExtRBSusers]    Script Date: 04/30/2013 17:59:09 ******/
665SET ANSI_NULLS ON
666GO
667SET QUOTED_IDENTIFIER ON
668GO
669ALTER PROCEDURE [dbo].[proc_sync_ExtRBSusers]
670    @SiteName varchar(30),
671      @database sysname,
672      @sourceserver VARCHAR(32),
673      @username sysname = 'dev',
674      @Rpassword VARCHAR(255) = '',
675    @Lpassword VARCHAR(255) = 'usg',
676      @debug CHAR(1) = 'N'
677AS
678SET NOCOUNT ON
679/*****
6802010 R1 SECURITY UNIFICATION BUILD
681lAST MODIFIED ON  : 9/20/2010 by Kevin Maxwell
682        Update seccompanies to fix sccclientID
683*****/
684--EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER  all"
685--DECLARE @value varchar(10)
686--SELECT @value = Cast(max(sususerid)as varchar(10)) from HRMS_SITE..rbsusers
687--IF @value IS NOT NULL
688--EXEC('USE HRMS_SITE dbcc checkident (''secusers'',reseed,' + @value + ')')
689--SELECT @value = Cast(max(sucusercompanyID)as varchar(10)) from HRMS_SITE..rbsuserCompany
690--IF @value IS NOT NULL
691--EXEC('USE HRMS_SITE dbcc checkident (''secusercontext'',reseed,' + @value + ')')
692
693IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSADMINUsers') > 0
694      DROP TABLE ##tempRBSADMINUsers
695IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUsers') > 0
696      DROP TABLE ##tempRBSUsers
697IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSHistory') > 0
698      DROP TABLE ##tempRBSHistory
699IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserHistory') > 0
700      DROP TABLE ##tempRBSUserHistory
701IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserCompany') > 0
702      DROP TABLE ##tempRBSUserCompany
703IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserPrefs') > 0
704      DROP TABLE ##tempRBSUserPrefs
705IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserAddresses') > 0
706      DROP TABLE ##tempRBSUserAddresses
707IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSPersons') > 0
708      DROP TABLE ##tempRBSPersons
709IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempUserIDMap') > 0
710      DROP TABLE ##tempUserIDMap
711IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempUserCompanyIDMap') > 0
712      DROP TABLE ##tempUserCompanyIDMap
713IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSCompanyValues') > 0
714      DROP TABLE ##tempRBSCompanyValues
715IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserQuestions') > 0
716      DROP TABLE ##tempRBSUserQuestions
717IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSPhoneNumbers') > 0
718      DROP TABLE ##tempRBSPhoneNumbers
719IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSAdminRoles') > 0
720      DROP TABLE ##tempRBSAdminRoles
721IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempSiteEvent') > 0
722      DROP TABLE ##tempSiteEvent
723IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUsersALL') > 0
724      DROP TABLE ##tempRBSUsersALL
725IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserRoles') > 0
726      DROP TABLE ##tempRBSUserRoles
727IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSQualList') > 0
728      DROP TABLE ##tempRBSQualList
729IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSQualifiers') > 0
730      DROP TABLE ##tempRBSQualifiers
731IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSExceptions') > 0
732      DROP TABLE ##tempRBSExceptions
733IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempSecDefaultPasswords') > 0
734      DROP TABLE ##tempSecDefaultPasswords
735IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##COServer') > 0
736      DROP TABLE ##COServer
737
738DECLARE @statement VARCHAR(8000),
739      @localcontextID INT, --changed to userID
740      @remotecontextID INT,
741    @remoteCompanyServer varchar(50),
742      @sourceserverstring VARCHAR(1000),
743    @remoteCOsourceserverstring VARCHAR(1000), 
744      @v_rowcount INT
745--IF @sourceserver = @@servername
746--BEGIN
747--    RAISERROR('Cannot use CURRENT server as @sourceserver.',16,1)
748--    RETURN
749--END
750SELECT @localcontextID = SccID FROM hrms_site..RBSCompanies 
751    INNER JOIN hrms_site..RBSDbs s ON sdbcompanyID = sccID
752      WHERE sdbDBName = @database
753
754IF @localcontextID IS NULL
755BEGIN
756      RAISERROR('Database not found in current (target) server.',16,1)
757      RETURN
758END
759SET @statement = 'Local ContextID for ' + UPPER(@database) + ': ' + CONVERT(VARCHAR(10),@localcontextID)
760IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
761SET @statement = 'Remote (source) server: ' + UPPER(@sourceserver)
762IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
763IF @debug = 'Y' PRINT ''
764SET @sourceserverstring = ''
765--'OPENDATASOURCE(''SQLOLEDB'',''Data Source=' 
766--    + @sourceserver + ';User ID=' 
767--    + @username + ';Password=' + @Rpassword + ''')'
768SET @statement = 'SELECT * INTO #doesdbexist FROM ' + @SiteName + '.dbo.RBSDBs WHERE sdbDBName = ''' + @database + ''''
769EXEC(@statement)
770--proc will exit immediately if this fails i.e. if server/user/password fail to locate HRMS_SITE.dbo.RBSDBs
771SET @v_rowcount = @@rowcount
772IF @v_rowcount != 1
773BEGIN 
774      SET @statement = 
775      CASE @v_rowcount
776            WHEN 0 THEN 'Database not found in specified source server.'
777            ELSE 'Database found more than once in specified source server. Cannot proceed.'
778      END -- CASE
779      RAISERROR(@statement,16,1)
780      RETURN
781END
782SET @statement = 'SELECT sdbservername INTO ##COServer FROM ' + @SiteName + '.dbo.RBSDBs WHERE sdbDBName = ''' + @database + ''''
783EXEC(@statement)
784SET @v_rowcount = @@rowcount
785IF @v_rowcount != 1
786BEGIN 
787      SET @statement = 
788      CASE @v_rowcount
789            WHEN 0 THEN 'Database not found on specified Company server in SecDBs.'
790            ELSE 'Database found more than once in specified source server. Cannot proceed.'
791      END -- CASE
792      RAISERROR(@statement,16,1)
793      RETURN
794END
795SELECT @remoteCompanyServer = sdbservername from ##COServer
796SET @statement = 'Remote (Company source) server: ' + UPPER(@remoteCompanyServer)
797IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
798IF @debug = 'Y' PRINT ''
799SET @remoteCOsourceserverstring = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source=' 
800      + @remoteCompanyServer + ';User ID=' 
801      + @username + ';Password=' + @Rpassword + ''').'
802/************** CLEAR OUT ANY ORPHANED DATA FIRST ********************/
803-- orphaned data may yield unexpected results 
804-- e.g. a stray web user suddenly being associated with the database
805
806IF @debug = 'Y' PRINT 'Deleting orphans...'
807DELETE FROM HRMS_SITE..RBSUserCompany 
808 WHERE succompanyid NOT IN (SELECT sccID FROM hrms_site..RBSCompanies)
809SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUserCompany'
810IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
811EXEC ('USE HRMS_SITE 
812IF EXISTS(SELECT * FROM SYSCONSTRAINTS WHERE OBJECT_NAME(CONSTID) = ''FK_SSOUsers_UserName'')
813ALTER TABLE [SSOUsers] NOCHECK CONSTRAINT [FK_SSOUsers_UserName]')
814DELETE FROM HRMS_SITE..RBSUsers 
815 WHERE NOT EXISTS (SELECT * FROM HRMS_SITE..RBSUserCompany WHERE susUserID = sucUserID)
816AND sususertypeid <> 3
817AND susallCompanies = 0 --do not remove the converted 3 to 2
818SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUsers'
819IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
820DELETE FROM HRMS_SITE..RBSHistory 
821 WHERE NOT EXISTS (SELECT * FROM HRMS_SITE..RBSUsers WHERE sphUserID = SusUserID)
822SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSHistory'
823IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
824DELETE FROM HRMS_SITE..RBSUserHistory 
825 WHERE NOT EXISTS (SELECT * FROM HRMS_SITE..RBSUsers WHERE ruhUserID = SusUserID)
826SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUserHistory'
827IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
828DELETE FROM HRMS_SITE..RBSPhoneNumbers
829  WHERE NOT EXISTS (SELECT * FROM HRMS_SITE..RBSUsers WHERE phnUserID = SusUserID)
830SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSPhoneNumbers'
831IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
832DELETE FROM HRMS_SITE..RBSPersons
833  WHERE NOT EXISTS (SELECT * FROM HRMS_SITE..RBSUsers WHERE sprUserID = SusUserID)
834SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSPersons'
835IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
836DELETE FROM HRMS_SITE..RBSAdminRoles
837  WHERE NOT EXISTS (SELECT * FROM HRMS_SITE..RBSUsers WHERE rarUserID = SusUserID)
838SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSAdminRoles'
839IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
840DELETE FROM HRMS_SITE..RBSUserPrefs
841  WHERE NOT EXISTS (SELECT * FROM HRMS_SITE..RBSUsers WHERE uspUserID = SusUserID)
842SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUserPrefs'
843IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
844DELETE FROM HRMS_SITE..RBSUserAddresses
845  WHERE NOT EXISTS (SELECT * FROM HRMS_SITE..RBSUsers WHERE usaUserID = SusUserID)
846SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUserAddresses'
847IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
848EXEC('DELETE FROM HRMS_SITE..RBSUserQuestions 
849  WHERE NOT EXISTS (SELECT * FROM HRMS_SITE..RBSUsers U WHERE U.susUserID = UserID)')
850SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUserQuestions'
851IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
852DELETE HRMS_SITE..RBSCompanyValues
853Where svlCompanyID = @localcontextID
854SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSCompanyValues'
855IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
856DELETE HRMS_SITE..SiteEvent
857Where stvContextID = @localcontextID
858SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from SiteEvent'
859IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
860
861IF @debug = 'Y' PRINT ''
862/************** Bye-bye orphans *************/
863/************** GATHER DATA FROM SOURCE SERVER ********************/
864
865IF @debug = 'Y' PRINT 'Retrieving remote RBSUserCompany data...'
866SET @statement = 'SELECT a.* INTO ##tempRBSUserCompany FROM '
867      + @SiteName +'.dbo.RBSUserCompany a INNER JOIN '
868      + @SiteName +'.dbo.RBSCompanies ON SccID = sucCompanyID INNER JOIN '
869      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
870      + ' WHERE sdbDBName = ''' + @database + ''''
871EXEC(@statement)
872IF @debug = 'Y' PRINT 'Retrieving remote RBSUsers data...'
873/*s.susdefaultpwd as ''susOriginalDefaultPWD'',*/
874SET @statement = 'SELECT B.* INTO ##tempRBSUsers FROM '
875    + '(SELECT a.*  FROM '
876    + @SiteName +'.dbo.RBSUsers a INNER JOIN '
877--   + @sourceserverstring + '.HRMS_SITE.dbo.SecUsers s ON s.sususername = a.sususername INNER JOIN '
878      + @SiteName +'.dbo.RBSUserCompany U ON U.sucUserID = a.susUserID INNER JOIN '
879      + @SiteName +'.dbo.RBSCompanies ON SccID = sucCompanyID INNER JOIN '
880      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
881      + ' Where sdbDBName = ''' + @database + '''      UNION ALL SELECT c.*  FROM '  + @SiteName +'.dbo.RBSUsers c WHERE c.susallcompanies = 1) B'
882EXEC(@statement)
883
884IF @debug = 'Y' PRINT 'Retrieving remote RBSPersons data...'
885SET @statement = 'SELECT B.* INTO ##tempRBSPersons FROM '
886    + '(SELECT a.* FROM '
887    + @SiteName +'.dbo.RBSPersons a INNER JOIN '
888      + @SiteName +'.dbo.RBSUsers on sususerid = spruserID INNER JOIN '
889      + @SiteName +'.dbo.RBSUserCompany U ON U.sucUserID = susUserID INNER JOIN '
890      + @SiteName +'.dbo.RBSCompanies ON SccID = sucCompanyID INNER JOIN '
891      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
892      + ' Where sdbDBName = ''' + @database + 
893    '''   UNION ALL SELECT c.*  FROM ' 
894    + @SiteName +'.dbo.RBSpersons c INNER JOIN ' 
895    + @SiteName +'.dbo.RBSUsers on sususerid = c.spruserID WHERE susallcompanies = 1) B'
896EXEC(@statement)
897--select * from ##temprbspersons
898
899IF @debug = 'Y' PRINT 'Retrieving remote RBSHistory data...'
900SET @statement = 'SELECT B.* INTO ##tempRBSHistory FROM '
901    + '(SELECT a.* FROM '
902      + @SiteName +'.dbo.RBSHistory a INNER JOIN '
903      + @SiteName +'.dbo.RBSUsers on sususerID = sphUserID INNER JOIN '
904      + @SiteName +'.dbo.RBSUserCompany U ON U.sucUserID = susUserID INNER JOIN '
905      + @SiteName +'.dbo.RBSCompanies ON SccID = sucCompanyID INNER JOIN '
906      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
907      + ' WHERE sdbDBName = ''' + @database + 
908    '''   UNION ALL SELECT c.*  FROM ' 
909    + @SiteName +'.dbo.RBSHistory c INNER JOIN ' 
910    + @SiteName +'.dbo.RBSUsers on sususerid = c.sphuserID WHERE susallcompanies = 1) B'
911
912EXEC(@statement)
913IF @debug = 'Y' PRINT 'Retrieving remote RBSUserHistory data...'
914SET @statement = 'SELECT B.* INTO ##tempRBSUserHistory FROM '
915    + '(SELECT a.* FROM '
916      + @SiteName +'.dbo.RBSUserHistory a INNER JOIN '
917      + @SiteName +'.dbo.RBSUsers on sususerID = ruhUserID INNER JOIN '
918      + @SiteName +'.dbo.RBSUserCompany U ON U.sucUserID = susUserID INNER JOIN '
919      + @SiteName +'.dbo.RBSCompanies ON SccID = sucCompanyID INNER JOIN '
920      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
921      + ' WHERE sdbDBName = ''' + @database + 
922    '''   UNION ALL SELECT c.*  FROM ' 
923    + @SiteName +'.dbo.RBSUserHistory c INNER JOIN ' 
924    + @SiteName +'.dbo.RBSUsers on sususerid = c.ruhuserID WHERE susallcompanies = 1) B'
925
926EXEC(@statement)
927IF @debug = 'Y' PRINT 'Retrieving remote RBSUserAddresses data...'
928SET @statement = 'SELECT B.* INTO ##tempRBSUserAddresses FROM '
929    + '(SELECT a.* FROM '
930      + @SiteName +'.dbo.RBSUserAddresses a INNER JOIN '
931      + @SiteName +'.dbo.RBSUsers on sususerID = usaUserID INNER JOIN '
932      + @SiteName +'.dbo.RBSUserCompany U ON U.sucUserID = susUserID INNER JOIN '
933      + @SiteName +'.dbo.RBSCompanies ON SccID = sucCompanyID INNER JOIN '
934      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
935      + ' WHERE sdbDBName = ''' + @database + 
936    '''   UNION ALL SELECT c.*  FROM ' 
937    + @SiteName +'.dbo.RBSUserAddresses c INNER JOIN ' 
938    + @SiteName +'.dbo.RBSUsers on sususerid = c.usauserID WHERE susallcompanies = 1) B'
939
940EXEC(@statement)
941IF @debug = 'Y' PRINT 'Retrieving remote RBSPhonenumbers data...'
942SET @statement = 'SELECT B.* INTO ##tempRBSPhonenumbers FROM '
943    + '(SELECT a.* FROM '
944      + @SiteName +'.dbo.RBSPhonenumbers a INNER JOIN '
945      + @SiteName +'.dbo.RBSUsers on sususerID = phnUserID INNER JOIN '
946      + @SiteName +'.dbo.RBSUserCompany U ON U.sucUserID = susUserID INNER JOIN '
947      + @SiteName +'.dbo.RBSCompanies ON SccID = sucCompanyID INNER JOIN '
948      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
949      + ' WHERE sdbDBName = ''' + @database + 
950    '''   UNION ALL SELECT c.*  FROM ' 
951    + @SiteName +'.dbo.RBSPhonenumbers c INNER JOIN ' 
952    + @SiteName +'.dbo.RBSUsers on sususerid = c.phnuserID WHERE susallcompanies = 1) B'
953
954EXEC(@statement)
955
956IF @debug = 'Y' PRINT 'Retrieving remote RBSUserPrefs data...'
957--SET @statement = 'SELECT B.* INTO ##tempRBSUserPrefs FROM '
958--    + '(SELECT a.* FROM '
959--    + @sourceserverstring + '.HRMS_SITE.dbo.RBSUserPrefs a INNER JOIN '
960--    + @sourceserverstring + '.HRMS_SITE.dbo.RBSUsers on sususerID = uspUserID INNER JOIN '
961--    + @sourceserverstring + '.HRMS_SITE.dbo.RBSUserCompany U ON U.sucUserID = susUserID INNER JOIN '
962--    + @sourceserverstring + '.HRMS_SITE.dbo.RBSCompanies ON SccID = sucCompanyID INNER JOIN '
963--    + @sourceserverstring + '.HRMS_SITE.dbo.RBSDBs ON sDBCompanyID = sccID '
964--    + ' WHERE sdbDBName = ''' + @database + 
965--    '''   UNION ALL SELECT c.*  FROM ' 
966--    + @sourceserverstring + '.HRMS_SITE.dbo.RBSUserPrefs c INNER JOIN ' 
967--    + @sourceserverstring + '.HRMS_SITE.dbo.RBSUsers on sususerid = c.uspuserID WHERE susallcompanies = 1) B'
968SET @statement = 'SELECT a.* INTO ##tempRBSUserPrefs FROM '
969      + @SiteName +'.dbo.RBSUserPrefs a INNER JOIN '
970      + @SiteName +'.dbo.RBSUsers on sususerID = uspUserID INNER JOIN '
971      + @SiteName +'.dbo.RBSUserCompany U ON U.sucUserID = susUserID INNER JOIN '
972      + @SiteName +'.dbo.RBSCompanies ON SccID = sucCompanyID INNER JOIN '
973      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
974      + ' WHERE sdbDBName = ''' + @database + ''''
975EXEC(@statement)
976IF @debug = 'Y' PRINT 'Retrieving remote RBSUserQuestions data...'
977SET @statement = 'SELECT B.* INTO ##tempRBSUserQuestions FROM '
978    + '(SELECT a.* FROM '
979      + @SiteName +'.dbo.RBSUserQuestions a INNER JOIN '
980      + @SiteName +'.dbo.RBSUsers r on r.sususerID = a.UserID INNER JOIN '
981      + @SiteName +'.dbo.RBSUserCompany U ON U.sucUserID = r.susUserID INNER JOIN '
982      + @SiteName +'.dbo.RBSCompanies ON SccID = sucCompanyID INNER JOIN '
983      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
984      + ' WHERE sdbDBName = ''' + @database + 
985    '''   UNION ALL SELECT c.*  FROM ' 
986    + @SiteName +'.dbo.RBSUserQuestions c INNER JOIN ' 
987    + @SiteName +'.dbo.RBSUsers r on r.sususerid = c.userID WHERE r.susallcompanies = 1) B'
988
989EXEC(@statement)
990IF @debug = 'Y' PRINT 'Retrieving remote RBSAdminRoles data...'
991SET @statement = 'SELECT B.* INTO ##tempRBSAdminRoles FROM '
992    + '(SELECT a.* FROM '
993      + @SiteName +'.dbo.RBSAdminRoles a INNER JOIN '
994      + @SiteName +'.dbo.RBSUsers r on r.sususerID = rarUserID INNER JOIN '
995      + @SiteName +'.dbo.RBSUserCompany U ON U.sucUserID = r.susUserID INNER JOIN '
996      + @SiteName +'.dbo.RBSCompanies ON SccID = sucCompanyID INNER JOIN '
997      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
998      + ' WHERE sdbDBName = ''' + @database + 
999    '''   UNION ALL SELECT c.*  FROM ' 
1000    + @SiteName +'.dbo.RBSAdminRoles c INNER JOIN ' 
1001    + @SiteName +'.dbo.RBSUsers on sususerid = c.raruserID WHERE susallcompanies = 1) B'
1002
1003EXEC(@statement)
1004IF @debug = 'Y' PRINT 'Retrieving remote SiteEvent data...'
1005SET @statement = 'SELECT a.* INTO ##tempSiteEvent FROM '
1006      + @SiteName +'.dbo.SiteEvent a INNER JOIN '
1007      + @SiteName +'.dbo.RBSCompanies ON SccID = a.stvContextID INNER JOIN '
1008      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
1009      + ' and sdbDBName = ''' + @database + ''''
1010EXEC(@statement)
1011IF @debug = 'Y' PRINT 'Retrieving remote RBSCompanyValues data...'
1012SET @statement = 'SELECT a.* INTO ##tempRBSCompanyValues FROM '
1013      + @SiteName +'.dbo.RBSCompanyValues a INNER JOIN '
1014      + @SiteName +'.dbo.RBSCompanies ON SccID = svlCompanyID INNER JOIN '
1015      + @SiteName +'.dbo.RBSDBs ON sDBCompanyID = sccID '
1016      + ' and sdbDBName = ''' + @database + ''''
1017EXEC(@statement)
1018
1019DELETE FROM ##tempRBSUserCompany WHERE sucUserID NOT IN (SELECT susUserID FROM ##tempRBSUsers)
1020DELETE FROM ##tempRBSHistory WHERE sphUserID NOT IN (SELECT susUserID FROM ##tempRBSUsers)
1021DELETE FROM ##tempRBSUserHistory WHERE ruhUserID NOT IN (SELECT susUserID FROM ##tempRBSUsers)
1022DELETE FROM ##tempRBSPhoneNumbers WHERE phnUserID NOT IN (SELECT susUserID FROM ##tempRBSUsers)
1023DELETE FROM ##tempRBSPersons WHERE sprUserID NOT IN (SELECT susUserID FROM ##tempRBSUsers)
1024DELETE FROM ##tempRBSUserprefs WHERE uspUserID NOT IN (SELECT susUserID FROM ##tempRBSUsers)
1025DELETE FROM ##tempRBSUserAddresses WHERE usaUserID NOT IN (SELECT susUserID FROM ##tempRBSUsers)
1026DELETE FROM ##tempRBSUserQuestions  WHERE UserID NOT IN (SELECT susUserID FROM ##tempRBSUsers)
1027DELETE FROM ##tempRBSAdminRoles WHERE rarUserID NOT IN (SELECT susUserID FROM ##tempRBSUsers)
1028
1029--select * from ##temprbspersons
1030
1031IF @debug = 'Y'
1032BEGIN
1033    PRINT ''
1034      SELECT @statement = 'Retrieved ' + CONVERT(VARCHAR(10),COUNT(*)) + ' web users.' FROM ##tempRBSUsers
1035      RAISERROR( @statement,0,1) WITH NOWAIT
1036      SELECT @statement = 'Retrieved ' + CONVERT(VARCHAR(10),COUNT(*)) + ' personal records.' FROM ##tempRBSPersons
1037      RAISERROR( @statement,0,1) WITH NOWAIT
1038      SELECT @statement = 'Retrieved ' + CONVERT(VARCHAR(10),COUNT(*)) + ' history records.' FROM ##tempRBSHistory
1039      RAISERROR( @statement,0,1) WITH NOWAIT
1040    SELECT @statement = 'Retrieved ' + CONVERT(VARCHAR(10),COUNT(*)) + ' Userhistory records.' FROM ##tempRBSUserHistory
1041      RAISERROR( @statement,0,1) WITH NOWAIT
1042      SELECT @statement = 'Retrieved ' + CONVERT(VARCHAR(10),COUNT(*)) + ' User prefs records.' FROM ##tempRBSUserPrefs
1043      RAISERROR( @statement,0,1) WITH NOWAIT
1044    SELECT @statement = 'Retrieved ' + CONVERT(VARCHAR(10),COUNT(*)) + ' User/Phone Numbers records.' FROM ##tempRBSPhoneNumbers
1045      RAISERROR( @statement,0,1) WITH NOWAIT
1046    SELECT @statement = 'Retrieved ' + CONVERT(VARCHAR(10),COUNT(*)) + ' User/Address records.' FROM ##tempRBSUserAddresses
1047      RAISERROR( @statement,0,1) WITH NOWAIT
1048    SELECT @statement = 'Retrieved ' + CONVERT(VARCHAR(10),COUNT(*)) + ' User/Questions records.' FROM ##tempRBSUserQuestions
1049      RAISERROR( @statement,0,1) WITH NOWAIT
1050    SELECT @statement = 'Retrieved ' + CONVERT(VARCHAR(10),COUNT(*)) + ' Company Value records.' FROM ##tempRBSCompanyValues
1051      RAISERROR( @statement,0,1) WITH NOWAIT
1052    SELECT @statement = 'Retrieved ' + CONVERT(VARCHAR(10),COUNT(*)) + ' Site Event records.' FROM ##tempSiteEvent
1053      RAISERROR( @statement,0,1) WITH NOWAIT
1054      PRINT ''
1055END
1056
1057/*********************** CLEAR DATA FROM TARGET (THIS) SERVER ***********/
1058-- Data for the same "contextID" might not actually be for the same database
1059-- e.g. if database was added to SecCompanies via sp_usg_sync_seccontexts
1060-- might be data already here with same ContextID as was assigned, by coincidence
1061-- We'll get rid of it
1062-- Even if it is data for the right database... well we're refreshing/replacing it (duh)
1063SET @statement = 'Deleting data specifically for the same CompanyID (' + CONVERT(VARCHAR(10),@localcontextID)
1064      + ') as the database, ' + UPPER(@database)
1065IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1066DELETE FROM HRMS_SITE..RBSUserCompany WHERE sucCompanyID = @localcontextID
1067SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUserCompany'
1068IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1069DELETE FROM HRMS_SITE..RBSCompanyValues WHERE svlCompanyID = @localcontextID
1070SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSCompanyValues'
1071IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1072DELETE FROM HRMS_SITE..SiteEvent WHERE stvContextID = @localcontextID
1073SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from SiteEvent'
1074IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1075DELETE FROM HRMS_SITE..RBSUsers 
1076 WHERE susUserID NOT IN (SELECT sucUserID FROM HRMS_SITE..RBSUserCompany)
1077AND sususertypeid <> 3
1078AND susallcompanies = 0 
1079SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUsers'
1080IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1081DELETE FROM HRMS_SITE..RBSHistory 
1082 WHERE sphUserID NOT IN (SELECT susUserID FROM HRMS_SITE..RBSUsers WITH (NOLOCK))
1083SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSHistory'
1084IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1085DELETE FROM HRMS_SITE..RBSUserHistory 
1086 WHERE ruhUserID NOT IN (SELECT susUserID FROM HRMS_SITE..RBSUsers WITH (NOLOCK))
1087SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUserHistory'
1088IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1089DELETE FROM HRMS_SITE..RBSPhoneNumbers
1090WHERE phnUserID NOT IN (SELECT susUserID FROM HRMS_SITE..RBSUsers WITH (NOLOCK))
1091SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSPhoneNumbers'
1092IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1093DELETE FROM HRMS_SITE..RBSPersons 
1094 WHERE sprUserID NOT IN (SELECT susUserID FROM HRMS_SITE..RBSUsers WITH (NOLOCK))
1095SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSPersons'
1096IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1097DELETE FROM HRMS_SITE..RBSUserprefs 
1098 WHERE uspUserID NOT IN (SELECT susUserID FROM HRMS_SITE..RBSUsers WITH (NOLOCK))
1099SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUserprefs'
1100IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1101DELETE FROM HRMS_SITE..RBSUserAddresses
1102WHERE usaUserID NOT IN (SELECT susUserID FROM HRMS_SITE..RBSUsers WITH (NOLOCK))
1103SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUserAddresses'
1104IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1105
1106EXEC('DELETE FROM HRMS_SITE..RBSUserQuestions
1107WHERE UserID NOT IN (SELECT susUserID FROM HRMS_SITE..RBSUsers WITH (NOLOCK))')
1108SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows from RBSUserQuestions'
1109IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1110
1111IF @debug = 'Y' PRINT ''
1112
1113/******************* LOCAL DATA DELETED *********************/
1114/*** REMOVE from ##tempsecusers any users that are NOT 'EMPLOYEE' type that are already in SecUsers
1115and thus are single-users that have access to multiple-contexts ***/
1116IF @debug = 'Y'
1117BEGIN
1118      PRINT 'Correcting duplicate names...'
1119      SELECT @statement = CONVERT(VARCHAR(10),COUNT(sucUserCompanyID)) + ' users are EMPLOYEE type.'
1120      FROM ##tempRBSUserCompany JOIN ##tempRBSUsers  ON sucUserID = susUserID
1121      WHERE susUserTypeID = 1
1122      RAISERROR( @statement,0,1) WITH NOWAIT
1123    SELECT @statement = CONVERT(VARCHAR(10),COUNT(sucUserCompanyID)) + ' users are COMPANY ADMIN types.'
1124      FROM ##tempRBSUserCompany JOIN ##tempRBSUsers  ON sucUserID = susUserID
1125      WHERE susUserTypeID = 2
1126      RAISERROR( @statement,0,1) WITH NOWAIT
1127      SELECT @statement = CONVERT(VARCHAR(10),COUNT(sucUserCompanyID)) + ' users are SITEADMIN/SUPERUSER type.'
1128      FROM ##tempRBSUserCompany JOIN ##tempRBSUsers ON sucUserID = susUserID
1129      WHERE susUserTypeID = 3
1130      RAISERROR( @statement,0,1) WITH NOWAIT
1131END
1132SELECT * INTO ##tempRBSUsersALL FROM ##tempRBSUsers
1133-- We'll need this later; we're removing data from ##tempSecUsers
1134-- for easy insertion into SecUsers BUT we still NEED the data
1135-- later - ALL of it - to create an OldUserID --> NewUserID map table
1136-- after we insert all of the "EMPLOYEE" SecUsers
1137-- We're about to remove data from ##tempSecUsers for names that are duplicate but are siteadmin/superuser accounts
1138-- to facilitate fixing the ones that need to be added with new names (employee users)
1139/***
1140
1141Confused? 
1142Some users, if they already exist, should simply not be added at all - siteadmin and superuser types
1143Others should be added, but they'll need new unique usernames - employee types
1144The ones that aren't added DO still need SecUserContext records added so those siteadmin users can still
1145access the database we're doing this for
1146Fixing the UserID in SecUserContext is one reason we need ALL of the data later for the map table ;)
1147and it was only a matter of time before I put an 'x' instead of a 'c' in the name of one of these tables
1148***/
1149/**Update RBSSite users that are type 2 on merged server but 1 on this server**/
1150UPDATE HRMS_SITE..RBSUSERS
1151SET sususerTypeID = 2,
1152    susallCompanies = 1  
1153WHERE susUserName in (SELECT susUserName from ##tempRBSUsers WHERE sususertypeID = 2)
1154/**SAV type 2 and susall company susers for mapping table**/
1155SELECT t.* INTO ##tempRBSADMINUsers
1156FROM ##tempRBSUsers t
1157JOIN HRMS_SITE..RBSUsers u ON t.susUserName = u.susUserName
1158WHERE u.susUserTypeID >= 2
1159   AND u.susallcompanies = 1   --remove type 3 and the previous type3 now type 2
1160/**Remove type 2 and susall company susers**/
1161DELETE FROM ##tempRBSUsers
1162FROM ##tempRBSUsers t
1163JOIN HRMS_SITE..RBSUsers u ON t.susUserName = u.susUserName
1164WHERE u.susUserTypeID >= 2
1165   AND u.susallcompanies = 1   --remove type 3 and the previous type3 now type 2
1166
1167SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) 
1168+ ' of the retrieved web users are already-existing non-EMPLOYEE (e.g. SITEADMIN or SUPERUSER) users.'
1169IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1170/** Delete RBSUserCompany record for removed users **/
1171DELETE FROM ##tempRBSUserCompany
1172WHERE NOT EXISTS (SELECT * FROM ##tempRBSUsers WHERE susUserID = sucUserID)
1173SET @statement = 'These users will have access to ' + UPPER(@database) + ' but do not need new user accounts.'
1174IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1175DELETE FROM ##tempRBSHistory 
1176 WHERE NOT EXISTS (SELECT * FROM ##tempRBSUsers WHERE sphUserID = susUserID)
1177
1178/*** Correct any duplicate UserNames before we try to insert them ***/
1179DECLARE @usernameincrement INT,
1180          @totalrowsupdated INT
1181SET @usernameincrement = 0
1182SET @totalrowsupdated = 0
1183IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempDuplicateUsers') > 0
1184      DROP TABLE ##tempDuplicateUsers
1185CREATE TABLE ##tempDuplicateUsers (UserID INT, BadUserName VARCHAR(50), GoodUserName VARCHAR(50))
1186INSERT INTO ##tempDuplicateUsers
1187SELECT t.susUserID, t.susUserName, t.susUserName
1188FROM ##tempRBSUsers t
1189   WHERE t.susUserName IN (SELECT susUserName FROM HRMS_SITE..RBSUsers where susUserTypeID =1 )
1190AND t.susUserTypeID = 1 
1191SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' users are duplicate EMPLOYEE type rows and will be updated.'
1192IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1193IF @debug = 'Y' PRINT '(Number will be appended to username)'
1194IF @debug = 'Y' PRINT ''
1195IF @debug = 'Y' AND (SELECT COUNT(*) FROM ##tempDuplicateUsers) > 0 SELECT BadUserName FROM ##tempDuplicateUsers
1196WHILE (SELECT COUNT(*) FROM ##tempDuplicateUsers) > 0
1197BEGIN
1198      SET @usernameincrement = @usernameincrement + 1
1199      UPDATE ##tempDuplicateUsers 
1200         SET GoodUserName = BadUserName + CONVERT(VARCHAR(5),@usernameincrement)
1201      IF @debug = 'Y' AND (SELECT COUNT(*) FROM ##tempDuplicateUsers) > 0 SELECT BadUserName, GoodUserName FROM ##tempDuplicateUsers
1202      DELETE FROM ##tempDuplicateUsers 
1203         WHERE GoodUserName IN (SELECT susUserName FROM HRMS_SITE..RBSUsers)
1204      UPDATE ##tempRBSUsers 
1205         SET susUserName = GoodUserName
1206        FROM ##tempRBSUsers JOIN ##tempDuplicateUsers ON susUserID = UserID
1207        SET @totalrowsupdated = @totalrowsupdated + @@ROWCOUNT
1208      TRUNCATE TABLE ##tempDuplicateUsers
1209--INSERT updated Users that are already in the RBSUSER table 
1210      INSERT INTO ##tempDuplicateUsers
1211      SELECT  susUserID, susUserName, susUserName 
1212          FROM ##tempRBSUsers WHERE susUserName IN (SELECT susUserName FROM HRMS_SITE..RBSUsers  where susUserTypeID = 1 )
1213--INSERT one instance of a duplicate user.
1214    INSERT INTO ##tempDuplicateUsers
1215      SELECT DISTINCT susUserID, susUserName, susUserName 
1216          FROM ##tempRBSUsers WHERE sususerID IN(select Max(sususerid) from ##temprbsusers where sususername in (
1217                                                 select  sususername from ##temprbsusers
1218                                                 where susUserTypeID = 1 
1219                                                 group by sususername having count(sususername) >1)
1220                                                 group by sususername)
1221END
1222SET @statement = 'Corrected ' + CONVERT(VARCHAR(10),@totalrowsupdated) + ' duplicate-named EMPLOYEE-type SecUsers (some possibly more than one pass).'
1223IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1224IF @debug = 'Y' PRINT ''
1225UPDATE ##tempRBSUsersALL 
1226 SET susUserName = u.susUserName
1227FROM ##tempRBSUsersALL t JOIN ##tempRBSUsers u ON t.susUserID = u.susUserID
1228/*** Update contextID ***/
1229UPDATE ##tempRBSUserCompany 
1230 SET sucCompanyID = @localcontextID
1231SET @statement = 'UPDATED ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows in RBSUserCompany'
1232IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1233--/**Update RBSSite users that are type 2 on merged server but 1 on this server**/
1234UPDATE HRMS_SITE..RBSUSERS
1235SET sususerTypeID = 2,
1236    susallCompanies = 1  
1237WHERE susUserName in (SELECT susUserName from ##tempRBSUsers WHERE sususertypeID = 2)
1238/**UPDATE USERS THAT ARE REGULAR BUT TYPE TWO IN SITE ALREADY**/
1239UPDATE ##tempRBSUsers
1240SET sususertypeID =2 
1241WHERE susUserName in (SELECT susUserName from HRMS_SITE..RBSUSERS WHERE sususertypeID = 2)
1242
1243/**DELETE USERS TYPE 2 THAT EXIST IN RBSUSERS***/
1244DELETE ##tempRBSUsers
1245WHERE susUsername in (SELECT susUserName from HRMS_SITE..RBSUSERS WHERE sususertypeID = 2)
1246/*** Insert SecUsers -- but we need to update UserID in other tables afterwards, susUserID is an IDENTITY ***/
1247/*** susUserName is also guaranteed unique though... can use that to re-join and make a map table ***/
1248IF @debug = 'Y' PRINT 'Inserting new data...'
1249
1250INSERT INTO HRMS_SITE.dbo.RBSUsers ( susUserName,susallcompanies,susChgPwdAttempts,susDefaultPwd,
1251                                     susFullName, susLastLogon, susNetworkIdentity,susNetworkData, susPassword,
1252                                     susIsDefaultPwd,susPasswordHint,susSecPassword,susSecLastPChange, susStatus, susTotalRetries, susUserTypeID,susUserKey) 
1253       SELECT  DISTINCT r.susUserName,r.susallcompanies,r.susChgPwdAttempts,
1254        r.susDefaultPwd, r.susFullName, r.susLastLogon, r.susNetworkIdentity,
1255        r.susNetworkData, r.susPassword,r.susIsDefaultPwd,r.susPasswordHint, 
1256        r.susSecPassword,r.susSecLastPChange,r.susStatus, r.susTotalRetries,  r.susUserTypeID,r.susUserKey  
1257        FROM ##tempRBSUsers r
1258        
1259SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' New rows were added to RBSUsers.'
1260IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1261
1262INSERT INTO HRMS_SITE.dbo.RBSUsers ( susUserName,susallcompanies,susChgPwdAttempts,susDefaultPwd,
1263                                     susFullName, susLastLogon, susNetworkIdentity,susNetworkData, susPassword,
1264                                     susIsDefaultPwd,susPasswordHint,susSecPassword,susSecLastPChange, susStatus, susTotalRetries, susUserTypeID,susUserKey) 
1265       SELECT  DISTINCT r.susUserName,r.susallcompanies,r.susChgPwdAttempts,
1266        r.susDefaultPwd, r.susFullName, r.susLastLogon, r.susNetworkIdentity,
1267        r.susNetworkData, r.susPassword,r.susIsDefaultPwd,r.susPasswordHint, 
1268        r.susSecPassword,r.susSecLastPChange,r.susStatus, r.susTotalRetries,  r.susUserTypeID,r.susUserKey  
1269        FROM ##tempRBSAdminUsers r
1270        WHERE r.sususername NOT IN (SELECT susUserName from HRMS_SITE..RBSUSERS WHERE sususertypeID > 1)
1271SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' New ADMIN rows were added to RBSUsers.'
1272IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1273
1274SELECT u.SusUserID AS NewUserID, t.SusUserID AS OldUserID
1275  INTO ##tempUserIDMap
1276  FROM HRMS_SITE..RBSUsers u 
1277  JOIN ##tempRBSUsers t ON u.susUserName = t.susUserName
1278  
1279
1280/**Add RBSAdmin users to mapping table**/
1281INSERT ##tempUserIDMap(  NewUserID, OldUserID)
1282SELECT DISTINCT u.SusUserID AS NewUserID, t.SusUserID AS OldUserID
1283  FROM HRMS_SITE..RBSUsers u 
1284  JOIN ##tempRBSADMINUsers t ON u.susUserName = t.susUserName
1285CREATE NONCLUSTERED INDEX [IDX_TempRBSUsers] ON [dbo].[##tempUserIDMap] 
1286(
1287      [OldUserID] ASC
1288)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
1289
1290/*** Update other retrieved data with new UserID's ***/
1291
1292UPDATE ##tempRBSHistory
1293SET sphUserID = NewUserID
1294FROM ##tempRBSHistory JOIN ##tempUserIDMap ON sphUserID = OldUserID
1295UPDATE ##tempRBSUserHistory
1296SET ruhUserID = NewUserID
1297FROM ##tempRBSUserHistory JOIN ##tempUserIDMap ON ruhUserID = OldUserID
1298UPDATE ##tempRBSUserHistory
1299SET ruhCompanyID = @localcontextID
1300UPDATE ##tempRBSpersons
1301SET sprUserID = NewUserID
1302FROM ##tempRBSPersons JOIN ##tempUserIDMap ON sprUserID = OldUserID
1303
1304UPDATE ##tempRBSUserCompany
1305SET sucUserID = NewUserID
1306FROM ##tempUserIDMap 
1307WHERE sucUserID = OldUserID
1308
1309
1310UPDATE ##tempRBSUserprefs
1311SET uspUserID = NewUserID
1312FROM ##tempRBSUserprefs JOIN ##tempUserIDMap ON uspUserID = OldUserID
1313UPDATE ##tempRBSPhoneNumbers
1314SET phnUserID = NewUserID
1315FROM ##tempRBSPhoneNumbers JOIN ##tempUserIDMap ON phnUserID = OldUserID
1316UPDATE ##tempRBSUserAddresses
1317SET usaUserID = NewUserID
1318FROM ##tempRBSUserAddresses JOIN ##tempUserIDMap ON usaUserID = OldUserID
1319UPDATE ##tempRBSUserQuestions
1320SET UserID = NewUserID
1321FROM ##tempRBSUserQuestions T JOIN ##tempUserIDMap ON T.UserID = OldUserID
1322UPDATE ##tempRBSAdminRoles
1323SET rarUserID = NewUserID
1324FROM ##tempRBSAdminRoles JOIN ##tempUserIDMap ON rarUserID = OldUserID
1325
1326
1327-- update rows that didn't get a row in the map table
1328-- because the map table was based on ##tempsecusers; some ##tempsecusers were
1329-- removed earlier because they already existed by name in SecUsers
1330-- the would be SiteAdmin users... and key should = name for them
1331/** now we can insert data from the other tables **/
1332/** BUT SecUserContext also has sucUserContextID, which is an identity and
1333      must be updated in SecGroups in the company database **/
1334/** SecUserContext also has a sucUserKey - tied to susUserName, so we can make another map table ***/
1335
1336/**LETS delete any potential duplicates */
1337
1338IF @debug = 'Y' PRINT 'Deleting Duplicates...'
1339
1340DELETE FROM HRMS_SITE..RBSHistory 
1341 WHERE sphUserID in (SELECT sphUserID FROM ##TempRBSUsers)
1342SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' Duplicate rows from RBSHistory'
1343IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1344DELETE FROM HRMS_SITE..RBSUserHistory 
1345 WHERE ruhUserID in (SELECT ruhUserID FROM ##TempRBSUsers)
1346SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' Duplicate rows from RBSUserHistory'
1347IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1348DELETE FROM HRMS_SITE..RBSPhoneNumbers
1349  WHERE phnUserID in (SELECT phnUserID FROM ##tempRBSPersons)
1350SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' Duplicate rows from RBSPhoneNumbers'
1351IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1352DELETE FROM HRMS_SITE..RBSPersons
1353  WHERE sprUserID in (SELECT sprUserID FROM ##tempRBSPersons)
1354SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' Duplicate rows from RBSPersons'
1355IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1356DELETE FROM HRMS_SITE..RBSAdminRoles
1357  WHERE rarUserid in (SELECT rarUserid FROM ##tempRBSAdminRoles)
1358SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' Duplicate rows from RBSAdminRoles'
1359IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1360DELETE FROM HRMS_SITE..RBSUserPrefs
1361  WHERE uspUserID in (SELECT uspUserID FROM ##tempRBSUserPrefs)
1362SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' Duplicate rows from RBSUserPrefs'
1363IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1364DELETE FROM HRMS_SITE..RBSUserAddresses
1365  WHERE usaUserID in (SELECT usaUserID FROM ##tempRBSUserAddresses)
1366SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' Duplicate rows from RBSUserAddresses'
1367IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1368EXEC('DELETE FROM HRMS_SITE..RBSUserQuestions 
1369  WHERE UserID in (SELECT UserID FROM ##tempRBSUserQuestions)') 
1370SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' Duplicate rows from RBSUserQuestions'
1371IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1372
1373IF @debug = 'Y' PRINT ''
1374
1375/************** Bye-bye orphans *************/
1376DELETE HRMS_SITE..RBSUserCompany 
1377WHERE sucUserCompanyID IN (SELECT sucUserCompanyID FROM ##tempRBSUserCompany)
1378AND sucCompanyid = @localcontextID
1379
1380SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' adding Employee rows to RBSUserCompany..'
1381IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1382
1383INSERT INTO HRMS_SITE..RBSUserCompany ( sucUserID, sucEEID,sucCompanyID)
1384SELECT  DISTINCT r.sucUserID, r.suceeid,r.sucCompanyID FROM ##tempRBSUserCompany r
1385JOIN HRMS_SITE..RBSUSERS ON sususerid = r.sucuserid 
1386                  
1387SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' Employee rows were added to RBSUserCompany.'
1388IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1389
1390
1391SELECT u.sucUserCompanyID AS NewUserCompanyID, t.sucUserCompanyID AS OldUserCompanyID
1392INTO ##tempUserCompanyIDMap
1393FROM HRMS_SITE..RBSUserCompany u JOIN ##tempRBSUserCompany t ON u.sucEEID = t.sucEEID ---check this out fix
1394
1395INSERT INTO HRMS_SITE..RBSHistory (sphChanged,sphIsDefault,sphPassword,sphSecPassword,sphUserID)
1396SELECT DISTINCT sphChanged,sphIsDefault,sphPassword,sphSecPassword,sphUserID FROM ##tempRBSHistory
1397SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were added to RBSHistory.'
1398IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1399
1400INSERT INTO HRMS_SITE..RBSUserHistory (ruhAllCompanies,ruhcompanyID,ruhDateStamp,ruhEEID,ruhStatus,ruhUserFullName,ruhUserID,ruhUserName,ruhUserTypeID)
1401SELECT DISTINCT ruhAllCompanies,ruhcompanyID,ruhDateStamp,ruhEEID,ruhStatus,ruhUserFullName,ruhUserID,ruhUserName,ruhUserTypeID FROM ##tempRBSUserHistory
1402SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were added to RBSUserHistory.'
1403IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1404      
1405      
1406INSERT INTO HRMS_SITE..RBSPersons (sprUserID,sprNameFirst,sprnameLast,sprNameMiddle,sprNameSuffix,sprPhoneContact)
1407SELECT DISTINCT sprUserID,sprNameFirst,sprnameLast,sprNameMiddle,sprNameSuffix,sprPhoneContact FROM ##tempRBSPersons
1408WHERE sprUserID not in (SELECT sprUserid FROM HRMS_SITE..RBSPersons with (NOLOCK))
1409SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were added to RBSPersons.'
1410IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1411
1412INSERT INTO HRMS_SITE..RBSUserprefs (uspUserId,uspPrefCode,uspPreferences,uspLanguageCode)
1413SELECT uspUserId,uspPrefCode,uspPreferences,uspLanguageCode FROM ##TempRBSUserprefs
1414SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were added to RBSUserPrefs.'
1415IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1416
1417INSERT INTO HRMS_SITE..RBSPhoneNumbers (phnUserID,phnTypeID,phnPhone,phnExt,phnCountrycode)
1418SELECT DISTINCT phnUserID,phnTypeID,phnPhone,phnExt,phnCountrycode FROM ##TempRBSPhoneNumbers
1419SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were added to RBSPhoneNumbers.'
1420IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1421
1422INSERT INTO HRMS_SITE..RBSUserAddresses (usaCity,usaCountry, usaEmail, usaLine1, usaLine2 
1423      , usaPostalCode, usaSMS, usaState, usaUserID )
1424SELECT DISTINCT usaCity,usaCountry, usaEmail, usaLine1, usaLine2 , usaPostalCode , usaSMS , usaState 
1425      , usaUserID  FROM ##TempRBSUserAddresses
1426SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were added to RBSUserAddresses.'
1427IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1428
1429INSERT INTO HRMS_SITE..RBSUserQuestions (UserID,QuestionID,Answer,LanguageCode)
1430SELECT DISTINCT UserID,QuestionID,Answer,LanguageCode FROM ##TempRBSUserquestions
1431SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were added to RBSUserQuestions.'
1432IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1433
1434INSERT INTO HRMS_SITE..RBSAdminRoles (rarRoleID,rarUserID)
1435SELECT DISTINCT rarRoleID,rarUserID FROM ##TempRBSAdminRoles
1436SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were added to RBSAdminRoles.'
1437IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1438
1439IF @debug = 'Y' PRINT ''
1440/**Update secvalues **/
1441
1442UPDATE ##TEMPRBSCompanyVALUES
1443       SET SVLCompanyID =  @localcontextID
1444
1445UPDATE ##TEMPSITEEVENT
1446       SET StvContextID =  @localcontextID
1447
1448DELETE  HRMS_SITE..RBSCompanyValues
1449WHERE  svlcompanyID = @localcontextID
1450SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' Duplicate rows from RBSCompanyValues'
1451IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT 
1452
1453INSERT HRMS_SITE..RBSCompanyValues(svlCode,svlCompanyID,svlValue)
1454SELECT svlCode,svlCompanyID,svlValue FROM ##TEMPRBSCompanyVALUES
1455SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were added to RBScompanyValues.'
1456IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1457
1458DELETE  HRMS_SITE..SITEEVENT
1459WHERE  stvContextID = @localcontextID
1460SET @statement = 'Deleted ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + ' Duplicate rows from SiteEvent'
1461IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1462
1463INSERT HRMS_SITE..SITEEVENT(stvCompletedDate,stvPaygroup,stvPercontrol,stvProcessEventCode,
1464stvcontextID,stvScheduledDate,stvStepSet)
1465SELECT stvCompletedDate,stvPaygroup,stvPercontrol,stvProcessEventCode,stvcontextID,
1466stvScheduledDate,stvStepSet FROM ##TEMPSITEEVENT
1467SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were added to SiteEvent.'
1468IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1469
1470
1471--Now lets update the userid int the Company Tables.
1472--Emppers,RbsuserRoles,RBSExceptions,RBSQualifiers,RBSQualList
1473PRINT ''
1474DECLARE @companyserver sysname
1475DECLARE @CompanyServerString varchar(400)
1476SELECT @companyserver = sdbservername FROM HRMS_SITE..RBSDBs WHERE sdbDBName = @database
1477SET @statement = 'Building Connection for the ' + UPPER(@database) + ' database, on the ' + UPPER(@companyserver) + ' company server...'
1478IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1479--setup remote connection string
1480      SET @sourceserverstring = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source=' 
1481            + @sourceserver + ';User ID=' 
1482            + @username + ';Password=' + @Rpassword + ''').'
1483--setup local company server string
1484IF @companyserver = @@servername
1485      SET @Companyserverstring = ''
1486ELSE
1487SET @Companyserverstring = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source=' 
1488            + @Companyserver + ';User ID=' 
1489            + @username + ';Password=' + @Lpassword + ''').'
1490            
1491RAISERROR( @Companyserverstring,0,1) WITH NOWAIT
1492
1493
1494
1495--Setup IDMap on Company Server
1496SET @statement = 
1497'IF EXISTS(SELECT * FROM ' + @Companyserverstring + 'UTILITY.dbo.SYSOBJECTS WHERE NAME = ''UserIDMap'')'
1498+ ' DELETE ' + @Companyserverstring  
1499+ 'UTILITY.dbo.USERIDMAP WHERE [DATABASE] = ''' + @database + ''''
1500EXEC(@statement)
1501SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were Deleted to USERIDMAP.'
1502RAISERROR( @statement,0,1) WITH NOWAIT
1503
1504SET @statement = 
1505'INSERT ' + @Companyserverstring   
1506+ 'UTILITY.dbo.USERIDMAP ([DATABASE],OldUserID,NewUserID) SELECT ''' + @DATABASE + ''' as ''Database'',OldUserID,NewUserID FROM ##tempUserIDMap' 
1507EXEC(@statement)
1508SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were inserted in USERIDMAP.'
1509RAISERROR( @statement,0,1) WITH NOWAIT
1510
1511
1512SET @statement = 
1513'IF EXISTS(SELECT * FROM ' + @Companyserverstring + 'UTILITY.dbo.SYSOBJECTS WHERE NAME = ''UserCompanyIDMap'')'
1514+ ' DELETE ' + @Companyserverstring  
1515+ 'UTILITY.dbo.USERCOMPANYIDMAP'
1516EXEC(@statement)
1517SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were Deleted to USERCOMPANYIDMAP.'
1518RAISERROR( @statement,0,1) WITH NOWAIT
1519
1520
1521SET @statement = 
1522'INSERT ' + @Companyserverstring   
1523+ 'UTILITY.dbo.USERCOMPANYIDMAP (OldUserCompanyID,NewUserCompanyID) SELECT OldUserCompanyID,NewUserCompanyID FROM ##tempUserCompanyIDMap' 
1524EXEC(@statement)
1525SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were inserted in USERCOMPANYIDMAP.'
1526RAISERROR( @statement,0,1) WITH NOWAIT
1527
1528--Update RBS tables in company
1529SET @statement = 
1530'EXEC ' + @Companyserverstring + 'UTILITY.dbo.proc_update_RBSTables ' + @database
1531EXEC(@statement)
1532SET @statement = 'Executing update of company RBSTables'
1533RAISERROR( @statement,0,1) WITH NOWAIT
1534
1535SET @statement = 'Updating wkcTables in the ' + UPPER(@database) + ' database, on the ' + UPPER(@companyserver) + ' company server...'
1536IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1537SET @statement = 
1538'EXEC ' + @Companyserverstring + 'UTILITY.dbo.proc_update_wkctables ' + @database
1539EXEC(@statement)
1540RAISERROR( N'Done updating wkc',0,1) WITH NOWAIT
1541
1542SET @statement = 'Updating jmsJob in the ' + UPPER(@database) + ' database, on the ' + UPPER(@companyserver) + ' company server...'
1543IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1544
1545SET @statement = 
1546'EXEC ' + @Companyserverstring + 'UTILITY.dbo.PROC_UPDATE_jmsJOB ' + @database
1547EXEC(@statement)
1548SET @statement = 'UPDATED ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + '  rows in jmsJob'
1549IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
1550
1551IF @debug IN ('Y','F')
1552BEGIN
1553      PRINT ''
1554      PRINT 'Final results:'
1555      PRINT '--------------'
1556      
1557      SELECT @statement = 'There are now ' + CONVERT(VARCHAR(10),COUNT(u.susUserID)) + ' WEB USERS for ' + UPPER(@database) + '.'
1558      FROM HRMS_SITE..RBSUserCompany JOIN HRMS_SITE..RBSUsers u ON sucUserID = susUserID
1559      WHERE sucCompanyID = @localContextID
1560      RAISERROR( @statement,0,1) WITH NOWAIT
1561      
1562      SELECT @statement = CONVERT(VARCHAR(10),COUNT(susUserID)) 
1563            +  ' of them are CONVERTED SITEADMIN TO COMPANY ADMIN accounts.'
1564      FROM HRMS_SITE..RBSUserCompany 
1565      INNER JOIN HRMS_SITE..RBSUsers      ON sucUserID = susUserID
1566      WHERE sucCompanyID = @localContextID AND susUserTypeID > 1
1567      AND susallcompanies = 1
1568      RAISERROR( @statement,0,1) WITH NOWAIT
1569      
1570      SELECT @statement = CONVERT(VARCHAR(10),COUNT(susUserID)) 
1571            +  ' of them are EMPLOYEE accounts.'
1572      FROM HRMS_SITE..RBSUserCompany 
1573      INNER JOIN HRMS_SITE..RBSUsers      ON sucUserID = susUserID
1574      WHERE sucCompanyID = @localContextID AND susUserTypeID = 2
1575      RAISERROR( @statement,0,1) WITH NOWAIT
1576      
1577      SELECT @statement = 'There are now ' + CONVERT(VARCHAR(10),COUNT(p.sprUserID)) + ' person records for ' + UPPER(@database) + '.'
1578      FROM HRMS_SITE..RBSPersons p INNER JOIN HRMS_SITE..RBSUsers ON susUserID = sprUserID
1579      INNER JOIN HRMS_SITE..RBSUserCompany ON sucUserID = susUserID
1580      WHERE sucCompanyID = @localContextID
1581      RAISERROR( @statement,0,1) WITH NOWAIT
1582      
1583      SELECT @statement = 'There are now ' + CONVERT(VARCHAR(10),COUNT(h.sphUserID)) + ' history records for ' + UPPER(@database) + '.'
1584      FROM HRMS_SITE..RBSHistory h INNER JOIN HRMS_SITE..RBSUserCompany ON sphUserID = sucUserID
1585      WHERE sucCompanyID = @localContextID
1586      RAISERROR( @statement,0,1) WITH NOWAIT
1587
1588      SELECT @statement = 'There are now ' + CONVERT(VARCHAR(10),COUNT(h.ruhUserID)) + ' User history records for ' + UPPER(@database) + '.'
1589      FROM HRMS_SITE..RBSUserHistory h INNER JOIN HRMS_SITE..RBSUserCompany ON ruhUserID = sucUserID
1590      WHERE sucCompanyID = @localContextID
1591      RAISERROR( @statement,0,1) WITH NOWAIT
1592      
1593      SELECT @statement = 'There are now ' + CONVERT(VARCHAR(10),COUNT(s.sucCompanyID)) + ' User/Company records for ' + UPPER(@database) + '.'
1594      FROM HRMS_SITE..RBSUserCompany s
1595      WHERE sucCompanyID = @localContextID
1596      RAISERROR( @statement,0,1) WITH NOWAIT
1597
1598EXEC ('USE HRMS_SITE
1599IF EXISTS(SELECT * FROM SYSCONSTRAINTS WHERE OBJECT_NAME(CONSTID) = ''FK_SSOUsers_UserName'')
1600ALTER TABLE [SSOUsers] CHECK CONSTRAINT [FK_SSOUsers_UserName]')
1601
1602      IF @debug = 'Y'
1603      BEGIN
1604            PRINT ''
1605            PRINT 'Temp tables left behind:'
1606            PRINT '------------------------'
1607            PRINT '##tempRBSUserCompany'
1608            PRINT '##tempRBSUsers'
1609            PRINT '##tempRBSHistory'
1610            PRINT '##tempRBSUserHistory'
1611            PRINT '##tempSecPersons'
1612            PRINT '##tempUserIDMap'
1613            PRINT '##tempUserCompanyIDMap'
1614        PRINT '##tempAdminRoles'
1615        PRINT '##tempSiteEvent' 
1616        PRINT '##tempUserprefs'
1617        PRINT '##tempPhoneNumbers' 
1618        PRINT '##tempUserQuestions'
1619        PRINT '##tempUserAddresses'
1620        PRINT '##tempCompanyValues'
1621
1622      END
1623END
1624
1625IF @debug != 'Y'
1626BEGIN
1627      IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserCompany') > 0
1628            DROP TABLE ##tempRBSUserCompany
1629      IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUsers') > 0
1630            DROP TABLE ##tempRBSUsers
1631      IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSHistory') > 0
1632            DROP TABLE ##tempRBSHistory
1633      IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserHistory') > 0
1634            DROP TABLE ##tempRBSUserHistory
1635      IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSPersons') > 0
1636            DROP TABLE ##tempRBSPersons
1637      
1638    IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempUserIDMap') > 0
1639           DROP TABLE ##tempUserIDMap
1640    IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempUserCompanyIDMap') > 0
1641            DROP TABLE ##tempUserCompanyIDMap
1642    IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSCompanyValues') > 0
1643            DROP TABLE ##tempRBSCompanyValues
1644    IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserQuestions') > 0
1645            DROP TABLE ##tempRBSUserQuestions
1646    IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserPrefs') > 0
1647            DROP TABLE ##tempRBSUserPrefs
1648    IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSPhoneNumbers') > 0
1649            DROP TABLE ##tempRBSPhoneNumbers
1650    IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempRBSUserAddresses') > 0
1651            DROP TABLE ##tempRBSUserAddresses
1652    IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempSiteEvent') > 0
1653            DROP TABLE ##tempSiteEvent
1654    IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##tempAdminRoles') > 0
1655            DROP TABLE ##tempAdminRoles
1656
1657--EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER  all"
1658END
1659GO
1660
1661
1662
1663/*********************************************************************************************************************************************************************************************/
1664
1665
1666
1667/****** Object:  StoredProcedure [dbo].[proc_Setup_EXTCustomerdbs]    Script Date: 08/20/2014 14:53:30 ******/
1668USE [UTILITY]
1669GO
1670
1671IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_Setup_EXTCustomerdbs]') AND type in (N'P', N'PC'))
1672DROP PROCEDURE [dbo].[proc_Setup_EXTCustomerdbs]
1673GO
1674
1675SET ANSI_NULLS ON
1676GO
1677
1678SET QUOTED_IDENTIFIER ON
1679GO
1680
1681CREATE PROCEDURE [dbo].[proc_Setup_EXTCustomerdbs]
1682@p_dbname varchar(50),
1683@p_Sitedbpath varchar(200), --path to the site databases
1684@p_Companydbpath varchar(200), --path to the  company databases
1685@p_dbidentifier varchar(5) = '1000',--number such as '1000'
1686@p_backuptype char(3) = 'bak',
1687@p_dbserver varchar(50),
1688@p_buildno varchar(5),
1689@p_Sitedbfull varchar(200) = 'full.sqb', --path to the site databases
1690@p_Sitedbdiff varchar(200) = 'diff.sqb', --path to the site databases
1691@p_Companydbfull varchar(200) = 'full.sqb', --path to the  company databases
1692@p_Companydbdiff varchar(200) = 'diff.sqb', --path to the  company databases
1693@p_Dispatchdbfull varchar(200) = 'full.sqb', 
1694@p_Dispatchdbdiff varchar(200) = 'diff.sqb', 
1695@p_GlobalSitedbfull varchar(200) = 'full.sqb', 
1696@p_GlobalSitedbdiff varchar(200) = 'diff.sqb', 
1697@p_ChkhrSitedbfull varchar(200) = 'nofull', 
1698@p_ChkhrSitedbdiff varchar(200) = 'nodiff', 
1699@p_GlobalCompanydbfull varchar(200) = 'full.sqb', 
1700@p_GlobalCompanydbdiff varchar(200) = 'diff.sqb', 
1701@p_Datadictdbfull varchar(200) = 'full.sqb', 
1702@p_Datadictdbdiff varchar(200) = 'diff.sqb', 
1703@p_Systemdbfull varchar(200) = 'full.sqb', 
1704@p_Systemdbdiff varchar(200) = 'diff.sqb', 
1705@p_Customdbfull varchar(200) = 'full.sqb', 
1706@p_Customdbdiff varchar(200) = 'diff.sqb', 
1707@p_ChkhrCompanydbfull varchar(200) = 'nofull', 
1708@p_ChkhrCompanydbdiff varchar(200) = 'nodiff', 
1709@p_Plansourcedbfull varchar(200) = 'nofull', 
1710@p_Plansourcedbdiff varchar(200) = 'nodiff' ,
1711@p_MetadataSitedbfull varchar(200) = 'nofull',
1712@p_MetadataSitedbdiff varchar(200) = 'nodiff' ,
1713@p_MetadataCompanydbfull varchar(200) = 'nofull',
1714@p_MetadataCompanydbdiff varchar(200) = 'nodiff'
1715
1716AS
1717
1718/*
1719EXAMPLE values
1720SET @p_dbname = 'ULTIPRO_SB11'
1721SET @p_Sitedbpath = '\\dbrefreshsite64\d$\Customer\Multiplefile\' --path to the site databases
1722SET @p_Companydbpath = '\\dbrefreshsql64\d$\Customer\Multiplefile\' --path to the  company databases
1723SET @p_dbidentifier  = '1000'--number such as '1000'
1724SET @p_backuptype  = 'SQB'
1725SET @p_dbserver = 'DBrefreshsql64'
1726SET @p_buildno = '14356'
1727SET @p_Sitedbfull  = 'full.sqb' --path to the site databases
1728SET @p_Sitedbdiff  = 'diff.sqb' --path to the site databases
1729SET @p_Companydbfull  = 'full.sqb' --path to the  company databases
1730SET @p_Companydbdiff  = 'diff.sqb' --path to the  company databases */
1731
1732------------------------------------------------------------------
1733-- 		SCRIPT TO Populate Customer DBs info into DbBackupExtLocs table from any UNC PATH		--
1734------------------------------------------------------------------
1735-- Kevin Maxwell   12/5/2012
1736------------------------------------------------------------------
1737IF OBJECT_ID('tempdb..##CUSTbak_db') > 0
1738drop table ##CUSTbak_db
1739IF OBJECT_ID('tempdb..##tmp') > 0
1740drop table ##tmp
1741IF OBJECT_ID('tempdb..##HeaderTemp') > 0
1742drop table ##HeaderTemp
1743IF OBJECT_ID('tempdb..##Env_db') > 0
1744drop table ##Env_db
1745
1746IF(SELECT SUBSTRING(@p_Sitedbpath,LEN(@p_Sitedbpath),1)) <> '\'
1747SET  @p_Sitedbpath = @p_Sitedbpath + '\'
1748IF(SELECT SUBSTRING(@p_Companydbpath,LEN(@p_Companydbpath),1)) <> '\'
1749SET  @p_Companydbpath = @p_Companydbpath + '\'
1750
1751SET NOCOUNT ON
1752
1753declare @CUSTppath nvarchar(500)
1754DECLARE @filepath varchar(2000)
1755declare @location varchar(15)
1756declare @identifier varchar(5)
1757declare @dbname varchar(50)
1758declare @hdbname varchar(50)
1759declare @user sysname 
1760declare	@password VARCHAR(255)
1761declare @statement VARCHAR(8000)
1762declare @servercontext varchar(8000)
1763declare @code varbinary(256)
1764declare	@pass varchar(50)
1765declare @passCurrent varchar(50)
1766declare @codeACS varbinary(256)
1767declare	@passACS varchar(50)
1768
1769SET @code = 0x010000006FACDDFA5C63D41016D52845EA0ABB74ED9B247E49A63B9FC8E7BC1152DC7EDD3CA3A7900BD993B8
1770SET @pass = cast(DECRYPTBYPASSPHRASE('CTE',@code) as varchar)
1771SET @codeACS = 0x01000000F9BD39D5234140B0755FB27BB0FA81D0567BB5CB4E06C1F9D6235F1378BF5A2D
1772SET @passACS = cast(DECRYPTBYPASSPHRASE('CTE',@codeACS) as varchar)
1773SET @passCurrent = @pass
1774
1775    IF @p_Sitedbpath IS NULL
1776	   SET @p_Sitedbpath = @p_Companydbpath
1777	IF @p_Companydbpath IS NULL
1778	   SET @p_Companydbpath = @p_Sitedbpath
1779	      
1780	SET @identifier = ISNULL(@p_dbidentifier,'1000')
1781	SET @user = 'dev'
1782	SET @password = 'usg'
1783	
1784DELETE UTILITY..DbBackupExtLocs WHERE GroupNo = @identifier
1785DELETE UTILITY..DbFileExtGroups WHERE GroupNo = @identifier
1786
1787CREATE TABLE ##Env_db (dbname varchar(50), Location varchar(10))
1788INSERT ##Env_db
1789 VALUES ('HRMS_SITE','SITE'),('HRMS_DISPATCH','SITE'),('HRMS_GLOBALDATA','SITE'),('CHKHR_OASIS','SITE'),('CHKHR_OASIS','COMPANY'),
1790        ('HRMS_GLOBALDATA','COMPANY'),('ULTIPRO_DATADICT','COMPANY'),
1791        ('ULTIPRO_SYSTEM','COMPANY'),('ULTIPRO_CUSTOM','COMPANY'),(@p_dbname,'COMPANY'),('CUSTOM_PLANSOURCE','COMPANY'),
1792		('METADATA','SITE'), ('METADATA','COMPANY')
1793        
1794  --IF @p_Sitedbpath <> @p_Companydbpath
1795  -- UPDATE ##Env_db
1796  --    SET  Location = 'SITE'       
1797
1798CREATE TABLE ##CUSTbak_db([PFILE]sql_variant,bakpath varchar(200),dbname varchar(50),Stype varchar(50))
1799  IF @p_backuptype = 'bak'
1800    BEGIN
1801	  INSERT ##CUSTbak_db([PFILE]) EXECUTE('xp_cmdshell ' + '''' + 'dir /B ' + @p_Sitedbpath + '*.bak /O:-D' + '''')
1802	  UPDATE ##CUSTbak_db SET bakpath = @p_Sitedbpath, Stype = 'site' Where cast(PFILE as nvarchar(500)) like '%HRMS_SITE%'
1803	   UPDATE ##CUSTbak_db SET bakpath = @p_Sitedbpath, Stype = 'company' Where cast(PFILE as nvarchar(500))  NOT like '%HRMS_SITE%'
1804	   IF @p_Sitedbpath <> @p_Companydbpath
1805	     BEGIN
1806	       INSERT ##CUSTbak_db ([PFILE]) EXECUTE('xp_cmdshell ' + '''' + 'dir /B ' + @p_Companydbpath + '*.bak /O:-D' + '''')
1807	       UPDATE ##CUSTbak_db SET bakpath = @p_Companydbpath ,Stype = 'company' FROM  ##CUSTbak_db WHERE [Stype] IS NULL
1808	     END
1809	END  
1810	
1811  IF @p_backuptype = 'SQB'
1812    BEGIN
1813    DECLARE cPATH cursor for
1814    SELECT DBNAME , LOCATION FROM ##Env_db ORDER BY Location DESC
1815    OPEN cPATH
1816    FETCH NEXT FROM cPATH INTO @dbname ,@location 
1817	WHILE @@fetch_status = 0
1818	BEGIN
1819		 
1820	  print @filepath
1821	  IF @location = 'SITE'
1822	    BEGIN
1823	      SET @filepath = @p_Sitedbpath + @dbname + '\'
1824	      INSERT ##CUSTbak_db([PFILE]) EXECUTE('xp_cmdshell ' + '''' + 'dir /B ' + @filepath + '*.SQB /O:-D' + '''') 
1825          UPDATE ##CUSTbak_db SET bakpath = @filepath , Stype = 'SITE',dbname = @dbname 
1826           WHERE cast(PFILE as nvarchar(500)) like '%' + @dbname + '%'
1827             AND dbname is NULL
1828        END   
1829	  ELSE 
1830	    BEGIN 
1831	      SET @filepath = @p_Companydbpath + @dbname + '\' 
1832	      INSERT ##CUSTbak_db([PFILE]) EXECUTE('xp_cmdshell ' + '''' + 'dir /B ' + @filepath + '*.SQB /O:-D' + '''')
1833	      UPDATE ##CUSTbak_db SET bakpath = @filepath, Stype = 'COMPANY',dbname = @dbname 
1834	       WHERE cast(PFILE as nvarchar(500)) like '%' + @dbname + '%'
1835	        AND dbname is NULL
1836	    END  
1837	--SELECT * FROM ##CUSTbak_db WHERE PFILE IS NOT NULL
1838    FETCH NEXT FROM cPATH INTO @dbname ,@location 
1839    END
1840    close cPATH
1841    deallocate cPATH
1842    END
1843    
1844 CREATE TABLE ##tmp
1845(ULocation nvarchar(10)
1846 ,UDatabase nvarchar(260)
1847,LogicalName nvarchar(128)
1848,PhysicalName varchar(260)
1849,[Type] char(1)
1850,FileGroupName nvarchar(128)
1851,Size numeric(20,0)
1852,MaxSize numeric(20,0),
1853FileId tinyint,
1854CreateLSN sql_variant,
1855DropLSN numeric(25, 0),
1856UniqueID sql_variant,
1857ReadOnlyLSN sql_variant,
1858ReadWriteLSN sql_variant,
1859BackupSizeInBytes bigint,
1860SourceBlockSize int,
1861FileGroupId int,
1862LogGroupGUID sql_variant,
1863DifferentialBaseLSN sql_variant,
1864DifferentialBaseGUID sql_variant,
1865IsReadOnly bit,
1866IsPresent bit,
1867TDEThumbprint sql_variant
1868
1869)
1870CREATE table ##HeaderTemp
1871        (BackupName  nvarchar(128), 
1872        BackupDescription  nvarchar(255) ,
1873        BackupType  smallint ,
1874        ExpirationDate  datetime ,
1875        Compressed  bit ,
1876        Position  smallint ,
1877        DeviceType  tinyint ,
1878        UserName  nvarchar(128) ,
1879        ServerName  nvarchar(128) ,
1880        DatabaseName  nvarchar(128) ,
1881        DatabaseVersion  int ,
1882        DatabaseCreationDate  datetime ,
1883        BackupSize  numeric(20,0) ,
1884        FirstLSN  numeric(25,0) ,
1885        LastLSN  numeric(25,0) ,
1886        CheckpointLSN  numeric(25,0) ,
1887        DatabaseBackupLSN  numeric(25,0) ,
1888        BackupStartDate  datetime ,
1889        BackupFinishDate  datetime ,
1890        SortOrder  smallint ,
1891        CodePage  smallint ,
1892        UnicodeLocaleId  int ,
1893        UnicodeComparisonStyle  int ,
1894        CompatibilityLevel  tinyint ,
1895        SoftwareVendorId  int ,
1896        SoftwareVersionMajor  int ,
1897        SoftwareVersionMinor  int ,
1898        SoftwareVersionBuild  int ,
1899        MachineName  nvarchar(128) ,
1900        Flags  int ,
1901        BindingID  uniqueidentifier ,
1902        RecoveryForkID  uniqueidentifier ,
1903        Collation  nvarchar(128) ,
1904        FamilyGUID  uniqueidentifier ,
1905        HasBulkLoggedData  bit ,
1906        IsSnapshot  bit ,
1907        IsReadOnly  bit ,
1908        IsSingleUser  bit ,
1909        HasBackupChecksums  bit ,
1910        IsDamaged  bit ,
1911        BeginsLogChain  bit ,
1912        HasIncompleteMetaData  bit ,
1913        IsForceOffline  bit ,
1914        IsCopyOnly  bit ,
1915        FirstRecoveryForkID  uniqueidentifier ,
1916        ForkPointLSN  numeric(25,0) NULL,
1917        RecoveryModel  nvarchar(60) ,
1918        DifferentialBaseLSN  numeric(25,0) NULL,
1919        DifferentialBaseGUID  uniqueidentifier ,
1920        BackupTypeDescription  nvarchar(60) ,
1921        BackupSetGUID  uniqueidentifier NULL,
1922        CommpressedBackupSize int)
1923    
1924 -- setup the company linkserver
1925	IF @p_dbserver = @@servername
1926	BEGIN
1927		SET @statement = @p_dbserver + ' is the current server, no linking involved.'
1928		PRINT @statement
1929	END
1930	ELSE 
1931	BEGIN
1932		SET @statement = 'Linking server ' + @p_dbserver + '...'
1933		EXEC('IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname = ''' + @p_dbserver + ''')' + 
1934		     ' EXEC master.dbo.sp_dropserver @server= ''' + @p_dbserver + ''', @droplogins=''droplogins''')
1935
1936		PRINT @statement
1937		SET XACT_ABORT OFF
1938		SET @statement = 'master.dbo.sp_addlinkedserver @server = ''' + @p_dbserver + ''', @srvproduct = ''SQL Server'''
1939		--PRINT @statement
1940		EXEC (@statement)
1941		IF @@ERROR != 0
1942		BEGIN
1943			PRINT ''
1944			PRINT @statement
1945			SET @statement = 'Something went wrong creating the link to server: ' + @p_dbserver
1946			PRINT @statement
1947			GOTO ABORT
1948		END
1949		SET XACT_ABORT ON
1950		SELECT @statement = 'master.dbo.sp_addlinkedsrvlogin @rmtsrvname = ''' + @p_dbserver + ''', @useself = ''false'', @rmtuser = ''' 
1951	   	+ @user + ''', @rmtpassword = ' + 
1952		(CASE 
1953		 WHEN @password IS NULL THEN 'null'
1954		 ELSE '''' + @password + ''''
1955		 END -- CASE
1956		)
1957	--	PRINT @statement
1958		EXEC (@statement)
1959	
1960		SELECT @@servername
1961		IF @p_dbserver = @@servername
1962			SET @servercontext = ''
1963		ELSE
1964			SET @servercontext = '[' + @p_dbserver + '].'
1965		
1966			  /*Clean up tables on remote servver */
1967		EXEC('DELETE ' + @servercontext + 'UTILITY.dbo.DbBackupExtLocs WHERE GroupNo = ''' + @identifier + '''
1968	          DELETE ' + @servercontext + 'UTILITY.dbo.DbFileExtGroups WHERE GroupNo = ''' + @identifier + '''')
1969	END	
1970		
1971	DECLARE @cmd varchar(max)
1972	DECLARE @cmdencrypt varchar(max)
1973	DECLARE @exitcode int
1974	DECLARE @sqlerrorcode int 
1975	DECLARE @pfile nvarchar(500)
1976	DECLARE @bakp nvarchar(200)
1977	DECLARE @stype nvarchar(50)
1978	DECLARE cFILE cursor for
1979	
1980	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
1981	 WHERE PFILE is not null and Stype = 'site' and cast(PFILE as nvarchar(500)) like '%' + @p_Sitedbfull + '%'
1982	 UNION ALL
1983	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
1984	 WHERE PFILE is not null and Stype = 'site' and cast(PFILE as nvarchar(500)) like '%' + @p_Sitedbdiff + '%'
1985	 UNION ALL 
1986	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
1987	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_Companydbfull + '%'
1988	 UNION ALL 
1989	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
1990	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_Companydbdiff + '%'
1991	 UNION ALL
1992 	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
1993	 WHERE PFILE is not null and Stype = 'site' and cast(PFILE as nvarchar(500)) like '%' + @p_Dispatchdbfull + '%'
1994	 UNION ALL
1995	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
1996	 WHERE PFILE is not null and Stype = 'site' and cast(PFILE as nvarchar(500)) like '%' + @p_DIspatchdbdiff + '%'
1997	 UNION ALL  
1998	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
1999	 WHERE PFILE is not null and Stype = 'site' and cast(PFILE as nvarchar(500)) like '%' + @p_GlobalSitedbfull + '%'
2000	 UNION ALL
2001	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2002	 WHERE PFILE is not null and Stype = 'site' and cast(PFILE as nvarchar(500)) like '%' + @p_GlobalSitedbdiff + '%'
2003	 UNION ALL 
2004	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2005	 WHERE PFILE is not null and Stype = 'site' and cast(PFILE as nvarchar(500)) like '%' + @p_ChkhrSitedbfull + '%'
2006	 UNION ALL
2007	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2008	 WHERE PFILE is not null and Stype = 'site' and cast(PFILE as nvarchar(500)) like '%' + @p_ChkhrSitedbdiff + '%'
2009	 UNION ALL 
2010	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2011	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_GlobalCompanydbfull + '%'
2012	 UNION ALL
2013	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2014	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_GlobalCompanydbdiff + '%'
2015	 UNION ALL
2016	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2017	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_Datadictdbfull + '%'
2018	 UNION ALL
2019	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2020	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_Datadictdbdiff + '%'
2021	 UNION ALL
2022	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2023	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_Systemdbfull + '%'
2024	 UNION ALL
2025	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2026	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_Systemdbdiff + '%'
2027	 UNION ALL
2028	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2029	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_Customdbfull + '%'
2030	 UNION ALL
2031	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2032	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_Customdbdiff + '%'
2033	 UNION ALL
2034	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2035	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_ChkhrCompanydbfull + '%'
2036	 UNION ALL
2037	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2038	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_ChkhrCompanydbdiff + '%'
2039	 UNION ALL
2040	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2041	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_Plansourcedbfull + '%'
2042	 UNION ALL
2043	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2044	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_Plansourcedbdiff + '%'
2045	 UNION ALL 
2046	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2047	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_MetadataCompanydbfull + '%'
2048	 UNION ALL 
2049	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2050	 WHERE PFILE is not null and Stype = 'company' and cast(PFILE as nvarchar(500)) like '%' + @p_MetadataCompanydbdiff + '%'
2051	 UNION ALL 
2052	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2053	 WHERE PFILE is not null and Stype = 'site' and cast(PFILE as nvarchar(500)) like '%' + @p_MetadataSitedbfull + '%'
2054	 UNION ALL 
2055	SELECT top(1) cast(PFILE as nvarchar(500)),bakpath,dbname,Stype FROM ##CUSTbak_db
2056	 WHERE PFILE is not null and Stype = 'site' and cast(PFILE as nvarchar(500)) like '%' + @p_MetadataSitedbdiff + '%'
2057	OPEN cFile
2058	FETCH NEXT FROM cFILE INTO @pfile ,@bakp, @hdbname, @stype
2059	WHILE @@fetch_status = 0
2060	BEGIN
2061	 
2062	SET @CUSTppath = @bakp + @pfile --File path @@@@@@
2063    
2064    	IF @p_backuptype = 'bak'
2065      	BEGIN
2066       	    print 'restore filelistonly from disk = ''' + @CUSTppath + ''''
2067	    INSERT ##tmp(LogicalName,PhysicalName,[Type],FileGroupName ,Size ,MaxSize ,FileId ,CreateLSN,
2068                      DropLSN,UniqueID,ReadOnlyLSN ,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,
2069                      FileGroupId,LogGroupGUID ,DifferentialBaseLSN ,DifferentialBaseGUID,IsReadOnly,IsPresent,TDEThumbprint)
2070	      
2071	    EXEC ('restore filelistonly from disk = ''' + @CUSTppath + '''')
2072	     
2073	    /*restore header for the database name setup */
2074            INSERT ##HeaderTemp
2075	    EXEC ('restore Headeronly from disk = ''' + @CUSTppath + '''')
2076            SELECT @hdbname = databasename from ##HeaderTemp
2077      	END   
2078	IF @p_backuptype = 'SQB'
2079	BEGIN
2080		IF CHARINDEX('N1D111VP01',@CUSTppath )>0
2081			SET @passCurrent = @passACS
2082		ELSE
2083			SET @passCurrent = @pass
2084			
2085		SET @cmdencrypt = '-SQL "RESTORE FILELISTONLY FROM DISK = ''' + @CUSTppath + ''' WITH PASSWORD =''' + @passCurrent + '''"'
2086		SET @cmd = '-SQL "RESTORE FILELISTONLY FROM DISK = ''' + @CUSTppath + '''"'
2087	    --SET @cmd = 'master.dbo.sqlbackup ' + '''-SQL "RESTORE FILELISTONLY FROM DISK = ''''' + @CUSTppath + '''''"''' 
2088		--Print 'Test running encrypted RESTORE FILELISTONLY command: '
2089		BEGIN TRY
2090		  Print 'Running encrypted RESTORE FILELISTONLY command to extract info: '
2091		  Print @CUSTppath
2092			INSERT ##tmp(LogicalName,PhysicalName,[Type],FileGroupName ,Size ,MaxSize ,FileId ,CreateLSN,
2093			  DropLSN,UniqueID,ReadOnlyLSN ,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,
2094			  FileGroupId,LogGroupGUID ,DifferentialBaseLSN ,DifferentialBaseGUID,IsReadOnly,IsPresent)
2095			EXECUTE master.dbo.sqlbackup @cmdencrypt
2096		END TRY
2097		BEGIN CATCH
2098			BEGIN TRY
2099			  Print 'Running encrypted RESTORE FILELISTONLY command to extract info: '
2100			  Print @CUSTppath
2101				INSERT ##tmp(LogicalName,PhysicalName,[Type],FileGroupName ,Size ,MaxSize ,FileId ,CreateLSN,
2102				  DropLSN,UniqueID,ReadOnlyLSN ,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,
2103				  FileGroupId,LogGroupGUID ,DifferentialBaseLSN ,DifferentialBaseGUID,IsReadOnly,IsPresent)
2104				EXECUTE master.dbo.sqlbackup @cmdencrypt
2105			END TRY
2106			BEGIN CATCH
2107				Print 'Running non-encrypted RESTORE FILELISTONLY command to extract info: '
2108				Print @CUSTppath
2109				Print @cmd
2110				INSERT ##tmp(LogicalName,PhysicalName,[Type],FileGroupName ,Size ,MaxSize ,FileId ,CreateLSN,
2111				  DropLSN,UniqueID,ReadOnlyLSN ,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,
2112				  FileGroupId,LogGroupGUID ,DifferentialBaseLSN ,DifferentialBaseGUID,IsReadOnly,IsPresent)
2113				EXECUTE master.dbo.sqlbackup @cmd
2114			END CATCH
2115	  	END CATCH --ELSE END
2116	  
2117		/* Add the Database name and location */	
2118		UPDATE ##tmp 
2119		SET UDatabase = @hdbname,
2120		    ULocation = @stype
2121		WHERE CHARINDEX(@hdbname,@CUSTppath )>0 
2122		AND UDatabase IS NULL
2123
2124		/*Insert into the DbBackupExtLocs table */		
2125		INSERT UTILITY..DbBackupExtLocs(Server,backuploc,GroupNo, dbname,dataname, logname, HasMultipleFileGroups, BuildNo, DataPath, Logpath )
2126		SELECT DISTINCT TOP (1) CASE @stype
2127		                        WHEN 'SITE' THEN UTILITY.dbo.fn_TeamSiteServer()
2128		                        ELSE @p_dbserver END AS Server,
2129		     @CUSTppath,@identifier,@hdbname, Logicalname,Logicalname,0,@p_buildno,NULL,NULL
2130		FROM ##tmp 
2131		WHERE UDatabase = @hdbname 
2132		    AND Type = 'D'
2133	  
2134	  	UPDATE UTILITY..DbBackupExtLocs
2135		SET logname = logicalName
2136		FROM ##tmp 
2137		WHERE Udatabase = @hdbname 
2138		    AND dbname = @hdbname 
2139		    AND Type = 'L'
2140
2141		/* Update if Multiple file groups */	 	  
2142		UPDATE UTILITY..DbBackupExtLocs
2143		SET HasMultipleFileGroups = 1
2144		WHERE dbname IN (
2145			select UDatabase from 
2146			(select distinct ULocation, UDatabase, LogicalName, PhysicalName from ##tmp) as tmp2  
2147			group by UDatabase,ULocation
2148			having COUNT(*) > 2)
2149		
2150		/* Update the server restore paths*/		
2151		UPDATE UTILITY..DbBackupExtLocs
2152		SET DataPath = TeamDataPath,
2153		    Logpath =  TeamLogPath
2154		FROM UTILITY..TeamEnv
2155		WHERE TeamServer = @@servername
2156		    AND GroupNo = @identifier 
2157		    
2158		/*Save the Multiple file group data*/		  
2159		IF EXISTS(SELECT * FROM UTILITY..DbBackupExtLocs WHERE HasMultipleFileGroups = 1 AND dbname = @hdbname)
2160		   IF NOT EXISTS( SELECT * FROM UTILITY..DBFileExtGroups WHERE dbname = @hdbname)
2161           		INSERT UTILITY..DBFileExtGroups (GroupNo,dbname, LogicalFileName, PhysicalFileName)
2162           		SELECT distinct @identifier,UDatabase,LogicalName,SUBSTRING(Physicalname,LEN(Physicalname)- CHARINDEX('\',REVERSE(Physicalname)) + 2,LEN(Physicalname))
2163             		FROM ##tmp
2164            		WHERE UDATABASE = @hdbname
2165	-----------------------------------------------------------------------------------------------
2166		IF @p_dbserver <> @@servername
2167		BEGIN
2168	 
2169		  /*Insert into the DbBackupExtLocs table */		
2170	 	  EXEC('INSERT ' + @servercontext + 'UTILITY.dbo.DbBackupExtLocs(Server,backuploc,GroupNo, dbname, dataname, logname, HasMultipleFileGroups, BuildNo, DataPath, Logpath)
2171		      SELECT  DISTINCT TOP (1) CASE ''' + @stype + '''
2172		        WHEN ''SITE'' THEN UTILITY.dbo.fn_TeamSiteServer()
2173		        ELSE ''' + @p_dbserver + ''' END AS Server,
2174		        ''' + @CUSTppath + ''',''' + @identifier + ''',''' + @hdbname + ''',Logicalname,Logicalname,0,' + @p_buildno + ',NULL,NULL
2175		        FROM ##tmp 
2176		        WHERE UDatabase = ''' + @hdbname + '''
2177		          AND Type = ''D''')
2178	        
2179		 EXEC('UPDATE ' + @servercontext + 'UTILITY.dbo.DbBackupExtLocs
2180		          SET logname = logicalName
2181		         FROM ##tmp 
2182		        WHERE Udatabase = ''' + @hdbname + '''
2183		          AND dbname = ''' + @hdbname + '''
2184		          AND Type = ''L''')       
2185
2186	         /* Update if Multiple file groups */	 	  
2187		 EXEC('UPDATE ' + @servercontext + 'UTILITY.dbo.DbBackupExtLocs
2188			       SET HasMultipleFileGroups = 1
2189			     WHERE dbname IN (
2190			     select UDatabase from 
2191				(select distinct ULocation, UDatabase, LogicalName, PhysicalName from ##tmp) as tmp2   
2192			      group by UDatabase,ULocation
2193			      having COUNT(*) > 2)')
2194		
2195	         /* Update the server restore paths*/		
2196		 EXEC('UPDATE ' + @servercontext + 'UTILITY.dbo.DbBackupExtLocs
2197			        SET DataPath = TeamDataPath,
2198				         Logpath =  TeamLogPath
2199			        FROM ' + @servercontext + 'UTILITY.dbo.TeamEnv
2200			       WHERE TeamServer = ''' + @p_dbserver + '''
2201			      AND GroupNo = ''' + @identifier + '''')
2202		    
2203		/*Save the Multiple file group data*/		  
2204			EXEC('IF EXISTS(SELECT * FROM ' + @servercontext + 'UTILITY.dbo.DbBackupExtLocs WHERE HasMultipleFileGroups = 1 AND dbname = ''' + @hdbname + ''')
2205			       IF NOT EXISTS( SELECT * FROM ' + @servercontext + 'UTILITY.dbo.DBFileExtGroups WHERE dbname = ''' + @hdbname +''')
2206	               INSERT ' + @servercontext + 'UTILITY.dbo.DBFileExtGroups (GroupNo,dbname, LogicalFileName, PhysicalFileName)
2207	               SELECT distinct ''' + @identifier + ''',UDatabase,LogicalName,SUBSTRING(Physicalname,LEN(Physicalname)- CHARINDEX(''\'',REVERSE(Physicalname)) + 2,LEN(Physicalname))
2208	                 FROM ##tmp
2209	                WHERE UDATABASE = ''' + @hdbname + '''')
2210   
2211	      END
2212	  
2213	END
2214	FETCH NEXT FROM cFILE INTO @pfile,@bakp,@hdbname,@stype
2215   	END
2216
2217    	--drop the remote link server
2218  	IF @p_dbserver <> @@servername
2219	  EXEC('IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname = ''' + @p_dbserver + ''')' + 
2220		     ' EXEC master.dbo.sp_dropserver @server= ''' + @p_dbserver + ''', @droplogins=''droplogins''')
2221
2222	CLOSE cFILE
2223	DEALLOCATE cFILE
2224	ABORT:
2225	--select * from ##CUSTbak_db WHERE PFILE is not NULL
2226	
2227	--select * from ##tmp
2228	
2229	select * from UTILITY.dbo.DbBackupExtLocs
2230	
2231	select * from UTILITY.dbo.DBFileExtGroups
2232
2233
2234GO
2235
2236
2237/*********************************************************************************************************************************************************************************************/
2238USE [UTILITY]
2239GO
2240/****** Object:  StoredProcedure [dbo].[proc_sync_Extsite]    Script Date: 11/1/2018 4:49:24 PM ******/
2241SET ANSI_NULLS ON
2242GO
2243SET QUOTED_IDENTIFIER ON
2244GO
2245ALTER PROCEDURE [dbo].[proc_sync_Extsite]
2246AS
2247/*****
2248LAST MODIFIED ON	: 10/28/2018 by Kevin Maxwell
2249*****/
2250IF EXISTS(SELECT * FROM MASTER..SYSDATABASES WHERE NAME = 'HRMS_SITE')
2251   BEGIN
2252		DECLARE @string varchar(100)
2253		DECLARE @groupNo varchar(5)
2254		DECLARE @cnt int
2255		DECLARE @ID VARCHAR(3)
2256		DECLARE @statement VARCHAR(2000)
2257		
2258	--Delete any left over records of NSI DBs (usually UltiPro_DataDict)
2259		DELETE FROM HRMS_SITE..RBSDBS WHERE sdbType   = 'NSI'
2260		DELETE FROM HRMS_SITE..RBSDBS WHERE sdbdbname = 'HRMS_WEBHELP'
2261		DELETE FROM HRMS_SITE..RBSDBS WHERE sdbdbname = 'HRMS_NotificationsNSMain'
2262		DELETE FROM HRMS_SITE..RBSDBS WHERE sdbdbname = 'HRMS_NotificationsUltiPro'
2263		
2264		/*Who am I */
2265		SELECT  @id = teamid 
2266		  FROM Teamenv
2267		 WHERE ((TeamServer = CAST(serverproperty('MachineName') AS varchar)) 
2268		   AND (TeamInstance = CAST(serverproperty('InstanceName') AS varchar)))
2269			OR 
2270			   ((TeamServer = CAST(serverProperty('MachineName') as varchar)) AND 
2271			   (serverProperty('InstanceName') IS NULL) AND (TeamInstance  = 'DEFAULT'))
2272		 
2273		SET @string = ''''
2274		/*find company servers*/
2275		set nocount on 
2276		if object_id('tempdb..#temp')is NOT null
2277		drop table #temp
2278		create table #temp(id int identity,server varchar(50))
2279
2280        EXEC ('use [HRMS_SITE]
2281        GRANT EXECUTE ON [dbo].[fn_IsRBS] TO [public]')
2282		 
2283		insert #temp(server)
2284		select CASE TeamInstance
2285			   WHEN 'DEFAULT' THEN TeamServer
2286			   ELSE teamServer + '\' + TeamInstance END
2287			   As CompanyServer
2288		from dbo.Teamenv
2289		where teamID in (select companyServerID from SiteSetup where gatewayid = dbo.fn_TeamEnvid(@@servername)
2290							and gatewayid <> companyserverid)
2291        and teamtype <> 4
2292		select @cnt = max(id) from #temp
2293
2294		/*if there is at least one company server that is not itself create list for sp*/
2295		if (@cnt <> 0)  
2296		Begin
2297		   while @cnt > 0
2298		   begin
2299		     select @string = @string +  server from #temp where id = @cnt
2300		   	select @cnt = @cnt - 1
2301			if @cnt <> 0 
2302			  select @string = @string + ','
2303		    end
2304			select @string = @string + ''''
2305		End
2306		ELSE /*if the only company server is itself get the name*/
2307		Begin
2308		  select @string = @string + @@Servername + ''''
2309		End
2310		/*execute the sp with the parameter. */ 
2311		
2312--**************************************************************************************
2313
2314   /*Disable the Audit Triggers*/
2315
2316IF Object_ID( 'tempdb..##Script') > 0
2317DROP TABLE ##Script
2318EXEC('USE HRMS_SITE select '' USE HRMS_SITE alter table ''+ (select Schema_name(schema_id) from sys.objects o 
2319where o.object_id = parent_id) + ''.''+object_name(parent_id) + '' DISABLE TRIGGER ''+
2320Name as TRGSCRIPT INTO ##Script
2321from sys.triggers t 
2322where Name like ''trg_audit%''
2323and  is_disabled = 0')
2324
2325DECLARE audit_trg CURSOR
2326READ_ONLY
2327FOR  Select TRGSCRIPT from ##Script
2328
2329DECLARE @Script varchar(800)
2330
2331OPEN audit_trg
2332
2333FETCH NEXT FROM audit_trg INTO @Script
2334WHILE (@@fetch_status <> -1)
2335BEGIN
2336	IF (@@fetch_status <> -2)
2337	BEGIN
2338            EXEC ('USE HRMS_SITE ' + @script)
2339         PRINT @Script           
2340	END
2341	FETCH NEXT FROM audit_trg INTO @Script
2342END
2343CLOSE audit_trg
2344DEALLOCATE audit_trg
2345
2346	IF EXISTS(SELECT * FROM hrms_site..sysObjects WHERE NAME = 'Secvalues' and type = 'v')
2347       SET @statement = 'proc_sync_RBScontexts [' + @string + ']' /*new unification*/
2348
2349	  print @string
2350	  print 'Company server =' + @statement 
2351	  EXEC (@Statement )
2352	  PRINT 'Synching Site database Tables'
2353
2354	   UPDATE hrms_site..secCompanies 
2355	      SET sccWebIsActive = 'Y'
2356
2357   
2358END /*end rbsconfig before SECURITY UNIFICATION*/
2359--************************************************************************** 
2360
2361		/*UPDATE HRMS_GLOBALDATA*/
2362		IF EXISTS(select * from dbo.vw_TeamSrvInfo with (nolock) WHERE dbname = 'HRMS_SITE')
2363			BEGIN
2364			  IF EXISTS( SELECT * FROM hrms_site..sysobjects where name = 'RBSDbs' and type = 'u')
2365				BEGIN
2366				 /*add rights to sitlinkusr to apply global macros*/
2367				 EXEC ('USE HRMS_SITE
2368					IF NOT EXISTS(SELECT * FROM HRMS_SITE..SYSUSERS WHERE NAME = ''sitelinkusr'')
2369					  EXEC sp_grantdbaccess ''sitelinkusr'', ''sitelinkusr''	
2370					GRANT SELECT, DELETE ON HRMS_SITE.dbo.RbsConnections TO sitelinkusr
2371					GRANT SELECT, DELETE ON HRMS_SITE.dbo.RbsDBs TO sitelinkusr
2372					GRANT SELECT, DELETE ON HRMS_SITE.dbo.SecLoginToType TO sitelinkusr
2373                    GRANT EXECUTE ON HRMS_SITE.dbo.fn_isRBS to Public')
2374				END 
2375			  EXEC UTILITY.dbo.proc_ChangeJobOwner
2376			  EXEC UTILITY.dbo.proc_drop_old_webuser
2377		      
2378
2379  END /* syncing RBS */
2380 
2381 /*synching SEC and RBS WEB users for environment*/
2382     
2383IF EXISTS(select * from dbo.vw_TeamSrvInfo with (nolock) WHERE dbname = 'HRMS_SITE')
2384  BEGIN
2385	EXEC UTILITY.dbo.proc_cleanup_RBSSite
2386
2387		  /**Enable triggers**/
2388	IF EXISTS(select * from ##Script)
2389	  BEGIN
2390		DECLARE audit_trg CURSOR
2391		READ_ONLY
2392		FOR  Select TRGSCRIPT from ##Script
2393
2394		OPEN audit_trg
2395
2396		FETCH NEXT FROM audit_trg INTO @Script
2397		WHILE (@@fetch_status <> -1)
2398		BEGIN
2399			IF (@@fetch_status <> -2)
2400			BEGIN
2401				 SELECT @Script = REPLACE(@Script,'DISABLE','ENABLE')
2402					EXEC ('USE HRMS_SITE ' + @script)
2403				 PRINT @Script           
2404			END
2405			FETCH NEXT FROM audit_trg INTO @Script
2406		END
2407		CLOSE audit_trg
2408		DEALLOCATE audit_trg
2409
2410		IF Object_ID( 'tempdb..##Script') > 0
2411		DROP TABLE ##Script
2412
2413	  END
2414END
2415
2416IF EXISTS(select * from Master..sysdatabases where name = 'ULTIPRO_SYSTEM')
2417  BEGIN
2418     /*Drop temp table*/
2419    IF OBJECT_ID('tempdb.dbo.CompanyResync') IS NOT NULL
2420      DROP TABLE TempDB.dbo.CompanyResync
2421      
2422    DECLARE @db_name varchar(50)
2423    DECLARE @db_server varchar(50)
2424    DECLARE c_object INSENSITIVE CURSOR FOR 
2425    /*ONLY GEN the Macros for databases that are from other server type*/
2426	SELECT dbname,server FROM (
2427    /*IF the server is and EN type then GEN the dbs from the Service Bureau Setup*/ 
2428	SELECT A.dbname,A.Server
2429	  FROM [UTILITY].[dbo].[vw_TeamEnvSrvInfo]A
2430	  JOIN [UTILITY].[dbo].vw_DBList B ON A.dbname = b.dbname
2431	 WHERE A.dbtype IN ('SB','CB')
2432	 AND  B.dbtype in ('COMPANY','DATADICT','SYSTEM' ) 
2433     AND  A.Teamtype = 2   
2434     AND  A.Server  = CAST(serverproperty('servername')AS VARCHAR(32))
2435	UNION 
2436    /*IF the server is and SB type then GEN the dbs from the Enterprise Setup*/
2437	SELECT A.dbname, A.Server
2438	  FROM [UTILITY].[dbo].[vw_TeamEnvSrvInfo]A
2439	  JOIN [UTILITY].[dbo].vw_DBList B ON A.dbname = b.dbname
2440	 WHERE A.dbtype IN ('EN','EX','CE','CW','DV','KM','PE','PA','UA','DF','KC','DC')
2441	 AND  B.dbtype in ('COMPANY','DATADICT','SYSTEM' ) 
2442     AND  A.Teamtype = 3  
2443     AND  A.Server  = CAST(serverproperty('servername')AS VARCHAR(32))
2444    UNION 
2445    /*ALWAYS gen the DATADICT*/
2446	SELECT A.dbname, A.Server
2447	  FROM [UTILITY].[dbo].[vw_TeamEnvSrvInfo]A
2448	  JOIN [UTILITY].[dbo].vw_DBList B ON A.dbname = b.dbname
2449	 WHERE B.dbtype = 'DATADICT' 
2450    UNION 
2451    /*ALWAYS gen the GLOBALDATA*/
2452	SELECT A.dbname, A.Server
2453	  FROM [UTILITY].[dbo].[vw_TeamEnvSrvInfo]A
2454	  JOIN [UTILITY].[dbo].vw_DBList B ON A.dbname = b.dbname
2455	 WHERE B.dbtype = 'GLOBALDATA' 
2456    UNION 
2457    /*ALWAYS gen the system DATABASE*/
2458	SELECT A.dbname, A.Server
2459	  FROM [UTILITY].[dbo].[vw_TeamEnvSrvInfo]A
2460	  JOIN [UTILITY].[dbo].vw_DBList B ON A.dbname = b.dbname
2461	 WHERE B.dbtype = 'SYSTEM' 
2462	UNION
2463    /*ALWAYS gen THE DATABASES FROM A EXTBASELINE TEMP BASELINE*/ 
2464    SELECT DISTINCT dbname, Server
2465        FROM [UTILITY].[dbo].[DbBackupExtLocs]
2466      WHERE dbname like '%ULTIPRO%' 
2467	  AND dbname NOT IN ('ULTIPRO_CUSTOM','ULTIPRO_SYSTEM','ULTIPRO_DATADICT')
2468        -- AND BuildNo = (SELECT name from HRMS_Globaldata..globalconfig WHERE value = 'Version\Data Version')
2469    UNION
2470    /*ALWAYS gen THE DATABASES FROM A USEALTBASELINE TEMP BASELINE*/
2471    SELECT A.dbname, A.Server
2472      FROM [UTILITY].[dbo].[vw_TeamEnvSrvInfo]A
2473     WHERE TEAMTYPE = 3
2474       AND EXISTS(SELECT *  FROM [UTILITY].[dbo].TEAMENV WHERE USEALTBaseline = 'Y' AND TEAMID =(
2475    SELECT [UTILITY].[dbo].[fn_TeamEnvid] ( CAST(serverproperty('servername')AS VARCHAR(32))))) )R 
2476     WHERE server = CAST(serverproperty('servername')AS VARCHAR(32))
2477     ORDER BY SUBSTRING(dbname,CHARINDEX('_',dbname,0),4)
2478
2479	OPEN c_object
2480	FETCH NEXT FROM c_object INTO @db_name,@db_server
2481	WHILE @@FETCH_STATUS != -1
2482		BEGIN
2483			/*Generate Macros the new way using procedure in Global database*/
2484            PRINT 'GENERATING MACROS FOR DATABASE ' + @db_name
2485           -- EXEC HRMS_GlobalData.dbo.HRMS_ApplyResyncScripts @p_ApplyMacros='Y', @p_DBName=@db_name
2486			FETCH NEXT FROM c_object INTO @db_name,@db_server
2487		END
2488	CLOSE c_object
2489	DEALLOCATE c_object
2490
2491  END
2492
2493  	UPDATE METADATA.dbo.DiscoveredServers
2494	SET Servername = TeamServer
2495   	FROM UTILITY..TEAMENV WHERE TEAMTYPE IN (2,3)
2496   		AND Name = 'Company server'
2497   	UPDATE METADATA.dbo.DiscoveredServers
2498    SET Servername = TeamServer
2499    FROM UTILITY..TEAMENV WHERE TEAMTYPE IN (1,2)
2500   	AND Name = 'Site server'
2501
2502GO
2503/*********************************************************************************************************************************************************************************************/
2504
2505
2506/****** Object:  StoredProcedure [dbo].[proc_Sync_ExtWEBUsers]    Script Date: 05/01/2013 17:28:07 ******/
2507SET ANSI_NULLS ON
2508GO
2509SET QUOTED_IDENTIFIER ON
2510GO
2511ALTER PROCEDURE [dbo].[proc_Sync_ExtWEBUsers]
2512
2513AS 
2514DECLARE add_RBSusers CURSOR
2515READ_ONLY
2516FOR  
2517select distinct GroupNo from UTILITY.dbo.DbBackupExtLocs
2518  
2519DECLARE @GroupNo int
2520Declare @Dbname varchar(50)
2521declare @CODBName varchar(50)
2522DECLARE @TempSitename varchar(40)
2523
2524OPEN add_RBSusers
2525
2526FETCH NEXT FROM add_RBSusers INTO @GroupNo
2527WHILE (@@fetch_status <> -1)
2528BEGIN
2529	IF (@@fetch_status <> -2)
2530	BEGIN
2531              
2532	DECLARE add_DBRBSusers CURSOR
2533	READ_ONLY
2534	FOR  
2535	select distinct dbname from UTILITY..DbBackupExtLocs
2536        where dbname like 'ULTIPRO%' 
2537        AND dbname NOT IN ('ULTIPRO_CUSTOM','ULTIPRO_SYSTEM','ULTIPRO_DATADICT')
2538           and groupno = @GroupNo  
2539	OPEN add_DBRBSusers
2540
2541	FETCH NEXT FROM add_DBRBSusers INTO @CODBName
2542	WHILE (@@fetch_status <> -1)
2543	BEGIN
2544	  IF (@@fetch_status <> -2)
2545		BEGIN           
2546        PRINT ''
2547        Select @TempSitename = dbname from UTILITY..DbBackupExtLocs
2548        where dbname like 'HRMS_SITE%'
2549           and groupno = @GroupNo 
2550                
2551			IF @TempSitename IS NOT NULL
2552				EXEC UTILITY.[dbo].[proc_sync_ExtRBSusers]
2553					@SiteName = @TempSitename,
2554					@database = @COdbname,
2555					@sourceserver = @@servername,
2556					@username = dev,
2557					@Rpassword = N'usg',
2558					@Lpassword = N'usg',
2559					@debug = N'Y'
2560					PRINT ''     
2561						
2562	  END		  
2563	
2564	FETCH NEXT FROM add_DBRBSusers INTO @CODBName
2565	END
2566END
2567	IF EXISTS(SELECT * FROM MASTER..SYSDATABASES WHERE NAME = @TempSitename)
2568	BEGIN
2569		EXEC UTILITY..proc_kill_users @TempSitename
2570		EXEC ('DROP DATABASE ' + @TempSitename + '')
2571	END
2572	CLOSE add_DBRBSusers
2573    DEALLOCATE add_DBRBSusers
2574    
2575	FETCH NEXT FROM add_RBSusers INTO @GroupNo
2576END
2577CLOSE add_RBSusers
2578DEALLOCATE add_RBSusers
2579GO
2580
2581
2582
2583/*********************************************************************************************************************************************************************************************/
2584
2585
2586
2587USE [UTILITY]
2588GO
2589/****** Object:  StoredProcedure [dbo].[proc_update_wkcTables]    Script Date: 04/30/2013 17:59:09 ******/
2590SET ANSI_NULLS ON
2591GO
2592SET QUOTED_IDENTIFIER ON
2593GO
2594ALTER PROCEDURE [dbo].[proc_update_wkcTables]
2595@database varchar(50)
2596as
2597
2598declare @statement varchar(400)
2599declare @debug char(1)
2600
2601set @debug = 'Y'
2602SET @statement = 'Updating wkcBprDef in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
2603IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2604SET @statement = 
2605'UTILITY.dbo.proc_update_wkcBprdef ' + @database
2606EXEC(@statement)
2607SET @statement = 'UPDATED ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + '  rows in wkcBprDef'
2608IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2609SET @statement = 'Updating wkcAprDelegations in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
2610IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2611SET @statement = 
2612'IF EXISTS(SELECT * FROM ' + @database + '.dbo.SYSCOLUMNS WHERE NAME = ''adlDelUID'')'
2613+ ' UPDATE ' + @database 
2614+ '.dbo.wkcAprDelegations SET adlDelUID = NewUserID '
2615+ ' FROM UTILITY.dbo.UserIDMap'
2616+ ' WHERE adlDelUID = OldUserID'
2617+ ' AND [Database] = ''' + @database + '''' 
2618+ ' OPTION (MAXDOP 1)' 
2619EXEC(@statement)
2620SET @statement = 'UPDATED ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + '  rows in wkcAprDelegations'
2621IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2622SET @statement = 'Updating wkcAprDelegations in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
2623IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2624SET @statement = 
2625'IF EXISTS(SELECT * FROM ' + @database + '.dbo.SYSCOLUMNS WHERE NAME = ''adlToUID'')'
2626+ ' UPDATE ' + @database 
2627+ '.dbo.wkcAprDelegations SET adlToUID = NewUserID '
2628+ ' FROM UTILITY.dbo.UserIDMap'
2629+ ' WHERE adlToUID = OldUserID'
2630+ ' AND [Database] = ''' + @database + ''''
2631+ ' OPTION (MAXDOP 1)' 
2632EXEC(@statement)
2633SET @statement = 'UPDATED ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + '  rows in wkcAprDelegations'
2634IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2635SET @statement = 'Updating wkcObsInst in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
2636IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2637SET @statement = 
2638'IF EXISTS(SELECT * FROM ' + @database + '.dbo.SYSCOLUMNS WHERE NAME = ''obiObserverUID'')'
2639+ ' UPDATE ' + @database 
2640+ '.dbo.wkcObsInst SET obiObserverUID = NewUserID '
2641+ ' FROM ' + @database + '.dbo.wkcObsInst ' 
2642+ ' JOIN UTILITY.dbo.UserIDMap ON OldUserID = obiObserverUID '
2643+ ' WHERE [Database] = ''' + @database + ''''
2644+ ' OPTION (MAXDOP 1)' 
2645EXEC(@statement)
2646SET @statement = 'UPDATED ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + '  rows in wkcObsInst'
2647IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2648SET @statement = 'Updating wkcAprPerformerInst in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
2649IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2650SET @statement = 
2651'IF EXISTS(SELECT * FROM '  + @database + '.dbo.SYSCOLUMNS WHERE NAME = ''apfPerformerUID'')'
2652+ ' UPDATE ' + @database 
2653+ '.dbo.wkcAprPerformerInst SET apfPerformerUID = NewUserID '
2654+ ' FROM ' + @database + '.dbo.wkcAprPerformerInst '
2655+ ' JOIN UTILITY.dbo.UserIDMap ON OldUserID = apfPerformerUID'
2656+ ' WHERE [Database] = ''' + @database + ''''
2657+ ' OPTION (MAXDOP 1)' 
2658EXEC(@statement)
2659SET @statement = 'UPDATED ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + '  rows in wkcAprPerformerInst'
2660IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2661SET @statement = 'Updating wkcActInst in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
2662IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2663SET @statement = 
2664'IF EXISTS(SELECT * FROM ' + @database + '.dbo.SYSCOLUMNS WHERE NAME = ''aciPerformerUID'')'
2665+ ' UPDATE ' + @database 
2666+ '.dbo.wkcActInst SET aciPerformerUID = NewUserID '
2667+ ' FROM ' + @database + '.dbo.wkcActInst '
2668+ ' JOIN UTILITY.dbo.UserIDMap ON OldUserID = aciPerformerUID '
2669+ ' WHERE [Database] = ''' + @database + ''''
2670+ ' OPTION (MAXDOP 1)' 
2671EXEC(@statement)
2672SET @statement = 'UPDATED ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + '  rows in wkcActInst'
2673IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2674SET @statement = 'Updating wkcBprInst in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
2675IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2676SET @statement = 
2677'IF EXISTS(SELECT * FROM '  + @database + '.dbo.SYSCOLUMNS WHERE NAME = ''bpiInitiatorUID'')'
2678+ ' UPDATE ' + @database 
2679+ '.dbo.wkcBprInst SET bpiInitiatorUID = NewUserID '
2680+ ' FROM ' + @database + '.dbo.wkcBprInst '
2681+ ' JOIN UTILITY.dbo.UserIDMap ON OldUserID = bpiInitiatorUID'
2682+ ' WHERE [Database] = ''' + @database + ''''
2683+ ' OPTION (MAXDOP 1)' 
2684EXEC(@statement)
2685SET @statement = 'UPDATED ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + '  rows in wkcBprInst'
2686IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2687SET @statement = 'Updating wkcAprInst in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
2688IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2689SET @statement = 'UTILITY.dbo.proc_update_wkcAprInst ' + @database
2690EXEC(@statement)
2691SET @statement = 'UPDATED ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + '  rows in wkcAprInst'
2692IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2693SET @statement = 'Updating wkcAprDef in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
2694IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2695SET @statement = 'UTILITY.dbo.proc_update_wkcAprDef ' + @database
2696EXEC(@statement)
2697SET @statement = 'UPDATED ' + CONVERT(VARCHAR(10),@@ROWCOUNT) + '  rows in wkcAprDef'
2698IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2699SET @statement = 'Updating jmsJob in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
2700IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2701SET @statement = 'Updating wkc Completed tables in the ' + UPPER(@database) + ' database, on the ' + UPPER(@@SERVERNAME) + ' company server...'
2702IF @debug = 'Y' RAISERROR( @statement,0,1) WITH NOWAIT
2703SET @statement = 
2704'UTILITY.dbo.PROC_UPDATE_wkcCompletedtables ' + @database
2705EXEC(@statement)
2706GO
2707
2708
2709/*********************************************************************************************************************************************************************************************/
2710
2711
2712
2713USE [UTILITY]
2714GO
2715/****** Object:  StoredProcedure [dbo].[proc_Fixuserobjects]    Script Date: 05/07/2013 14:46:43 ******/
2716SET ANSI_NULLS ON
2717GO
2718SET QUOTED_IDENTIFIER ON
2719GO
2720ALTER PROCEDURE [dbo].[proc_Fixuserobjects]
2721      @database sysname = '%',
2722      @drop CHAR(1) = 'N',
2723      @objectname sysname = '%',
2724      @includesystemdbs CHAR(1) = 'N'
2725AS
2726
2727/***************************************
2728proc_Fixuserobjects [@database][,@drop][,@includesystemdbs]
2729
2730Displays all user-owned objects in all databases.
2731
2732@database         Specify a specific pattern of database name or a single database
2733@drop             if 'Y', drop the user-owned objects found (default: 'N')
2734@includesystemdbs if 'Y', check master, msdb, model, and tempdb (default: 'N')
2735
2736It specifically excludes any objects owned by 'INFORMATION_SCHEMA' or 'system_function_schema'
2737
2738Kevin Maxwell, 9/12/2006
2739***************************************/
2740
2741SET NOCOUNT ON
2742
2743DECLARE @db sysname,
2744      @statement VARCHAR(8000),
2745      @object sysname
2746
2747IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##usgviewuserobjects') > 0
2748      DROP TABLE ##usgviewuserobjects
2749
2750CREATE TABLE ##usgviewuserobjects (ObjNum INT IDENTITY, DBName VARCHAR(50), OBJID int, OBJName sysname, OBJType CHAR(2), Owner sysname NULL)
2751
2752
2753SET @db = ''
2754
2755WHILE @db IS NOT NULL
2756BEGIN
2757      SELECT @db = MIN(name) FROM master.dbo.sysdatabases
2758      WHERE name LIKE @database
2759      AND name > @db
2760      AND (@includesystemdbs = 'Y' 
2761            OR (@includesystemdbs = 'N' AND name NOT IN ('master','msdb','model','tempdb'))
2762            )
2763      
2764      IF @db IS NULL BREAK
2765
2766      SET @statement = 'USE ' + @db + ' INSERT INTO ##usgviewuserobjects (DBName, OBJID, OBJName, OBJType, Owner) SELECT ''' + @db
2767      + ''' AS DBName, id, name, type, NULL AS owner FROM ' + @db + '.dbo.sysobjects WHERE user_name(uid) != ''dbo'''
2768      + ' AND name LIKE ''' + @objectname + ''''
2769
2770      EXEC (@statement)
2771
2772      SET @statement = 'USE ' + @db 
2773      + ' UPDATE ##usgviewuserobjects SET Owner = [TABLE_SCHEMA] FROM ##usgviewuserobjects  INNER JOIN ' + @db + '.[INFORMATION_SCHEMA].[TABLES]  ON (OBJName = [TABLE_NAME]  COLLATE SQL_Latin1_General_CP1_CI_AS)
2774          WHERE DBName = ''' + @db + ''''
2775      EXEC (@statement)
2776END
2777
2778DELETE FROM ##usgviewuserobjects WHERE Owner IN ('INFORMATION_SCHEMA','system_function_schema','dbo','sys')
2779DELETE FROM ##usgviewuserobjects WHERE DBName LIKE 'UTA_%' AND Owner = 'archive'
2780
2781--SELECT * FROM ##usgviewuserobjects
2782IF (SELECT count(*) FROM ##usgviewuserobjects) > 0 
2783      SELECT      ( CASE OBJType
2784                  WHEN 'U' THEN 'TABLE'
2785                  WHEN 'S' THEN 'SYSTEM_TABLE'
2786                  WHEN 'IT' THEN 'INTERNAL_TABLE'
2787                  WHEN 'P' THEN 'PROCEDURE'
2788                  WHEN 'FN' THEN 'FUNCTION'
2789                  WHEN 'TR' THEN 'TRIGGER'
2790                  WHEN 'V' THEN 'VIEW'
2791                  ELSE 'UNKNOWN'
2792                          END -- CASE
2793                  ) AS 'Object Type', DBName AS 'Database', CONVERT(VARCHAR(100),OBJName) AS 'Object', CONVERT(VARCHAR(100),Owner) AS Owner
2794      FROM ##usgviewuserobjects
2795      WHERE Owner IS NOT NULL
2796      ORDER BY DBName, Owner, OBJName
2797ELSE PRINT 'No user objects found.'
2798                  
2799
2800
2801IF @drop = 'Y' AND (SELECT count(*) FROM ##usgviewuserobjects) > 0
2802BEGIN
2803      DECLARE @type CHAR(2),
2804            @Owner sysname
2805      SET CONCAT_NULL_YIELDS_NULL ON
2806
2807      DECLARE c_object INSENSITIVE CURSOR FOR 
2808            SELECT DBName, OBJName, OBJType, Owner FROM ##usgviewuserobjects
2809
2810      OPEN c_object
2811      FETCH NEXT FROM c_object INTO @db, @object, @type, @owner
2812      WHILE @@FETCH_STATUS != -1
2813      BEGIN
2814            SET @statement = 'USE ' + @db + ' DROP ' + 
2815                  ( CASE @type
2816                        WHEN 'U' THEN 'TABLE'
2817                        WHEN 'P' THEN 'PROCEDURE'
2818                        WHEN 'FN' THEN 'FUNCTION'
2819                        WHEN 'TR' THEN 'TRIGGER'
2820                        WHEN 'V' THEN 'VIEW'
2821                        ELSE NULL
2822                    END -- CASE
2823                  )
2824            + ' [' + RTRIM(@owner) + '].' + RTRIM(@object)
2825
2826            --PRINT @statement
2827            EXEC (@statement)
2828
2829            FETCH NEXT FROM c_object INTO @db, @object, @type, @owner
2830      END
2831      CLOSE c_object
2832      DEALLOCATE c_object
2833
2834      PRINT ''
2835      PRINT 'All listed objects have been dropped.'
2836END
2837
2838DROP TABLE ##usgviewuserobjects
2839GO
2840
2841/*********************************************************************************************************************************************************************************************/
2842
2843
2844USE [UTILITY]
2845GO
2846/****** Object:  StoredProcedure [dbo].[proc_sync_RBScontexts]    Script Date: 10/9/2017 3:32:54 PM ******/
2847SET ANSI_NULLS ON
2848GO
2849SET QUOTED_IDENTIFIER ON
2850GO
2851ALTER PROCEDURE [dbo].[proc_sync_RBScontexts]
2852      @companyserverslist VARCHAR(8000) = NULL,
2853      @remotedispatch sysname = NULL,
2854      @user sysname = 'dev',
2855      @password VARCHAR(255) = 'usg',
2856      @deleteifnotfound CHAR(1) = 'Y'
2857AS
2858/********************
2859sp_usg_sync_seccontexts @companserverslist[,@remotedispatch][,@user][,@password]
2860This procedure will link to all servers specified in @companyserverslist 
2861(except itself), scan the databases present on each server. And compare
2862what databases it finds with what is specified in SecContexts.
2863If it finds a listed database on an expected server, nothing happens.
2864If it finds a listed database on one of the servers specified, 
2865SecDbs is corrected to point that database to that server.
2866If it does not find the database amongst any of the servers listed, the entries
2867for that database are removed from SecDbs, SecDbToDet, and SecCompanies.
2868  !! unless @deleteifnotfound = 'N'
2869If it finds a database on the listed servers that is NOT in SecContexts, it 
2870will create the appropriate default entries.
2871If it finds the same database on more than one of the servers listed, an error
2872will be raised.
2873@companyserverslist MUST be supplied, and in the format:
2874'server1[,server2][,server3]...'
2875Example for a single server: sp_usg_sync_seccontexts 'oneserver'
2876Example for a dual-server farm: sp_usg_sync_seccontexts 'server1,server2'
2877Note that the entire list itself is within the quotes, not each individual server.
2878This sp uses a function to convert the list to a table; that function expects a single string.
2879If @remotedispatch is specified, SecDbs.sdbServerName for the HRMS_DISPATCH database will be set to it.
2880Otherwise it will default to the current server. It will be set to the current server for HRMS_SITE.
2881@user and @password, if supplied, are used for linking servers.
2882The defaults are 'dev' with no password if either or both are omitted.
2883********************/
2884/*****
2885Last changed on: 10/27/2003 by Greg Spinney
2886      Added @deleteifnotfound, @user & @password, and default @companyserverslist features
2887*****/
2888/*****
2889Last changed on: 2/10/2004 by Greg Spinney
2890      Altered chunk that adds new SecContexts records based on the existence or lack thereof of 
2891      the sccMasterCoID column in SecCompanies i.e. if the column is present, an INSERT statement
2892      is generated to include that column; if not an alternate INSERT statement is created without it.
2893*****/
2894/*****
2895lAST CHANGED ON   : 5/5/2005 by Kevin Maxwell
2896        Added additional LTRIM RTRIM to insert  in temp ##KnownCompanyDbs SctClientID 
2897*****/
2898/*****
2899lAST CHANGED ON   : 5/13/2005 by Kevin Maxwell
2900        Update seccompanies to fix sccclientID
2901*****/
2902/*****
2903lAST CHANGED ON   : 5/05/2012 by Kevin Maxwell
2904        Cleanup company security, then reapply security in RBSConnections, before dropping temp link server.
2905*****/
2906SET NOCOUNT ON
2907DECLARE @statement VARCHAR(8000),
2908      @server Varchar(50),
2909      @servercontext VARCHAR(255),
2910      @v_company VARCHAR(50),
2911      @gateway varchar(50),
2912      @dispatch varchar(50),
2913      @dbcursorindex varchar(50),
2914      @companyservers VARCHAR(1000)
2915SET @gateway = CONVERT(sysname,serverproperty('servername'))
2916--set hrms_dispatch site_server value
2917EXEC ('UPDATE HRMS_DISPATCH.dbo.Dispconfig SET value  = ''' + @gateway + ''' WHERE Name = ''Site\Server''')
2918IF @remotedispatch IS NOT NULL
2919      SET @dispatch = @remotedispatch
2920ELSE
2921      SET @dispatch = CONVERT(sysname,serverproperty('servername'))
2922IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##CompanyServers') > 0
2923      DROP TABLE ##CompanyServers
2924IF (SELECT count(name) FROM tempdb.dbo.sysobjects WHERE name = '##KnownCompanyDbs') > 0
2925      DROP TABLE ##KnownCompanyDbs
2926CREATE TABLE ##CompanyServers
2927(
2928      SctServerName VARCHAR(30),
2929      IsAlreadyLinked CHAR(1),
2930      HasSystemDB CHAR(1),
2931)
2932IF @companyserverslist IS NULL
2933BEGIN
2934      SET @companyserverslist = ''
2935      SELECT @Server = MIN(SctServerName) FROM HRMS_SITE.dbo.SecContexts
2936      WHILE @Server IS NOT NULL
2937      BEGIN
2938            SET @companyserverslist = @companyserverslist +
2939            (CASE WHEN @companyserverslist = '' THEN @Server
2940            ELSE ',' + @Server
2941            END -- CASE
2942            )
2943            SELECT @Server = MIN(SctServerName) FROM HRMS_SITE.dbo.SecContexts 
2944            WHERE SctServerName > @Server
2945      END
2946END
2947--PRINT @companyserverslist
2948--GOTO ABORT
2949INSERT INTO ##CompanyServers (SctServerName, IsAlreadyLinked, HasSystemDB)
2950SELECT DISTINCT Item AS SctServerName, 'N' AS IsAlreadyLinked, 'Y' AS HasSystemDB FROM fn_ListToTable(@companyserverslist)
2951UPDATE ##CompanyServers
2952SET IsAlreadyLinked = 'Y'
2953WHERE SctServerName IN (SELECT srvname FROM master.dbo.sysservers)
2954CREATE TABLE ##KnownCompanyDbs
2955(
2956      SctContextID INT,
2957      SctServerName VARCHAR(30),
2958      SctDBName VARCHAR(50),
2959      InstancesFound INT,
2960      SctContextKey CHAR(5),
2961      SctContextName VARCHAR(40),
2962      SctClientID VARCHAR(5),
2963      Version VARCHAR(15)
2964)
2965INSERT INTO ##KnownCompanyDbs (SctContextID, SctServerName, SctDBName, InstancesFound, SctContextKey, SctContextName)
2966SELECT SctContextID, SctServerName, SctDbName, 0 As InstancesFound, SctContextKey, SctContextName
2967FROM HRMS_SITE.dbo.SecContexts
2968ORDER BY SctServerName, SctDbName
2969SELECT SctServerName AS "Servers" from ##CompanyServers
2970SELECT * from ##KnownCompanyDbs
2971DECLARE c_servers CURSOR FOR
2972      SELECT SctServerName FROM ##CompanyServers
2973OPEN c_servers
2974FETCH NEXT FROM c_servers INTO @server
2975WHILE (@@FETCH_STATUS >= 0)
2976BEGIN
2977      IF @server = @@servername
2978      BEGIN
2979            SET @statement = @server + ' is the current server, no linking involved.'
2980            PRINT @statement
2981      END
2982      ELSE IF (SELECT IsAlreadyLinked FROM ##CompanyServers WHERE SctServerName = @server) = 'N'
2983      BEGIN
2984            SET @statement = 'Linking server ' + @server + '...'
2985            PRINT @statement
2986            SET XACT_ABORT OFF
2987            SET @statement = 'master.dbo.sp_addlinkedserver @server = ''' + @server + ''', @srvproduct = ''SQL Server'''
2988            --PRINT @statement
2989            EXEC (@statement)
2990            IF @@ERROR != 0
2991            BEGIN
2992                  PRINT ''
2993                  PRINT @statement
2994                  SET @statement = 'Something went wrong creating the link to server: ' + @server
2995                  PRINT @statement
2996                  GOTO ABORT
2997            END
2998            SET XACT_ABORT ON
2999      END
3000      ELSE BEGIN
3001            SET @statement = @server + ' is already linked.'
3002            PRINT @statement
3003      END
3004      SELECT @statement = 'master.dbo.sp_addlinkedsrvlogin @rmtsrvname = ''' + @server + ''', @useself = ''false'', @rmtuser = ''' 
3005      + @user + ''', @rmtpassword = ' + 
3006            (CASE 
3007             WHEN @password IS NULL THEN 'null'
3008            ELSE '''' + @password + ''''
3009            END -- CASE
3010            )
3011--    PRINT @statement
3012      EXEC (@statement)
3013--    EXEC ('SELECT * FROM [' + @server + '].master.dbo.sysdatabases')
3014      FETCH NEXT FROM c_servers INTO @server
3015END
3016CLOSE c_servers
3017      /*** Cursor through each server, look for company databases.
3018            If database is in list of ##KnownCompanyDbs, increment InstancesFound
3019            If database is NOT in list of ##KnownCompanyDbs, add to table with SctContextID = NULL
3020            Later... 
3021                  1) any database with InstancesFound = 0 will be removed from SecContexts
3022                  2) any database with InstancesFound > 1, report existence of duplicate database
3023                        - duplicate may be ok, but cannot programmatically verify which SecContexts 
3024                        entry should be for which instance
3025                  3) any database with InstancesFound = 1 with SctContextID = NULL, create default SecContexts entry
3026                  4) any database with InstancesFound = 1 with SctContextID not null, update SecContexts.SctServername
3027      ***/
3028OPEN c_servers
3029FETCH NEXT FROM c_servers INTO @server
3030WHILE (@@FETCH_STATUS >= 0)
3031BEGIN
3032
3033PRINT @server
3034SELECT @@servername
3035      IF @server = @@servername
3036            SET @servercontext = ''
3037      ELSE
3038            SET @servercontext = '[' + @server + '].'
3039      SET @statement = 'IF (SELECT name FROM ' + @servercontext + 'master.dbo.sysdatabases WHERE name = ''ULTIPRO_SYSTEM'') IS NULL '
3040      + ' UPDATE ##CompanyServers SET HasSystemDB = ''N'' WHERE SctServerName = ''' + @server + ''''
3041      EXEC (@statement)
3042      IF (SELECT HasSystemDB FROM ##CompanyServers WHERE SctServerName = @server) = 'N'
3043      BEGIN
3044            SET @statement = 'There is no ULTIPRO_SYSTEM database on server: ' + @server
3045            RAISERROR(@statement,16,1)
3046            GOTO SKIP_SERVER
3047      END
3048      EXEC ('UPDATE ' + @servercontext + 'ULTIPRO_SYSTEM.dbo.SysVers SET svrType = ''' + @gateway + ''' WHERE svrObject = ''Site\Server''')
3049      SET @statement = 'Sysvers entry for Site\Server set to ''' + @gateway + ''' on server: ' + @server
3050      PRINT @statement
3051      EXEC ('UPDATE ##KnownCompanyDbs SET InstancesFound = InstancesFound + 1, SctServerName = ''' + @server + ''' WHERE SctDbName IN (SELECT name FROM ' + @servercontext + 'master.dbo.sysdatabases)')
3052      EXEC ('INSERT INTO ##KnownCompanyDbs (SctContextID, SctDbName, SctServerName, InstancesFound) SELECT NULL AS SctContextID, name AS SctDbName, ''' 
3053            + @server + ''' AS SctServerName, 1 AS InstancesFound FROM ' 
3054            + @servercontext + 'master.dbo.sysdatabases WHERE name NOT IN (SELECT SctDbName FROM ##KnownCompanyDbs) '
3055            + ' AND name NOT IN (''ULTIPRO_AUDIT'',''ULTIPRO_DATADICT'',''ULTIPRO_SYSTEM'',''ULTIPRO_WORK'')'
3056            + ' AND name LIKE ''ULTIPRO%''')
3057      DECLARE c_company CURSOR FOR
3058            SELECT SctDbName FROM ##KnownCompanyDbs
3059            WHERE SctServerName = @server
3060              AND SctContextID IS NULL
3061      OPEN c_company
3062      FETCH NEXT FROM c_company INTO @v_company
3063      WHILE @@FETCH_STATUS <> -1
3064      BEGIN
3065            SET @statement = 'UPDATE ##KnownCompanyDbs SET SctContextKey = (SELECT CmmCoid FROM ' + @servercontext + @v_company 
3066                  + '.dbo.CompMast), SctContextName = (SELECT LEFT(CmmCompanyName,40) FROM ' + @servercontext + @v_company
3067                  + '.dbo.CompMast), SctClientID = (SELECT LTRIM(RTRIM(CmmCompanyCode)) FROM ' + @servercontext + @v_company
3068                  + '.dbo.CompMast), Version = (SELECT cvrVersion FROM ' + @servercontext + @v_company
3069                  + '.dbo.CompVers WHERE cvrObject = ''DataVersion'') '
3070                  + ' WHERE SctDbName = ''' 
3071                  + @v_company + ''''
3072      BEGIN TRY
3073            EXEC (@statement)
3074    END TRY
3075      BEGIN CATCH
3076        DELETE ##KnownCompanyDbs WHERE sctdbname = @v_company  
3077      END CATCH
3078            FETCH NEXT FROM c_company INTO @v_company
3079      END
3080      CLOSE c_company
3081      DEALLOCATE c_company
3082    SKIP_SERVER:
3083      FETCH NEXT FROM c_servers INTO @server
3084END
3085CLOSE c_servers
3086print ''
3087--select * from ##KnownCompanyDbs
3088UPDATE ##KnownCompanyDbs SET SctServerName = '<Multiple Servers>' WHERE InstancesFound > 1
3089UPDATE ##KnownCompanyDbs SET SctServerName = '<Not found>' WHERE InstancesFound = 0
3090/******************* DATABASES NOT FOUND... WILL BE REMOVED *******************/
3091IF @deleteifnotfound != 'Y'
3092BEGIN
3093      PRINT ''
3094      SELECT 'The following entries were found in SecContexts, but no corresponding database was found:'
3095      SELECT * FROM ##KnownCompanyDBs WHERE InstancesFound = 0
3096      PRINT 'The entries will NOT be removed, per @deleteifnotfound'
3097      GOTO SKIPDELETE
3098END
3099PRINT ''
3100PRINT 'Databases not found, SecContexts entry will be removed: '
3101SELECT * FROM ##KnownCompanyDbs WHERE InstancesFound = 0
3102/*DELETE 
3103FROM HRMS_SITE.dbo.SecContexts
3104WHERE SctContextID IN (SELECT SctContextID FROM ##KnownCompanyDBs WHERE InstancesFound = 0)
3105*/
3106
3107
3108SET @statement = 'Deleting from RBSDBs...'
3109RAISERROR( @statement,0,1) WITH NOWAIT
3110SET @statement = 'DELETE FROM HRMS_SITE.dbo.RBSDBs
3111FROM HRMS_SITE.dbo.RBSDBS INNER JOIN ##KnownCompanyDBs ON sdbDBName = sctDBName WHERE InstancesFound = 0'
3112EXEC(@statement)
3113SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were removed from RBSDBs.'
3114RAISERROR( @statement,0,1) WITH NOWAIT
3115
3116SET @statement = 'Deleting from RBScompanyvalues...'
3117RAISERROR( @statement,0,1) WITH NOWAIT
3118SET @statement = 'Delete HRMS_Site..RBScompanyvalues
3119where svlcompanyid not in (select sdbcompanyid from HRMS_SITE..rbsdbs where sdbcompanyid is not NULL)'
3120EXEC(@statement)
3121SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were removed from RBScompanyvalues.'
3122RAISERROR( @statement,0,1) WITH NOWAIT
3123
3124--Disable triggers
3125EXEC('USE HRMS_SITE
3126	  EXEC(''Disable trigger ALL ON RBSUSERCOMPANY'')')
3127	
3128SET @statement = 'Deleting from RBSUserCompany...'
3129RAISERROR( @statement,0,1) WITH NOWAIT
3130SET @statement = 'DELETE HRMS_Site..RBSUserCompany
3131WHERE succompanyid NOT IN (SELECT sdbcompanyid FROM HRMS_SITE..rbsdbs WHERE sdbcompanyid IS NOT NULL)'
3132EXEC(@statement)
3133SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were removed from RBSUserCompany.'
3134RAISERROR( @statement,0,1) WITH NOWAIT
3135
3136--Enable triggers
3137EXEC('USE HRMS_SITE
3138	  EXEC(''Enable trigger ALL ON RBSUSERCOMPANY'')')
3139
3140SET @statement = 'Deleting from RBSUserMenuPref...'
3141RAISERROR( @statement,0,1) WITH NOWAIT
3142SET @statement = 'DELETE HRMS_Site..RBSUserMenuPref
3143WHERE CompanyID NOT IN (SELECT sdbcompanyid FROM HRMS_SITE..rbsdbs WHERE sdbcompanyid IS NOT NULL)'
3144EXEC(@statement)
3145SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were removed from RBSUserMenuPref.'
3146RAISERROR( @statement,0,1) WITH NOWAIT
3147
3148SET @statement = 'Deleting from SSOUsers...'
3149RAISERROR( @statement,0,1) WITH NOWAIT
3150SET @statement = 'DELETE HRMS_SITE..SSOUsers
3151WHERE HomeRealm not in (SELECT defHomeRealm FROM HRMS_SITE..SSOCoDefaultsHeader 
3152											JOIN HRMS_SITE..RbsCompanies ON defClientID = sccClientID
3153											JOIN HRMS_SITE..rbsdbs on sdbCompanyID = sccID 
3154                             WHERE sdbcompanyid IS NOT NULL)'
3155EXEC(@statement)
3156SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were removed from SSOUsers.'
3157RAISERROR( @statement,0,1) WITH NOWAIT
3158
3159SET @statement = 'Deleting from SSOCoDefaultsDetail...'
3160RAISERROR( @statement,0,1) WITH NOWAIT
3161SET @statement = 'DELETE HRMS_SITE..SSOCoDefaultsDetail
3162WHERE optDefID not in (SELECT defID FROM HRMS_SITE..SSOCoDefaultsHeader 
3163											JOIN HRMS_SITE..RbsCompanies ON defClientID = sccClientID
3164											JOIN HRMS_SITE..rbsdbs on sdbCompanyID = sccID 
3165                             WHERE sdbcompanyid IS NOT NULL)'
3166EXEC(@statement)
3167SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were removed from SSOCoDefaultsDetail.'
3168RAISERROR( @statement,0,1) WITH NOWAIT
3169
3170SET @statement = 'Deleting from SSOCoDefaultsHeader...'
3171RAISERROR( @statement,0,1) WITH NOWAIT
3172SET @statement = 'DELETE HRMS_SITE..SSOCoDefaultsHeader 
3173WHERE defClientID not in (SELECT sccClientID FROM HRMS_SITE..rbscompanies 
3174                                             JOIN HRMS_SITE..rbsdbs on sdbCompanyID = sccID 
3175                             WHERE sdbcompanyid IS NOT NULL)'
3176EXEC(@statement)
3177SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were removed from SSOCoDefaultsHeader.'
3178RAISERROR( @statement,0,1) WITH NOWAIT
3179
3180EXEC('USE HRMS_SITE;
3181IF EXISTS (SELECT * FROM sys.objects WHERE type = ''TR'' and name = ''trg_rbsCompanies_UES_DEL'')
3182BEGIN
3183	PRINT ''Disabling UES DELETE trigger on rbsCompanies table in HRMS_SITE'';
3184	disable trigger trg_rbsCompanies_UES_DEL ON rbsCompanies;
3185END
3186IF EXISTS (SELECT * FROM sys.objects WHERE type = ''TR'' and name = ''trg_RbsCompanies_INS_UPD'')
3187BEGIN
3188	PRINT ''Disabling UES INS/UPDATE trigger on rbsCompanies table in HRMS_SITE'';
3189	disable trigger trg_RbsCompanies_INS_UPD ON rbsCompanies;
3190END
3191IF EXISTS (SELECT * FROM sys.objects WHERE type = ''TR'' and name = ''trg_RbsCompanies_UES_INS'')
3192BEGIN
3193	PRINT ''Disabling UES INS/UPDATE trigger on rbsCompanies table in HRMS_SITE'';
3194	disable trigger trg_RbsCompanies_UES_INS ON rbsCompanies;
3195END
3196USE UTILITY;
3197')
3198
3199SET @statement = 'Deleting from rbscompanies...'
3200RAISERROR( @statement,0,1) WITH NOWAIT
3201SET @statement = 'DELETE HRMS_SITE..rbscompanies
3202WHERE sccID not in (SELECT sdbcompanyid FROM HRMS_SITE..rbsdbs WHERE sdbcompanyid IS NOT NULL)'
3203EXEC(@statement)
3204SET @statement = CONVERT(VARCHAR(10),@@ROWCOUNT) + ' rows were removed from rbscompanies.'
3205RAISERROR( @statement,0,1) WITH NOWAIT
3206
3207SKIPDELETE:
3208/******************* DATABASES FOUND WITH WRONG SERVERNAMES... WILL BE CORRECTED ******************/
3209PRINT ''
3210PRINT 'Databases found, SctServerName will be corrected:'
3211--SELECT B.* 
3212--FROM HRMS_SITE.dbo.SecContexts A INNER JOIN ##KnownCompanyDbs B ON A.SctContextID = B.SctContextID
3213--WHERE A.SctServerName != B.SctServername
3214--  AND B.InstancesFound = 1 
3215--  AND B.SctContextID IS NOT NULL
3216UPDATE HRMS_SITE.dbo.RBSDbs
3217   SET SdbServerName = UPPER(SctServername)
3218  FROM HRMS_SITE.dbo.RBSDbs INNER JOIN ##KnownCompanyDbs ON SdbDBName = SctDBName
3219WHERE ##KnownCompanyDbs.InstancesFound = 1
3220/*
3221UPDATE A 
3222SET A.SctServerName = B.SctServerName 
3223FROM HRMS_SITE.dbo.SecContexts A INNER JOIN ##KnownCompanyDbs B ON A.SctContextID = B.SctContextID
3224WHERE A.SctServerName != B.SctServerName
3225  AND B.InstancesFound = 1
3226*/
3227/******************* DATABASES FOUND WITH NO RECORD IN HRMS_SITE... WILL BE CREATED *****************/
3228PRINT ''
3229PRINT 'Databases found, no SecContexts record... will be created:'
3230--SELECT SctClientID, SctContextKey, SctContextName, SctDBName, SctServerName
3231--  FROM ##KnownCompanyDbs
3232-- WHERE ##KnownCompanyDbs.SctContextID IS NULL
3233IF (SELECT c.name FROM HRMS_SITE.dbo.sysobjects o 
3234      INNER JOIN HRMS_SITE.dbo.syscolumns c ON o.id = c.id
3235      WHERE o.name = 'RBSCompanies'
3236      AND c.name = 'sccMasterCoID') IS NULL
3237      SET @statement = 
3238            'INSERT INTO HRMS_SITE.dbo.RBSCompanies (SccClientID,SccCompanyName,SccSecGroupHostID)        SELECT LTRIM(RTRIM(SctClientID)), SctContextName, ''ALL'' AS SccGroupHostID       FROM ##KnownCompanyDbs WHERE ##KnownCompanyDbs.SctContextID IS NULL'
3239      ELSE SET @statement = 
3240            'INSERT INTO HRMS_SITE.dbo.RBSCompanies (SccClientID,SccCompanyName,SccSecGroupHostID, sccMasterCoID,sccreasonID,sccWebIsActive)        SELECT LTRIM(RTRIM(SctClientID)), SctContextName, ''ALL'' AS SccGroupHostID, SctContextKey,1,''Y''   FROM ##KnownCompanyDbs WHERE ##KnownCompanyDbs.SctContextID IS NULL'
3241EXEC (@statement)
3242
3243
3244INSERT INTO HRMS_SITE.dbo.RBSDBs (SdbCompanyID,SdbType,SdbVersion,SdbServerName,SdbDBName)
3245SELECT SctContextID,'SITEADMIN' AS SdbType, Version, UPPER(SctServerName), SctDBName
3246FROM ##KnownCompanyDbs WHERE ##KnownCompanyDbs.SctContextID IS NULL
3247AND SctDBname not in (SELECT sdbdbname FROM HRMS_SITE.dbo.RBSDBs)
3248
3249UPDATE ##KnownCompanyDbs
3250   SET SctContextID = SccID
3251FROM HRMS_SITE.dbo.RBSCompanies
3252WHERE SctContextname = sccCompanyname
3253
3254UPDATE HRMS_SITE.dbo.RBSDBs
3255   SET sdbCompanyID = SctContextID
3256FROM ##KnownCompanyDbs
3257WHERE sdbdbname = sctDBName
3258/*
3259INSERT INTO HRMS_SITE.dbo.SecContexts (SctClientID, SctContextKey, SctContextName, SctDBName, SctServerName)
3260SELECT SctClientID, SctContextKey, SctContextName, SctDBName, SctServerName
3261  FROM ##KnownCompanyDbs
3262WHERE ##KnownCompanyDbs.SctContextID IS NULL
3263*/
3264/****************** SAME DATABASE FOUND MORE THAN ONCE WITHIN SAME FARM... RAISE ERROR *************/
3265IF (SELECT count(*) FROM ##KnownCompanyDbs WHERE (InstancesFound > 1)) > 0
3266BEGIN
3267      PRINT ''
3268      RAISERROR ('** ACTION REQUIRED **',16,1)
3269      PRINT ''
3270      PRINT 'Databases found more than once: '
3271      SELECT * FROM ##KnownCompanyDbs WHERE InstancesFound > 1 AND SctContextID IS NOT NULL
3272END
3273/*re-Run perms on Company servers and Drop temp link server*/
3274OPEN c_servers
3275FETCH NEXT FROM c_servers INTO @server
3276WHILE (@@FETCH_STATUS >= 0)
3277BEGIN
3278      IF @server != @@servername
3279      BEGIN
3280          EXEC HRMS_GlobalData.dbo.HRMS_DB_Setup_Permissions @p_CreateBaseLogins = 1, @p_CreateRBSConnections = 1   
3281          SET @servercontext = '[' + @server + '].'
3282          SET @statement = 'EXEC' + @servercontext + 'HRMS_GlobalData.dbo.HRMS_ApplyResyncScripts @p_ApplyMacros=''Y'', @p_DBName=''HRMS_GLOBALDATA''  '  
3283                         + 'EXEC' + @servercontext + 'HRMS_GlobalData.dbo.HRMS_DB_Setup_Permissions @p_CreateBaseLogins = 1, @p_CreateRBSConnections = 1   ' 
3284                         + 'EXEC' + @servercontext + 'UTILITY.dbo.proc_update_JMS_UES'
3285          EXEC (@statement)
3286            EXEC ('master.dbo.sp_droplinkedsrvlogin @rmtsrvname = ''' + @server + ''', @locallogin = null')
3287            IF (SELECT IsAlreadyLinked FROM ##CompanyServers WHERE SctServerName = @server) = 'N'
3288                  EXEC ('master.dbo.sp_dropserver ''' + @server + '''')
3289      END
3290      FETCH NEXT FROM c_servers INTO @server
3291END
3292UPDATE HRMS_SITE.dbo.RBSDbs SET SdbServerName = @gateway WHERE sdbType = 'GATEWAY'
3293UPDATE HRMS_SITE.dbo.RBSDbs SET SdbServerName = @dispatch WHERE sdbType IN ('DPM','DISPATCH')
3294/* IF THE ENVIRONMENT IS AN ENTERPRISE UPDATE THE UES_JMS */
3295IF EXISTS(SELECT * FROM MASTER..sysdatabases WHERE name = 'ULTIPRO_DATADICT')
3296   BEGIN
3297      EXEC HRMS_GlobalData.dbo.HRMS_DB_Setup_Permissions @p_CreateBaseLogins = 1, @p_CreateRBSConnections = 1   
3298      EXEC UTILITY.dbo.proc_update_JMS_UES
3299   END
3300/************ SecValues (added 8/26/2003) ****************/
3301--IF (SELECT COUNT(*) FROM HRMS_SITE..SecValues WHERE svlType = 'COMPANY' AND IsNumeric(svlKey) = 1) = 0
3302--BEGIN
3303--    RAISERROR('WARNING: SecValues could not be created; no valid default values for ''COMPANY'' svlType found.',16,1)
3304--    GOTO ABORT
3305--END
3306DECLARE c_dbcursor CURSOR FOR
3307      SELECT sddKey FROM ##KnownCompanyDbs
3308      INNER JOIN HRMS_SITE..SecDbs ON SctDBName = sdbDBName
3309      INNER JOIN HRMS_SITE..SecDbToDet ON sdbID = sddDBID
3310OPEN c_dbcursor
3311FETCH NEXT FROM c_dbcursor INTO @dbcursorindex
3312WHILE @@FETCH_STATUS != -1
3313BEGIN
3314      IF (SELECT COUNT(*) FROM HRMS_SITE..SecValues WHERE svlKey = CONVERT(VARCHAR(50),@dbcursorindex)) = 0
3315            INSERT HRMS_SITE.dbo.RbsCompanyValues (svlCode, svlCompanyID, svlValue)
3316            SELECT svlCode, CONVERT(VARCHAR(50),@dbcursorindex) AS svlKey, svlValue
3317            FROM HRMS_SITE..SecValues WHERE ISNUMERIC(svlKey) = 1 
3318                  AND svlKey IS NOT NULL
3319                  AND svlKey = (SELECT MIN(svlKey) FROM HRMS_SITE..SecValues WHERE svlType = 'COMPANY'
3320                              AND ISNUMERIC(svlKey) = 1)
3321                  AND svlType = 'COMPANY'
3322      FETCH NEXT FROM c_dbcursor INTO @dbcursorindex
3323END
3324CLOSE c_dbcursor
3325DEALLOCATE c_dbcursor
3326/************ SecCompanies Padding issue (added 5/13/2005) ****************/
3327UPDATE HRMS_SITE.dbo.RBScompanies
3328   SET sccclientid = CASE CHARINDEX('',sccclientid + '"')
3329                  WHEN 0 THEN SUBSTRING(sccclientid, 1, LEN(sccclientid))
3330                  ELSE SUBSTRING(sccclientid, 1, CHARINDEX('',sccclientid + '"')) END
3331ABORT:
3332DROP TABLE ##CompanyServers
3333DROP TABLE ##KnownCompanyDbs
3334DEALLOCATE c_servers
3335SET XACT_ABORT ON
3336
3337GO
3338/******END  Object:  StoredProcedure [dbo].[proc_sync_RBScontexts] ******/
3339
3340
3341USE [UTILITY]
3342GO
3343/****** Object:  StoredProcedure [dbo].[proc_Manage_RBStriggers]    Script Date: 06/07/2013 10:33:30 ******/
3344SET ANSI_NULLS ON
3345GO
3346SET QUOTED_IDENTIFIER ON
3347GO
3348ALTER procedure [dbo].[proc_Manage_RBStriggers]
3349@database varchar(50),
3350@enableDisable int
3351--1 enable , 0 disable
3352as
3353
3354If @enabledisable = 0
3355BEGIN
3356EXEC('USE ' + @Database + 
3357 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[HRMS_trg_Secgroups]'') AND type = ''TR'')
3358	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] DISABLE TRIGGER HRMS_trg_Secgroups')
3359EXEC('USE ' + @Database + 
3360 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_I_RBSUserRoles]'') AND type = ''TR'')
3361	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] DISABLE TRIGGER trg_I_RBSUserRoles')
3362EXEC('USE ' + @Database + 
3363 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[MsgTriggerRbsUserRoles]'') AND type = ''TR'')
3364	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] DISABLE TRIGGER MsgTriggerRbsUserRoles')
3365EXEC('USE ' + @Database + 
3366 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsUserRoles_DEL]'') AND type = ''TR'')
3367	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] DISABLE TRIGGER trg_Audit_RbsUserRoles_DEL')
3368EXEC('USE ' + @Database + 
3369 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsUserRoles_INS]'') AND type = ''TR'')
3370	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] DISABLE TRIGGER trg_Audit_RbsUserRoles_INS')
3371EXEC('USE ' + @Database + 
3372 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsUserRoles_MOD]'') AND type = ''TR'')
3373	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] DISABLE TRIGGER trg_Audit_RbsUserRoles_MOD')
3374EXEC('USE ' + @Database + 
3375 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_RbsUserRoles_ONB_INS_UPD]'') AND type = ''TR'')
3376	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] DISABLE TRIGGER trg_RbsUserRoles_ONB_INS_UPD')
3377EXEC('USE ' + @Database + 
3378 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_RbsUserRoles_UTM_IUD]'') AND type = ''TR'')
3379	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] DISABLE TRIGGER trg_RbsUserRoles_UTM_IUD')
3380EXEC('USE ' + @Database + 
3381 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_U_rbsUserRoles]'') AND type = ''TR'')
3382	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] DISABLE TRIGGER trg_U_rbsUserRoles')
3383EXEC('USE ' + @Database + 
3384 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_UID_rbsUserRoles]'') AND type = ''TR'')
3385	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] DISABLE TRIGGER trg_UID_rbsUserRoles')
3386EXEC('USE ' + @Database + 
3387 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trRbsUserRoles_Del]'') AND type = ''TR'')
3388	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] DISABLE TRIGGER trRbsUserRoles_Del')
3389
3390EXEC('USE ' + @Database + 
3391 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_RbsExceptions_UTM_IUD]'') AND type = ''TR'')
3392	ALTER TABLE ' + @Database + '.[dbo].[RBSExceptions] DISABLE TRIGGER trg_RbsExceptions_UTM_IUD')
3393EXEC('USE ' + @Database + 
3394 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_RbsQualList_UTM_IUD]'') AND type = ''TR'')
3395	ALTER TABLE ' + @Database + '.[dbo].[RBSQualList] DISABLE TRIGGER trg_RbsQualList_UTM_IUD')
3396
3397EXEC('USE ' + @Database + 
3398 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsQualifiers_DEL]'') AND type = ''TR'')
3399	ALTER TABLE ' + @Database + '.[dbo].[RBSQualifiers] DISABLE TRIGGER trg_Audit_RbsQualifiers_DEL')
3400EXEC('USE ' + @Database + 
3401 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsQualifiers_INS]'') AND type = ''TR'')
3402	ALTER TABLE ' + @Database + '.[dbo].[RBSQualifiers] DISABLE TRIGGER trg_Audit_RbsQualifiers_INS')
3403EXEC('USE ' + @Database + 
3404 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsQualifiers_MOD]'') AND type = ''TR'')
3405	ALTER TABLE ' + @Database + '.[dbo].[RBSQualifiers] DISABLE TRIGGER trg_Audit_RbsQualifiers_MOD')
3406EXEC('USE ' + @Database + 
3407 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_UID_rbsQualifiers]'') AND type = ''TR'')
3408	ALTER TABLE ' + @Database + '.[dbo].[RBSQualifiers] DISABLE TRIGGER trg_UID_rbsQualifiers')
3409
3410EXEC('USE ' + @Database + 
3411 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trRptSvWeb_EnforceRI]'') AND type = ''TR'')
3412	ALTER TABLE ' + @Database + '.[dbo].[RptSvWeb] DISABLE TRIGGER trRptSvWeb_EnforceRI')
3413	
3414END
3415
3416If @enabledisable = 1
3417BEGIN
3418EXEC('USE ' + @Database + 
3419 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[HRMS_trg_Secgroups]'') AND type = ''TR'')
3420	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] ENABLE TRIGGER HRMS_trg_Secgroups')
3421EXEC('USE ' + @Database + 
3422 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_I_RBSUserRoles]'') AND type = ''TR'')
3423	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] ENABLE TRIGGER trg_I_RBSUserRoles')
3424EXEC('USE ' + @Database + 
3425 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[MsgTriggerRbsUserRoles]'') AND type = ''TR'')
3426	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] ENABLE TRIGGER MsgTriggerRbsUserRoles')
3427EXEC('USE ' + @Database + 
3428 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsUserRoles_DEL]'') AND type = ''TR'')
3429	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] ENABLE TRIGGER trg_Audit_RbsUserRoles_DEL')
3430EXEC('USE ' + @Database + 
3431 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsUserRoles_INS]'') AND type = ''TR'')
3432	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] ENABLE TRIGGER trg_Audit_RbsUserRoles_INS')
3433EXEC('USE ' + @Database + 
3434 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsUserRoles_MOD]'') AND type = ''TR'')
3435	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] ENABLE TRIGGER trg_Audit_RbsUserRoles_MOD')
3436EXEC('USE ' + @Database + 
3437 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_RbsUserRoles_ONB_INS_UPD]'') AND type = ''TR'')
3438	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] ENABLE TRIGGER trg_RbsUserRoles_ONB_INS_UPD')
3439EXEC('USE ' + @Database + 
3440 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_RbsUserRoles_UTM_IUD]'') AND type = ''TR'')
3441	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] ENABLE TRIGGER trg_RbsUserRoles_UTM_IUD')
3442EXEC('USE ' + @Database + 
3443 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_U_rbsUserRoles]'') AND type = ''TR'')
3444	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] ENABLE TRIGGER trg_U_rbsUserRoles')
3445EXEC('USE ' + @Database + 
3446 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_UID_rbsUserRoles]'') AND type = ''TR'')
3447	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] ENABLE TRIGGER trg_UID_rbsUserRoles')
3448EXEC('USE ' + @Database + 
3449 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trRbsUserRoles_Del]'') AND type = ''TR'')
3450	ALTER TABLE ' + @Database + '.[dbo].[RBSUserRoles] ENABLE TRIGGER trRbsUserRoles_Del')
3451
3452EXEC('USE ' + @Database + 
3453 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_RbsExceptions_UTM_IUD]'') AND type = ''TR'')
3454	ALTER TABLE ' + @Database + '.[dbo].[RBSExceptions] ENABLE TRIGGER trg_RbsExceptions_UTM_IUD')
3455EXEC('USE ' + @Database + 
3456 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_RbsQualList_UTM_IUD]'') AND type = ''TR'')
3457	ALTER TABLE ' + @Database + '.[dbo].[RBSQualList] ENABLE TRIGGER trg_RbsQualList_UTM_IUD')
3458
3459EXEC('USE ' + @Database + 
3460 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsQualifiers_DEL]'') AND type = ''TR'')
3461	ALTER TABLE ' + @Database + '.[dbo].[RBSQualifiers] ENABLE TRIGGER trg_Audit_RbsQualifiers_DEL')
3462EXEC('USE ' + @Database + 
3463 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsQualifiers_INS]'') AND type = ''TR'')
3464	ALTER TABLE ' + @Database + '.[dbo].[RBSQualifiers] ENABLE TRIGGER trg_Audit_RbsQualifiers_INS')
3465EXEC('USE ' + @Database + 
3466 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_Audit_RbsQualifiers_MOD]'') AND type = ''TR'')
3467	ALTER TABLE ' + @Database + '.[dbo].[RBSQualifiers] ENABLE TRIGGER trg_Audit_RbsQualifiers_MOD')
3468EXEC('USE ' + @Database + 
3469 '  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trg_UID_rbsQualifiers]'') AND type = ''TR'')
3470	ALTER TABLE ' + @Database + '.[dbo].[RBSQualifiers] ENABLE TRIGGER trg_UID_rbsQualifiers')
3471
3472EXEC('USE ' + @Database + 
3473'  IF  EXISTS (SELECT * FROM ' + @Database + '.dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trRptSvWeb_EnforceRI]'') AND type = ''TR'')
3474ALTER TABLE ' + @Database + '.[dbo].[RptSvWeb] ENABLE TRIGGER trRptSvWeb_EnforceRI')
3475END
3476
3477GO
3478
3479
3480/*********************************************************************************************************************************************************************************************/
3481
3482
3483
3484USE [UTILITY]
3485GO
3486/****** Object:  StoredProcedure [dbo].[proc_setup_devuser]    Script Date: 05/28/2013 14:20:07 ******/
3487SET ANSI_NULLS ON
3488GO
3489SET QUOTED_IDENTIFIER ON
3490GO
3491ALTER PROCEDURE [dbo].[proc_setup_devuser]
3492
3493AS
3494--Modified 2\18\2011 KM to fix ULtipro users
3495SET NOCOUNT ON
3496--script to alter the check_policy to off
3497
3498DECLARE addusers CURSOR
3499READ_ONLY
3500FOR  select 'ALTER LOGIN [' + name + '] WITH DEFAULT_DATABASE=[MASTER], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'  
3501from sys.sql_logins
3502where is_policy_checked = 1
3503DECLARE @SCRIPT varchar(max)
3504OPEN addusers
3505FETCH NEXT FROM addusers INTO @SCRIPT
3506WHILE (@@fetch_status <> -1)
3507BEGIN
3508	IF (@@fetch_status <> -2)
3509	BEGIN
3510	  RAISERROR( @SCRIPT,0,1) WITH NOWAIT
3511      EXEC(@SCRIPT)
3512	END
3513	FETCH NEXT FROM addusers INTO @SCRIPT
3514END
3515CLOSE addusers
3516DEALLOCATE addusers
3517
3518EXEC PROC_SETUP_DEFAULTUSERS
3519 
3520--RESYNCING SIDS
3521
3522declare @DBCount varchar(10)
3523select @DBCount=Cast(count(name) as varchar(10)) from master.dbo.sysdatabases
3524	 where name not like 'ReportServer%'
3525print 'Number of DB: ' + @DBCount
3526		
3527declare @dbname sysname 
3528declare my_cursor Cursor FORWARD_ONLY STATIC
3529FOR
3530	select name from master.dbo.sysdatabases
3531      where name not like 'ReportServer%'
3532
3533open my_cursor 
3534fetch next from my_cursor into @dbname
3535while (@@fetch_status = 0)
3536BEGIN
3537	BEGIN
3538		set quoted_identifier OFF 
3539		declare @cmd Nvarchar(800) 
3540		declare @crlf char(2) 
3541		Set @crlf = char(13)+char(10) 
3542
3543		--sync the sql logins
3544		print 'Working on ' + @dbname
3545		IF Object_id('tempdb..#users') is NOT NULL
3546		  DROP TABLE #users
3547		create table #users(username varchar(100))
3548		set @CMD = 'Insert #users ' +
3549			' Select name from ' + @DBNAME + '..sysusers' +
3550			' where sid > 0x01 '+ 
3551			' and sid IS NOT NULL' +
3552			' and issqluser = 1' + 
3553			' and LEN(name)< 35' +
3554			' and name <> ''dbo'''
3555		EXEC (@CMD)
3556		declare @userCount varchar(10)
3557		select @userCount=Cast(count(username) as varchar(10)) from #users
3558             where username not in ('Guest')
3559		print 'Number of users: ' + @userCount
3560		
3561		declare @Uname varchar(100)
3562        declare @password varchar(100)
3563		declare u_cursor cursor local
3564		FOR
3565			Select username from #users
3566             where username not in ('Guest')
3567		 
3568		open U_cursor 
3569		fetch next from U_cursor into @Uname
3570
3571		while (@@fetch_status=0)
3572		BEGIN  
3573          --PRINT UPPER(@dbname) + '-' + @UNAME
3574            SELECT @password = CASE @uname 
3575					WHEN 'xpt' then 'xpt'
3576					WHEN 'dev' then 'usg'
3577					WHEN 'context' then 'bait'
3578					WHEN 'ULTI' then 'itlu'
3579					--WHEN 'xpt' then 'password'
3580					--WHEN 'dev' then 'password'
3581                    --WHEN 'context' then 'password'
3582					--WHEN 'ULTI' then 'password'
3583                    WHEN 'hrms_user' then 'ultipro'
3584                    WHEN 'ULTIPRO_LOGIN' THEN 'blimpie'
3585					WHEN 'HRMSDISPATCH' THEN 'disp'
3586					WHEN 'HRMSSITE' THEN 'site'
3587					--WHEN 'HRMSDISPATCH' THEN 'password'
3588                    --WHEN 'HRMSSITE' THEN 'password'
3589                    WHEN 'keyuser' THEN 'password'
3590                    WHEN 'aspstate' THEN 'aspstate'
3591                    WHEN 'UTM_Admin' THEN 'password'
3592                    WHEN 'UTM_Web'  THEN 'password' 
3593                    WHEN 'USCOMEX6Q42N0' THEN '6Q42N0'
3594					--WHEN 'USCOMEX6Q42N0' THEN 'password'
3595                    WHEN 'ProjectixWebUser' THEN 'password'
3596                    WHEN 'Onecoreuser' THEN 'password'
3597                    WHEN 'talentuser' THEN 'password' 
3598                    WHEN 'workbrain' THEN 'workbrain'
3599                    WHEN 'archive' THEN 'archive'
3600                    WHEN 'SiteLinkusr' THEN 'WeRhere4U' 
3601                    WHEN 'EmpcountUser' THEN 'empAdminPass1'
3602                    ELSE
3603                        'password2' END 
3604			IF len(@uname) > 35
3605			 SET @PASSWORD = 'NULL'
3606			IF OBJECT_ID('SYS.SERVER_PRINCIPALS')IS NOT NULL 
3607			SET @cmd =  'EXEC ' + @DBNAME + '..sp_change_users_login ''auto_fix'',' + '''' +  @Uname + '''' +
3608						 ',NULL' + ','+ '''' + @PASSWORD + ''''
3609			else
3610			SET @cmd =  'EXEC ' + @DBNAME + '..sp_change_users_login ''auto_fix'',' + '''' +  @Uname + '''' +
3611						 ',NULL' 
3612			BEGIN
3613				print @cmd
3614				EXEC (@cmd)
3615			END 
3616			fetch next from U_cursor into @Uname
3617		END 
3618		close U_cursor 
3619		deallocate U_cursor 
3620	END
3621	print 'Fetching next DB'	
3622	fetch next from my_cursor into @dbname
3623	print 'Next DB: ' + @dbname
3624END
3625close my_cursor 
3626deallocate my_cursor 
3627
3628--ADD THE ULTIPRO COMPANY DEFAULT USERS
3629/***************************************************************************************/
3630DECLARE add_users CURSOR
3631READ_ONLY
3632FOR  select name
3633   from master..sysdatabases
3634   where name like 'ulti_%'
3635      OR NAME LIKE 'HRMS_%'
3636      OR NAME like 'UTILITY'
3637  
3638 
3639
3640DECLARE @dname varchar(40)
3641
3642OPEN add_users
3643
3644FETCH NEXT FROM add_users INTO @dname
3645WHILE (@@fetch_status <> -1)
3646BEGIN
3647	IF (@@fetch_status <> -2)
3648	BEGIN
3649        
3650        PRINT ''
3651	PRINT 'UPDATING ' + @dname
3652        EXEC ('USE ' + @dname + 
3653              ' if not exists(select name from sysusers where name = ''DEV'')
3654	            EXEC sp_grantdbaccess ''DEV'', ''DEV''	
3655                EXEC sp_addrolemember N''db_owner'', N''DEV''
3656                if not exists(select name from sysusers where name = ''USCOMEX6Q42N0'')
3657                EXEC sp_grantdbaccess ''USCOMEX6Q42N0'', ''USCOMEX6Q42N0''	
3658                EXEC sp_addrolemember ''db_owner'', ''USCOMEX6Q42N0''')
3659         PRINT ' '             
3660	END
3661	FETCH NEXT FROM add_users INTO @dname
3662END
3663CLOSE add_users
3664DEALLOCATE add_users
3665/*Grant trace rights to dev*/
3666  EXEC('USE MASTER
3667        GRANT ALTER TRACE TO DEV;')
3668
3669GO
3670
3671/*********************************************************************************************************************************************************************************************/
3672
3673USE [UTILITY]
3674GO
3675/****** Object:  StoredProcedure [dbo].[proc_kill_users]    Script Date: 03/31/2014 11:09:51 ******/
3676SET ANSI_NULLS ON
3677GO
3678SET QUOTED_IDENTIFIER ON
3679GO
3680ALTER PROCEDURE [dbo].[proc_kill_users] @v_dbname varchar(50)
3681AS
3682SET NOCOUNT ON
3683-- Kills 20 connections every 2 seconds, so that the server doesnt get bogged down with 100 kill requests
3684DECLARE @count INT
3685DECLARE @spid VARCHAR(10)
3686DECLARE @dbname varchar(20)
3687SET @dbname = @v_dbname  --set this to the correct dbname
3688PRINT 'All connections to ' + @dbname + ' are going to be terminated.'
3689DECLARE c_killCursor CURSOR LOCAL FAST_FORWARD
3690 FOR
3691      SELECT spid 
3692      FROM master.dbo.sysprocesses a WITH (NOLOCK)
3693      JOIN master.sys.dm_exec_sessions WITH (NOLOCK) ON session_id = a.spid
3694                                       AND is_user_process = 1
3695      JOIN master.dbo.sysdatabases b WITH (NOLOCK) ON b.dbid = a.dbid
3696      WHERE b.name = @dbname
3697      AND spid <> (SELECT @@spid)
3698OPEN c_killCursor
3699FETCH NEXT FROM c_killCursor INTO @spid
3700WHILE @@FETCH_STATUS = 0
3701 BEGIN
3702      SET @count = 0
3703      WHILE @@FETCH_STATUS = 0 AND @count < 20
3704        BEGIN
3705            SET @count = @count + 1
3706            PRINT '----- KILLING SPID ' + @spid
3707            IF EXISTS(SELECT spid FROM master.dbo.sysprocesses with (READPAST)WHERE spid = @spid)
3708            EXEC('KILL  ' + @spid)
3709            FETCH NEXT FROM c_killCursor INTO @spid
3710        END
3711      WAITFOR DELAY '000:00:01'
3712 END
3713 DECLARE job_killCursor CURSOR LOCAL FAST_FORWARD
3714  FOR
3715      SELECT job_id FROM sys.dm_exec_background_job_queue WITH (NOLOCK)
3716       WHERE database_id = db_id(@dbname)
3717OPEN job_killCursor
3718FETCH NEXT FROM job_killCursor INTO @spid
3719WHILE @@FETCH_STATUS = 0
3720 BEGIN
3721      SET @count = 0
3722      WHILE @@FETCH_STATUS = 0 AND @count < 20
3723        BEGIN
3724            SET @count = @count + 1
3725            PRINT '----- KILLING JOB SPID ' + @spid
3726            IF EXISTS(SELECT job_id FROM sys.dm_exec_background_job_queue with (READPAST) WHERE job_id = @spid)
3727            EXEC('KILL STATS JOB ' + @spid)
3728            FETCH NEXT FROM job_killCursor INTO @spid
3729        END
3730      WAITFOR DELAY '000:00:01'
3731 END
3732PRINT ' '
3733PRINT '----------------------------------------------------------------------------------'
3734PRINT 'All connections to ' + @dbname + ' have been terminated.'
3735CLOSE c_killCursor
3736DEALLOCATE c_killCursor
3737CLOSE job_killCursor
3738DEALLOCATE job_killCursor
3739SET NOCOUNT OFF
3740
3741GO
3742
3743
3744
3745
3746/*********************************************************************************************************************************************************************************************/
3747USE [UTILITY]
3748GO
3749/****** Object:  StoredProcedure [dbo].[proc_set_DBReadWrite]    Script Date: 3/21/2016 2:32:11 PM ******/
3750SET ANSI_NULLS ON
3751GO
3752SET QUOTED_IDENTIFIER ON
3753GO
3754ALTER PROCEDURE [dbo].[proc_set_DBReadWrite]
3755AS
3756
3757DECLARE c_Database CURSOR FAST_FORWARD FOR 
3758  SELECT CONVERT(VARCHAR(255), name)
3759    FROM Master..sysdatabases (NOLOCK)
3760   WHERE Name like 'ULTIPRO_%'
3761      OR Name like 'HRMS_%'
3762      OR Name = 'Supersite'
3763      OR Name = 'UES_MESSAGING'
3764DECLARE @v_DatabaseName     VARCHAR(255);
3765DECLARE @cmd varchar(300)
3766
3767BEGIN  --main
3768OPEN c_Database
3769FETCH NEXT FROM c_Database INTO @v_DatabaseName 
3770WHILE (@@fetch_status <> -1)
3771 begin  --while
3772  IF (@@fetch_status <> -2)
3773  begin
3774EXEC('use master ALTER DATABASE ' + @v_databasename + ' SET  READ_WRITE')
3775--print @cmd
3776  end  --if
3777  FETCH NEXT FROM c_Database INTO @v_DatabaseName
3778 end   --while
3779
3780--cleanup section
3781CLOSE c_Database
3782DEALLOCATE c_Database
3783END  --main
3784
3785GO
3786
3787
3788
3789
3790/*********************************************************************************************************************************************************************************************/
3791USE [UTILITY]
3792GO
3793/****** Object:  StoredProcedure [dbo].[proc_register_SQL_CLR_Objects]    Script Date: 3/21/2016 8:00:40 PM ******/
3794SET ANSI_NULLS ON
3795GO
3796SET QUOTED_IDENTIFIER ON
3797GO
3798ALTER PROCEDURE [dbo].[proc_register_SQL_CLR_Objects]
3799as 
3800BEGIN
3801DECLARE @cmd varchar(500)
3802DECLARE @cmptlvl varchar(3)
3803
3804select @cmptlvl = compatibility_level from sys.databases where name = 'HRMS_GLOBALDATA'
3805
3806SET @cmd =  'sqlcmd.exe  -S' + @@SERVERNAME + ' -dMASTER -q"EXEC Master.dbo.sp_dbcmptlevel @dbname=N''HRMS_GLOBALDATA'', @new_cmptlevel=''' + @cmptlvl + ''';"'
3807EXEC xp_cmdshell @cmd
3808
3809EXEC('ALTER DATABASE hrms_globaldata SET TRUSTWORTHY  ON')
3810END
3811BEGIN
3812DECLARE @v_Assembly SYSNAME,
3813       @v_DropSQL VARCHAR(MAX)
3814SET @v_Assembly = 'UltimateSoftware.SQLSecurityCLR%' 
3815SET @v_DropSQL = 'USE HRMS_GlobalData '
3816
3817SELECT @v_DropSQL = @v_DropSQL + 
3818                    '  IF OBJECT_ID(''dbo.[' + RTRIM(so.Name) + ']'') IS NOT NULL DROP ' +
3819                    CASE WHEN LEFT(so.XType, 1) = 'P' THEN 'PROCEDURE ' ELSE 'FUNCTION ' END +
3820                    'dbo.[' + RTRIM(so.Name) + ']' + CHAR(13) + CHAR(10)
3821  FROM HRMS_GlobalData.sys.assemblies sa WITH (NOLOCK)
3822  JOIN HRMS_GlobalData.sys.module_assembly_usages smau WITH (NOLOCK) ON sa.assembly_id = smau.assembly_id
3823  JOIN HRMS_GlobalData..sysobjects so WITH (NOLOCK) ON so.id = smau.[object_id]
3824  WHERE sa.name like @v_Assembly 
3825
3826SELECT @v_DropSQL = @v_DropSQL + 
3827                    'DROP ASSEMBLY [' + RTRIM(sa.Name) + ']' + CHAR(13) + CHAR(10)
3828  FROM HRMS_GlobalData.sys.assemblies sa WITH (NOLOCK)
3829  WHERE sa.name like @v_Assembly
3830  ORDER BY CASE WHEN sa.name LIKE '%XmlSerializers' THEN 0 ELSE 1 END
3831  
3832IF @v_DropSQL <> ''  EXEC(@v_DropSQL)
3833 
3834DECLARE @v_sqlpath VARCHAR(400)
3835DECLARE @instance VARCHAR(30)
3836DECLARE @path VARCHAR(200)
3837
3838IF (SELECT SERVERPROPERTY('instancename')) IS NULL 
3839BEGIN
3840  SET @instance = 'MSSQLSERVER'
3841  SET @path = 'SOFTWARE\MICROSOFT\MSSQLSERVER\SETUP'
3842
3843  EXEC master.dbo.xp_regread
3844         'HKEY_LOCAL_MACHINE',
3845         @path,
3846         'SQLPATH',
3847         @PARAM = @v_sqlpath OUTPUT
3848END
3849ELSE
3850BEGIN
3851  SET @instance = CAST(SERVERPROPERTY('instancename')AS VARCHAR(50))
3852  SET @path = 'SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\' + @instance + '\SETUP'
3853
3854  EXEC master.dbo.xp_regread
3855         'HKEY_LOCAL_MACHINE',
3856         @path,
3857         'SQLPATH',
3858         @PARAM = @v_sqlpath OUTPUT
3859END
3860 
3861DECLARE @PROCDLL VARCHAR(1000)
3862SET @PROCDLL =  @v_sqlpath + '\BINN\' + 'UltimateSoftware.SQLSecurityCLR.dll' 
3863EXEC('USE HRMS_GlobalData EXEC(''CREATE ASSEMBLY [UltimateSoftware.SQLSecurityCLR] FROM ''''' + @PROCDLL + ''''' WITH PERMISSION_SET = UNSAFE'')')
3864
3865EXEC('USE HRMS_GLOBALDATA
3866--EXEC(''CREATE PROCEDURE dbo.HRMS_RBS_Create_SQLLogins
3867--AS 
3868--EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.SecurityStoredProcedures].HRMS_RBS_Create_SQLLogins
3869--'')
3870
3871EXEC(''CREATE FUNCTION dbo.fn_SecPwdToRBSPwd(@SecCrypt NVARCHAR(100), @SecUserName NVARCHAR(100), @UserKey UNIQUEIDENTIFIER)
3872  RETURNS NVARCHAR(MAX)
3873AS
3874EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].ConvertSecPwdToRBSPwd
3875'')
3876
3877EXEC(''CREATE FUNCTION dbo.fn_SecHistPwdToRBSHistPwd(@SecCrypt NVARCHAR(100), @SecUserName NVARCHAR(100))
3878  RETURNS NVARCHAR(MAX)
3879AS
3880EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].ConvertSecHistPwdToRBSHistPwd
3881'')
3882
3883EXEC(''CREATE FUNCTION dbo.fn_SecSQLPwdToRBSSQLPwd(@SQLCrypt NVARCHAR(500), @LoginName NVARCHAR(500), @LoginType NVARCHAR(500))
3884  RETURNS NVARCHAR(MAX)
3885AS
3886EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].ConvertSecSQLPwdToRBSSQLPwd
3887'')
3888
3889EXEC(''CREATE FUNCTION dbo.fn_RBSEncryptDefPwd(@DefPwd NVARCHAR(500), @UserKey UNIQUEIDENTIFIER)
3890  RETURNS NVARCHAR(MAX)
3891AS
3892EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].EncryptDefaultPassword
3893'')
3894
3895EXEC(''CREATE FUNCTION dbo.fn_Encrypt(@InStr NVARCHAR(500), @Seed NVARCHAR(500))
3896  RETURNS NVARCHAR(MAX)
3897AS
3898EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].Encrypt
3899'')
3900
3901EXEC(''CREATE FUNCTION dbo.fn_RBSDecryptDefPwd(@DefPwd NVARCHAR(500), @UserKey UNIQUEIDENTIFIER)
3902  RETURNS NVARCHAR(MAX)
3903AS
3904EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].DecryptDefaultPassword
3905'')
3906GRANT EXECUTE ON fn_Encrypt TO  PUBLIC
3907GRANT EXECUTE ON fn_RBSDecryptDefPwd TO  PUBLIC
3908GRANT EXECUTE ON fn_RBSEncryptDefPwd TO  PUBLIC
3909GRANT EXECUTE ON fn_SecHistPwdToRBSHistPwd TO  PUBLIC
3910GRANT EXECUTE ON fn_SecSQLPwdToRBSSQLPwd TO  PUBLIC')
3911END  
3912    
3913--64 bit------------------------------------------------------------------------------------ 
3914IF (SELECT CASE WHEN CHARINDEX('64', CAST(SERVERPROPERTY('Edition') AS
3915VARCHAR(100))) > 0 THEN 64 ELSE 32 END) = 64 
3916BEGIN
3917EXEC('USE HRMS_GLOBALDATA
3918EXEC(''CREATE FUNCTION [dbo].[MakeKey12Single](@ServerID INT)
3919
3920RETURNS [nchar](12) WITH EXECUTE AS CALLER
3921AS 
3922EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[MakeKey12Single]'')
3923
3924EXEC(''CREATE FUNCTION [dbo].[CalcACheck](@SQLServer NVARCHAR(200), @DBName NVARCHAR(200), @GenNumber NCHAR(12), @DoLocking NCHAR(1), @IsSession NCHAR(1))
3925RETURNS INT WITH EXECUTE AS CALLER
3926AS 
3927EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[CalcACheck]'')
3928
3929
3930EXEC(''CREATE FUNCTION [dbo].[BCPOut](@SQLServer NVARCHAR(200), @SQLCommand NVARCHAR(200), @Options NVARCHAR(200), @FileName NVARCHAR(200))
3931RETURNS INT WITH EXECUTE AS CALLER
3932AS 
3933EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[BCPOut]'')
3934
3935
3936EXEC(''CREATE FUNCTION [dbo].[Drop_DPM_Message](@MsgParams NVARCHAR(2000))
3937RETURNS BIT WITH EXECUTE AS CALLER
3938AS 
3939EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[Drop_DPM_Message]'')
3940
3941
3942EXEC(''CREATE FUNCTION [dbo].[MakeKey12Table](@count [int], @Junk [nvarchar](4), @ServerID INT)
3943RETURNS  TABLE (
3944	[NewKey] [nchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
3945	[Junk] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
3946) WITH EXECUTE AS CALLER
3947AS
3948EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[MakeKey12Table]'')
3949
3950EXEC(''CREATE FUNCTION [dbo].[ParseKey12](@key [nchar](12))
3951RETURNS  TABLE (
3952	[KeyDate] [datetime] NULL,
3953	[KeyCount] [int] NULL,
3954	[KeyServerID] [int] NULL,
3955	[KeyOffset] [int] NULL
3956) WITH EXECUTE AS CALLER
3957AS
3958EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[ParseKey12]'')
3959
3960EXEC(''CREATE FUNCTION [dbo].[GetKey12Date](@key [nchar](12))
3961RETURNS DATETIME 
3962WITH EXECUTE AS CALLER
3963AS
3964EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[GetKey12Date]'')
3965
3966EXEC (''IF OBJECT_ID(N''''dbo.fn_GetTimedKey'''') IS NOT NULL
3967         DROP FUNCTION dbo.fn_GetTimedKey'') 
3968        
3969EXEC(''CREATE FUNCTION dbo.fn_GetTimedKey()
3970  RETURNS char(12)
3971AS
3972BEGIN
3973/*
3974{***
3975DECLARE @v_ID INT
3976EXEC master..xp_regread
3977   @rootkey = ''''HKEY_LOCAL_MACHINE'''',
3978   @key = ''''Software\US Group'''',
3979   @value_name = ''''ServerID'''',
3980   @value = @v_ID OUTPUT
3981IF @v_ID IS NULL
3982BEGIN
3983  SET @v_ID = FLOOR(RAND() * 100) + 1
3984  EXEC master..xp_regwrite
3985     @rootkey = ''''HKEY_LOCAL_MACHINE'''',
3986     @key = ''''Software\US Group'''',
3987     @value_name = ''''ServerID'''',
3988     @type = ''''REG_DWORD'''',
3989     @value = @v_ID
3990END
3991
3992SELECT ''''  DECLARE @ServerID INT'''' + CHAR(13) + CHAR(10) +
3993       ''''  SET @ServerID = '''' + CONVERT(VARCHAR(5), @v_ID)
3994***}
3995*/
3996  DECLARE @ServerID INT
3997  SET @ServerID = 8
3998  RETURN [HRMS_GlobalData].[dbo].[MakeKey12Single](@ServerID)
3999END'')
4000GRANT SELECT ON [dbo].[MakeKey12Table] TO [public]
4001GRANT EXECUTE ON [dbo].[MakeKey12Single] TO [public]
4002GRANT EXECUTE ON [dbo].[CalcACheck] TO [public]
4003GRANT SELECT ON [dbo].[ParseKey12] TO [public]
4004GRANT EXECUTE ON [dbo].[fn_GetTimedKey] TO [public]
4005
4006')
4007
4008END --global functions
4009
4010IF (SELECT CASE WHEN CHARINDEX('64', CAST(SERVERPROPERTY('Edition') AS
4011VARCHAR(100))) > 0 THEN 64 ELSE 32 END) = 64 
4012BEGIN
4013EXEC('USE MASTER
4014EXEC(''IF OBJECT_ID(N''''dbo.xp_usgcalccheck'''') IS NOT NULL '' + 
4015       ''  Drop Procedure dbo.xp_usgcalccheck'')
4016EXEC(''CREATE PROCEDURE xp_usgcalccheck
4017@p_GenNumber CHAR(12),
4018@p_SQLServer VARCHAR(200),
4019@p_DBName VARCHAR(200),
4020@p_DoLocking CHAR(1),
4021@p_IsSession CHAR(1)
4022AS
4023BEGIN
4024  DECLARE @v_Result INT
4025  SET @v_Result = HRMS_GlobalData.dbo.CalcACheck(@p_SQLServer, @p_DBName, @p_GenNumber, @p_DoLocking, @p_IsSession)
4026  IF @v_Result <> 1
4027  BEGIN
4028    DECLARE @v_MSG VARCHAR(1000)
4029    SET @v_MSG = ''''CalcCheck result=$'''' + CONVERT(VARCHAR(10), @v_Result)
4030    RAISERROR(@v_MSG, 1, 0)
4031  END
4032END
4033GRANT EXECUTE ON [dbo].[xp_usgcalccheck] TO [public]'')')
4034END
4035IF (SELECT CASE WHEN CHARINDEX('64', CAST(SERVERPROPERTY('Edition') AS
4036VARCHAR(100))) > 0 THEN 64 ELSE 32 END) =64 
4037BEGIN
4038EXEC('USE MASTER
4039EXEC(''IF OBJECT_ID(N''''dbo.xp_usg_gettimedkey_val'''') IS NOT NULL '' + 
4040       ''  Drop procedure dbo.xp_usg_gettimedkey_val'')
4041EXEC(''CREATE PROCEDURE xp_usg_gettimedkey_val 
4042@Key char(12) OUTPUT
4043AS
4044BEGIN
4045/*
4046{***
4047DECLARE @v_ID INT
4048EXEC master..xp_regread
4049   @rootkey = ''''HKEY_LOCAL_MACHINE'''',
4050   @key = ''''Software\US Group'''',
4051   @value_name = ''''ServerID'''',
4052   @value = @v_ID OUTPUT
4053IF @v_ID IS NULL
4054BEGIN
4055  SET @v_ID = FLOOR(RAND() * 100) + 1
4056  EXEC master..xp_regwrite
4057     @rootkey = ''''HKEY_LOCAL_MACHINE'''',
4058     @key = ''''Software\US Group'''',
4059     @value_name = ''''ServerID'''',
4060     @type = ''''REG_DWORD'''',
4061     @value = @v_ID
4062END
4063
4064SELECT ''''  DECLARE @ServerID INT'''' + CHAR(13) + CHAR(10) +
4065       ''''  SET @ServerID = '''' + CONVERT(VARCHAR(5), @v_ID)
4066***}
4067*/
4068  DECLARE @ServerID INT
4069  SET @ServerID = 8
4070  SET @Key = [HRMS_GlobalData].[dbo].[MakeKey12Single](@ServerID)
4071END'') ')
4072END
4073
4074IF (SELECT CASE WHEN CHARINDEX('64', CAST(SERVERPROPERTY('Edition') AS
4075VARCHAR(100))) > 0 THEN 64 ELSE 32 END) =64 
4076BEGIN
4077EXEC('USE MASTER
4078EXEC(''IF OBJECT_ID(N''''dbo.xp_usg_gettimedkey_res'''') IS NOT NULL '' + 
4079       ''  Drop procedure dbo.xp_usg_gettimedkey_res'')
4080EXEC(''CREATE PROCEDURE xp_usg_gettimedkey_res AS
4081BEGIN
4082/*
4083{***
4084DECLARE @v_ID INT
4085EXEC master..xp_regread
4086   @rootkey = ''''HKEY_LOCAL_MACHINE'''',
4087   @key = ''''Software\US Group'''',
4088   @value_name = ''''ServerID'''',
4089   @value = @v_ID OUTPUT
4090IF @v_ID IS NULL
4091BEGIN
4092  SET @v_ID = FLOOR(RAND() * 100) + 1
4093  EXEC master..xp_regwrite
4094     @rootkey = ''''HKEY_LOCAL_MACHINE'''',
4095     @key = ''''Software\US Group'''',
4096     @value_name = ''''ServerID'''',
4097     @type = ''''REG_DWORD'''',
4098     @value = @v_ID
4099END
4100
4101SELECT ''''  DECLARE @ServerID INT'''' + CHAR(13) + CHAR(10) +
4102       ''''  SET @ServerID = '''' + CONVERT(VARCHAR(5), @v_ID)
4103***}
4104*/
4105  DECLARE @ServerID INT
4106  SET @ServerID = 8
4107  SELECT CONVERT(CHAR(12), [HRMS_GlobalData].[dbo].[MakeKey12Single](@ServerID))
4108END'')')
4109END
4110
4111IF (SELECT CASE WHEN CHARINDEX('64', CAST(SERVERPROPERTY('Edition') AS
4112VARCHAR(100))) > 0 THEN 64 ELSE 32 END) =64 
4113BEGIN
4114EXEC(' USE MASTER
4115EXEC(''IF OBJECT_ID(N''''dbo.xp_usg_gettimedkey_mtp'''') IS NOT NULL '' + 
4116       ''  Drop procedure dbo.xp_usg_gettimedkey_mtp'')
4117EXEC(''CREATE PROCEDURE xp_usg_gettimedkey_mtp
4118@Count int,
4119@Junk int
4120AS
4121BEGIN
4122/*
4123{***
4124DECLARE @v_ID INT
4125EXEC master..xp_regread
4126   @rootkey = ''''HKEY_LOCAL_MACHINE'''',
4127   @key = ''''Software\US Group'''',
4128   @value_name = ''''ServerID'''',
4129   @value = @v_ID OUTPUT
4130IF @v_ID IS NULL
4131BEGIN
4132  SET @v_ID = FLOOR(RAND() * 100) + 1
4133  EXEC master..xp_regwrite
4134     @rootkey = ''''HKEY_LOCAL_MACHINE'''',
4135     @key = ''''Software\US Group'''',
4136     @value_name = ''''ServerID'''',
4137     @type = ''''REG_DWORD'''',
4138     @value = @v_ID
4139END
4140
4141SELECT ''''  DECLARE @ServerID INT'''' + CHAR(13) + CHAR(10) +
4142       ''''  SET @ServerID = '''' + CONVERT(VARCHAR(5), @v_ID)
4143***}
4144*/
4145  DECLARE @ServerID INT
4146  SET @ServerID = 8
4147  SELECT NewKey AS NewTimedKey, CONVERT(INT, Junk) AS SPid
4148  FROM [HRMS_GlobalData].[dbo].[MakeKey12Table](@Count, @Junk, @ServerID)
4149END'')
4150
4151GRANT EXECUTE ON [dbo].[xp_usg_gettimedkey_mtp] TO [public]
4152GRANT EXECUTE ON [dbo].[xp_usg_gettimedkey_res] TO [public]
4153GRANT EXECUTE ON [dbo].[xp_usg_gettimedkey_val] TO [public]')
4154END
4155--end master functions
4156
4157GO
4158
4159USE [UTILITY]
4160GO
4161
4162IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_register_SQL_CLR_Objects_2018R2]') AND type in (N'P', N'PC'))
4163DROP PROCEDURE [dbo].[proc_register_SQL_CLR_Objects_2018R2]
4164GO
4165
4166/****** Object:  StoredProcedure [dbo].[proc_register_SQL_CLR_Objects_2018R2]    Script Date: 10/27/2018 12:05:52 PM ******/
4167SET ANSI_NULLS ON
4168GO
4169
4170SET QUOTED_IDENTIFIER ON
4171GO
4172
4173CREATE PROCEDURE [dbo].[proc_register_SQL_CLR_Objects_2018R2]
4174as 
4175BEGIN
4176EXEC('USE master
4177IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4178BEGIN
4179  DECLARE
4180    @sid varbinary(85),
4181    @user nvarchar(256),
4182    @v_String nvarchar(2000)
4183  SET @sid = (SELECT sid FROM sys.sysdatabases WHERE name = ''master'');
4184  SET @user = (SELECT SUSER_SNAME(@sid));
4185  SELECT @v_String = ''ALTER AUTHORIZATION ON DATABASE::HRMS_GLOBALDATA TO '' + CAST ( @user AS VARCHAR(100) )
4186  
4187  EXEC sp_executesql @v_String
4188END
4189
4190IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4191BEGIN
4192  EXEC sp_configure ''clr enabled'', 1
4193  RECONFIGURE WITH OVERRIDE      
4194
4195  ALTER DATABASE HRMS_GLOBALDATA SET TRUSTWORTHY ON
4196END
4197
4198IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4199BEGIN
4200  IF (CHARINDEX(''(64-bit)'', @@VERSION) > 0)
4201  BEGIN 
4202    IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[xp_usgcalccheck]'') AND OBJECTPROPERTY(id,N''IsExtendedProc'') = 1)
4203      EXEC dbo.sp_dropextendedproc N''xp_usgcalccheck''
4204    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[xp_usgcalccheck]'') AND type in (N''P'', N''PC''))
4205      DROP PROCEDURE [dbo].[xp_usgcalccheck]
4206    IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[xp_usg_gettimedkey_val]'') AND OBJECTPROPERTY(id,N''IsExtendedProc'') = 1)
4207      EXEC dbo.sp_dropextendedproc N''xp_usg_gettimedkey_val''
4208    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[xp_usg_gettimedkey_val]'') AND type in (N''P'', N''PC''))
4209      DROP PROCEDURE [dbo].[xp_usg_gettimedkey_val]
4210    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[xp_usg_gettimedkey_res]'') AND type in (N''P'', N''PC''))
4211      DROP PROCEDURE [dbo].[xp_usg_gettimedkey_res]
4212    IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[xp_usg_gettimedkey_res]'') AND OBJECTPROPERTY(id,N''IsExtendedProc'') = 1)
4213      EXEC dbo.sp_dropextendedproc N''xp_usg_gettimedkey_res''
4214    IF OBJECT_ID(''master.dbo.xp_usg_gettimedkey_mtp'', ''X'') IS NOT NULL 
4215      EXEC sp_dropextendedproc ''master.dbo.xp_usg_gettimedkey_mtp''
4216    IF OBJECT_ID(''master.ULTIKEYS.xp_usg_gettimedkey_mtp'', ''X'') IS NOT NULL 
4217      EXEC sp_dropextendedproc ''master.ULTIKEYS.xp_usg_gettimedkey_mtp''
4218    IF OBJECT_ID(''master.dbo.xp_usg_gettimedkey_mtp'', ''P'') IS NOT NULL 
4219      DROP PROCEDURE dbo.xp_usg_gettimedkey_mtp
4220  END
4221END')
4222
4223
4224EXEC('USE HRMS_GLOBALDATA
4225IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4226BEGIN
4227
4228  /* Dynamically drop all assemblies in the security clr */
4229  IF (CHARINDEX(''(64-bit)'', @@VERSION) > 0)
4230  BEGIN 
4231    IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[fn_GetTimedKey]''))
4232      DROP FUNCTION [dbo].[fn_GetTimedKey]
4233  END
4234
4235  DECLARE @v_Assembly SYSNAME, 
4236          @v_DropSQL VARCHAR(MAX) 
4237  SET @v_Assembly = ''UltimateSoftware.SQLSecurityCLR'' 
4238  SET @v_DropSQL = '''' 
4239  SELECT @v_DropSQL = @v_DropSQL + 
4240                      ''DROP '' + 
4241                      CASE WHEN LEFT(so.XType, 1) = ''P'' THEN ''PROCEDURE '' ELSE ''FUNCTION '' END + 
4242                      ''dbo.['' + RTRIM(so.Name) + '']'' + CHAR(13) + CHAR(10) 
4243    FROM sys.assemblies sa WITH (NOLOCK) 
4244    JOIN sys.module_assembly_usages smau WITH (NOLOCK) ON sa.assembly_id = smau.assembly_id 
4245    JOIN sysobjects so WITH (NOLOCK) ON so.id = smau.[object_id] 
4246    WHERE sa.name LIKE @v_Assembly + ''%''
4247
4248  SELECT @v_DropSQL = @v_DropSQL + 
4249                      ''DROP ASSEMBLY ['' + RTRIM(sa.Name) + '']'' + CHAR(13) + CHAR(10) 
4250    FROM sys.assemblies sa WITH (NOLOCK) 
4251    WHERE sa.name LIKE @v_Assembly + ''%''
4252    ORDER BY CASE WHEN sa.name LIKE ''%XmlSerializers'' THEN 0 ELSE 1 END
4253
4254  IF @v_DropSQL <> '''' EXEC(@v_DropSQL)  
4255END
4256
4257IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4258BEGIN
4259  /* Find the SQL Binn path */
4260  DECLARE 
4261    @Path VARCHAR(255),
4262    @RegistryKey VARCHAR(255),
4263    @InstanceName VARCHAR(50)
4264  
4265  IF (SELECT SERVERPROPERTY(''instancename'')) IS NULL 
4266  BEGIN
4267    SET @instanceName = ''MSSQLSERVER''
4268    SET @RegistryKey = ''SOFTWARE\MICROSOFT\MSSQLSERVER\SETUP''
4269  
4270    EXEC master.dbo.xp_regread ''HKEY_LOCAL_MACHINE'',@RegistryKey,''SQLPATH'', @Path OUTPUT
4271  END
4272  ELSE
4273  BEGIN
4274    SET @instanceName = CAST(SERVERPROPERTY(''instancename'')AS VARCHAR(50))
4275    SELECT @RegistryKey = ''SOFTWARE\Microsoft\Microsoft SQL Server\'' + @InstanceName + ''\Setup''
4276  
4277    EXEC master..xp_regread ''HKEY_LOCAL_MACHINE'', @RegistryKey, ''SQLPath'', @Path OUTPUT
4278  END
4279  
4280  SELECT @Path = @Path + ''\Binn''
4281  
4282  IF (CHARINDEX(''(64-bit)'', @@VERSION) > 0)
4283  BEGIN 
4284  /* AUTHORIZATION [dbo] */
4285    CREATE ASSEMBLY [UltimateSoftware.SQLSecurityCLR] FROM @Path + ''\UltimateSoftware.SQLSecurityCLR.dll'' WITH PERMISSION_SET = UNSAFE /* Required for thread handling */
4286    CREATE ASSEMBLY [UltimateSoftware.SQLSecurityCLR.XmlSerializers] FROM @Path + ''\UltimateSoftware.SQLSecurityCLR.XmlSerializers.dll'' WITH PERMISSION_SET = SAFE
4287  END
4288  ELSE
4289  BEGIN 
4290    CREATE ASSEMBLY [UltimateSoftware.SQLSecurityCLR] FROM @Path + ''\UltimateSoftware.SQLSecurityCLR.dll'' WITH PERMISSION_SET = SAFE
4291  END
4292END
4293
4294DECLARE @SQL NVARCHAR(MAX) = ''''; 
4295
4296IF EXISTS (SELECT 1 FROM sys.databases WHERE name = ''HRMS_SITE'')
4297BEGIN
4298    SET @SQL = ''
4299        IF NOT EXISTS ( SELECT 1 FROM [HRMS_SITE].[sys].[objects] WHERE [name] = ''''TimedKeyServerID'''' )
4300        BEGIN
4301
4302            CREATE TABLE [HRMS_SITE].dbo.TimedKeyServerID 
4303            (
4304                ServerName VARCHAR(50) NOT NULL,
4305                ServerID INT IDENTITY(1,1) NOT NULL
4306            );
4307
4308            ALTER TABLE [HRMS_SITE].dbo.TimedKeyServerID  ADD  CONSTRAINT [pk_TimedKeyServerID] PRIMARY KEY CLUSTERED 
4309            (
4310                ServerName ASC
4311            );
4312
4313            CREATE UNIQUE NONCLUSTERED INDEX UDX_TimedKeyServerID_ServerID ON [HRMS_SITE].dbo.TimedKeyServerID ( ServerID );
4314
4315        END'';
4316END
4317
4318IF @SQL <> '''' EXEC sp_executesql @SQL; 
4319
4320IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4321BEGIN
4322EXEC(''CREATE FUNCTION [dbo].[CalcCheckUes](@SQLServer [nvarchar](200), @DBName [nvarchar](200), @GenNumber [nchar](12), @DoLocking [nchar](1), @AppServer [nvarchar](50), @ApiKey [nvarchar](50), @ClientId [nvarchar](50), @IsSessionId [nchar](1))
4323RETURNS [int] WITH EXECUTE AS CALLER
4324AS 
4325EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[CalcCheckUes]
4326'')
4327END
4328
4329
4330IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4331BEGIN
4332EXEC(''CREATE FUNCTION dbo.fn_SecPwdToRBSPwd(@SecCrypt NVARCHAR(100), @SecUserName NVARCHAR(100), @UserKey UNIQUEIDENTIFIER) 
4333  RETURNS NVARCHAR(MAX) 
4334AS EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].ConvertSecPwdToRBSPwd 
4335'')
4336END
4337
4338IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4339BEGIN
4340EXEC(''CREATE FUNCTION dbo.fn_SecHistPwdToRBSHistPwd(@SecCrypt NVARCHAR(100), @SecUserName NVARCHAR(100)) 
4341  RETURNS NVARCHAR(MAX) 
4342AS EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].ConvertSecHistPwdToRBSHistPwd 
4343'')
4344END
4345
4346IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4347BEGIN
4348EXEC(''CREATE FUNCTION dbo.fn_SecSQLPwdToRBSSQLPwd(@SQLCrypt NVARCHAR(500), @LoginName NVARCHAR(500), @LoginType NVARCHAR(500)) 
4349  RETURNS NVARCHAR(MAX) 
4350AS EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].ConvertSecSQLPwdToRBSSQLPwd 
4351'')
4352END
4353
4354IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4355BEGIN
4356EXEC(''CREATE FUNCTION dbo.fn_RBSEncryptDefPwd(@DefPwd NVARCHAR(500), @UserKey UNIQUEIDENTIFIER) 
4357  RETURNS NVARCHAR(MAX) 
4358AS EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].EncryptDefaultPassword 
4359'')
4360END
4361
4362
4363IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4364BEGIN
4365EXEC(''CREATE FUNCTION dbo.fn_Encrypt(@InStr NVARCHAR(500), @Seed NVARCHAR(500)) 
4366  RETURNS NVARCHAR(MAX) 
4367AS EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].Encrypt 
4368'')
4369END
4370
4371
4372IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4373BEGIN
4374EXEC(''CREATE FUNCTION dbo.fn_RBSDecryptDefPwd(@DefPwd NVARCHAR(500), @UserKey UNIQUEIDENTIFIER) 
4375  RETURNS NVARCHAR(MAX) 
4376AS EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SQLSecurityCLR.USRBSEncryption].DecryptDefaultPassword 
4377'')
4378END
4379
4380IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4381BEGIN
4382  IF (CHARINDEX(''(64-bit)'', @@VERSION) > 0)
4383  BEGIN 
4384    EXEC (''
4385CREATE FUNCTION [dbo].[fn_Decompress](@StrBuffer NVARCHAR(MAX))
4386RETURNS NVARCHAR(MAX) WITH EXECUTE AS CALLER
4387AS 
4388EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USCompress.CompressUserDefinedFunctions].[Decompress]
4389'') 
4390    EXEC(''
4391CREATE FUNCTION [dbo].[DecompressUpdateCompress](@Row [NVARCHAR](MAX))
4392RETURNS [NVARCHAR](MAX) WITH EXECUTE AS CALLER
4393AS 
4394EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USCompress.CompressUserDefinedFunctions].[DecompressUpdateCompress]
4395'')
4396    EXEC(''
4397CREATE FUNCTION [dbo].[MakeKey12Single](@ServerID INT)
4398RETURNS [nchar](12) WITH EXECUTE AS CALLER
4399AS 
4400EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[MakeKey12Single]
4401'')
4402
4403    EXEC(''
4404CREATE FUNCTION [dbo].[CalcACheck](@SQLServer NVARCHAR(200), @DBName NVARCHAR(200), @GenNumber NCHAR(12), @DoLocking NCHAR(1), @IsSession NCHAR(1))
4405RETURNS INT WITH EXECUTE AS CALLER
4406AS 
4407EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[CalcACheck]
4408'')
4409
4410    EXEC(''
4411CREATE FUNCTION [dbo].[BCPOut](@SQLServer NVARCHAR(200), @SQLCommand NVARCHAR(200), @Options NVARCHAR(200), @FileName NVARCHAR(200))
4412RETURNS INT WITH EXECUTE AS CALLER
4413AS 
4414EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[BCPOut]
4415'')
4416
4417    EXEC(''
4418CREATE FUNCTION [dbo].[Drop_DPM_Message](@MsgParams NVARCHAR(2000))
4419RETURNS BIT WITH EXECUTE AS CALLER
4420AS 
4421EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[Drop_DPM_Message]
4422'')
4423
4424    EXEC(''
4425CREATE FUNCTION [dbo].[MakeKey12Table](@count [int], @Junk [nvarchar](4), @ServerID INT)
4426RETURNS  TABLE (
4427    [NewKey] [nchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
4428    [Junk] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
4429) WITH EXECUTE AS CALLER
4430AS
4431EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[MakeKey12Table]
4432'')
4433
4434    EXEC(''
4435CREATE FUNCTION [dbo].[ParseKey12](@key [nchar](12))
4436RETURNS  TABLE (
4437    [KeyDate] [datetime] NULL,
4438    [KeyCount] [int] NULL,
4439    [KeyServerID] [int] NULL,
4440    [KeyOffset] [int] NULL
4441) WITH EXECUTE AS CALLER
4442AS
4443EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[ParseKey12]
4444'')
4445
4446    EXEC(''
4447CREATE FUNCTION [dbo].[GetKey12Date](@key [nchar](12))
4448RETURNS DATETIME 
4449WITH EXECUTE AS CALLER
4450AS
4451EXTERNAL NAME [UltimateSoftware.SQLSecurityCLR].[UltimateSoftware.SqlServer.USGXPReplacements.UserDefinedFunctions].[GetKey12Date]
4452'')
4453
4454    DECLARE @v_SQL VARCHAR(MAX),
4455            @ServerID INT /*, 
4456            @SiteServerPrefix VARCHAR(50) = HRMS_GLOBALDATA.dbo.fn_SiteServer();
4457    IF @SiteServerPrefix = ''SITE_SERVER.''
4458    BEGIN
4459        SELECT @ServerID = ( ServerID % 1296 ) FROM SITE_SERVER.HRMS_SITE.dbo.TimedKeyServerID WHERE ServerName = LTRIM(RTRIM(@@SERVERNAME));
4460        
4461        IF @ServerID IS NULL 
4462        BEGIN
4463            INSERT INTO SITE_SERVER.HRMS_SITE.dbo.TimedKeyServerID ( ServerName ) VALUES ( LTRIM(RTRIM(@@SERVERNAME)) );
4464            SELECT @ServerID = ( ServerID % 1296 ) FROM SITE_SERVER.HRMS_SITE.dbo.TimedKeyServerID WHERE ServerName = LTRIM(RTRIM(@@SERVERNAME));
4465        END
4466    END
4467    ELSE
4468    BEGIN
4469        SELECT @ServerID = ( ServerID % 1296 ) FROM HRMS_SITE.dbo.TimedKeyServerID WHERE ServerName =LTRIM(RTRIM(@@SERVERNAME)); 
4470
4471        IF @ServerID IS NULL
4472        BEGIN 
4473            INSERT INTO HRMS_SITE.dbo.TimedKeyServerID ( ServerName ) VALUES ( LTRIM(RTRIM(@@SERVERNAME)) );
4474            SELECT @ServerID = ( ServerID % 1296 ) FROM HRMS_SITE.dbo.TimedKeyServerID WHERE ServerName = LTRIM(RTRIM(@@SERVERNAME));
4475        END
4476    END
4477*/
4478    SELECT @ServerID = ( ServerID % 1296 ) FROM SITE_SERVER.HRMS_SITE.dbo.TimedKeyServerID WHERE ServerName =LTRIM(RTRIM(@@SERVERNAME)); 
4479
4480    IF @ServerID IS NULL
4481    BEGIN 
4482        INSERT INTO SITE_SERVER.HRMS_SITE.dbo.TimedKeyServerID ( ServerName ) VALUES ( LTRIM(RTRIM(@@SERVERNAME)) );
4483        SELECT @ServerID = ( ServerID % 1296 ) FROM SITE_SERVER.HRMS_SITE.dbo.TimedKeyServerID WHERE ServerName = LTRIM(RTRIM(@@SERVERNAME));
4484    END
4485
4486
4487    SET @v_SQL = ''
4488CREATE FUNCTION dbo.fn_GetTimedKey()
4489  RETURNS char(12)
4490AS
4491BEGIN
4492  DECLARE @ServerID INT
4493  SET @ServerID = '' + CONVERT(VARCHAR(10), ISNULL(@ServerID,20)) + ''
4494  RETURN [HRMS_GlobalData].[dbo].[MakeKey12Single](@ServerID)
4495END
4496''
4497    EXEC(@v_SQL)
4498
4499    DECLARE @v_Path VARCHAR(500)
4500    EXEC master..xp_regread 
4501       @rootkey = ''HKEY_LOCAL_MACHINE'', 
4502       @key = ''Software\US Group'', 
4503       @value_name = ''FileDirectory'', 
4504       @value = @v_Path OUTPUT 
4505
4506    IF @v_Path IS NULL
4507      EXEC master..xp_regread
4508         @rootkey = ''HKEY_LOCAL_MACHINE'',
4509         @key = ''Software\Wow6432Node\US Group'',
4510         @value_name = ''FileDirectory'',
4511         @value = @v_Path OUTPUT 
4512
4513    SET @v_Path = RTRIM(@v_Path) 
4514    IF @v_Path IS NULL 
4515      SET @v_Path = ''**NO PATH IN REGISTRY**'' 
4516    ELSE 
4517      IF RIGHT(@v_Path, 1) <> ''\'' SET @v_Path = @v_Path + ''\'' 
4518
4519    IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(''dbo.HRMS_bulk_getdefaultpath'') AND sysstat & 0xf = 4)
4520      DROP PROCEDURE dbo.HRMS_bulk_getdefaultpath
4521
4522    SET @v_SQL = ''
4523CREATE PROCEDURE dbo.HRMS_Bulk_GetDefaultPath
4524  @defaultpath VARCHAR(128) OUTPUT
4525AS
4526SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
4527BEGIN
4528  SET NOCOUNT ON
4529  SET @defaultpath = '''''' + @v_Path + ''''''
4530END
4531''
4532    EXEC(@v_SQL)
4533
4534    GRANT SELECT ON [dbo].[MakeKey12Table] TO [public]
4535    GRANT EXECUTE ON [dbo].[MakeKey12Single] TO [public]
4536    GRANT EXECUTE ON [dbo].[CalcACheck] TO [public]
4537    GRANT SELECT ON [dbo].[ParseKey12] TO [public]
4538    GRANT EXECUTE ON [dbo].[fn_GetTimedKey] TO [public]
4539    GRANT EXECUTE ON dbo.HRMS_Bulk_GetDefaultPath TO public
4540    GRANT EXECUTE ON [dbo].[DecompressUpdateCompress] TO [public]
4541  END
4542END')
4543
4544EXEC('USE Master
4545
4546IF EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name IN (''HRMS_Site'', ''ULTIPRO_System''))
4547BEGIN
4548  IF (CHARINDEX(''(64-bit)'', @@VERSION) > 0)
4549  BEGIN 
4550    EXEC(''
4551CREATE PROCEDURE [dbo].[xp_usgcalccheck]
4552@p_GenNumber CHAR(12),
4553@p_SQLServer VARCHAR(200),
4554@p_DBName VARCHAR(200),
4555@p_DoLocking CHAR(1),
4556@p_IsSession CHAR(1),
4557@p_AppServer VARCHAR(50) = NULL,
4558@p_ApiKey VARCHAR(50) = NULL,
4559@p_ClientId VARCHAR(50) = NULL,
4560@p_UesCalcApiEnabled BIT = 0
4561AS
4562BEGIN
4563  DECLARE @v_Result INT
4564  IF @p_UesCalcApiEnabled = 1
4565  BEGIN
4566    SET @v_Result = HRMS_GLOBALDATA.dbo.CalcCheckUes(@p_SQLServer, @p_DBName, @p_GenNumber, @p_DoLocking, @p_AppServer, @p_ApiKey, @p_ClientId, @p_IsSession)
4567  END
4568  ELSE
4569  BEGIN
4570    SET @v_Result = HRMS_GlobalData.dbo.CalcACheck(@p_SQLServer, @p_DBName, @p_GenNumber, @p_DoLocking, @p_IsSession)
4571  END
4572  IF @v_Result <> 1
4573  BEGIN
4574    DECLARE @v_MSG VARCHAR(1000)
4575    SET @v_MSG = ''''CalcCheck result=$'''' + CONVERT(VARCHAR(10), @v_Result)
4576    RAISERROR(@v_MSG, 1, 0)
4577  END
4578END
4579'')
4580
4581    DECLARE @v_SQL VARCHAR(MAX),
4582            @ServerID INT
4583
4584    SELECT @ServerID = ( ServerID % 1296 ) FROM SITE_SERVER.HRMS_SITE.dbo.TimedKeyServerID WHERE ServerName =LTRIM(RTRIM(@@SERVERNAME)); 
4585
4586    IF @ServerID IS NULL
4587    BEGIN 
4588        INSERT INTO SITE_SERVER.HRMS_SITE.dbo.TimedKeyServerID ( ServerName ) VALUES ( LTRIM(RTRIM(@@SERVERNAME)) );
4589        SELECT @ServerID = ( ServerID % 1296 ) FROM SITE_SERVER.HRMS_SITE.dbo.TimedKeyServerID WHERE ServerName = LTRIM(RTRIM(@@SERVERNAME));
4590    END
4591
4592    SET @v_SQL = ''
4593CREATE PROCEDURE xp_usg_gettimedkey_val 
4594@Key char(12) OUTPUT
4595AS
4596BEGIN
4597  DECLARE @ServerID INT
4598  SET @ServerID = '' + CONVERT(VARCHAR(10), ISNULL(@ServerID, 20)) + ''
4599  SET @Key = [HRMS_GlobalData].[dbo].[MakeKey12Single](@ServerID)
4600END
4601''
4602    EXEC(@v_SQL)
4603
4604    SET @v_SQL = ''
4605CREATE PROCEDURE xp_usg_gettimedkey_res AS
4606BEGIN
4607  DECLARE @ServerID INT
4608  SET @ServerID = '' + CONVERT(VARCHAR(10), ISNULL(@ServerID, 20)) + ''
4609  SELECT CONVERT(CHAR(12), [HRMS_GlobalData].[dbo].[MakeKey12Single](@ServerID))
4610END
4611''
4612    EXEC(@v_SQL)
4613
4614    SET @v_SQL = ''
4615CREATE PROCEDURE xp_usg_gettimedkey_mtp
4616@Count int,
4617@Junk int
4618AS
4619BEGIN
4620  DECLARE @ServerID INT
4621  SET @ServerID = '' + CONVERT(VARCHAR(10), ISNULL(@ServerID, 20)) + ''
4622  SELECT NewKey AS NewTimedKey, CONVERT(INT, Junk) AS SPid FROM [HRMS_GlobalData].[dbo].[MakeKey12Table](@Count, @Junk, @ServerID)
4623END
4624''
4625    EXEC(@v_SQL)
4626
4627    GRANT EXECUTE ON [dbo].[xp_usg_gettimedkey_mtp] TO [public]
4628    GRANT EXECUTE ON [dbo].[xp_usg_gettimedkey_res] TO [public]
4629    GRANT EXECUTE ON [dbo].[xp_usg_gettimedkey_val] TO [public]
4630    GRANT EXECUTE ON [dbo].[xp_usgcalccheck] TO [public]
4631  END
4632END')
4633
4634END
4635GO
4636
4637
4638/****** Object:  StoredProcedure [dbo].[proc_restore_baselineSLC]    Script Date: 08/20/2014 14:55:02 ******/
4639USE [UTILITY]
4640GO
4641
4642/****** Object:  StoredProcedure [dbo].[proc_restore_baselineSLC]    Script Date: 1/3/2019 5:18:16 PM ******/
4643SET ANSI_NULLS ON
4644GO
4645
4646SET QUOTED_IDENTIFIER ON
4647GO
4648
4649ALTER PROCEDURE [dbo].[proc_restore_baselineSLC] @Buildno INT
4650	,@RestoreType VARCHAR(25) = 'latest' --default to latest (could be 'latest','full', or, 'full+diff') 
4651AS
4652DECLARE @v_dbname VARCHAR(25)
4653	,@v_dbnameR VARCHAR(25)
4654	,@v_dbnameS VARCHAR(25)
4655	,@v_bakloc VARCHAR(255)
4656	,@v_baklocdiff VARCHAR(255)
4657	,@v_data VARCHAR(100)
4658	,@v_log VARCHAR(100)
4659	,@v_datapath VARCHAR(255)
4660	,@v_Logpath VARCHAR(255)
4661	,@v_restore VARCHAR(4000)
4662	,@v_restorediff VARCHAR(2000)
4663	,@v_restoreencrypt VARCHAR(2000)
4664	,@v_restorediffencrypt VARCHAR(2000)
4665	,@code VARBINARY(256)
4666	,@pass VARCHAR(50)
4667	,@exitcode INT
4668	,@sqlerrorcode INT
4669	,@statement VARCHAR(2000)
4670	,@dropcmd VARCHAR(255)
4671	,@filenameloc VARCHAR(2000)
4672	,@v_ERR INT
4673	,@v_mult BIT
4674	,@v_dbid INT
4675	,@CMD NVARCHAR(1000)
4676	,@ext CHAR(3)
4677	,@option VARCHAR(30)
4678	,@codeACS VARBINARY(256)
4679	,@passACS VARCHAR(50)
4680	,@passCurrent VARCHAR(50)
4681
4682SET @code = 0x010000006FACDDFA5C63D41016D52845EA0ABB74ED9B247E49A63B9FC8E7BC1152DC7EDD3CA3A7900BD993B8
4683SET @pass = cast(DECRYPTBYPASSPHRASE('CTE', @code) AS VARCHAR)
4684SET @codeACS = 0x01000000F9BD39D5234140B0755FB27BB0FA81D0567BB5CB4E06C1F9D6235F1378BF5A2D
4685SET @passACS = cast(DECRYPTBYPASSPHRASE('CTE', @codeACS) AS VARCHAR)
4686SET @passCurrent = @pass
4687
4688/* close cursor if open */
4689IF (
4690		CURSOR_STATUS('global', 'c_db') >= 0 /* cursor is open */
4691		OR CURSOR_STATUS('local', 'c_db') >= 0
4692		)
4693	CLOSE c_db
4694
4695IF (
4696		CURSOR_STATUS('global', 'c_db') = - 1 /* cursor is closed */
4697		OR CURSOR_STATUS('local', 'c_db') = - 1
4698		)
4699	DEALLOCATE c_db
4700
4701--Disable Processing Audit during restore
4702DECLARE @jobid UNIQUEIDENTIFIER
4703DECLARE @retval INT
4704
4705SELECT @jobid = job_id
4706FROM msdb..sysjobs
4707WHERE name = 'Process Audit Data'
4708
4709IF @jobid IS NOT NULL
4710BEGIN
4711	EXECUTE proc_get_SQL_job_execution_status @job_name = 'Process Audit Data'
4712		,@execution_status = @retval OUTPUT
4713
4714	IF (@retval = 1)
4715		EXEC msdb.dbo.sp_stop_job @job_id = @jobid
4716
4717	EXEC msdb.dbo.sp_update_job @job_id = @jobid
4718		,@enabled = 0
4719
4720	PRINT '*Process Audit Data* job disabled'
4721END
4722
4723--Check if FlexClone
4724IF (
4725		SELECT isFlexClone
4726		FROM UTILITY.dbo.TeamEnv
4727		WHERE teamid = UTILITY.dbo.fn_TeamEnvid(CONVERT(VARCHAR(50), serverproperty('servername')))
4728		) = 1
4729BEGIN
4730	SELECT '*** FlexClone setup - attaching databases if necessary'
4731
4732	EXEC UTILITY.dbo.proc_attach_Envdatabases
4733
4734	SELECT '*** FlexClone setup - Restoring NonFlexclone databases if necessary'
4735
4736	EXEC UTILITY.dbo.proc_restore_Otherbaseline
4737END
4738ELSE
4739BEGIN
4740	SET @v_dbnameR = ''
4741
4742	DECLARE c_db CURSOR
4743	FOR
4744	SELECT DISTINCT GroupNo AS dbid
4745		,dbname
4746		,backuploc
4747		,dataname
4748		,[logname]
4749		,datapath
4750		,logpath
4751		,HasMultipleFileGroups
4752	FROM [UTILITY].[dbo].[dbbackupEXTlocs] /* WHERE buildno = @Buildno */
4753	WHERE dbbackupEXTlocs.SERVER = @@SERVERNAME
4754		AND (
4755			backuploc LIKE '%_FULL%'
4756			OR backuploc LIKE '%_MONTHLY%'
4757			OR backuploc LIKE '%.bak%'
4758			) /*if backup has 'full' in the name otherwise remove this */
4759	ORDER BY dbname
4760
4761	OPEN c_db
4762
4763	FETCH NEXT
4764	FROM c_db
4765	INTO @v_dbid
4766		,@v_dbname
4767		,@v_bakloc
4768		,@v_data
4769		,@v_log
4770		,@v_Datapath
4771		,@v_Logpath
4772		,@v_mult
4773
4774	WHILE (@@FETCH_STATUS <> - 1)
4775	BEGIN
4776		/*Find the type of backup */
4777		SET @ext = SUBSTRING(@v_bakloc, LEN(@v_bakloc) - 2, LEN(@v_bakloc))
4778		/*Remove leading and trailing spaces if any*/
4779		SET @v_dbname = RTRIM(LTRIM(@v_dbname))
4780
4781		--PRINT @EXT 
4782		IF EXISTS (
4783				SELECT *
4784				FROM MASTER..SYSDATABASES
4785				WHERE NAME = @v_dbname
4786					AND (
4787						(MODE <> 0)
4788						OR (STATUS = 40)
4789						)
4790				)
4791		BEGIN
4792			PRINT 'Dropping the database stuck in restore mode'
4793
4794			SET @dropcmd = 'USE MASTER
4795                        DROP DATABASE [' + @v_dbname + ']'
4796
4797			EXEC (@dropcmd)
4798		END
4799
4800		IF @v_dbname LIKE 'UTA_%'
4801		BEGIN
4802			SET @CMD = 'EXEC master.dbo.xp_cmdshell ''MKDIR ' + @v_Datapath + @v_dbname + ''''
4803
4804			--PRINT @CMD
4805			EXEC sp_executesql @CMD
4806
4807			IF (@v_Datapath <> @v_Logpath)
4808			BEGIN
4809				SET @CMD = 'EXEC master.dbo.xp_cmdshell ''MKDIR ' + @v_Logpath + @v_dbname + ''''
4810
4811				--PRINT @CMD
4812				EXEC sp_executesql @CMD
4813
4814				SET @v_Logpath = @v_Logpath + @v_dbname + '\'
4815			END
4816			ELSE
4817			BEGIN
4818				SET @v_Logpath = @v_Datapath + @v_dbname + '\'
4819			END
4820
4821			SET @v_Datapath = @v_Datapath + @v_dbname + '\'
4822		END
4823
4824		IF (@v_mult = 0)
4825		BEGIN
4826			IF @ext = 'BAK'
4827				SET @v_restore = 'RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + '''' + ' WITH STATS = 20 , REPLACE , ' + ' MOVE ' + '''' + @v_data + '''' + ' TO ' + '''' + @v_Datapath + @v_dbname + '_data.mdf''' + ',' + ' MOVE ' + '''' + @v_log + '''' + ' TO ' + '''' + @v_Logpath + @v_dbname + '_log.ldf'''
4828
4829			IF @ext = 'SQB'
4830			BEGIN
4831				SET @v_dbnameS = @v_dbname
4832
4833				IF CHARINDEX('N1D111VP01', @v_bakloc) > 0
4834					SET @passCurrent = @passACS
4835				ELSE
4836					SET @passCurrent = @pass
4837
4838				IF @RestoreType = 'latest'
4839				BEGIN
4840					SET @option = ' LATEST_ALL '
4841
4842					SELECT @v_bakloc = SUBSTRING(@v_bakloc, 0, CHARINDEX(@v_dbname + '_', @v_bakloc, 0) + LEN(@v_dbname)) + '*.SQB'
4843
4844					SET @v_restore = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE,' + ' MOVE ' + '''' + @v_data + '''' + ' TO ' + '''' + @v_Datapath + @v_dbnameS + '_data.mdf''' + ',' + ' MOVE ' + '''' + @v_log + '''' + ' TO ' + '''' + @v_Logpath + @v_dbnameS + '_log.ldf''"'
4845					SET @v_restoreencrypt = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE, PASSWORD = ''' + @passCurrent + ''',' + ' MOVE ' + '''' + @v_data + '''' + ' TO ' + '''' + @v_Datapath + @v_dbnameS + '_data.mdf''' + ',' + ' MOVE ' + '''' + @v_log + '''' + ' TO ' + '''' + @v_Logpath + @v_dbnameS + '_log.ldf''"'
4846				END
4847				ELSE IF (@RestoreType = 'full')
4848				BEGIN
4849					SET @option = ''
4850					SET @v_restore = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE,' + ' MOVE ' + '''' + @v_data + '''' + ' TO ' + '''' + @v_Datapath + @v_dbnameS + '_data.mdf''' + ',' + ' MOVE ' + '''' + @v_log + '''' + ' TO ' + '''' + @v_Logpath + @v_dbnameS + '_log.ldf''"'
4851					SET @v_restoreencrypt = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE, PASSWORD = ''' + @passCurrent + ''',' + ' MOVE ' + '''' + @v_data + '''' + ' TO ' + '''' + @v_Datapath + @v_dbnameS + '_data.mdf''' + ',' + ' MOVE ' + '''' + @v_log + '''' + ' TO ' + '''' + @v_Logpath + @v_dbnameS + '_log.ldf''"'
4852				END
4853				ELSE IF (@RestoreType = 'full+diff')
4854				BEGIN
4855					SET @option = ''
4856
4857					IF EXISTS (
4858							SELECT *
4859							FROM utility..[dbbackupEXTlocs]
4860							WHERE dbname = @v_dbname
4861								AND SERVER = @@servername
4862								AND GroupNo = @v_dbid
4863								AND (
4864									backuploc LIKE '%_DIFF%'
4865									OR backuploc LIKE '%_INTRA%'
4866									)
4867							)
4868					BEGIN
4869						SET @v_restore = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + ' WITH NORECOVERY, DISCONNECT_EXISTING, REPLACE,' + ' MOVE ' + '''' + @v_data + '''' + ' TO ' + '''' + @v_Datapath + @v_dbnameS + '_data.mdf''' + ',' + ' MOVE ' + '''' + @v_log + '''' + ' TO ' + '''' + @v_Logpath + @v_dbnameS + '_log.ldf''"'
4870						SET @v_restoreencrypt = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + ' WITH NORECOVERY, DISCONNECT_EXISTING, REPLACE, PASSWORD = ''' + @passCurrent + ''',' + ' MOVE ' + '''' + @v_data + '''' + ' TO ' + '''' + @v_Datapath + @v_dbnameS + '_data.mdf''' + ',' + ' MOVE ' + '''' + @v_log + '''' + ' TO ' + '''' + @v_Logpath + @v_dbnameS + '_log.ldf''"'
4871
4872						SELECT TOP (1) @v_baklocdiff = backuploc
4873						FROM utility..[dbbackupEXTlocs]
4874						WHERE dbname = @v_dbname
4875							AND SERVER = @@servername
4876							AND GroupNo = @v_dbid
4877							AND (
4878								backuploc LIKE '%_DIFF%'
4879								OR backuploc LIKE '%_INTRA%'
4880								)
4881
4882						SET @v_restorediff = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_baklocdiff + ''' ' + @option + ' WITH RECOVERY, DISCONNECT_EXISTING"'
4883						SET @v_restorediffencrypt = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_baklocdiff + ''' ' + @option + ' WITH RECOVERY, DISCONNECT_EXISTING, PASSWORD = ''' + @passCurrent + '''"'
4884					END
4885					ELSE
4886					BEGIN
4887						SET @v_restore = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE,' + ' MOVE ' + '''' + @v_data + '''' + ' TO ' + '''' + @v_Datapath + @v_dbnameS + '_data.mdf''' + ',' + ' MOVE ' + '''' + @v_log + '''' + ' TO ' + '''' + @v_Logpath + @v_dbnameS + '_log.ldf''"'
4888						SET @v_restoreencrypt = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE, PASSWORD = ''' + @passCurrent + ''',' + ' MOVE ' + '''' + @v_data + '''' + ' TO ' + '''' + @v_Datapath + @v_dbnameS + '_data.mdf''' + ',' + ' MOVE ' + '''' + @v_log + '''' + ' TO ' + '''' + @v_Logpath + @v_dbnameS + '_log.ldf''"'
4889					END
4890				END
4891			END
4892		END
4893		ELSE /*if Multi = 1 */
4894		BEGIN
4895			PRINT 'BUILDING MULTI restore'
4896
4897			BEGIN
4898				IF @ext = 'BAK'
4899					SET @v_restore = 'RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + '''' + ' WITH STATS = 20 , REPLACE , '
4900
4901				IF @ext = 'SQB'
4902				BEGIN
4903					SET @v_dbnameS = @v_dbname
4904
4905					IF CHARINDEX('N1D111VP01', @v_bakloc) > 0
4906						SET @passCurrent = @passACS
4907					ELSE
4908						SET @passCurrent = @pass
4909
4910					IF @RestoreType = 'latest'
4911					BEGIN
4912						SET @option = ' LATEST_ALL '
4913
4914						SELECT @v_bakloc = SUBSTRING(@v_bakloc, 0, CHARINDEX(@v_dbname + '_', @v_bakloc, 0) + LEN(@v_dbname)) + '*.SQB'
4915
4916						SET @v_restore = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE,'
4917						SET @v_restoreencrypt = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + CHAR(10) + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE, PASSWORD = ''' + @passCurrent + ''','
4918					END
4919					ELSE IF (@RestoreType = 'full')
4920					BEGIN
4921						SET @option = ''
4922						SET @v_restore = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + CHAR(10) + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE,'
4923						SET @v_restoreencrypt = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + CHAR(10) + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE, PASSWORD = ''' + @passCurrent + ''','
4924					END
4925					ELSE IF (@RestoreType = 'full+diff')
4926					BEGIN
4927						SET @option = ''
4928
4929						IF EXISTS (
4930								SELECT *
4931								FROM utility..[dbbackupEXTlocs]
4932								WHERE dbname = @v_dbname
4933									AND SERVER = @@servername
4934									AND GroupNo = @v_dbid
4935									AND (
4936										backuploc LIKE '%_DIFF%'
4937										OR backuploc LIKE '%_INTRA%'
4938										)
4939								)
4940						BEGIN
4941							SET @v_restore = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + CHAR(10) + ' WITH NORECOVERY, DISCONNECT_EXISTING, REPLACE,'
4942							SET @v_restoreencrypt = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + CHAR(10) + ' WITH NORECOVERY, DISCONNECT_EXISTING, REPLACE, PASSWORD = ''' + @passCurrent + ''','
4943
4944							SELECT TOP (1) @v_baklocdiff = backuploc
4945							FROM utility..[dbbackupEXTlocs]
4946							WHERE dbname LIKE @v_dbname + '%'
4947								AND SERVER = @@servername
4948								AND GroupNo = @v_dbid
4949								AND (
4950									backuploc LIKE '%_DIFF%'
4951									OR backuploc LIKE '%_INTRA%'
4952									)
4953
4954							SET @v_restorediff = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_baklocdiff + ''' ' + @option + CHAR(10) + ' WITH RECOVERY, DISCONNECT_EXISTING"'
4955							SET @v_restorediffencrypt = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_baklocdiff + ''' ' + @option + CHAR(10) + ' WITH RECOVERY, DISCONNECT_EXISTING, PASSWORD = ''' + @passCurrent + '''"'
4956						END
4957						ELSE /* if no diff then restore normal way*/
4958						BEGIN
4959							SET @v_restore = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + CHAR(10) + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE,'
4960							SET @v_restoreencrypt = '-SQL "RESTORE DATABASE ' + @v_dbname + ' FROM DISK = ''' + @v_bakloc + ''' ' + @option + CHAR(10) + ' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE, PASSWORD = ''' + @passCurrent + ''','
4961						END
4962					END
4963				END
4964			END
4965
4966			/* Lets build the restore command for multiple filegroups */
4967			DECLARE @LogicalFileName VARCHAR(100)
4968				,@PhysicalFileName VARCHAR(100)
4969
4970			DECLARE c_FileGroups CURSOR
4971			FOR
4972			SELECT LogicalFileName
4973				,PhysicalFileName
4974			FROM dbo.DBFileEXTGroups(NOLOCK)
4975			WHERE dbname = @v_dbname
4976
4977			OPEN c_FileGroups
4978
4979			FETCH NEXT
4980			FROM c_FileGroups
4981			INTO @LogicalFileName
4982				,@PhysicalFileName
4983
4984			WHILE (@@FETCH_STATUS <> - 1)
4985			BEGIN
4986				SET @v_restore = @v_restore + CHAR(10) + ' MOVE ' + '''' + @LogicalFileName + '''' + CHAR(10) + ' TO ' + '''' + @v_Datapath + @PhysicalFileName + '''' + ','
4987				SET @v_restoreencrypt = @v_restoreencrypt + CHAR(10) + ' MOVE ' + '''' + @LogicalFileName + '''' + CHAR(10) + ' TO ' + '''' + @v_Datapath + @PhysicalFileName + '''' + ','
4988
4989				FETCH NEXT
4990				FROM c_FileGroups
4991				INTO @LogicalFileName
4992					,@PhysicalFileName
4993			END
4994
4995			CLOSE c_FileGroups
4996
4997			DEALLOCATE c_FileGroups
4998
4999			/*Fix up the restore script*/
5000			SET @v_restore = SUBSTRING(@v_restore, 1, LEN(@v_restore) - 1)
5001			SET @v_restore = @v_restore + '"'
5002			SET @v_restoreencrypt = SUBSTRING(@v_restoreencrypt, 1, LEN(@v_restoreencrypt) - 1)
5003			SET @v_restoreencrypt = @v_restoreencrypt + '"'
5004		END /*end Multi = 1 */
5005
5006		PRINT ''
5007
5008		--PRINT 'Executing script on database ' + @v_dbname 
5009		SET @statement = 'proc_kill_users @v_dbname=''' + @v_dbname + ''''
5010
5011		IF EXISTS (
5012				SELECT *
5013				FROM MASTER..SYSDATABASES
5014				WHERE MODE = 0
5015					AND NAME = @v_dbname
5016				)
5017			EXEC (@statement) --KILL CONNECTIONS
5018
5019		--LETS PUT THE DISPATCH IN SINGLE USER MODE
5020		--IF @v_dbname = 'HRMS_DISPATCH' AND EXISTS(SELECT * FROM MASTER..SYSDATABASES WHERE NAME = 'HRMS_DISPATCH')
5021		--EXEC(' USE MASTER  ALTER DATABASE [HRMS_DISPATCH] SET SINGLE_USER WITH ROLLBACK IMMEDIATE')
5022		--PRINT ' '
5023		/*Saving the int3rdparty data*/
5024		IF EXISTS (
5025				SELECT *
5026				FROM [UTILITY].[dbo].[vw_DBList]
5027				WHERE dbname = @v_dbname
5028					AND dbtype = 'COMPANY'
5029				)
5030		BEGIN
5031			PRINT 'SAVING.. ' + @v_dbname + ' int3rdParty table data'
5032
5033			EXEC UTILITY.dbo.proc_save_int3rdParty @dbname = @v_dbname
5034		END
5035
5036		/*Saving the SUPERSITE data*/
5037		IF EXISTS (
5038				SELECT *
5039				FROM [UTILITY].[dbo].[vw_DBList]
5040				WHERE dbname = @v_dbname
5041					AND dbtype = 'SUPERSITE'
5042				)
5043		BEGIN
5044			IF NOT EXISTS (
5045					SELECT *
5046					FROM MASTER..SYSDATABASES
5047					WHERE NAME = @v_dbname
5048						AND (
5049							(MODE <> 0)
5050							OR (STATUS = 40)
5051							)
5052					)
5053			BEGIN
5054				PRINT 'SAVING.. ' + @v_dbname + ' Supersite table data'
5055
5056				EXEC UTILITY.dbo.proc_save_SupersiteData
5057			END
5058		END
5059
5060		/*Saving UES_Messaging table data */
5061		IF EXISTS (
5062				SELECT *
5063				FROM [UTILITY].[dbo].[vw_DBList]
5064				WHERE dbname = @v_dbname
5065					AND dbtype = 'MESSAGING'
5066				)
5067		BEGIN
5068			IF NOT EXISTS (
5069					SELECT *
5070					FROM MASTER..SYSDATABASES
5071					WHERE NAME = @v_dbname
5072						AND (
5073							(MODE <> 0)
5074							OR (STATUS = 40)
5075							)
5076					)
5077			BEGIN
5078				PRINT 'SAVING.. ' + @v_dbname + ' UES_Messaging table data'
5079
5080				EXEC UTILITY.dbo.proc_Save_UES_messaging_Data
5081			END
5082		END
5083
5084		/*Saving RBSSiteValues table data */
5085		IF EXISTS (
5086				SELECT *
5087				FROM [UTILITY].[dbo].[vw_DBList]
5088				WHERE dbname = @v_dbname
5089					AND dbtype = 'SITE'
5090				)
5091		BEGIN
5092			IF NOT EXISTS (
5093					SELECT *
5094					FROM MASTER..SYSDATABASES
5095					WHERE NAME = @v_dbname
5096						AND (
5097							(MODE <> 0)
5098							OR (STATUS = 40)
5099							)
5100					)
5101			BEGIN
5102				PRINT 'SAVING.. ' + @v_dbname + ' RBSSiteValues table data'
5103
5104				EXEC UTILITY.dbo.proc_save_RBSSiteValuesData
5105			END
5106		END
5107
5108		PRINT ' '
5109
5110		IF @v_dbname <> @v_dbnameR /*if we restore tihs database already lets not do it again */
5111		BEGIN
5112			IF EXISTS (
5113					SELECT *
5114					FROM MASTER..SYSDATABASES
5115					WHERE NAME = @v_dbname
5116					)
5117			BEGIN
5118				PRINT 'Setting database ' + @v_dbname + ' to single user mode...'
5119
5120				SET @statement = 'USE MASTER  ALTER DATABASE [' + @v_dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
5121
5122				RAISERROR (
5123						@statement
5124						,0
5125						,1
5126						)
5127				WITH NOWAIT
5128
5129				EXEC (@statement)
5130
5131				PRINT 'Dropping database ' + @v_dbname + ' before restoring...'
5132
5133				SET @statement = 'USE MASTER
5134				                  EXEC UTILITY..proc_kill_users ''' + @v_dbname + '''
5135								    DROP DATABASE [' + @v_dbname + ']'
5136
5137				RAISERROR (
5138						@statement
5139						,0
5140						,1
5141						)
5142				WITH NOWAIT
5143
5144				EXEC (@statement)
5145			END
5146
5147			PRINT 'Restoring database ' + @v_dbname + ' from ' + @v_bakloc + ' with encryption'
5148
5149			--RAISERROR(@v_restoreencrypt,0,1) WITH NOWAIT
5150			EXECUTE master..sqlbackup @v_restoreencrypt
5151				,@exitcode OUTPUT
5152				,@sqlerrorcode OUTPUT
5153
5154			PRINT 'SQL Backup exit code: ' + cast(@exitcode AS VARCHAR)
5155
5156			IF @exitcode = '715'
5157			BEGIN
5158				PRINT 'Restoring database ' + @v_dbname + ' from ' + @v_bakloc + ' without encryption'
5159
5160				EXECUTE master..sqlbackup @v_restore
5161					,@exitcode OUTPUT
5162					,@sqlerrorcode OUTPUT
5163
5164				PRINT 'SQL Backup exit code: ' + cast(@exitcode AS VARCHAR)
5165			END
5166
5167			SET @v_ERR = @exitcode
5168
5169			IF @v_ERR <> 0 AND @v_ERR <> 204
5170				AND (
5171					@v_ERR <> 485
5172					OR @sqlerrorcode <> 0
5173					)
5174			BEGIN
5175				RAISERROR (
5176						'FAILURE TO RESTORE BASELINE DATABASE '
5177						,16
5178						,10
5179						)
5180
5181				RETURN (@v_ERR)
5182			END
5183
5184			IF @RestoreType = 'full+diff'
5185				IF EXISTS (
5186						SELECT *
5187						FROM utility..[dbbackupEXTlocs]
5188						WHERE dbname = @v_dbname
5189							AND SERVER = @@servername
5190							AND GroupNo = @v_dbid
5191							AND (
5192								backuploc LIKE '%_DIFF%'
5193								OR backuploc LIKE '%_INTRA%'
5194								)
5195						)
5196				BEGIN
5197					PRINT 'Restoring database ' + @v_dbname + ' from ' + @v_baklocdiff + ' with encryption'
5198
5199					--RAISERROR(@v_restorediffencrypt,0,1) WITH NOWAIT
5200					EXECUTE master..sqlbackup @v_restorediffencrypt
5201						,@exitcode OUTPUT
5202						,@sqlerrorcode OUTPUT
5203
5204					PRINT 'SQL Backup exit code: ' + cast(@exitcode AS VARCHAR)
5205
5206					IF @exitcode = '715'
5207					BEGIN
5208						PRINT 'Restoring database ' + @v_dbname + ' from ' + @v_baklocdiff + ' without encryption'
5209
5210						EXECUTE master..sqlbackup @v_restorediff
5211							,@exitcode OUTPUT
5212							,@sqlerrorcode OUTPUT
5213
5214						PRINT 'SQL Backup exit code: ' + cast(@exitcode AS VARCHAR)
5215					END
5216
5217					SET @v_ERR = @exitcode
5218
5219					IF @v_ERR <> 0
5220						AND (
5221							@v_ERR <> 485
5222							OR @sqlerrorcode <> 0
5223							)
5224					BEGIN
5225						RAISERROR (
5226								'FAILURE TO RESTORE BASELINE DATABASE '
5227								,16
5228								,10
5229								)
5230
5231						RETURN (@v_ERR)
5232					END
5233				END
5234		END
5235
5236		PRINT ''
5237		/*updating Metadata for the new CLR*/
5238		IF NOT EXISTS (
5239					SELECT *
5240					FROM MASTER..SYSDATABASES
5241					WHERE NAME = 'METADATA'
5242						AND (
5243							(MODE <> 0)
5244							OR (STATUS = 40)
5245							)
5246					)
5247			BEGIN
5248				PRINT 'Updating.. ' + 'METADATA' + ' UES_Messaging table data'
5249
5250				EXEC('ALTER AUTHORIZATION ON DATABASE::METADATA TO sa;
5251                      ALTER DATABASE [METADATA] SET TRUSTWORTHY ON')
5252			END
5253		IF (
5254				SELECT STATUS
5255				FROM master..sysdatabases
5256				WHERE name = @v_dbname
5257				) = 4104
5258			EXEC ('ALTER DATABASE [' + @v_dbname + '] SET  MULTI_USER WITH NO_WAIT')
5259
5260		/*Set HRMS_DISPATCH back to MULTI USER */
5261		--IF @v_dbname = 'HRMS_DISPATCH' 
5262		--EXEC('ALTER DATABASE [HRMS_DISPATCH] SET MULTI_USER WITH NO_WAIT ')
5263		/*adding back the int3rdparty data*/
5264		IF EXISTS (
5265				SELECT *
5266				FROM [UTILITY].[dbo].[vw_DBList]
5267				WHERE dbname = @v_dbname
5268					AND dbtype = 'COMPANY'
5269					AND @v_dbname NOT LIKE 'HRMS_SITE%'
5270				)
5271		BEGIN
5272			PRINT 'Updating.. ' + @v_dbname + ' int3rdParty table data'
5273
5274			EXEC UTILITY.dbo.proc_Update_int3rdParty @dbname = @v_dbname
5275		END
5276
5277		/*adding back the SUPERSITE data*/
5278		IF EXISTS (
5279				SELECT *
5280				FROM [UTILITY].[dbo].[vw_DBList]
5281				WHERE dbname = @v_dbname
5282					AND dbtype = 'SUPERSITE'
5283				)
5284		BEGIN
5285			IF NOT EXISTS (
5286					SELECT *
5287					FROM MASTER..SYSDATABASES
5288					WHERE NAME = @v_dbname
5289						AND (
5290							(MODE <> 0)
5291							OR (STATUS = 40)
5292							)
5293					)
5294			BEGIN
5295				PRINT 'Updating.. ' + @v_dbname + ' supersite table data'
5296
5297				EXEC UTILITY.dbo.proc_Insert_SupersiteData
5298			END
5299		END
5300
5301		/*adding back the MESSAGING data*/
5302		IF EXISTS (
5303				SELECT *
5304				FROM [UTILITY].[dbo].[vw_DBList]
5305				WHERE dbname = @v_dbname
5306					AND dbtype = 'MESSAGING'
5307				)
5308		BEGIN
5309			IF NOT EXISTS (
5310					SELECT *
5311					FROM MASTER..SYSDATABASES
5312					WHERE NAME = @v_dbname
5313						AND (
5314							(MODE <> 0)
5315							OR (STATUS = 40)
5316							)
5317					)
5318			BEGIN
5319				PRINT 'Updating.. ' + @v_dbname + ' UES_Messaging table data'
5320
5321				EXEC UTILITY.dbo.proc_Insert_UES_messaging_Data
5322			END
5323		END
5324
5325		IF EXISTS (
5326				SELECT *
5327				FROM [UTILITY].[dbo].[vw_DBList]
5328				WHERE dbname = @v_dbname
5329					AND dbtype = 'GLOBALDATA'
5330				)
5331		BEGIN
5332			IF NOT EXISTS (
5333					SELECT *
5334					FROM MASTER..SYSDATABASES
5335					WHERE NAME = @v_dbname
5336						AND (
5337							(MODE <> 0)
5338							OR (STATUS = 40)
5339							)
5340					)
5341			BEGIN
5342				EXEC ('ALTER AUTHORIZATION ON DATABASE::HRMS_GLOBALDATA TO [sa]')
5343
5344				PRINT 'Updating.. ' + @v_dbname + ' CLR registration'
5345
5346				IF EXISTS (
5347						SELECT value
5348						FROM HRMS_GLOBALDATA..globalconfig
5349						WHERE NAME = 'Version\Data Version'
5350							AND VALUE >= 26139
5351						)
5352				BEGIN
5353					IF EXISTS (
5354							SELECT *
5355							FROM MASTER.dbo.SYSDATABASES
5356							WHERE NAME = 'HRMS_SITE'
5357							)
5358						EXEC (
5359								' USE HRMS_SITE;
5360					   TRUNCATE TABLE TimedKeyServerID
5361					   GRANT SELECT,INSERT ON TimedKeyServerID TO PUBLIC'
5362								)
5363
5364					EXEC proc_register_SQL_CLR_Objects_2018R2
5365				END
5366				ELSE
5367				BEGIN
5368					EXEC proc_register_SQL_CLR_Objects
5369				END
5370			END
5371		END
5372
5373		IF EXISTS (
5374				SELECT *
5375				FROM [UTILITY].[dbo].[vw_DBList]
5376				WHERE dbname = @v_dbname
5377					AND dbtype = 'UTM'
5378				)
5379		BEGIN
5380			IF NOT EXISTS (
5381					SELECT *
5382					FROM MASTER..SYSDATABASES
5383					WHERE NAME = @v_dbname
5384						AND (
5385							(MODE <> 0)
5386							OR (STATUS = 40)
5387							)
5388					)
5389			BEGIN
5390				PRINT 'Updating .. ' + @v_dbname + ' NTI_Master table data'
5391
5392				EXEC UTILITY.dbo.proc_UTM_DBupdate @v_dbname
5393			END
5394		END
5395
5396		PRINT ' '
5397		PRINT '----------------------------------------------------------------------------------'
5398
5399		SET @v_dbnameR = @v_dbname
5400
5401		--change customer db to correct name
5402		/*IF @v_dbname = 'HRMS_SITE' 
5403  IF @ext = 'SQB'
5404            BEGIN
5405              --EXEC proc_kill_users 'HRMS_SITE'
5406              --EXEC ('ALTER DATABASE HRMS_SITE MODIFY NAME = HRMS_SITE_' + @v_dbid  )
5407              --EXEC proc_kill_users 'HRMS_SITE_ORIG'
5408              --EXEC ('ALTER DATABASE HRMS_SITE_ORIG MODIFY NAME = HRMS_SITE ' )
5409              --EXEC('ALTER DATABASE [HRMS_SITE] SET MULTI_USER WITH NO_WAIT ')
5410           END 
5411*/
5412		FETCH NEXT
5413		FROM c_db
5414		INTO @v_dbid
5415			,@v_dbname
5416			,@v_bakloc
5417			,@v_data
5418			,@v_log
5419			,@v_Datapath
5420			,@v_Logpath
5421			,@v_mult
5422	END
5423
5424	CLOSE c_db
5425
5426	DEALLOCATE c_db
5427END --if not FlexClone
5428
5429--***************************************************
5430--PRINT 'RESTORING 3RD PARTY SERVERS'
5431--IF EXISTS(SELECT * FROM master..sysdatabases WHERE name = 'hrms_site')
5432--   IF(SELECT COUNT(DISTINCT(serverName)) from UTILITY..Uint3rdPartyDbSetup WHERE allowrestore = 'TRUE')>0
5433--     EXEC UTILITY.dbo.proc_restore_Team3rdPartyEnv
5434--PRINT 'RESTORING UES_MESSAGING' 
5435--IF EXISTS(SELECT * FROM master..sysdatabases WHERE name = 'SuperSite')
5436--   EXEC UTILITY.dbo.proc_UES_messaging_setup @Buildno    
5437--fix database owner
5438EXEC proc_fix_databaseOwner
5439
5440--SET databases to read\write
5441EXEC proc_set_DBReadWrite
5442
5443--drop user objects
5444EXEC proc_Fixuserobjects @drop = 'N'
5445
5446EXEC proc_ChangeJobOwner
5447
5448--cleanup Everest security
5449--Commenting out due to issue deleting roles
5450--EXEC proc_clean_EverestSecurity
5451--Clean orphan users 
5452--EXEC proc_srv_cleanorphans
5453--UPDATING DPM QUEUES
5454EXEC proc_Setup_Queues
5455
5456--setup default dev user for sync_seccontexts
5457EXEC proc_setup_devuser
5458
5459--add and setup paycalcuser
5460EXEC proc_set_paycalc_user
5461
5462--copy and register security DLL
5463IF EXISTS (
5464		SELECT *
5465		FROM dbo.vw_TeamSrvInfo
5466		WHERE dbname IN (
5467				'HRMS_SITE'
5468				,'ULTIPRO_DATADICT'
5469				)
5470		)
5471	IF EXISTS (
5472			SELECT value
5473			FROM HRMS_GLOBALDATA..globalconfig
5474			WHERE NAME = 'Version\Data Version'
5475				AND VALUE >= 26139
5476			)
5477	BEGIN
5478		IF EXISTS (
5479				SELECT *
5480				FROM MASTER.dbo.SYSDATABASES
5481				WHERE NAME = 'HRMS_SITE'
5482				)
5483			EXEC (
5484					' USE HRMS_SITE;
5485	   TRUNCATE TABLE TimedKeyServerID
5486	   GRANT SELECT,INSERT ON TimedKeyServerID TO PUBLIC'
5487					)
5488
5489		EXEC proc_register_SQL_CLR_Objects_2018R2
5490	END
5491	ELSE
5492		EXEC proc_register_SQL_CLR_Objects
5493
5494TRUNCATE TABLE dbo.DBFILESETUP
5495
5496--Enable 'Process Audit Data' job
5497SELECT @jobid = job_id
5498FROM msdb..sysjobs
5499WHERE name = 'Process Audit Data'
5500
5501IF @jobid IS NOT NULL
5502BEGIN
5503	EXECUTE proc_get_SQL_job_execution_status @job_name = 'Process Audit Data'
5504		,@execution_status = @retval OUTPUT
5505
5506	IF (@retval = 1)
5507		EXEC msdb.dbo.sp_stop_job @job_id = @jobid
5508
5509	EXEC msdb.dbo.sp_update_job @job_id = @jobid
5510		,@enabled = 1
5511
5512	PRINT '*Process Audit Data* job Enabled'
5513END
5514
5515/*SET Trace Flag and Startup Parameter*/
5516EXEC UTILITY.[dbo].[proc_set_startup_param] '2371'
5517
5518GO
5519
5520/*********************************************************************************************************************************************************************************************/
5521
5522USE [UTILITY]
5523GO
5524/****** Object:  StoredProcedure [dbo].[proc_cleanup_RBSSite]    Script Date: 10/29/2018 1:08:30 PM ******/
5525SET ANSI_NULLS ON
5526GO
5527SET QUOTED_IDENTIFIER ON
5528GO
5529ALTER procedure [dbo].[proc_cleanup_RBSSite]
5530as
5531/*****
55322009 Q1 SECURITY UNIFICATION BUILD
5533lAST MODIFIED ON	: 7/17/2012 by Kevin Maxwell
5534        Update seccompanies to fix sccclientID
5535        EXCLUDE UTA sites for EREC SSO
5536*****/
5537SET NOCOUNT ON
5538IF EXISTS(SELECT * FROM hrms_site..sysObjects WHERE NAME = 'Secvalues' and type = 'v')
5539BEGIN
5540  IF EXISTS(SELECT * FROM hrms_site..SYSOBJECTS WHERE NAME = 'SYNTRANSPEND')
5541   BEGIN
5542	--cleanup syn config tables
5543	Truncate table HRMS_SITE..synTrans
5544	Truncate table HRMS_SITE..synTransPend
5545
5546		--cleanup synconfig for 
5547DELETE HRMS_SITE.[dbo].[SynConfigCompany]
5548DELETE HRMS_SITE.[dbo].[SynConfig]
5549  FROM UTILITY..[USynconfigEnvExclusions]
5550 WHERE sycConfigName = ConfigName
5551   AND Siteserver = @@servername
5552   
5553 	--cleanup synconfig company  
5554DECLARE synconfig CURSOR
5555READ_ONLY
5556FOR  select sycConfigID from HRMS_SITE..synconfig
5557declare @idt int
5558OPEN synconfig
5559
5560FETCH NEXT FROM synconfig INTO @idt
5561WHILE (@@fetch_status <> -1)
5562BEGIN
5563	IF (@@fetch_status <> -2)
5564	BEGIN
5565        INSERT INTO HRMS_SITE.[dbo].[SynConfigCompany] ([sgcConfigID], [sgcSccID], [sgcContinueInd], [sgcSortOrder])
5566		SELECT @idt, sccID, 0, 1            
5567		FROM HRMS_SITE..rbsCompanies WITH (NOLOCK)
5568		LEFT JOIN HRMS_SITE..SynConfigCompany WITH (NOLOCK) ON sccID = sgcSccID
5569        WHERE (sgcConfigID <> @idt) OR (sgcsccid is NULL)
5570
5571    END
5572FETCH NEXT FROM synconfig INTO @idt
5573END
5574
5575CLOSE synconfig
5576DEALLOCATE synconfig
5577
5578		DELETE HRMS_SITE..synServer
5579
5580
5581	--insert missing sec users for the RBSSync service to update.
5582	INSERT INTO HRMS_SITE.dbo.SynTransPend(stpCompanyID, stpCreateDate, stpIdent, stpTransTypeID, stpTypeID)
5583	  SELECT distinct sucCompanyID
5584		   , GETDATE()
5585		   , r.susUserID
5586		   , (SELECT sttTransTypeID from hrms_site.dbo.SynTransType WITH (NOLOCK) WHERE sttTransTypeDesc = 'Add')
5587		   , 'U'
5588	  FROM hrms_site..rbsusers r
5589	left join hrms_site..rbsusercompany on sucuserid = r.sususerid
5590	left join hrms_site..secusers s on s.sususerid = r.sususerid
5591	where s.sususerid is NULL
5592	and succompanyid is not null
5593  END
5594END
5595--cleanup RBSSiteValues
5596
5597UPDATE HRMS_SITE..RBSSITEVALUES
5598   SET stlValue = ''
5599WHERE stlcode in ('CLASSICWEB','DOTNETWEB') 
5600
5601
5602UPDATE HRMS_SITE..RBSSiteValues
5603   SET stlValue = 'LocalHost'
5604WHERE stlcode in ('HostServerName') 
5605
5606--UPDATE HRMS_SITE..RBSSiteValues
5607--   SET stlValue = '300'
5608--WHERE stlcode in ('CacheSetTimeout') 
5609IF (select count(*) from utility..sitesetup) = 1
5610BEGIN 
5611
5612   UPDATE HRMS_SITE..RBSSITESETUP
5613      SET steSiteKey = '48EB9B6CDF4241E5',
5614         steSiteTypeCode = 'ENTPS'
5615END
5616ELSE
5617  BEGIN
5618  
5619     UPDATE HRMS_SITE..RBSSITESETUP
5620        SET steSiteKey = '241F41C6EA25E94A',
5621         steSiteTypeCode = 'SB'
5622
5623   END
5624
5625/*JIRA 16231 cleanup job on restore.*/
5626IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view 
5627WHERE name = N'FutureDatedWorkFlow')
5628EXEC msdb.dbo.sp_delete_job @job_name =N'FutureDatedWorkFlow', @delete_unused_schedule=1
5629
5630/*add the SITETYPE to the RBSSITEVALUES table
5631(0 = OnPremise, 1=SaaS, 2=WorkPlace)*/
5632
5633
5634IF NOT EXISTS(SELECT * FROM HRMS_SITE..RBSSiteValues WHERE stlCode = 'SITETYPE'
5635AND stlType = 'SITE')
5636  INSERT HRMS_SITE..RBSSiteValues(stlCode,stlType,stlValue)
5637  VALUES ('SITETYPE','SITE','1')
5638  
5639IF EXISTS(SELECT * FROM HRMS_SITE..RBSSiteValues WHERE stlCode = 'SITETYPE'
5640AND stlType = 'SITE')
5641  UPDATE HRMS_SITE..RBSsiteValues
5642     SET stlValue = (SELECT ISNULL(TeamSiteType,1) FROM UTILITY..TEAMENV WHERE 
5643                     TEAMID = [UTILITY].[dbo].[fn_TeamEnvid] (@@SERVERNAME))
5644   WHERE stlCode = 'SITETYPE'
5645     AND stlType = 'SITE' 
5646
5647IF NOT EXISTS(SELECT 1 FROM [UTILITY].[dbo].[vw_DBList]
5648 WHERE DBtype = 'SUPERSITE')
5649	UPDATE HRMS_SITE..RBSsiteValues
5650	 SET stlValue = '1'
5651	WHERE stlCode = 'SITETYPE'
5652	 AND stlType = 'SITE' 
5653ELSE
5654  BEGIN
5655	DECLARE @Code VARCHAR(20)
5656	DECLARE @Type VARCHAR(20)
5657	DECLARE @Value VARCHAR(255)
5658	DECLARE @HubServerName VARCHAR(50)
5659    SET @HubServerName = 'HUBSERVER'
5660    SET @Code = 'ROUTERENDPOINTURI'
5661    SET @Type = 'UES'
5662    SET @Value = 'net.tcp://' + @HubServerName + ':55556/services'
5663    DECLARE @Premise INT
5664
5665
5666	IF EXISTS (SELECT * FROM HRMS_SITE..RbsSiteValues WHERE stlType = @Type AND stlCode = @Code)
5667	  BEGIN
5668		UPDATE HRMS_SITE..RbsSiteValues
5669		SET stlValue = @Value
5670		WHERE stlType = @Type AND stlCode = @Code
5671	  END
5672	ELSE
5673	  BEGIN
5674		INSERT INTO HRMS_SITE..RbsSiteValues(stlCode, stlType, stlValue)
5675		VALUES( @Code, @Type, @Value )
5676	  END
5677  END
5678
5679-- update RBSSitevalues from Utility table
5680EXECUTE [UTILITY].[dbo].[proc_Update_RBSSiteValuesData] 
5681--SET NOCOUNT OFF
5682/* START 01/07/2013 - This code is to add a code to RBSCompanyValues according to the SITETYPE value */
5683IF OBJECT_ID('tempdb..#CompanyValues') > 0 
5684DROP TABLE #CompanyValues
5685
5686SELECT    CompanyId	=sccId,
5687	    Code	='PENDINGWORKFLOWAUDIT',
5688	    Value	='1',
5689	    RecordExists=case when svl.svlID is null then 0 else 1 end
5690INTO	#CompanyValues	
5691FROM	HRMS_SITE..rbsCompanies scc(nolock)
5692LEFT JOIN	HRMS_SITE..rbsCompanyValues svl(nolock)ON scc.sccId=svl.svlCompanyId AND svl.svlCode='PENDINGWORKFLOWAUDIT'
5693WHERE EXISTS(SELECT * FROM HRMS_SITE..rbsSiteValues (nolock) WHERE stlCode='SITETYPE' AND stlValue in('1','2'))
5694
5695
5696UPDATE HRMS_SITE..rbsCompanyValues
5697SET	svlValue		=cv.Value
5698FROM	HRMS_SITE..rbsCompanyValues svl(nolock)
5699inner join	#CompanyValues cv(nolock)on svl.svlCompanyId=cv.CompanyId and svl.svlCode=cv.Code
5700WHERE	svl.svlValue	<>cv.Value
5701
5702INSERT HRMS_SITE..rbsCompanyValues(svlCompanyId,svlCode,svlValue)
5703SELECT CompanyId,
5704	    Code,
5705	    Value
5706FROM	#CompanyValues
5707WHERE	RecordExists=0
5708
5709IF OBJECT_ID('tempdb..#CompanyValues') > 0 
5710DROP TABLE #CompanyValues
5711
5712/*Cleanup Report Servers */
5713IF EXISTS(SELECT * FROM HRMS_SITE..sysobjects where name = 'ReportEngine')
5714BEGIN
5715EXEC('USE [HRMS_SITE]
5716ALTER TABLE [dbo].[ReportEngine] DROP CONSTRAINT [FK_ReportEngine_ReportServer]
5717  truncate table reportengine
5718  truncate table reportserver')
5719
5720EXEC('USE [HRMS_SITE]
5721ALTER TABLE [dbo].[ReportEngine]  WITH CHECK ADD  CONSTRAINT [FK_ReportEngine_ReportServer] FOREIGN KEY([ReportServerId])
5722REFERENCES [dbo].[ReportServer] ([Id])')
5723
5724EXEC('USE [HRMS_SITE]
5725ALTER TABLE [dbo].[ReportEngine] CHECK CONSTRAINT [FK_ReportEngine_ReportServer]')
5726
5727EXEC('USE HRMS_DISPATCH
5728DELETE DpmServers')
5729END
5730
5731/* update Companylookup table in Dispatch */
5732IF EXISTS(SELECT * FROM sys.databases WHERE name = 'HRMS_DISPATCH') 
5733   EXEC ('HRMS_DISPATCH.dbo.DPM_SyncCompanies')
5734--SET NOCOUNT ON
5735/* END 01/07/2013 - This code is to add a code to RBSCompanyValues according to the SITETYPE value */
5736
5737GO
5738
5739
5740
5741/****** Object:  StoredProcedure [dbo].[proc_set_startup_param]    Script Date: 3/24/2020 2:43:14 PM ******/
5742DROP PROCEDURE IF EXISTS [dbo].[proc_set_startup_param]
5743GO
5744
5745/****** Object:  StoredProcedure [dbo].[proc_set_startup_param]    Script Date: 3/24/2020 2:43:14 PM ******/
5746SET ANSI_NULLS ON
5747GO
5748
5749SET QUOTED_IDENTIFIER ON
5750GO
5751
5752IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_set_startup_param]') AND type in (N'P', N'PC'))
5753BEGIN
5754EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[proc_set_startup_param] AS'
5755END
5756GO
5757
5758ALTER procedure [dbo].[proc_set_startup_param]
5759@param varchar(10)
5760
5761AS
5762
5763declare @Parameters varchar(max)= '-T' + @param,
5764@Argument_Number int,
5765@Argument varchar(max),
5766@Reg_Hive varchar(max),
5767@CMD varchar(max)
5768---------------------------------------------------------------------------------------------------------{Parameter Cleanup}
5769EXEC('DBCC TRACEON(' + @param + ', -1)')
5770
5771if exists (select * from sys.dm_server_registry where value_name like 'SQLArg%' and convert(varchar(max),value_data)=@Parameters)
5772begin
5773select
5774@Argument=value_name,@Reg_Hive=substring(registry_key,len('HKLM\')+1,len(registry_key))
5775from sys.dm_server_registry where value_name like 'SQLArg%' and convert(varchar(max),value_data)=@Parameters
5776
5777set  @CMD='master..xp_regdeletevalue
5778''HKEY_LOCAL_MACHINE'',
5779'''+@Reg_Hive+''',
5780'''+@Argument+''''
5781exec (@CMD)
5782end
5783---------------------------------------------------------------------------------------------------------{Add Parameter}
5784--select * from sys.dm_server_registry where value_name like 'SQLArg%'
5785select @Reg_Hive=substring(registry_key,len('HKLM\')+1,len(registry_key)) ,@Argument_Number=max(convert(int,right(value_name,1)))+1
5786from sys.dm_server_registry
5787 where value_name like 'SQLArg%'
5788group by substring(registry_key,len('HKLM\')+1,len(registry_key))
5789
5790set @Argument= 'SQLArg'+convert(varchar(1),@Argument_Number)
5791select @Argument,@Reg_Hive
5792
5793set  @CMD='master..xp_regwrite
5794''HKEY_LOCAL_MACHINE'',
5795'''+@Reg_Hive+''',
5796'''+@Argument+''',
5797''REG_SZ'',
5798'''+@Parameters+''''
5799
5800exec  (@CMD)
5801GO