Changing Database Server

I'm currently running on CE 10.
I've got a *.rpt file that I would like to publish using the wizard.
When i need to publish a file, I would be required to enter the database information.
The database server that is linked to the *.rpt file is "hobbit_UAT"(User acceptance test zone), but I would like to point the database server to "hobbit_PRD" (production) instead. Both servers have replicate data, but just on different stages. I've been looking for a way to do this but I can't.

If what I'm saying make sense, are there any solutions?

details given might be in-sufficient.
I'll try and provide watever information that i can, if u need it.

thanks in advance!

Who is Participating?
I don't use the publishing wizard myself so I'm not sure of the options but once the report is published you can log in through the Crystal Management Console and change the database easily.

Navigate to the folder and find the report object.  On the database tab deselect "use original database..." and instead select "use custom database..." and provide the authentication info.  

This is common practice for us.  We publish reports to a user testing folder pointing to a user testing database.  Once accepted we move the report to the appropriate folder and change the db to the production box as I described above.

This works with Oracle. If you are using some other database then you need to see whic connection properties to use.

Set oDatabase = Session("oRpt").Database

For Each oTable In oDatabase.Tables
    oTable.ConnectionProperties("Server") = session.Contents("Database")
    oTable.ConnectionProperties("User ID") = session.Contents("Username")
    oTable.ConnectionProperties("Password") = session.Contents("Password")

For Each oSection In Session("oRpt").Sections
    For Each oObject In sectVar.ReportObjects
        If oObject.Kind=5 Then
            Set oSubReport = Session("oRpt").OpenSubreport(oObject.Name)
            Set oDatabase = subRep.Database
            For Each oTable In oDatabase.Tables
                oTable.ConnectionProperties("Server") = session.Contents("Database")
                oTable.ConnectionProperties("User ID") = session.Contents("Username")
                oTable.ConnectionProperties("Password") = session.Contents("Password")
        End If

If you need more help please ask.

kebeenAuthor Commented:
That makes sense.

But if I have 30 reports, do i have to change all of it manually?

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

As far as I know you do have to change each one manually.  You can change repository and processing settings for a group of reports at once (from the Manage Objects panel) but to the best of my knowledge the database can only be changed individually.
kebeenAuthor Commented:
I was told by the CE consultants that there might be a way to perhaps write a script to change the database altogether.

Thanks a lot! I'll try it out at once...
kebeenAuthor Commented:

I tried, but the options are greyed out...
any idea wat's the course of it?

I can only enter the username and password of the database server.
but I cannot change the database to custom. (radio button greyed out as well....)

Here is a simple VB example I think it solves your proplem.
You create a new  VB project, then reference to Crystal Reports ActiveX 9 Designer Runtime Library ( file named is craxdrt9.dll ) and Microsoft Script Runtime ( file named scrrun.dll )

'recursive sub: scan main report and SubReports to change db information. CR 9 now does not surport subreport in subreport, but i write here as a reserve, you should modify as you like.

Private Sub ChangeReportDB(pReport As CRAXDRT.Report, pServerName As String, _
    pDBName As String, pUser As String, pPassword As String)
    Dim aTable As CRAXDRT.DatabaseTable
    For Each aTable In pReport.Database.Tables
        aTable.ConnectionProperties("Data Source").Value = pServerName
        aTable.ConnectionProperties("Initial Catalog").Value = pDBName
        aTable.ConnectionProperties("User ID").Value = pUser
        aTable.ConnectionProperties("Password").Value = pPassword
        ' commit changed
        if aTable.Location <> aTable.Name then
             aTable.Name = aTable.Location
             aTable.Location = aTable.Name
        end if
    ' Here is recursive, you can move this block to Command2_Click()
    Dim aSec As CRAXDRT.Section
    Dim subRptObj As CRAXDRT.SubreportObject
    Dim subRpt As CRAXDRT.Report ' convert SubreportObject object to Report object
    Dim rptObj As Object
    For Each aSec In pReport.Sections
        For Each rptObj In aSec.ReportObjects
            If rptObj.Kind = crSubreportObject Then
                Set subRptObj = rptObj
                Set subRpt = subRptObj.OpenSubreport
                ChangeReportDB subRpt, pServerName, pDBName, pUser, pPassword
            End If
End Sub

Private Sub Command2_Click()
    Dim app As New CRAXDRT.Application
    Dim rpt As CRAXDRT.Report
    Dim sec As CRAXDRT.Section
    Dim subrpt As CRAXDRT.SubreportObject
    Dim item As Object
    Dim fso As New FileSystemObject
    Dim fle As File
    Dim fld As Folder
    Set fld = fso.GetFolder("C:\source folder")  
    'scan on source folder to change db information and save to destination folder
    For Each fle In fld.Files
        Set rpt = app.OpenReport(fle.Path)
        ChangeReportDB rpt, "myServer", "myDatabase", "myUser", "myPassword"
        rpt.SaveAs "C:\Dest Folder\" + fle.Name, crDefaultFileFormat ' Save changed report to another Folder
        End If
End Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.