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
Re: Sync Search Database getting Wiped out daily
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
Re: Sync Search Database getting Wiped out daily
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 From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Rick Cook Sent: Friday, April 06, 2007 4:48 PM To: arslist@ARSLIST.ORG Subject: Re: Sync Search Database getting Wiped out daily ** Well, until you find out what's doing it (perhaps a DB backup/restore process?) you could do an export of those forms and their data each night. Make them into a deployable application to make that process easier. Rick From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Warren Baltimore Sent: Friday, April 06, 2007 3:11 PM To: arslist@ARSLIST.ORG Subject: Re: Sync Search Database getting Wiped out daily ** Man, that's a good one Are you running anykind of nightly maintenance processes? Is the system on a Windows platform? What version are you running? Warren On 4/6/07, Rawat, Jaspal [EMAIL PROTECTED] wrote: ** List, For some reason, the Sync Search database gets wiped out daily. I have to start this process on daily basis. After 1-2 hrs I always get message that Database Updated Successfully and I could view the Related Workflow for any object. After One day, When I try to see the Related Workflow again, This TAB is not there. I go to menu and select Sync Search database and It shows Last Synchronized At: Never We have got the all the Valid Server license. Any thoughts on why this is happening. Jaspal S Rawat __20060125___This posting was submitted with HTML in it___ -- Warren R. Baltimore II Remedy Developer UW Medicine IT Services School of Medicine University of Washington Box 358220 1325 Fourth Ave, Suite 2000 Seattle, WA 98101 The opinions expressed in this e-mail are in no way those of the University of Washington, or the State of Washington. They are my own. __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
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
Re: Sync Search Database getting Wiped out daily
Hey Carey:)...it was me that put that Escalation in place. You are correct...when the object_search_admin record is deleted the admin tool turns off the option of searching...weird huh. Jaspal and I work together on my current project so I just answered internally...but to answer your question...I was trying to automate the process some months back and found that it would not run properly from the command line of my escalation if this record still existed...I could run it just fine from the GUI but the command line wouldn't work (I have no idea why)...so I found when I deleted the search DB and ran it as a 'blank DB' it would run fine...I then found that all I needed to do was delete that one record and it ran fine...so I automated the delete and re-creation to happen nightlyI just forgot to add the Admin into the path on our new dev boxes...:) -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Carey Matthew Black Sent: Monday, April 09, 2007 11:34 AM 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
Sync Search Database getting Wiped out daily
List, For some reason, the Sync Search database gets wiped out daily. I have to start this process on daily basis. After 1-2 hrs I always get message that Database Updated Successfully and I could view the Related Workflow for any object. After One day, When I try to see the Related Workflow again, This TAB is not there. I go to menu and select Sync Search database and It shows Last Synchronized At: Never We have got the all the Valid Server license. Any thoughts on why this is happening. Jaspal S Rawat ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Sync Search Database getting Wiped out daily
Man, that's a good one Are you running anykind of nightly maintenance processes? Is the system on a Windows platform? What version are you running? Warren On 4/6/07, Rawat, Jaspal [EMAIL PROTECTED] wrote: ** List, For some reason, the Sync Search database gets wiped out daily. I have to start this process on daily basis. After 1-2 hrs I always get message that Database Updated Successfully and I could view the Related Workflow for any object. After One day, When I try to see the Related Workflow again, This TAB is not there. I go to menu and select Sync Search database and It shows Last Synchronized At: Never We have got the all the Valid Server license. Any thoughts on why this is happening. Jaspal S Rawat __20060125___This posting was submitted with HTML in it___ -- Warren R. Baltimore II Remedy Developer UW Medicine IT Services School of Medicine University of Washington Box 358220 1325 Fourth Ave, Suite 2000 Seattle, WA 98101 The opinions expressed in this e-mail are in no way those of the University of Washington, or the State of Washington. They are my own. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Sync Search Database getting Wiped out daily
Well, until you find out what's doing it (perhaps a DB backup/restore process?) you could do an export of those forms and their data each night. Make them into a deployable application to make that process easier. Rick _ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Warren Baltimore Sent: Friday, April 06, 2007 3:11 PM To: arslist@ARSLIST.ORG Subject: Re: Sync Search Database getting Wiped out daily ** Man, that's a good one Are you running anykind of nightly maintenance processes? Is the system on a Windows platform? What version are you running? Warren On 4/6/07, Rawat, Jaspal [EMAIL PROTECTED] wrote: ** List, For some reason, the Sync Search database gets wiped out daily. I have to start this process on daily basis. After 1-2 hrs I always get message that Database Updated Successfully and I could view the Related Workflow for any object. After One day, When I try to see the Related Workflow again, This TAB is not there. I go to menu and select Sync Search database and It shows Last Synchronized At: Never We have got the all the Valid Server license. Any thoughts on why this is happening. Jaspal S Rawat __20060125___This posting was submitted with HTML in it___ -- Warren R. Baltimore II Remedy Developer UW Medicine IT Services School of Medicine University of Washington Box 358220 1325 Fourth Ave, Suite 2000 Seattle, WA 98101 The opinions expressed in this e-mail are in no way those of the University of Washington, or the State of Washington. They are my own. __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
