Hi Larry, Glad it worked for you..
Just curious - what is the name of the Sybase equivalent system table that is like the Oracles all_tables or user_tables?? That information might be useful to me sometimes.. Cheers Joe ________________________________ From: L G Robinson <n...@ncsu.edu> To: arslist@ARSLIST.ORG Sent: Wednesday, February 4, 2009 4:08:42 PM Subject: Re: Trapping SQL error in an active link Hi Folks, Thanks for all of the great suggestions. I ended up implementing a combination of Jiri's and Joe's suggestions. I wrote a stored procedure that pokes around in the system tables to make sure that the table I want to query exists and has the correct permissions. If so, I issue the query. If not, just quietly return nothing. One I had that, it was a simple change to the active link to call the stored procedure instead of issuing the SELECT. Works great. Thanks again... you guys are great! Larry On Feb 3, 2009, at 10:02 AM, LJ Longwing wrote: > Sorry if this seems overly simply...but a VERY simple procedure would > be to not allow DB changes to the production DB in the middle of a > production day. That's what maintenance windows are for. I would say > a company policy preventing them from doing such a thing would stop > them, and stop the issue you are experiencing. On Feb 3, 2009, at 10:15 AM, Jiri Pospisil wrote: > Another option is to build the query into a database > function/procedure (or whatever this kind of objects might be called > in Sybase) The function/procedure would run the query and store the > result into a temporary table within the Remedy database. The active > link would first call the stored function/procedure and then read the > result from the temporary table. If there is an error, it will be > handled within the stored procedure and the set field from temporary > table will then simply return no records. > > The stored procedure would perhaps need to have a GUID as an input > parameter so that when the active link searches the temporary table, > it knows which entries to look for. You may even pass the query as > another input parameter to the stored procedure. Have done it in > ORACLE, but do not know if Sybase allows this. > > Hope this helps. > > Jiri Pospisil On Feb 3, 2009, at 11:42 AM, Joe DeSouza wrote: > Larry, > > If there is an equivalent of a all_tables or user_tables in sybase, > run a query to that table to check the existence of the table in > question, and if that query returns a negative, do not run the second > SQL that you currently are running against the table in question. > > Joe On Feb 3, 2009, at 12:57 PM, Carey Matthew Black wrote: > Larry, > > Since you on ARS 5.01.02... you options may be more limited.... > > However, my suggestion would be to externalize this work to a command > line script that lives on your AR System server. The process would be > like this... > > Active link: > Set Field action: > $PROCESS$ Create a GUID in a local tmp field > $PROCESS$ @@:Call the script and pass the GUID and any other data > elements needed to do the search. > Set Field action: > Try to get data from the ARS temp form using the GUID. > > The script would do something like this: > Login to the DB. > Try to get data for the requested search. > If data was found then create a record in an "ARS Form" and supply > the GUID for the AL to be able to find the right record. > If error was received from the DB... then just exit the script > normally.(no error, no output) > > > Then you could add an escalation to delete records from the "ARS Form" > that are more than say... a day old? > > P.S. If you can not use the GUID feature.. then use $ USER $ and $ > TIMESTAMP $. Those two values should work fairly well in combination > too. > > HTH. On Feb 3, 2009, at 9:51 AM, L G Robinson wrote: > Hi Folks, > > I have a problem with an active link set field action that > utilizes an SQL statement. The problem stems from the fact that > the SQL command is issued against a database table that is > maintained by another group. Occasionally, they will update the > table in the middle of the day, causing the table to briefly > cease to exist or lose it's permissions. When this happens, the > active link fails with one of the following error messages: > > ARERR [552] Failure during SQL operation to the database: > E911..E911Remedy not found. Specify owner.objectname or use > sp_help to check whether the object exists. > > ARERR [552] Failure during SQL operation to the database: > SELECT permission denied on object E911Remedy, database E911, > owner dbo (Sybase 10330) > > This data is not critical to the normal functioning of the > application, so I am looking for a solution that allows me to > "ignore" this error in the active link. As far as I can tell, I > can control what happens if "no requests match" and if "multiple > requests match", but there is no way to trap the SQL error > condition and choose to ignore it. > > About the only thing I have been able to come up with so far is > to run a cron job periodically that will check for the existence > of the table and the correct permissions. The cron job could then > set some external flag in the file system or call an API program > to set some internal global within my application. Then I could > check the global or the external flag in an active link before > invoking the SQL call. Seems overly complicated and convoluted to > me. > > Does anyone have a better approach? > > ARS: 5.01.02 patch 1313 on Solaris 2.8 > Sybase: 12.5.2/EBF 11790 on Solaris 2.8 > > > Thanks. > Larry > > > Larry Robinson n...@ncsu.edu > Office of Information Technology > NC State University 919-515-5432 Voice > Raleigh, NC 27695-7109 919-513-1893 FAX _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are"