Crystal export to pdf and excel

[prem73]

Hi,

I have an ASP page that exports crystal to pdf and excel and write the files to a specified location. This works fine in the QA server, but it hangs at the point where it exports in the production, can someone please help.

following is the code used.
<%@ Language=VBScript %>

<%


<%
'============================================================================
' WORKING WITH THE REPORT DESIGNER COMPONENT AND ASP TO SET REPORT PARAMETERS
'============================================================================
'
' CONCEPT
'
' ALWAYS REQUIRED STEPS (contained in AlwaysRequiredSteps.asp)
' - create the application object
' - create the report object
' - open the report
'
' PROVIDE THE AUTHENTICATION INFORMATION FOR THE DATABASE
' - Use the LogonServer Method for Login and
' - LogoffServer method to LogOff Method
'
' WORKING WITH REPORT PARAMETERS
' - Create a reference to the ParameterField Object
' - Manipulate the parameter fields properties and methods.
'
' MORE ALWAYS REQUIRED STEPS (contained in MoreRequiredSteps.asp)
' - retreive the records
' - create the page engine
'
' DISPLAY THE REPORT
' - display the report using a smart viewer
'==================================================================
%>


<%
' This is to call the PL/SQL that loads the WP_TURNAROUND_TEMP table

Dim conDBConnection
Dim rsDBRecordset
Dim strDBServer
Dim strDBUserID
Dim strDBPassword
Dim strODBC
Dim strDBConnectionString

strDBUserID = "userid"
strDBPassword = "pwd"
strODBC = "DSN"

strDBConnectionString = "Provider=MSDASQL.1;Password=" & strDBPassword & ";Persist Security Info=True;User ID=" & strDBUserID & ";Data Source=" & strODBC
Set conDBConnection = Server.CreateObject("ADODB.Connection")
Set rsDBRecordset = Server.CreateObject("ADODB.Recordset")
conDBConnection.open(strDBConnectionString)
rsDBRecordset.Open "sp_getdata",conDBConnection

Set rsDBRecordset = Nothing
conDBConnection.Close
Set conDBConnection = Nothing

%>
<%
' This line creates a string variable called reportname that we will use to pass
' the Crystal Report filename (.rpt file) to the OpenReport method contained in
' the AlwaysRequiredSteps.asp.

reportname = "rpt1.rpt"
%>

<%
'==================================================================
' ALWAYS REQUIRED STEPS
'
' Include the file AlwaysRequiredSteps.asp which contains the code
' for steps:
' - create the application object
' - create the report object
' - open the report
'==================================================================

%>

<!-- #include file="AlwaysRequiredSteps.asp" -->

<%
'==================================================================
' LOGGING INTO THE Oracle 9i Server with the LogonServer Method
' ****Remember to LogOff the Server when done quering the database
'==================================================================

Session("oApp").LogonServer "PDSODBC.DLL",CStr("DSN"),CStr(""),CStr("uid") , CStr("pwd")
' P2SORA7.DLL & PDSODBC.DLL & crdb_p2s.dll & crdb_odbc.dll


%>

<%
'==================================================================
' SETTING LOGON INFO FOR THE REPORT **** NOT USED FOR BATCH REPORTS
'==================================================================
'
' The datasource here is called "dsn". It points to the Prod Server. It is a System
' Datasource (DSN), and points to the "prod" database in Oracle 9i,
' The user id = cryRepUser and
' password = reporting.

'Create a reference to the tables collection of the main report rpt1.rpt
Set mainReportTableCollection = Session("oRpt").Database.Tables

For Each mnTable in mainReportTableCollection
With mnTable.ConnectionProperties
.Item("DSN") = "DSN"
.Item("user ID") = "uid"
.Item("Password") = "pwd"
End With
Next
%>

<%
'==================================================================
' EXPORTING A REPORT
'==================================================================
Set CrystalExportOptions = Session("oRpt").ExportOptions

'This line of code set a variable to reference the ExportOptions Object.

'The variables below (ExportFileName, ExportDirectory, ReportCacheVirtualDirectory,
'and ExportType) are used as follows:
' - ExportFileName is the actual file name that should be created by the export process
' - ExportDirectory is the physical directory where the ExportFileName will be placed
' - ExportType is a number that specifies the type of file that the export process should
' create. For a list of these numbers, please refer to the Crystal Reports Report Design Component
' help file (crrdc.hlp) in the "ExportOptions Object for the Object Model" section. Scroll down to
' the ExportType property. To change the type of file that the export process should be creating, change
' the value in the ExportType variable, and change the last portion of the ExportFileName
' variable.
' Some export types have other properties in the ExportOptions object that require values
' to be set. For example exporting to HTML. This type will require that you will also
' set the HTMLFileName property. For detailed information on which properties to set,
' refer to the "ExportOptions Object" topic in the help file (crrdc.hlp)

