Mark and Fred,
Back from vacation and back into the remote issue.
I created a simple test view in the MS SQL database ARSystem using the
following syntax
SELECT CAST(CONTROL_UNIQUE_ID AS int) AS UNIQUE_ID, CAST(CONTROL_NUM AS
varchar) AS Control_Num, CAST(PLANT_CODE AS varchar)
AS Plant_Code
FROM ORAORQ10..PROP_MGT.TEQUIP AS TEQUIP_1
Called this view view_prop_tequip
I was then able to make a View Form from that sql view.
I can run a
select *
from view_prop_tequip
where Plant_Code = 'X'
in the MS SQL Server Management Studio and get many records returned. It takes
9 min 43 sec to complete the query with 57814 records returned.
I can also do the same for the view created by Developer Studio.
Select *
>From vwf_Prop_Tequip_1
Where Plant_Code = 'X'
The select statement took 9 min 17 sec with 57814 returned.
Both take a few seconds to start displaying the data but they work.
Now the problem comes when I run the search on the view form in WUT.
I get the searching dialog until it times out. Nothing is returned even if I
enter an exact value into one of the three fields.
I'm not sure where to look now. I don't see much in the arsql.log except for
the select statement which has a TOP 20001 for my max getlist entry.
I'll try increasing that to 70000 but I'm stuck now.
Thank you,
---
John J. Reiser
Remedy Developer/Administrator
Senior Software Development Analyst
Lockheed Martin - MS2
The star that burns twice as bright burns half as long.
Pay close attention and be illuminated by its brilliance. - paraphrased by me
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Brittain, Mark
Sent: Friday, February 03, 2012 10:57 AM
To: [email protected]
Subject: EXTERNAL: Re: Create a View form of a remote oracle DB on ARSystem (MS
SQL)
Agree with Fred that your source in the Remedy view is going to be all upper
case. I think what you need is simply db_name.table_name. I have views set up
this way.
On queries I have table_name@db_name and all of my statements are lower case.
Mark
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Ben Chernys
Sent: Friday, February 03, 2012 10:24 AM
To: [email protected]
Subject: Re: Create a View form of a remote oracle DB on ARSystem (MS SQL)
That is as I said in my email: that tables, fields, views in the Oracle data
dictionary are stored in uppercase.
<snip>
and
seLECt disTINCT table_nAMe from all_tAB_columns where oWNer = 'ARADMIN' and
taBLe_name like 'a%'
will return no records.
<snip>
But for Oracle queries any table, column etc can be in mixed case. The SQL
parser handles case conversions.
I do not use ARS View forms instead getting the data directly into ARS through
my own queries.
Cheers
Ben
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Grooms, Frederick W
Sent: February-03-12 15:32
To: [email protected]
Subject: Re: Create a View form of a remote oracle DB on ARSystem (MS SQL)
Ben,
For normal queries you are correct, but I believe ARS queries the
all_user_tables or all_user_views to validate you have entered an existing view
or table name when you are creating a view form. The result of this is that
the name you put in for the view form's source must be in all uppercase for
Oracle.
i.e. The following will never find a record as CONTROL is stored all uppercase
in the database.
Select table_name from all_user_tables where table_name = 'control'
Fred
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Ben Chernys
Sent: Friday, February 03, 2012 2:58 AM
To: [email protected]
Subject: Re: Create a View form of a remote oracle DB on ARSystem (MS SQL)
Just an FYI
Oracle tables names, view names, columns names, functions, etc are NOT case
sensitive. Oracle data is. So, mixing case on view names is not important
unless the view name is being treated as data (as in an Oracle dictionary
table) where it is indeed stored in uppercase.
So, using a non-linked database query as an example:
select company from com_company
is entirely equivalent to
SeleCT COMpany from COM_company
So,
seLECt disTINCT table_nAMe from all_tAB_columns where oWNer = 'ARADMIN' and
taBLe_name like 'A%'
will return all ARS tables and views starting with 'A'
and
seLECt disTINCT table_nAMe from all_tAB_columns where oWNer = 'ARADMIN' and
taBLe_name like 'a%'
will return no records.
BTW if you do not have an Oracle client, queries on this table
(all_tab_columns) is a good way to get a "DESCRIBE".
Cheers
Ben
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Grooms, Frederick W
Sent: February-02-12 22:59
To: [email protected]
Subject: Re: Create a View form of a remote oracle DB on ARSystem (MS SQL)
I believe it is Case sensitive. All Oracle table and view names are
uppercase. Try link.db.OWNER.TABLE
You may find it cleaner to create a local database view that does the remote
query. Then you should not have to worry about the case sensitivity from
Remedy.
Fred
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Reiser, John J
Sent: Thursday, February 02, 2012 3:50 PM
To: [email protected]
Subject: Create a View form of a remote oracle DB on ARSystem (MS SQL)
Hello Listers,
MS SQL 2005 on a SAN
MS Windows 2003 Enterprise
AR System 7.6.03 Build 001
Our system connects to a remote Sybase DB via the DB Link for ARS Croom
Consulting.
The Sybase DB is being replaced by an Oracle DB.
We only need to read data so I am trying to make the oracle connection with a
View form.
The MS SQL dba created a Linked server to the Oracle DB and confirmed the
connectivity.
In the View form should I use the MS SQL connection string or an Oracle
connection string?
<linkname>.<db_name>.<owner>.<table> or <db_name>.<table>@<linkname>
The value that I tried to load was the ORAORQ10.propmgt.propmgt.equipment.
That gives an ARERR 552 message
I have also tried propmgt.equipment@ORAORQ10 That gives ARERR 481 but I'm not
sure if the "table not found" is because of case sensitivity or just the wrong
connection string format.
I've successfully connected to a different linked db but it is a remote MS SQL
so I use the link.db.owner.table format for that.
Thanks in advance.
---
John J. Reiser
Remedy Developer/Administrator
Senior Software Development Analyst
Lockheed Martin - MS2
The star that burns twice as bright burns half as long.
Pay close attention and be illuminated by its brilliance. - paraphrased by me
____________________________________________________________________________
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12
www.wwrug12.com ARSList: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12
www.wwrug12.com ARSList: "Where the Answers Are"
This e-mail is the property of NaviSite, Inc. It is intended only for the
person or entity to which it is addressed and may contain information that is
privileged, confidential, or otherwise protected from disclosure. Distribution
or copying of this e-mail, or the information contained herein, to anyone other
than the intended recipient is prohibited.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12
www.wwrug12.com ARSList: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
