• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 836
  • Last Modified:

ASP problem connecting with oracle database, even though designer works

Have spent multiple hours on the phone with Crystal Decisions on this one, and have not have any success.

I have a server running Windows 2000 Server. Oracle client is also installed. On that server, I have a virtual directory called Transfer, within which I have a folder with some crystal reports and another folder with some asp pages. There is a TNS name set up called ottr_test.

If I fire off a crystal report on the server with the full designer product, the report uses the native oracle driver and connects to the database on a solaris box, and the report runs to completion generating valid data without any issues. Database connectivity seems to be fine.

Within my asp page, I can open the report, get its parameters, etc, without any issues. If I try to .testconnectivity(), however, I always get “false.”  If I try to.LogOnServer, I get  “Microsoft VBScript runtime (0x800A0046) Permission denied /transfer/crtest_gpl.asp, line 79.”


Crystal Decisions had me set up a Com+ Application called “crystal” and then give it administrative rights. That hasn’t done the trick, either.

Does anyone have any ideas what I’m doing wrong? Or, what I might need to do to the server?

Example asp code….

<%

REPORT = "F:\Program Files\transfer\crystal\test simple report with odbc.rpt"

Set CRapp = Server.CreateObject("CrystalRuntime.Application")

%>
<HTML>
<HEAD>
      <TITLE>CR TEST</TITLE>
</HEAD>
<BODY bgcolor="#6666ff">
<H1>CR TEST</H1>
<H4><%=REPORT%></H4>
<%
Response.Write "CR Version " & CRapp.GetVersion() & "<BR>"
Set CRrep = CRapp.OpenReport(REPORT)
Response.Write "Report Opened.<BR>"
Response.Write "Tables: " & CRrep.Database.Tables.Count & "<BR>"
FOR i = 1 TO CRrep.Database.Tables.Count
      Response.Write ".... Name: " & CRrep.Database.Tables(i).Name & "<BR>"
      Response.Write ".... DLL: " & CRrep.Database.Tables(i).DllName & "<BR>"
      Response.Write ".... LOCATION: " & CRrep.Database.Tables(i).Location & "<BR>"
      Response.Write ".... SERVER: " & CRrep.Database.Tables(i).LogOnServerName & "<BR>"
      Response.Write ".... DATABASE: " & CRrep.Database.Tables(i).LogOnDatabaseName & "<BR>"
      Response.Write ".... USER: " & CRrep.Database.Tables(i).LogOnUserID & "<BR>"
      Response.Write ".... SESSION USER: " & CRrep.Database.Tables(i).SessionUserID & "<BR>"
      Response.Write ".... Connection: " & CRrep.Database.Tables(i).TestConnectivity() & "<BR>"
      Response.Write "-------------------------------------------------------------------------<BR>"
NEXT
Response.Write "Parameters: " & CRrep.ParameterFields.Count & "<BR>"
FOR i = 1 TO CRrep.ParameterFields.Count
      Response.Write ".... Name: " & CRrep.ParameterFields(i).Name & "<BR>"
      Response.Write ".... Kind: " & CRrep.ParameterFields(i).Kind & "<BR>"
      Response.Write ".... Min: " & CRrep.ParameterFields(i).MinimumValue & "<BR>"
      Response.Write ".... Max: " & CRrep.ParameterFields(i).MaximumValue & "<BR>"
      Response.Write ".... ParameterType: " & CRrep.ParameterFields(i).ParameterType & "<BR>"
      Response.Write ".... DiscreteOrRange: " & CRrep.ParameterFields(i).DiscreteOrRangeKind & "<BR>"
      Response.Write ".... ValueType: " & CRrep.ParameterFields(i).ValueType & "<BR>"
      Response.Write ".... Prompt: " & CRrep.ParameterFields(i).Prompt & "<BR>"
      Response.Write "-------------------------------------------------------------------------<BR>"
NEXT
Response.Write "Connecting ... <BR>"

'May need to set up a DSN or an Oracle database alias on webintel2

