Remedy and SQL

[phuzz]

Is it possible to manipulate the data in Remedy (more
specifically in the uderlying SQL tables) using standard
ADODB connections within Visual Basic. That is to say
can I run UPDATE statements on the Remedy tables directly
within SQL.

 

[jetspin]

Hi.

It is possible with some work. We were able to set up a web page to update employee data schema for phone numbers.

You may have already noticed that each field in remedy has a long descriptor number.

If you set up an odbc datasource connection to the sql remedy data base and use a sql query tool and look around at some tables, you will see lots of tables all with prefixes. Its kind of proprietory, but you may be able to figure it out. Add some info via remedy, do a query, delete some data, look at the same tables and you can kind of figure it out.

Anyway. We did this a couple years ago. With some digging, you can figure out how Remedy stores data and then go from there. We were able to set up sql statements from a web page
to query and update employee data on sql 6.5 so u can do it in VB.

Good Luck.

 

[chrishaw]

You can, but it isn't exactly recommended. The problem with doing updates of this sort is that when you do, you're bypassing any of the application logic you may have defined in Remedy. eg, if you have a required field, or if you're checking the format of a field in a filter or something of that sort, that is all enforced by the application server, not the database. So, you have to ensure that you are following any rules that are in Remedy with the data you write directly, otherwise it will cause problems.

As for writing to tables, instead of using the real tables (which start with T, and have field names of C + the field ID), use the views that are there in the database. They'll be named just like the schema in Remedy, but with underscores replacing spaces and x's replacing any special characters.

HTH

 

[wmd3]

I am trying to SELECT information out of a Remedy Database from a Visual Basic 6 program.

I am using ADODB and connecting to the Remedy ODBC Data Source.

I can open the connection with out error, but when I try to Execute my SQL Statment ("SELECT ...&quot I get the following error: [ISAM]No Data Found.

Can anyone help me?

Thanks in advance.
- Bill