Solved

Using SetDatabaseLogon

Posted on 2006-06-09
10
7,259 Views
Last Modified: 2007-11-27
ok,

I have a report I developed with Crystal. It is using a stored procedure from our development server and test db.
The production server is physically a different machine, geographically not at our office, and the db name is slightly different as well.

My question is: Is there a reliable way to change the server, and db from code (during runtime) so once we go live with some reports, they will still work on the other server machine with the other db?

I  Crystal article I came accross, says: "...When using the SetDatabaseLogon() method, the report must connect to the same server and database with which it was designed...."  <-- IS THIS CORRECT???

It can't be that I have to develop the report against the final server and db right from the beginning!

Am I missing something here?  Is there another approach?
Also, What do I do about the stored procedure(s) that the report is using? do they need to get "re-pointed" as well?

Our delivery is 6/14/2006 - and I only found this out now!

Thanks, Sharone
0
Comment
Question by:imkiosks
  • 3
  • 2
  • 2
  • +1
10 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 16875226
>>What do I do about the stored procedure(s) that the report is using? do they need to get "re-pointed" as well?
So long as they are identical on both machines there is no need to repoint them.  That the names are the same and the parameters and select list is the same.

I have not used the SetDatabaseLogon method.  So I can't accurately answer your question.
We were fortunate enough to think of the problem early in development and our development network was fully under our control that we built the database server to be "identical" to the prod environment.  Named the same and mapped the same.

The method of changing the database is determined by the version of Crystal and the method of calling from the application.  Which version of Crystal?  What method and language for the application?

mlmcc

0
 

Author Comment

by:imkiosks
ID: 16877478
Crystal 9. The lanuage for the ASP .NET application is a mix of C# and VB.NET: The web app itself is written in C#, and it uses a VB.net written dll to query the report for parameter fields (i.e. input for the stored proc), and supply the values to it.  The reason for the mix - 2 developers, one is comfortable in c#, the other in vb.net. The delivery date outweighed the C# syntax learning curve.

We have only designed 1 (out of about 8) report so far. So, changing the existing report to use the same name database has already been done. The stored procs WILL be identical. However, the machine name WILL be different.

We can't really change our development machine's name to match the production machine, because the hardware has not been purchased yet. Currently, our client is working with a test machine (we provided) in their lab. But, when they fully roll out with our solution, they will purchase another machine to be their production. They will still keep the lab machine for testing future updates.

So, you see the dilemma. We have to have these stored procs and reports working on 2 different machines. We were looking to have the connection information (i.e. machine name, user name, pwd, etc.) all configurable in the web.config file.

What do you think?
Thanks, Sharone.
0
 

Author Comment

by:imkiosks
ID: 16878229
I think I realized what I need to do. Using a .UDL which is defined to connect to the db via a system-level DSN I created, I can assign the .udl file to the report for the datasource.

Is that the right direction?
0
 
LVL 7

Assisted Solution

by:janmarini
janmarini earned 250 total points
ID: 16881411


This is some sample code I use in Visual Studio 2002 (ASP.NET) to change connection information for report created using CR 9.0.  I have the connection settings set up in the Web.Config so I can change them as needed without having to recompile the application.  I'm using the Report Document object.  OLEDB(ADO) SQL Server connection.

       
In your .aspx page codebehind (sample in vb)

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Web


--Page_Load Event--

Dim crReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument()
crReportDocument.Load(Server.MapPath("") & ReportName)
Call LogonReport(crReportDocument)  <------ supply logon info
...
...

In YourModule.vb - or you could have it in the same form loading the report.  I do it this way so I can
call it from any form

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Web

Public Sub LogonReport(ByVal rptReport As CrystalDecisions.CrystalReports.Engine.ReportDocument)
        Dim crReportDocument As New ReportDocument()
        Dim crTableLogonInfos As New TableLogOnInfos()
        Dim crTableLogonInfo As New TableLogOnInfo()
        Dim crTables As Tables
        Dim crTable As Table
        Dim crSections As Sections
        Dim crSection As Section
        Dim crReportObjects As ReportObjects
        Dim crReportObject As ReportObject
        Dim crSubreportObject As SubreportObject
        Dim crDataBase As Database
        Dim crConnInfo As New ConnectionInfo()
        Dim subRepDoc As New ReportDocument()

        'Logs into the tables in the report
        crReportDocument = rptReport
        crDataBase = crReportDocument.Database
        crTables = crDataBase.Tables
        For Each crTable In crTables
            With crConnInfo
                .ServerName = ConfigurationSettings.AppSettings("strServer")   <----------
                .DatabaseName = ConfigurationSettings.AppSettings("strDB")
                .UserID = ConfigurationSettings.AppSettings("strUID")
                .Password = ConfigurationSettings.AppSettings("strPWD")
            End With

            crTableLogonInfo = crTable.LogOnInfo
            crTableLogonInfo.ConnectionInfo = crConnInfo
            crTable.ApplyLogOnInfo(crTableLogonInfo)
            crTable.Location = strDBase & ".dbo." & crTable.Name
            crTable.ApplyLogOnInfo(crTableLogonInfo)
        Next

        'Logs into the tables in the Sub-reports
        crSections = crReportDocument.ReportDefinition.Sections
        For Each crSection In crSections
            crReportObjects = crSection.ReportObjects
            For Each crReportObject In crReportObjects
                If crReportObject.Kind = ReportObjectKind.SubreportObject Then
                    crSubreportObject = CType(crReportObject, SubreportObject)
                    subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
                    crDataBase = subRepDoc.Database
                    crTables = crDataBase.Tables
                    For Each crTable In crTables
                        With crConnInfo
                            .ServerName = ConfigurationSettings.AppSettings("strServer")
                            .DatabaseName = ConfigurationSettings.AppSettings("strDB")
                            .UserID = ConfigurationSettings.AppSettings("strUID")
                            .Password = ConfigurationSettings.AppSettings("strPWD")
                        End With
                        crTableLogonInfo = crTable.LogOnInfo
                        crTableLogonInfo.ConnectionInfo = crConnInfo
                        crTable.ApplyLogOnInfo(crTableLogonInfo)
                        crTable.Location = strDBase & ".dbo." & crTable.Name
                    Next
                End If
            Next
        Next
End Sub
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:imkiosks
ID: 16885699
janmarini,

Thanks for that!  I will try it - should it work for Stored Procs too?

I currently have a system DSN set up. The report is referencing a .udl file which uses the DSN. My code is similar to yours, and did work for a while. But then stopped working - with a Logon Failure error. I retraced my steps several times, but don't really see anything that changed. I even created a brand new project, report, and .udl file - but still the same error.

Both the DSN, and .udl file test successfully against the DB.

Any ideas?  I'm thinking of calling Crystal for support........
0
 

Expert Comment

by:asifsomi
ID: 16941285
sir i am using oracle with same problem,
what i pass in server and database in login info portion
0
 
LVL 7

Expert Comment

by:janmarini
ID: 16941600

Sorry, I don't use udl or Oracle, but here's some info on various connection strings:  http://www.connectionstrings.com/
0
 

Expert Comment

by:asifsomi
ID: 16941772
connectionstring dot com contains the connection strings for connecting with db not with crystal reports

any one help me
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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. …
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now