FOR i = 1 TO CRrep.Database.Tables.Count
      '##############################################################################
      'CRrep.Database.Tables(i).SetLogonInfo "dsn"   'optional:  ,"DB Name", "User ID", "Password"
       CRrep.Database.Tables(i).SetLogonInfo "ottr_test","","xxx","yyy"
       Response.Write i & ": " & CRrep.Database.Tables(i).TestConnectivity() & "<BR>"
      'maybe setting CRrep.Database.Tables(i).Location ...
      '##############################################################################
NEXT


'##############################################################################
'This is another option, but apparently shouldn't need it
'CRapp.LogOnServer "pdsodbc.dll", "Server Name"     'optional:   ,"DB Name", "User ID", "Password"
Response.Write "AT LINE BEFORE LOGON SERVER - LINE 77" & "<BR>"
'CRapp.LogOnServer "pdsora7.dll", "ottr_test" ,"", "xxx", "yyy"
Response.Write "AT LINE BEFORE LOGON SERVER - LINE 79" & "<BR>"
'##############################################################################

FOR i = 1 TO CRrep.Database.Tables.Count
      Response.Write i & ": " & CRrep.Database.Tables(i).TestConnectivity() & "<BR>"
NEXT

'Test Export
IF CRrep.ParameterFields.Count=0 THEN
      Response.Write "Testing Export ..."
      CRrep.DisplayProgressDialog = False
      CRrep.ExportOptions.DestinationType = 1
      CRrep.ExportOptions.DiskFileName = "F:\Program Files\transfer\storage\Test.rtf"
      CRrep.ExportOptions.FormatType = 35
      CRrep.Export False
      Response.Write "OK"
END IF

'##############################################################################
'CRapp.LogOffServer "pdsora7.dll", ""
'##############################################################################
%>
</BODY>
</HTML>
<%
CRrep.DiscardSavedData
Set CRrep = Nothing
Set CRapp = Nothing
%>
<!-- # include file="smartvieweractivex.asp" -->

0
pflugg
Asked:
pflugg
  • 2
1 Solution
 
pfluggAuthor Commented:
Oops....right code and example output is listed, below....

<%
'REPORT = "F:\Program Files\transfer\crystal\Test1.rpt"
REPORT = "F:\Program Files\transfer\crystal\K88_bmi_test.rpt"
'REPORT = "F:\Program Files\transfer\crystal\K65_HP.rpt"
'REPORT = "F:\Program Files\transfer\crystal\K84._Pharm_Bulletin.rpt"
'REPORT = "F:\Program Files\transfer\crystal\K1B_LtrActivate.rpt"

Set CRapp = Server.CreateObject("CrystalRuntime.Application")

%>
<HTML>
<HEAD>
      <TITLE>CR TEST</TITLE>
</HEAD>
<BODY bgcolor="#6666ff">
<H1>CR TEST</H1>
<H4><%=REPORT%></H4>
<%
Response.Write "CR Version " & CRapp.GetVersion() & "<BR>"
Set CRrep = CRapp.OpenReport(REPORT)
Response.Write "Report Opened.<BR>"
Response.Write "Tables: " & CRrep.Database.Tables.Count & "<BR>"
FOR i = 1 TO CRrep.Database.Tables.Count
      Response.Write ".... Name: " & CRrep.Database.Tables(i).Name & "<BR>"
      Response.Write ".... DLL: " & CRrep.Database.Tables(i).DllName & "<BR>"
      Response.Write ".... LOCATION: " & CRrep.Database.Tables(i).Location & "<BR>"
      Response.Write ".... SERVER: " & CRrep.Database.Tables(i).LogOnServerName & "<BR>"
      Response.Write ".... DATABASE: " & CRrep.Database.Tables(i).LogOnDatabaseName & "<BR>"
      Response.Write ".... USER: " & CRrep.Database.Tables(i).LogOnUserID & "<BR>"
      Response.Write ".... SESSION USER: " & CRrep.Database.Tables(i).SessionUserID & "<BR>"
      Response.Write ".... Connection: " & CRrep.Database.Tables(i).TestConnectivity() & "<BR>"
      Response.Write "-------------------------------------------------------------------------<BR>"
