Crystal 9, Oracle stored procedure, and ASP.NET 2.0

[Former Member]

Hello All,

I'm having trouble getting a Crystal 9 report that pulls data from Oracle native connection to run in the CrystalReportViewer control on my ASP.NET 2.0 web page. The report does have 2 subreports, and one of them uses a stored procedure returning a ref cursor. The rest of the report just pulls data from tables directly. I have Crystal designer 9 installed on my desktop and the report runs fine there. It is working for me when I run it on my local ASP.NET file system web server, but it is not working when I copy the web pages and report file to the development web server (W2K3 web edition). Here's what we've done to the web server so far:

- I have run CRRedist2005_x86.msi on the web server to install crystal.Net classes

- The web files reside in the 😧 drive, so I copied the CrystalReportWebFormViewer3 folder to the appropriate location on the D drive

- I copied the crdb_oracle.dll in the C:\Program Files\Common Files\Business Objects\2.7\Bin

- Our web user account has Read & Execute, List Contents, and Read permissions to the C:\Windows\Temp folder

The error that is returned is the u201CError in File C:\Windows\Temp\...: The request could not be submitted for background processingu201D. After some trial and error, I discovered that it was the stored procedure that is causing the error. Removing the stored procedure call is not an option as performance in this report is already an issue, and of course removing the ref cursor is not an option because that seems to be the only way Crystal can see data returned from a stored procedure.

This is the first time the company has ever done this as we currently use Crystal Enterprise for our web reporting, but we have a client that wants to see some reports from our web screens and time is running short on our deadline. Iu2019ve looked at the possibility of pulling the report from the web service that comes with Crystal Enterprise, but that idea was squashed by my IT manager (donu2019t get me started on that rant!). So, I guess my question is what do we need to install and/or configure on our web server to get this report to run?

Can someone please help me with this?

Thanks in advance,

Mike

Thanks in advance,

Mike

[former_member183750 · Accepted solution]

Hello Mike:

There are a few inconsistencies in your post that we need to resolve before proceeding. You say that you are using Crystal Reports 9 in ASP .NET 2.0 and that you copied the crdb_oracle.dll in the C:\Program Files\Common Files\Business Objects\2.7\Bin

Now, where to start...

CR 9, will not work on framework 2.0

C:\Program Files\Common Files\Business Objects\2.7\Bin directory is from CR 10.2 that ships with .NET 2005

Not sure where the crdb_oracle.dll came from, but I do not believe CR 10.2 installs it - it did not install it on my computer and I am pretty sure I did a full install...

So this leads to a number of questions:

1) What version of CR assemblies are you referencing in your project?

2) Where did the crdb_oracle.dll come from? Do you have a stand-alone version of CR and copied the file from there?

3) What is the database logon code you are using?

Ludek

[Former Member]

Hello All,

