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"
