Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ASP problem connecting with oracle database, even though designer works

Posted on 2003-03-16
3
Medium Priority
?
829 Views
Last Modified: 2007-12-19
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
Comment
Question by:pflugg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:pflugg
ID: 8147112
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
 

Accepted Solution

by:
pflugg earned 0 total points
ID: 8163098
I have resolved this issue with crystal decisions. Was a permissions problem with oracle client.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 8601678
Points refunded and placed in PAQ

Computer101
E-E Admin
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question