Rob, "...is there any other method which would allow me to see all the table names columns/joins etc used in the extract."
In SQL Server 2000 there is the SQL Profiler tool that displays in real-time every command that is executed (you can filter your results). There is probably a similar tool in your version of Oracle. Stephen -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Carey Matthew Black Sent: Monday, July 24, 2006 8:06 AM To: [email protected] Subject: Re: How to see the SQL code generated by Remedy Maybe this is a user and not an ARS administrator? Rob, Access to the SQL logs are restricted to the ARS Administrators. ( Think unix root, or windows Administrator group and you have the right idea.) If you are not blessed with that role in your company, then I would strongly suggest that you start to solve this problem by involving those people. Note that your "direct DB" approach will have some gotchas. All ARS "date" values are stored in "EPOCH" (integer) from on the DB. Also all ARS "Diary" fields are formatted unlimited length character strings with special formatting embedded in them. Attachments will likely be out of reach too. (But I doubt that will be much of a problem for this case.) Good luck. -- Carey Matthew Black Remedy Skilled Professional (RSP) ARS = Action Request System(Remedy) Solution = People + Process + Tools Fast, Accurate, Cheap.... Pick two. Never ascribe to malice, that which can be explained by incompetence. On 7/24/06, Shafqat Ayaz <[EMAIL PROTECTED]> wrote: > ** Hi Rob > what you have turned on seems to be the Active Link and Macro Logging > in the User Tool! > do it through the Admin Tool under File Server Information Logs, turn > on the SQL logging and the input will be different. > any problems dorp me a line and I will try and walk you through it > > regards > > shafqat > > > Rob Ashmore <[EMAIL PROTECTED]> wrote: > > Thanks Rick, > > I tried your suggestion but only got the following type of thing: > > Doesn't look anything like SQL to me. > > regards, > > Rob. > > ---------------------------------------------------------------------- > ----------------------------------------- > WFLG> /* Mon Jul 24 2006 15:56:03 */ > Workflow Trace Log -- ON > > /* Mon Jul 24 2006 15:56:11 */ > Import macro from file -- C:\Documents and > Settings\QITMAA\Application Data\AR System\HOME\arcmds\Futrixxx.arq > Parameters: > > Please enter From date=01/07/06 > Please enter To date=15/07/06 > /* Mon Jul 24 2006 15:56:23 */ > Import macro -- Futrix - Monthly Logged > /* Mon Jul 24 2006 15:56:24 */ > Starting processing of macro > Step: 0 > > Change current working schema -- HPD:HelpDesk (eap01) > Step: 1 > > Query the database for matching entries Number of matching entries > -- 3810 > Step: 2 > Form open action > Step: 3 > > Open a form entry list window > with polling interval (in minutes) -- 0 > /* Mon Jul 24 2006 15:56:27 */ > Start active link processing -- Operation - On Window Open For > Schema - HPD:HelpDesk On screen type - QUERY > > Checking HPD:HPD-DisableTableFields-RO (0) -> Failed qualification > > Checking HPD:HPD-HideStatusOnSubmit (0) -> Failed qualification > > Checking HPD:HPD-MBFSetRequesterSolutionMenu (0) -> Failed > qualification > > Checking HPD:HPD-OnQueryEnableFields (0) -> Passed qualification -- > perform if actions > 0: Set Characteristics > > etc etc etc > ---------------------------------------------------------------------- > ------------------------------------- > > >>> [EMAIL PROTECTED] 24/07/06 13:28 >>> > Probably the easiest way is to go into Remedy Administrator (or Remedy > User as an Administrator) and turn on the SQL logs for a bit. Note > that if you turn them on in Administrator, it will generate SQL logs > for all transactions for all users. This can use huge chunks of disk > space. Using the logs in User will only track the transactions run by you. > > Rick > > ________________________________ > > From: Action Request System discussion list(ARSList) on behalf of Rob > Ashmore > Sent: Sun 7/23/2006 7:28 PM > To: [email protected] > Subject: How to see the SQL code generated by Remedy > > > > Dear All, > > For the purposes of analysis and reporting, a csv file is currently > extracted from the Remedy Help Desk databases using the Remedy > front-end (to specify time ranges and the names of the required > columns). This is a messy / high maintenance approach to reading the > data and does not lend itself to a good automated 'load' process. > > Rather than use the above method I want to interrogate the underlying > Oracle tables using 3rd party SQL based software. It would help > immensely if I could see the SQL code which is generated by the Remedy > front-end when it produces the csv flat-file extract. I could then > simply copy it into my 3rd party software program, make any necessary > changes and run the code. (I would be using SAS software, more > specifically 'SAS pass-thru sql' ). > > Could anyone please suggest where / how I can get at the code > generated ( I presume in SQL) by the Remedy front-end. The sql > generated is probably not very complex but I really need to see which > columns come from which tables and how they are joined. > > I do already have read access to the 'ARADMIN' schema tables in Oracle > and can see them from within SAS. > > > If it is not possible to access the generated SQL, is there any other > method which would allow me to see all the table names /columns/joins > etc used in the extract. > > Thanks In Advance, > > Rob Ashmore > > (BTW it is probably painfully obvious that I am not really familiar > with the Remedy system. Hopefully the above makes sense though! ) > > > > > ______________________________________________________________________ > This email contains confidential information intended only for the > addressee. If you received it in error, please inform us by reply > email or by calling us from Australia on 131 137 or Internationally on > 612 93239500. > Please also delete this email and destroy any hard copy. > You must not disclose or use the information in this email. > ______________________________________________________________________ > > ______________________________________________________________________ > _________ UNSUBSCRIBE or access ARSlist Archives at > http://www.wwrug.org > > > > ______________________________________________________________________ > _________ UNSUBSCRIBE or access ARSlist Archives at > http://www.wwrug.org > > ______________________________________________________________________ > This email contains confidential information intended only for the > addressee. If you received it in error, please inform us by reply > email or by calling us from Australia on 131 137 or Internationally on > 612 93239500. > Please also delete this email and destroy any hard copy. > You must not disclose or use the information in this email. > ______________________________________________________________________ > > ______________________________________________________________________ > _________ UNSUBSCRIBE or access ARSlist Archives at > http://www.wwrug.org > > > > > > > > Shafqat Ayaz > > > > > ________________________________ > Do you Yahoo!? > Everyone is raving about the all-new Yahoo! Mail Beta. > __20060125_______________________This posting was submitted with HTML > in it___ > > ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
