On Tuesday 15 December 2009 18:21:37 Joe D'Souza wrote: > Victor, > > This article might help you and your DBA to implement the change.. I would > suggest backing up the database before you do it although it is not > necessary since it doesn't really alter the database or its table but just > the collation.. Do let me know if it helps. > > http://bytes.com/topic/sql-server/answers/80144-change-case-sensitivity-aft >er-database-set-up > > Joe > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[email protected]]on Behalf Of Victor Olufowobi Sent: Tuesday, > December 15, 2009 7:09 AM > To: [email protected] > Subject: Re: View form from Oracle based AR system to remote MS SQL > server > > > ** > Conny/Joe, > > Thanks very much for your suggestions and instructions - I've finally got > the view form working as needed. It was exacly as Conny explained - I > included "Next-ID-Commit: T" in ar.conf and the problem was solved!. > > Just one more question Joe, you wrote that there's a way of dealing with > case sensitivity problem. Could you write more about that?. I need to > access multiple tables from MSSQL server and would hate to ask the DBA to > redo all field names to upercase. > > Thank you all very much for your help. > > Victor > > > > > On Sun 13/12/09 21:16 , Joe D'Souza [email protected] sent: > > > ** > Victor, > > Try that suggestion from Conny before the stored procedure I suggested. > Maybe that is what is required to insert that missing commit. > > If Conny's suggestion still doesn't work, then write a stored procedure > that performs that update, with a commit at the end like I suggested a > couple of emails ago.. I'll copy the design of the body of that stored > procedure just for your benefit again because previously I suggested update > but it looks like its an insert you need to create a stored procedure for.. > BEGIN > insert into tablen...@..... ( ) values (); > commit; > END; > > Maybe before you try all this (either mine or Conny's suggestion) SQL > logging may indicate where a commit is missing.. That is worth a try too.. > Oracle SQL logging would also help you find where a commit is missing.. > > Joe > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[email protected]]on Behalf Of Conny Martin Sent: Sunday, > December 13, 2009 7:57 AM > To: [email protected] > Subject: AW: View form from Oracle based AR system to remote MS SQL > server > > > ** > Victor, > > running only the insert statement from sqlplus is not the whole > story. > > If you create an entry through ARS there are a bunch of statements > which gets executed. Immediately before the insert are 2 statements to > generate the request_id. You should see something like this in your > sql-logfile. > > update arschema set nextid = nextid + 1 where schemaid = xxxx; > select nextid from arschema where schemaid = xxxx; > insert into Txxxx (cxxx,cyyy,czzz) values ('x','y','z'); > > Try to run these 3 in sqlplus. IMHO error ORA-02047 indicates some > problem with handling distributed transactions. If this sequence of > statements generates an error in sqlplus try a commit; after the first 2 > statements. If this solves the problem you can set "Next-ID-Commit: T" in > ar.conf. This causes ARS to issue a commit after generating the nextid. > > HTH > > Kind Regards Conny > > > > > > -------------------------------------------------------------------------- > Von: Action Request System discussion list(ARSList) > [mailto:[email protected]] Im Auftrag von Victor Olufowobi Gesendet: > Sonntag, 13. Dezember 2009 09:41 > An: [email protected] > Betreff: Re: View form from Oracle based AR system to remote MS SQL > server > > > ** > Thanks again Joe, > > I will try what you suggested and have the outcome posted - but I > want you to consider the following: - I can update without problem using > the view form created. It's when I'm inserting (CREATE operation) the error > occurs - I can successfull run the INSERT command ARS is trying to run from > sqlplus > > Victor > > On Sat 12/12/09 14:56 , Joe D'Souza [email protected] sent: > > > Victor, > > Well that's half the battle won then.. Yes MS-SQL can be weird when > it comes to case sensitivity of view names and columns within views even.. > For e.g. if your ARS was hosted on a MS-SQL server, and the view name was > in small case and you tried creating a view form in upper case, it would > not recognize that name. There is a way to override that case sensitivity > but we won't deal with that since you already updated the view to have it > upper cased.. > > This is what I found for ORA-02047. > > http://ora-02047.ora-code.com/ > > Based on the clue provided with the explanation of the error code, > can you the Run Process Application-Release-Pending just an action before > updating the MS-SQL view data in your workflow - and if that does not work > the same action after the update action to that DB-Link? I'm assuming that > will force any pending commits before (or if necessary after) you try > updating the external view using that DB-Link in light of what the article > in the above link says.. > > If that does not work (and the commit is required while and not > before or after the update), try writing a stored procedure that has a > commit inside of it - e.g.. > > BEGIN > update tablen...@..... set ... where ...; > commit; > END; > > Use that stored procedure in a direct SQL at the point where you > want to run that update to the foreign database. Make sure that you declare > all the variables that you need while defining the stored procedure. > > Let me know how it goes.. > > Cheers > > Joe > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [[email protected]]on Behalf Of Victor > Sent: Saturday, December 12, 2009 3:48 AM > To: [email protected] > Subject: Re: View form from Oracle based AR system to remote MS SQL > server > > On Thursday 10 December 2009 21:16:14 Joe D'Souza wrote: > > Hello Victor, > > > > That is what I suspected with the invalid identifier error.. if > > your table was not recognized it would be invalid table or view > > name error which is not your case.. Which is why the later part > > of my previous email holds good.. Take a SQL log, you may find > > that offending column name or > > columns.. > > > There is something in the way that ARS is naming the offending > > column or columns causing that error.. > > > > When you find what column it is email the list or me directly and > > we'll > > try > > > to figure it out.. you might need to create a independent view in > > MS-SQL > > of > > > that table having names of fields that are legal to use in case > > there is a column name there that the ARS converts into something > > else.. This is just a speculative solution, we'll know more when > > you know more.. > > > > Cheers > > > > Joe > > > > -----Original Message----- > > From: Action Request System discussion list(ARSList) > > [[email protected]]on Behalf Of Victor > > Sent: Thursday, December 10, 2009 3:00 PM > > To: [email protected] > > Subject: Re: View form from Oracle based AR system to remote MS > > SQL server > > > > > > Thanks Joe, > > > > I will try all you said and have you posted tomorrow(I'm out of > > office at the moment) but I want to point out that will creating > > the view form I was able to load the table and the pick the > > fields required for the form. Only when I pressed "Create" the > > error occurred. > > > > .. and furthermore the ms sql username and password was > > hard-cored into > > the > > > public DB link created > > > > Victor > > > ___________________________________________________________________________ > > >____ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > > Platinum Sponsor:[email protected] ARSlist: "Where the > > Answers Are" > > Joe, > > Thank you for pointing me to the right direction!. > > I set on SQL log as suggested and found out that while SELECTing > fields from MSSQL server to create a view in Oracle for the view form, ARS > was unable to parse fields created in Oracle db with fields from MSSQL > server. This is because the field names in MSSQL were in lowercases while > in Oracle they were all in uppercases! (can this behaviour be changed?) > I redo the fields in SQL server to uppercases and I was able to > complete the creation of the view form!. > Thanks a lot for that. > > However, when I tried to submit to the form I received this error: > "ARERR [552] Failure during SQL operation to the database: > ORA-02047: cannot join the distributed transaction in progress" > > Once again I set on the SQL log. > ARS was trying to INSERT the required values to the view created > for the view form when the error occurred. > > I ran the command ARS was trying to run in sqlplus and it was > executed successfully. > > Do you have an Idea what might be the cause of the error in ARS? > > Thanks very much for you suggestions so far. > > Victor > > ___________________________________________________________________________ >____ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" Joe,
The problem is not with MSSQL server - it is definitively case-insensitive, the problem is rather with the ARS Oracle server. I was hoping there is a way to solve the fields name matching problem while creating view form to MSSQL tables without redoing the fields name to uppercase in MSSQL server or setting DB- Insensitive parameter to True in ARS configuration file.... If there's no other way I'll manage anyway. So thank you all for your help Victor _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