NEXT
Response.Write "Parameters: " & CRrep.ParameterFields.Count & "<BR>"
FOR i = 1 TO CRrep.ParameterFields.Count
      Response.Write ".... Name: " & CRrep.ParameterFields(i).Name & "<BR>"
      Response.Write ".... Kind: " & CRrep.ParameterFields(i).Kind & "<BR>"
      Response.Write ".... Min: " & CRrep.ParameterFields(i).MinimumValue & "<BR>"
      Response.Write ".... Max: " & CRrep.ParameterFields(i).MaximumValue & "<BR>"
      Response.Write ".... ParameterType: " & CRrep.ParameterFields(i).ParameterType & "<BR>"
      Response.Write ".... DiscreteOrRange: " & CRrep.ParameterFields(i).DiscreteOrRangeKind & "<BR>"
      Response.Write ".... ValueType: " & CRrep.ParameterFields(i).ValueType & "<BR>"
      Response.Write ".... Prompt: " & CRrep.ParameterFields(i).Prompt & "<BR>"
      Response.Write "-------------------------------------------------------------------------<BR>"
NEXT
Response.Write "Connecting ... <BR>"

'May need to set up a DSN or an Oracle database alias on webintel2

FOR i = 1 TO CRrep.Database.Tables.Count
      '##############################################################################
      'CRrep.Database.Tables(i).SetLogonInfo "dsn"   'optional:  ,"DB Name", "User ID", "Password"
      CRrep.Database.Tables(i).SetLogonInfo "ottr_test","","xxx","yyy"
      'maybe setting CRrep.Database.Tables(i).Location ...
      '##############################################################################
NEXT

'##############################################################################
'This is another option, but apparently shouldn't need it
'CRapp.LogOnServer "pdsodbc.dll", "Server Name"     'optional:   ,"DB Name", "User ID", "Password"
'CRapp.LogOnServer "pdsora7.dll", "ottr_test"     'optional:   ,"DB Name", "User ID", "Password"
'##############################################################################

FOR i = 1 TO CRrep.Database.Tables.Count
      Response.Write i & ": " & CRrep.Database.Tables(i).TestConnectivity() & "<BR>"
NEXT

'Test Export
IF CRrep.ParameterFields.Count=0 THEN
      Response.Write "Testing Export ..."
      CRrep.DisplayProgressDialog = False
      CRrep.ExportOptions.DestinationType = 1
      CRrep.ExportOptions.DiskFileName = "F:\Program Files\transfer\storage\Test.rtf"
      CRrep.ExportOptions.FormatType = 35
      CRrep.Export False
      Response.Write "OK"
END IF

'##############################################################################
'CRapp.LogOffServer "pdsora7.dll", ""
'##############################################################################
%>
</BODY>
</HTML>
<%
CRrep.DiscardSavedData
Set CRrep = Nothing
Set CRapp = Nothing
%>

example output....

CR TEST
F:\Program Files\transfer\crystal\K88_bmi_test.rpt
CR Version 2128
Report Opened.
Tables: 2
.... Name: STH_BMI
.... DLL: pdsora7.dll
.... LOCATION: OTTR.STH_BMI
.... SERVER: ottr_test
.... DATABASE:
.... USER: ottr
.... SESSION USER:
.... Connection: False
-------------------------------------------------------------------------
.... Name: PATIENT_DEMO
.... DLL: pdsora7.dll
.... LOCATION: OTTR.PATIENT_DEMO
.... SERVER: ottr_test
.... DATABASE:
.... USER: ottr
.... SESSION USER:
.... Connection: False
-------------------------------------------------------------------------
Parameters: 1
.... Name: {?PAT_ID}
.... Kind: 6
.... Min: 0
.... Max: 0
.... ParameterType: 0
.... DiscreteOrRange: 0
.... ValueType: 7
.... Prompt: Enter the patient id
-------------------------------------------------------------------------
Connecting ...
1: False
2: False
0
 
pfluggAuthor Commented:
I have resolved this issue with crystal decisions. Was a permissions problem with oracle client.
0
 
Computer101Commented:
Points refunded and placed in PAQ

Computer101
E-E Admin
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now