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

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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