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

Changing Database Server

Posted on 2004-04-22
Last Modified: 2007-12-19
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!

Question by:kebeen

Expert Comment

ID: 10887544
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.

LVL 42

Accepted Solution

frodoman earned 100 total points
ID: 10887858
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.


Author Comment

ID: 10888901
That makes sense.

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 42

Expert Comment

ID: 10889456
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.

Author Comment

ID: 10895213
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...

Author Comment

ID: 10895511

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....)


Expert Comment

ID: 10905668
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

840 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