Re: View form from Oracle based AR system to remote MS SQL server
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:arsl...@arslist.org]on Behalf Of Victor Olufowobi Sent: Tuesday,
December 15, 2009 7:09 AM
To: arslist@ARSLIST.ORG
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 jdso...@shyle.net 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:arsl...@arslist.org]on Behalf Of Conny Martin Sent: Sunday,
December 13, 2009 7:57 AM
To: arslist@ARSLIST.ORG
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 = ;
select nextid from arschema where schemaid = ;
insert into T (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:arsl...@arslist.org] Im Auftrag von Victor Olufowobi Gesendet:
Sonntag, 13. Dezember 2009 09:41
An: arslist@ARSLIST.ORG
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 jdso...@shyle.net 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
Re: View form from Oracle based AR system to remote MS SQL server
Column, table, view names in Oracle by default is case insensitive.. If you
notice, Oracle stores all its meta data in upper case, and yet you can write
a perfectly legal SQL query to Oracle in lower case and it will work.
On the other hand, MS-SQL in my past experience with it, is case sensitive
when referencing the same names..
Then I guess again it depends on how your two databases have been setup..
Try issuing queries in MS-SQL's query analyzer using mixed or the opposite
case and check your results.. And implementing the solution in the article
below will not really alter your tables or your data, so it may be worth
trying. You may need to restart MS-SQL or run RECONFIGURE.. or run
RECONFIGURE and then restart..
Joe
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org]on Behalf Of Victor
Sent: Wednesday, December 16, 2009 12:48 PM
To: arslist@ARSLIST.ORG
Subject: Re: View form from Oracle based AR system to remote MS SQL
server
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:arsl...@arslist.org]on Behalf Of Victor Olufowobi Sent: Tuesday,
December 15, 2009 7:09 AM
To: arslist@ARSLIST.ORG
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 jdso...@shyle.net 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:arsl...@arslist.org]on Behalf Of Conny Martin Sent: Sunday,
December 13, 2009 7:57 AM
To: arslist@ARSLIST.ORG
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 = ;
select nextid from arschema where schemaid = ;
insert into T (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:arsl...@arslist.org] Im Auftrag von Victor Olufowobi Gesendet:
Sonntag, 13. Dezember 2009 09:41
An: arslist@ARSLIST.ORG
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
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 jdso...@shyle.net 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:arsl...@arslist.org]on BEHALF OF Conny Martin
SENT: Sunday, December 13, 2009 7:57 AM
TO: arslist@ARSLIST.ORG
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 = ; select nextid from arschema where schemaid =
; insert into T (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:arsl...@arslist.org] IM AUFTRAG VON Victor Olufowobi
GESENDET: Sonntag, 13. Dezember 2009 09:41
AN: arslist@ARSLIST.ORG
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 BODY { FONT-FAMILY: Arial, Helvetica, sans-serif; FONT-SIZE:
12px }
Victor
On Sat 12/12/09 14:56 , Joe D'Souza jdso...@shyle.net 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/ [1]
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)
[arsl...@arslist.org [2]]On Behalf Of Victor
Sent: Saturday
Re: View form from Oracle based AR system to remote MS SQL server
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-after-database-set-up
Joe
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org]on Behalf Of Victor Olufowobi
Sent: Tuesday, December 15, 2009 7:09 AM
To: arslist@ARSLIST.ORG
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 jdso...@shyle.net 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:arsl...@arslist.org]on Behalf Of Conny Martin
Sent: Sunday, December 13, 2009 7:57 AM
To: arslist@ARSLIST.ORG
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 = ;
select nextid from arschema where schemaid = ;
insert into T (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:arsl...@arslist.org] Im Auftrag von Victor Olufowobi
Gesendet: Sonntag, 13. Dezember 2009 09:41
An: arslist@ARSLIST.ORG
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 jdso...@shyle.net 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
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 BODY { font-family:Arial, Helvetica,
sans-serif;font-size:12px; }
Victor
On Sat 12/12/09 14:56 , Joe D'Souza jdso...@shyle.net 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/ [1]
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)
[arsl...@arslist.org [2]]On Behalf Of Victor
Sent: Saturday, December 12, 2009 3:48 AM
To: arslist@ARSLIST.ORG [3]
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)
[arsl...@arslist.org [4]]On Behalf Of Victor
Sent: Thursday, December 10, 2009 3:00 PM
To: arslist@ARSLIST.ORG [5]
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 [6]
Platinum Sponsor:rmisoluti...@verizon.net [7] 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
Re: View form from Oracle based AR system to remote MS SQL server
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...@. ( column names separated by comma) values
(values corresponding to the column names);
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:arsl...@arslist.org]on Behalf Of Conny Martin
Sent: Sunday, December 13, 2009 7:57 AM
To: arslist@ARSLIST.ORG
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 = ;
select nextid from arschema where schemaid = ;
insert into T (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:arsl...@arslist.org] Im Auftrag von Victor Olufowobi
Gesendet: Sonntag, 13. Dezember 2009 09:41
An: arslist@ARSLIST.ORG
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 jdso...@shyle.net 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)
[arsl...@arslist.org]on Behalf Of Victor
Sent: Saturday, December 12, 2009 3:48 AM
To: arslist@ARSLIST.ORG
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
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) [mailto:arsl...@arslist.org]on Behalf Of Victor Sent: Thursday, December 10, 2009 3:00 PM To: arslist@ARSLIST.ORG 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:rmisoluti...@verizon.net 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:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: View form from Oracle based AR system to remote MS SQL server
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) [mailto:arsl...@arslist.org]on Behalf Of Victor Sent: Saturday, December 12, 2009 3:48 AM To: arslist@ARSLIST.ORG 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) [mailto:arsl...@arslist.org]on Behalf Of Victor Sent: Thursday, December 10, 2009 3:00 PM To: arslist@ARSLIST.ORG 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:rmisoluti...@verizon.net 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:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: View form from Oracle based AR system to remote MS SQL server
Victor, When you access the database view from oracle using SQL*Plus, are you logging into SQL*Plus using the same username and password (ARAdmin/AR#Admin# if you are using defaults) that is used by the AR System to connect to the Oracle database? If no, then do that and then try to access the table. If you get the same error when you try it that way, it means that the DB link you have created does not have sufficient privileges for that user that the AR System uses. Ask your DBA to fix that.. If you can view the contents of that table after logging in as the AR System database user ARAdmin, then you are trying to address that view incorrectly while creating the view form. Make sure that you are using the same name for the view when building it from the AR System development interface, as you are using when addressing it from SQL*Plus when connected as ARAdmin.. Check on the above before reading below... make sure you have enough privileges to query that db link from ARAdmin and that you are addressing the view name correctly.. My gut feelings are you are using the right name, and have sufficient privileges. Invalid Identifier errors in Oracle, is often due to the fact that a column name used in an SQL statement, is an invalid column name. You might want to turn up SQL logging to see what column is being addressed wrongly. I'm betting on that the Request Identifier column that you are selecting to create the view is being wrongly addressed by the SQL statement generated by Remedy. Check your SQL log, and the SQL statement that is generating that error. Copy that statement into SQL*Plus, execute it, and then try correcting that statement (use ed line editor of SQL*Plus) and correct the column name that is being addressed wrongly, then execute it till it runs correctly. Tell us what you find there.. what works wrong and what needs to be fixed on your SQL so that it may work correctly.. Cheers Joe -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of Victor Sent: Thursday, December 10, 2009 1:23 PM To: arslist@ARSLIST.ORG Subject: View form from Oracle based AR system to remote MS SQL server Listers, Is it possible to create view forms from an Oracle based AR system to remote MS SQL Server? I successfully created the database link and I could access the MS SQL tables from sqlplus but when I tried to create view forms I received: Failure during SQL operation to the database: ORA-00904: Invalid identifier (ARERR 552) Operation performed on ARSystem 6.3 on windows 2k server. The MS SQL server is also on windows 2k server. Any feedback will be appreciated Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: View form from Oracle based AR system to remote MS SQL server
On Thursday 10 December 2009 20:30:37 Joe D'Souza wrote: Victor, When you access the database view from oracle using SQL*Plus, are you logging into SQL*Plus using the same username and password (ARAdmin/AR#Admin# if you are using defaults) that is used by the AR System to connect to the Oracle database? If no, then do that and then try to access the table. If you get the same error when you try it that way, it means that the DB link you have created does not have sufficient privileges for that user that the AR System uses. Ask your DBA to fix that.. If you can view the contents of that table after logging in as the AR System database user ARAdmin, then you are trying to address that view incorrectly while creating the view form. Make sure that you are using the same name for the view when building it from the AR System development interface, as you are using when addressing it from SQL*Plus when connected as ARAdmin.. Check on the above before reading below... make sure you have enough privileges to query that db link from ARAdmin and that you are addressing the view name correctly.. My gut feelings are you are using the right name, and have sufficient privileges. Invalid Identifier errors in Oracle, is often due to the fact that a column name used in an SQL statement, is an invalid column name. You might want to turn up SQL logging to see what column is being addressed wrongly. I'm betting on that the Request Identifier column that you are selecting to create the view is being wrongly addressed by the SQL statement generated by Remedy. Check your SQL log, and the SQL statement that is generating that error. Copy that statement into SQL*Plus, execute it, and then try correcting that statement (use ed line editor of SQL*Plus) and correct the column name that is being addressed wrongly, then execute it till it runs correctly. Tell us what you find there.. what works wrong and what needs to be fixed on your SQL so that it may work correctly.. Cheers Joe -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of Victor Sent: Thursday, December 10, 2009 1:23 PM To: arslist@ARSLIST.ORG Subject: View form from Oracle based AR system to remote MS SQL server Listers, Is it possible to create view forms from an Oracle based AR system to remote MS SQL Server? I successfully created the database link and I could access the MS SQL tables from sqlplus but when I tried to create view forms I received: Failure during SQL operation to the database: ORA-00904: Invalid identifier (ARERR 552) Operation performed on ARSystem 6.3 on windows 2k server. The MS SQL server is also on windows 2k server. Any feedback will be appreciated Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are 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. Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
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:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: View form from Oracle based AR system to remote MS SQL server
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) [mailto:arsl...@arslist.org]on Behalf Of Victor Sent: Thursday, December 10, 2009 3:00 PM To: arslist@ARSLIST.ORG 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:rmisoluti...@verizon.net ARSlist: Where the Answers Are
