Solved

Changing Database Server

Posted on 2004-04-22
7
1,125 Views
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!



0
Comment
Question by:kebeen
[X]
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
7 Comments
 
LVL 2

Expert Comment

by:Ivan_Skrinjaric
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")
Next

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")
            Next
        End If
     Next
Next

If you need more help please ask.

Ivan.
0
 
LVL 42

Accepted Solution

by:
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.

frodoman
0
 
LVL 1

Author Comment

by:kebeen
ID: 10888901
That makes sense.

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

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
LVL 42

Expert Comment

by:frodoman
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.
0
 
LVL 1

Author Comment

by:kebeen
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...
0
 
LVL 1

Author Comment

by:kebeen
ID: 10895511
argh!!!

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


suggestions?
0
 

Expert Comment

by:nguyenntvietnam
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
        else
             aTable.Location = aTable.Name
        end if
    Next
   
    ' 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
        Next
    Next
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
    Next
End Sub
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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. …
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 …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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