'=====================================================================
' Date stamp used for providing unique filename every day the report is saved.
' Date stamp also helps in retrieving the file for viewing/downloading
'=====================================================================

vMonth = Month(Now)
sMonth = CStr(vMonth)
If len(sMonth) = 1 then
sMonth = "0" & sMonth
End If

vDay = Day(Now)
sDay = CStr(vDay)
If len(sDay) = 1 then
sDay = "0" & sDay
End If

vYear = Year(Now)
sYear = CStr(vYear)

'=====================================================================
' Time stamp and SessionID used for Testing Purpose only
'=====================================================================

'vHour = Hour(Now)
'sHour = CStr(vHour)
'If len(sHour) = 1 then
' sHour = "0" & sHour
'End If

'vMinute = Minute(Now)
'sMinute = CStr(vMinute)
'If len(sMinute) = 1 then
' sMinute = "0" & sMinute
'End If

'vSecond = Second(Now)
'sSecond = CStr(vSecond)
'If len(sSecond) = 1 then
' sSecond = "0" & sSecond
'End If

'idSession = Session.SessionID
'SessionID check

'==================================================================
' Getting the physical path of ASP page
'==================================================================

Path = Request.ServerVariables("PATH_TRANSLATED")
While (Right(Path, 1) <> "\" And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
Wend

'The While/Wend loop is used to determine the physical location
'of the ReportExportExcel.asp so that we can save the
'ExcelReportMMDDYYYY.doc in the same location.

ExportDirectory = Path
'This line of code specifies the location on the disk where the exported file
'is to be placed.


'==============================================
' PART 1 Exporting the file in PDF format
'==============================================

'ExportFileName1 = idSession & "rpt1" & sYear & sMonth & sDay & sHour & sMinute & sSecond & ".pdf"

ExportFileName1 = "rpt1" & sYear & sMonth & sDay & ".pdf"
'This line of code specifies the name and format that is to be used to save the
'Exported File.

ExportFolder = "PDF\"
'Sub Folder for saving the exported file

ExportType = "31"
'31 determines that the format to which the report is exported is PDF

CrystalExportOptions.DiskFileName = ExportDirectory & ExportFolder & ExportFileName1
'This line of code specifies the physical location and file name to give
'the export result.

CrystalExportOptions.FormatType = CInt(ExportType)
'This line of code specifies the export format.

CrystalExportOptions.DestinationType = CInt(1)
'This line of code specifies that the export destinatin is to be disk.

Session("oRpt").Export False
'This line of code turns of any prompting when exporting.

'Response.Redirect ("rpt1" & sYear & sMonth & sDay & ".xls")

'This line of code redirects the URL to the newly exported file.
'It is important to remember that the exporting is taking place on the web server
'so we need to redirect the user to the newly exported file or they will never
'have access to that file.

'==============================================
' PART 2 Exporting the file in Excel format
'==============================================

'ExportFileName2 = idSession & "rpt1" & sYear & sMonth & sDay & sHour & sMinute & sSecond & ".xls"

ExportFileName2 = "rpt1" & sYear & sMonth & sDay & ".xls"
'This line of code specifies the name and format that is to be used to save the
'Exported File.

ExportFolder = "Excel\"
'Sub Folder for saving the exported file

ExportType = "21"
'21 determines that the format to which the report is exported is Excel

CrystalExportOptions.DiskFileName = ExportDirectory & ExportFolder & ExportFileName2
'This line of code specifies the physical location and file name to give
'the export result.

CrystalExportOptions.FormatType = CInt(ExportType)
'This line of code specifies the export format.

CrystalExportOptions.DestinationType = CInt(1)
'This line of code specifies that the export destinatin is to be disk.

Session("oRpt").Export False
'This line of code turns of any prompting when exporting.

%>

<%
'==================================================================
' LOGGING OFF THE Oracle 9i Server with the LogoffServer Method
'==================================================================
session("oApp").LogOffServer "PDSODBC.DLL", Cstr("DSN"), Cstr(""), Cstr("uid"), Cstr("pwd")

'==================================================================
' Removing the Report, Application, ExportOptions Objects from the session
'==================================================================

Set oRpt = Nothing
Set oApp = Nothing

Set CrystalExportOptions = Nothing

Session.Abandon
Response.End
%>

 

[synapsevampire]

I'd 1st be suspicious of the export path, either not having permission to write or it not existing:

Path = Request.ServerVariables("PATH_TRANSLATED")
While (Right(Path, 1) <> "\" And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
Wend

-k

 

[prem73]

I have checked out this there is no issue with the path and permissions, I strongly believe that this has something to do with the crystal reports dll's that are being used.

 

[Turkbear]

Hi,
What differs between the QA and production sites?
By 'hangs', what exactly do you mean happens? Is there an error in the event or application log?





To Paraphrase:"The Help you get is proportional to the Help you give.."