This issue is now resolved. The report designer had to do a Set Location in the report to the appropriate server name (I don't know the full details, I'm just going on what she told me) and there were some permission issues with the web user in Oracle (again, going on someone else's word on this). This is now working flawlessly in all 3 web environments!

Thank you all for your help,

Mike

[Former Member]

Hello, Mike;

If you are passing an empty string yourself your Stored Procedure is set up to handle it and it should not be a problem. Where stored procedures are set to expect a value and we verify the connection with a NULL that can cause a problem the workaround resolves.

The runtimes can be found at:

http://resources.businessobjects.com/support/additional_downloads/runtime.asp

That should give you the files you are familiar with.

Elaine

[Former Member]

Hi Elaine,

I got the runtime installed on the server and copied the crystalreportviewers12 folder the correct location (our web pages are stored on the D drive). The first time I tested the report, it worked perfectly! The parameters I was using to test were hard-coded into the web page. I configured the web page to use "live" parameters today and tested again. It didn't work. Here's the error I get now:

Failed to retrieve data from the database. Details: [Database Vendor Code: 6550 ] Failed to retrieve data from the database. Details: [Database Vendor Code: 6550 ] Failed to retrieve data from the database. Error in File Benefit_Statement {557BB95D-816F-4D77-8F57-9E8CB4F6162B}.rpt: Failed to retrieve data from the database. Details: [Database Vendor Code: 6550 ]

The report runs fine in CR2008 with other parameters. When I opened the report in CR2008, I noticed that the parameters that were successful when hard-coded in the web page are also saved in the report, which would explain why the data will only come up for the one set of parameters. I had the report designer save the report without the data and tested again. Same result. I tried hard-coding other parameters into the web page. No luck. When we remove the sub-report that contains the stored procedure, the report runs fine with "live" parameters, proving that the parameters are being passed in the report ok but the stored procedure is still an issue. Any suggestions for what to look for next?

Thanks,

Mike

[former_member11696]

Hi Mike,

In CR Designer you have the ability to save the subreport. Do so and then try running it in your app as the main report it verify it does work. I don't see any info on if the SP used in the subreport has parameters and if so that you are setting the parameters in code also? I see you are getting them but not sure if you are setting them all or just the main reports parameters.

Thank you

Don

[Former Member]

Hi Don,

Thanks for the reply. I saved the sub-report and tried running it by itself in my app with hard-coded parameters and got the same error. It ran fine in CR2008 with the same parameter values. I don't pass any parameters to the sub-reports in my code in the full version of the report. The only information I pass to the sub-report is the connection info.

Thanks,

Mike

[Former Member]

Hello, Mike;

Full version 2008 does have an Oracle native driver but you need to install Crystal Reports 2008 Developer on your Visual Studio .NET system and test, then compile the application with the references to that version. Once that works, deploy with the correct runtimes to the server.

You can pass ODBC connections in code but it will not work if the report is using a native driver. If the application can find the values saved in the report, it will ignore the incorrect code and use the values saved in the report. Since it is a trusted connection and does not need permissions, that may be why it succeeds on your system and why it fails on the server.

ConnectionInfo for the Oracle Native driver is:

ConnectionInfo.DatabaseName = "" 'empty string for Oracle

ConnectionInfo.UserID = "uid"

ConnectionInfo.Password = "pwd"

'Pass the Server name for Native and OLEDB. DSN for ODBC.

ConnectionInfo.ServerName = "serverName"

There is a known issue with parameter fields from Stored procedures being passed as NULL when changing the database at runtime. Can the Stored procedure handle NULL values? You can set a Registry key for Crystal Reports that will resolve the problem. For Crystal Reports 2008 it would be:

\HKEY_CURRENT_USER\Software\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\DontVerifyAttachedRecordset

By default this is set to 'No'. In this case you want it set to 'Yes'.

Or you may find the key under HKEY_LOCAL_MACHINE.

There is a way to see what is loading on the web server that is different from your application system. Have your administrators run our Modules utility when the error is in the viewer on the server and you can do the same on your Development system when viewing a successful report. Save each file with a recognizable name e.g. webServer.mdl and devSystem.mdl. Then compare the two to see what is different in the files loading.

You can find the utility at:

[Click|https://smpdl.sap-ag.de/~sapidp/012002523100006252802008E/modules.zip]

IIS_WPG on a Windows 2003 Server is the equivalent of ASP_NET on XP so that is fine.

Elaine

[Former Member]

Hi Elaine,

Thanks very much for your help. I got Crystal Reports 2008 installed on my system. When I open the project to test on my system, VS2005 sensed the 2008 version of Crystal and upgraded my CR.NET DLL references to version 12. The report ran successfully from my system.

Now my question is rather than just manually coping the version 12 DLLs to the web server, is there not an install package already existing that does the upgrade to 12? I previously installed CRRedist2005_x86.msi to get the CR.NET 2005 DLLs on the web server, is there not something similar?

Regarding your comment about passing NULL paramters, I have a parameter that I'm currently setting the value to a blank string (""). Is that the same thing? I haven't changed the registry key to see what that does.

Thanks again,

Mike

[Former Member]

Hello, Mike;

Crystal Reports 9.0 is not supported in Visual Studio .NET 2005 and does not run on the 2.0 framework as you know.

The version shipped with Visual Studio .NET 2005 is 10.2. Because it is a bundled version for Microsoft and not a full version, there is no Oracle native database driver. Using a version 9.0 driver would not be supported.

Have you tested using ODBC, OLEDB or ADO.NET just to have a report to display for your proof of concept?

Make sure the application runs on your development system with full .NET installed first.

Then test on your Web server and let me know what you find. When you say "web user account" do you mean the IIS_WPG or ASP_NET Worker process has permissions to C:\Windows\Temp?

Elaine

[Former Member]

Hello Elaine and Ludek,

Thank you very much for your quick replies.

When I say that I'm using Crystal 9, I mean that the report was developed in the Crystal Reports 9 designer software. Something was installed on my system that gave me the crdb_oracle.dll. I don't have admin access to my computer, so it was a systems administrator that did the install so I don't know what it was that was installed to get Crystal reports that pull from Oracle to run in Crystal designer 9. The report runs fine from Crystal designer 9 on my desktop. We do have licenses for Crystal Reports 2008 as we are in the process of migrating to Business Objects.

The report would not run from the ASP.NET web server on my desktop until I copied crdb_oracle.dll to the C:\Program Files\Common Files\Business Objects\2.7\Bin and restarted the web server, hence the reason I tried copying this DLL from my desktop to the same folder on the web server. I have full Visual Studio 2005 installed on my desktop. I had another developer try to run the web page that launches the report from his desktop (he also has full VS2005, but no Crystal designer) and he got the same "background processing" error. The Crystal.NET DLLs are version 10.2.3600.0.

I don't have authority to install service packs etc. on the server, so I have to run it by my systems administrators before anything can be installed (again, don't get me started!). I know that we are not up to date on service packs as our administrators test them first before pushing them out to desktops and servers.

We have OraHome92 installed on our desktops and on the web server and the report runs fine on the web server if I remove the stored procedure call. The connection info I'm passing corresponds to what is set up in ODBC on the server, even though the report was developed using a native connection.

The "web user account" is IIS_WPG as we "apparently" don't use the ASPNET user account.

Here is the code I'm using to open the report in the CrystalReportViewer control (with confidential info excluded, of course):

Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine

Partial Class member_benefit_stmt
    Inherits System.Web.UI.Page

    Dim rptDoc As New ReportDocument

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
        CrystalReportViewer1.ParameterFieldInfo.Clear()

        Dim params As ParameterFields = CrystalReportViewer1.ParameterFieldInfo

        'Create parameters
        Dim paramCert As New ParameterField
        Dim paramGroup As New ParameterField
        paramCert.Name = "CertificateNo"
        paramGroup.Name = "Group"

        'Assign parameter values
        Dim paramCertValue As New ParameterDiscreteValue
        Dim paramGroupValue As New ParameterDiscreteValue
        paramCertValue.Value = "[cert value here]""
        paramCert.CurrentValues.Add(paramCertValue)
        paramGroupValue.Value = "[group value here]"
        paramGroup.CurrentValues.Add(paramGroupValue)

        'Add parameters to collection
        params.Add(paramCert)
        params.Add(paramGroup)

        'Run report
        rptDoc = SetupReportInfo(Server.MapPath("[rpt file name here]"))
        CrystalReportViewer1.ReportSource = rptDoc
    End Sub

    'Gets the Oracle connection string associated with the specified name from web.config and parses it into a ConnectionInfo object 
    Private Function GetOracleConnectionInfo(ByVal connString As String) As ConnectionInfo
        Dim connInfo As New ConnectionInfo()
        Dim connectionString As String = ConfigurationManager.ConnectionStrings(connString).ConnectionString
        Dim arrConnectionElements() As String
        Dim i As Integer
        Dim prop As String
        Dim val As String

        'Each element of the connection string ends in a semi-colon
        arrConnectionElements = connectionString.Split(";")
        For i = 0 To arrConnectionElements.Length - 1
            If Trim(arrConnectionElements(i)) <> "" Then
                'Split each part of the connection string into a key-value pair
                prop = Left(arrConnectionElements(i), InStr(1, arrConnectionElements(i), "=") - 1)
                val = Right(arrConnectionElements(i), (Len(arrConnectionElements(i)) - InStr(1, arrConnectionElements(i), "=")))
                'Determine which part of the connection string it is and assign the value to the appropriate ConnectionInfo property
                Select Case LCase(prop)
                    Case "user id"
                        connInfo.UserID = val
                    Case "password"
                        connInfo.Password = val
                    Case "data source"
                        connInfo.ServerName = val
                End Select
            End If
        Next
        connInfo.DatabaseName = ""
        Return connInfo
    End Function

    'Setup database connection information for the specified report
    Function SetupReportInfo(ByVal rptName As String) As ReportDocument
        Dim rpt As New ReportDocument()
        Dim subRpt As SubreportObject
        
        rpt.Load(rptName)
        'Setup login information for each table in the main report
        SetupReportTableLogonInfo(rpt)
        'Loop through each section looking for subreports
        For Each section As Section In rpt.ReportDefinition.Sections
            For Each rptObject As ReportObject In section.ReportObjects
                If rptObject.Kind = ReportObjectKind.SubreportObject Then
                    subRpt = CType(rptObject, SubreportObject)
                    Using subRptDoc As ReportDocument = subRpt.OpenSubreport(subRpt.SubreportName)
                        'Setup login information for each table in the subreport
                        SetupReportTableLogonInfo(subRptDoc)
                    End Using
                End If
            Next
        Next
        Return rpt
    End Function

    'Sets up database connection information for each table in the report
    Sub SetupReportTableLogonInfo(ByVal rpt As ReportDocument)
        Dim tblLogon As TableLogOnInfo
        Dim connInfo As ConnectionInfo = GetOracleConnectionInfo("[web.config connection string key here]")

        For Each tbl As Table In rpt.Database.Tables
            tblLogon = tbl.LogOnInfo
            tblLogon.ConnectionInfo = connInfo
            tbl.ApplyLogOnInfo(tblLogon)
        Next
    End Sub

    Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload
        rptDoc.Close()
        rptDoc.Dispose()
        GC.Collect()
    End Sub
End Class

I hope I've included enough information to move ahead on this issue. Let me know if you need any more info from me.

Thanks,

Mike