1 of 3 Oracle Service Name working

[wndrfl]

Hello,

I have a problem with Crystal Enterprise that I have not been able to resolve for a few weeks. We have a server with CE and another server (behind a firewall) with Oracle.

In tnsnames.ora we have 3 service name defined, with almost the same configuration: pointing to the server (firewall), selecting TCP port 1521. The only difference between them is the instance name like "inv" and "inv1".

I am sitting right in front of this CE server and just tested all 3 Oracle sources with the Net8 Assistant tool, each one is working properly, logging me in.

But!

Crystal Enterprise does not see all the 3. It can use only one. And every time the same. I tried to put that one to the beginning of tnsnames.ora and also to the end, it worked and the other 2 did not.

I am getting a "Cannot open SQL server." error message.

I cannot understand this. The three Oracle service definitions are almost the same, differing in only one character in the instance name.

Any hints?

 

[wndrfl]

(Info: CE8, Oracle8)

 

[Turkbear]

Hi,
If the message indicates 'SqlServer' then, as you indicated,the connection to Oracle is failing - is the firewall configured to deal with Oracle's random port assignment for the connection ( that you can connect to each from the server seems to indicate that it is)..

Does any report using those instances fail?

 

[wndrfl]

Goog morning,

there is no problem with the firewall for sure. It goes with the same setting for 'me' with my pc and Crystal Reports and for the server with Crystal Enterprise. There is no difference at all.

And while I am able to run the report, Crystal Reports can handle all of Pracle sources, the server can not. (Of course, tnsnames.ora files are the same as well.)

This is the most interesting in the whole story.

 

[Turkbear]

Hi, Just one more clarification ( maybe):
Can you, from the server that runs Crystal Enterprise , connect to the 3 instances of Orarcle using SqlPlus?

Can you run the reports in Crystal Repprts Pro ( again, from that server, not your PC)?

 

[wndrfl]

Hi,
yes and no.
Yes, I can connect to and log in all of the 3 Oracle databases from the server with Crystal Enterprise.
And no, I could not try it because there is no Crystal Reports installed on the server, only Enterprise.

 

[Turkbear]

Hi,
OK, that's enough to confirm that the Oracle client stuff is probably fine..

Given that, the only reason I can see for the reports themselves having problems is some difference in how the reports specify the data source...or a difference in the tnsnames.ora files between the PC and the CE Server.

Can you re-confirm that tnsnames.ora files are the same ( i.e pointing to the same host/instance/port for each case )?

 

[wndrfl]

They are _almost_ identical since I made them with Copy and Paste )
The only difference is that on the client, port 1522 is used. On the server it is 1521. I got these from firewall operations guys. (From my PC I have to use port 1522 and a CryptoCard to identify myself. On the other hand, port 1521 is open for the server's fix IP address.)

The appipriate line on the client:
(ADDRESS = (PROTOCOL = TCP)(HOST = fw-mc.hu.**.com)(PORT = 1522))
And on the server:
(ADDRESS = (PROTOCOL = TCP)(HOST = fw-mc.hu.**.com)(PORT = 1521))

And ... this line is the same for all 3 sources, and one of them is working. But only one!