Fred, Thanks for the tip on truncating. The entire process now only takes 35 minutes (400 forms, 7000 active-links, 1700 filters).
I had hoped the synch process was available from the API... I just created a stored procedure (SQL Server) that performs all the steps for "Synch Search Database", and have it scheduled to run nightly. This approach works when ARS is installed on a Windows server with the Administrator Tool. Here is the Direct SQL command: EXEC usp_SynchSearchTables '$SERVER$', 'Demo', 'secret', 'C:\Program Files\AR System\Admin\aradmin.exe' CREATE PROCEDURE dbo.usp_SynchSearchTables ( @Server VARCHAR(255), @User VARCHAR(255), @Password VARCHAR(255), @ARAdmin VARCHAR(255) ) -- Synchronizes the Remedy Object Search tables. AS SET NOCOUNT ON DECLARE @SchemaId VARCHAR(10), @SQL NVARCHAR(100), @Command VARCHAR(1000) SELECT @SchemaId = CONVERT(VARCHAR,SchemaId) FROM ARSchema WHERE [Name] = 'object_search_admin' SET @SQL = N'TRUNCATE TABLE H' + @SchemaId EXEC sp_executesql @SQL SET @SQL = N'TRUNCATE TABLE B' + @SchemaId EXEC sp_executesql @SQL SET @SQL = N'TRUNCATE TABLE T' + @SchemaId EXEC sp_executesql @SQL SELECT @SchemaId = CONVERT(VARCHAR,SchemaId) FROM ARSchema WHERE [Name] = 'object_search_details' SET @SQL = N'TRUNCATE TABLE H' + @SchemaId EXEC sp_executesql @SQL SET @SQL = N'TRUNCATE TABLE B' + @SchemaId EXEC sp_executesql @SQL SET @SQL = N'TRUNCATE TABLE T' + @SchemaId EXEC sp_executesql @SQL SELECT @SchemaId = CONVERT(VARCHAR,SchemaId) FROM ARSchema WHERE [Name] = 'object_search_ref' SET @SQL = N'TRUNCATE TABLE H' + @SchemaId EXEC sp_executesql @SQL SET @SQL = N'TRUNCATE TABLE B' + @SchemaId EXEC sp_executesql @SQL SET @SQL = N'TRUNCATE TABLE T' + @SchemaId EXEC sp_executesql @SQL IF CHARINDEX(' ', @ARAdmin) > 0 AND LEFT(@ARAdmin,1) <> '"' SET @ARAdmin = '""' + @ARAdmin + '""' SET @Command = @ARAdmin + ' -x ' + @Server + ' -u ' + @User + ' -p ' + @Password + ' -s' EXEC master..xp_cmdshell @Command GO Stephen -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Thursday, June 07, 2007 11:26 AM To: arslist@ARSLIST.ORG Subject: Re: Sync Search Database getting Wiped out daily No, the building of the Sync data is an Admin Tool function not an API function. Also in order to do this on a Windows server the Admin tool would have to be installed on the server as well. Theoretically if your server can connect to a Windows machine where the Admin tool is installed, it could start up the process remotely. Side Note... In order to speed up the Sync Process I have done the following: Add 2 new Display Only Integer fields to the "object_search_admin" form ($Details Schema ID$, $Ref Schema ID$). Add a filter "object_search_admin-TruncateOld`!" to the form "object_search_admin" Filter Run If: 'TR.Run/Not Run' = 1 Action 1: Set Fields From SQL: SELECT SCHEMAID FROM ARSCHEMA WHERE NAME='object_search_details' Set $Details Schema ID$ = $1$ Action 2: Set Fields From SQL: SELECT SCHEMAID FROM ARSCHEMA WHERE NAME='object_search_ref' Set $Ref Schema ID$ = $1$ Action 3: Direct SQL Command: TRUNCATE TABLE T$Details SchemaID$ Action 4: Direct SQL Command: TRUNCATE TABLE H$Details SchemaID$ Action 5: Direct SQL Command: TRUNCATE TABLE B$Details SchemaID$ Action 6: Direct SQL Command: TRUNCATE TABLE T$Ref Schema ID$ Action 7: Direct SQL Command: TRUNCATE TABLE H$Ref Schema ID$ Action 8: Direct SQL Command: TRUNCATE TABLE B$Ref Schema ID$ Watching the SQL logs as a Sync was started I saw that the Server had to query the Details form and delete each record as well as do the same to the Ref form. Truncating both forms with the filter cut the sync time on my server from approx 2 hours to just 35 minutes. Since the Admin tool uses the Remedy API to set the Run flag on the admin form this filter fires normally. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen Sent: Thursday, June 07, 2007 9:52 AM To: arslist@ARSLIST.ORG Subject: Re: Sync Search Database getting Wiped out daily Regarding "...can not be done if your Server is NOT a windows box", is the synch option available in any of the ARS API's? I searched through the help file for the 7.0 and 7.1 .Net API's but didn't see it mentioned. Stephen -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Carey Matthew Black Sent: Monday, April 09, 2007 1:34 PM To: arslist@ARSLIST.ORG Subject: Re: Sync Search Database getting Wiped out daily Jaspal, Well... in order for "Sync Search database gets wiped out daily" to be true then more had to be going on. The object_search_admin record would not remove all of the related "Sync Search database" data. (Unless the absence of the orbject_search_admin record some how short cuts the admin tool from even trying to find any data in the Sync Search DB forms. I guess that could be the case.) I also find it a shame that this same functionality (Escalation to rebuild the Sync DB) can not be done if your Server is NOT a windows box. [ AKA: All of this stuff should be server side and preferably one of the *Special* run process type actions. ] Thanks for telling us know about someone is automating this process. By the way: Do you know why they are deleting that record at all? ( I doubt it is really a necessary step.) -- Carey Matthew Black Remedy Skilled Professional (RSP) ARS = Action Request System(Remedy) Love, then teach Solution = People + Process + Tools Fast, Accurate, Cheap.... Pick two. On 4/9/07, Rawat, Jaspal <[EMAIL PROTECTED]> wrote: > ** > > Actually there was an escalation, that I didn't knew abt, which was > running nightly. > It was a two step run process so that we could have the latest Synced > Search DB. > > 1. Application-Query-Delete-Entry "object_search_admin" 1=1 2. CMD /C > ARAdmin.exe -x $SERVER$ -u Webservice -p pipeline -portnum 2112 -s > > The second run process was not running because the PATH variable was > not having the Remedy binaries path included. > > > Jaspal S Rawat ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"