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?

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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. …
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 …
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 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