Gary, Have you had any luck getting your connection? I was able to make a view of the Linked Oracle Table in MS SQL Management Studio but I had to CAST each column to int or char before the Dev Studio could load the view. I then went back into the MS SQL Manager and queried the remote DB from the initial view, the Remedy Txxx view (Remedy ARSystem makes a view labeled as a T-table) and finally the view that is my View form. I can return data from each instance but not from the WUT. It times out even if I put the Unique ID from the Oracle table in the Request-ID field.
I'm waiting for the Oracle DBA to possibly provide some insight. Log files don't show much except for the select TOP 70001 ... which is my query return limit. Hope your luck is better than mine. --- 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:arslist@ARSLIST.ORG] On Behalf Of Gary Dries Sent: Tuesday, March 06, 2012 1:23 PM To: arslist@ARSLIST.ORG Subject: EXTERNAL: Re: Trying to build a view form to a Remote Oracle DB from a Remedy SQL DB Thanks for the insight, this is definitely an option. The main concern with this solution is that I am not the SQL DBA and do not have and admin access to the DB or application servers, so I am now dependant on 3 parties to help support this process (myself, Oracle DBA, SQL DBA), plus it adds another point of failure. My main goal was to control as much of the process at the application level and let ARS do what it is designed to do. I have built a dblink and a view form from remedy to a remote db in the past, about 6 years ago, but in a different configuration ARS on Oracle and the external db on SQL and that worked with any issues at all. The difference was not only the order of the db servers but I also had full Server Admin rights, where I am at now, I do not have the elevated privileges. On 3/6/12, Reiser, John J <john.j.rei...@lmco.com> wrote: > Gary, > > Jean-Louis is correct. > I am trying the same thing on ARS 7.6.03. > Had the DBA create a Linked Server on MS SQL using DTS. > I created a view in the ARSystem database using MS SQL Server > management Studio. > 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 LINKNAME..DATABASENAME.TABLENAME AS TABLENAME_1 > I could then create an ARSystem View Form but that is as far as I got. > I can query either view in MS SQL Studio and get results but not from > the WUT running a search on the View Form. > I even tried a Direct SQL set fields action with no luck. > I'll post more if I get something working. > > > --- > 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:arslist@ARSLIST.ORG] On Behalf Of Halleux, Jean-Louis > Sent: Tuesday, March 06, 2012 11:04 AM > To: arslist@ARSLIST.ORG > Subject: EXTERNAL: Re: Trying to build a view form to a Remote Oracle > DB from a Remedy SQL DB > > Hi Gary, > > According to my experience, creating a view form on a db link does not > work well. What I usually do in that case is creating an SQL view, in > the DB direclty, that will use the db link; and then create the view > form on the SQL view. > > Best regards, > Jean-Louis Halleux > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Gary Dries > Sent: mardi 6 mars 2012 16:44 > To: arslist@ARSLIST.ORG > Subject: Trying to build a view form to a Remote Oracle DB from a > Remedy SQL DB > > I have a need to build a view form to a table on a Remote Oracle DB > 10g from our ARS 7.1.06 running sql 2005 DB server > > Has anyone been sucessful doing this? > > We created the DBlink between the 2 dbs and we are able to connect > from the sql server via other tools like odbc or sql plus and see the > table on oracle db. > > ARAdmin is the oracle table owner and has read/write access, (I am > still > scptical) attempted to create the view using the parameter > LINK.DATABASE.TABLEOWNER.TABLE and I always recieve the error in the > Admin tool "Failure during SQL operation to the database : > CMS_TEST.CMSTEST.ARADMIN.REMEDY_CASES (ARERR 552)" > > SQL Log posts this error > *** ERROR *** Invalid use of schema or catalog for OLE DB provider > "MSDASQL" for linked server "CMS_TEST". A four-part name was supplied, > but the provider does not expose the necessary interfaces to use a > catalog or schema. (SQL Server 7312) > > I have worked with BMC Support and they back up all of our > configuration and settings in the ar.cfg file, and all other system settings. > > Any Ideas? > > -- > Gary Dries > OSSNMS / Sr. Remedy Developer > T-Mobile USA > Office (972) 464-3235 > > ______________________________________________________________________ > __ > _______ > 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" > > ______________________________________________________________________ > _________ 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" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
