Change Oracle SID at runtime with vb.net and cr XI

[Parkaboy]

Have been trying to find a working solution to the problem of changing SID at runtime. Like many users we have a TEST and PROD environment. Currently running separate test and prod version of reports in lieu of the ability to change SID at runtime. Have scoured the forum and web with no success finding a WORKING solution.

Environment:
VS 2005, CR XI with SP2, Oracle 10.2, using integrated rpts.

If anyone is successfully changing SID's at runtime, I would very much appreciate a response. maintaining two different versions of the same report irritating and causing more problems that it is worth. Code, link, 3rd party product, doesn't matter, anything is welcome!

Why doesn't BO make this easier?? common sense dictates this a basic need ???

 

[IdoMillet]

There is at least one 3rd-party report viewer (see list at

http://www.kenhamady.com/bookmarks.html)

that allows users (interactively or via a command line) to change ODBC DSN as well as Oracle server.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[hilfy]

If you're running from within your own application, you have to set the Connection String for each table in the report.

In C# with the ReportDocument object model, this looks like:

CrystalDecisions.Shared.ConnectionInfo connectionInfo = new CrystalDecisions.Shared.ConnectionInfo();

connectionInfo.ServerName = <SID>;
connectionInfo.UserID = <user ID>;
connectionInfo.Password = <pw>;

Tables tables = reportDocument.Database.Tables;
foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
{
  TableLogOnInfo tableLogonInfo = table.LogOnInfo;
  tableLogonInfo.ConnectionInfo = connectionInfo;
  table.ApplyLogOnInfo(tableLogonInfo);
}

If the report has subreports, you also have to walk down the report sections to find the subreports and then set the connection info for each of the tables in the subreport.

-Dell


A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Parkaboy]

Thx for the replies. Using very similar code to yours in vb.net

Syntax is correct, loops thru all tables, then fails to logon.
No problem until I try to send to printer, then Returns an unhandled exception:

"Unable to connect: incorrect log on parameters."

server, UID, and PWD are correct. checked more times than I care to think about. Read other posts specifying to leave out database name when using Oracle so it looks correct.

Here is the code I am using. Do you see any "glaring"
discrepancies ????

Thx

        Dim crtableLogoninfos As New TableLogOnInfos()
        Dim crtableLogoninfo As New TableLogOnInfo()
        Dim crConnectionInfo As New ConnectionInfo()
        Dim CrTables As Tables
        Dim CrTable As Table
        'Dim TableCounter

       Dim crReportDocument As New ReportBusinessLicenseLite

        With crConnectionInfo
            .ServerName = "prod"
            'If you are connecting to Oracle there is no
            'DatabaseName. Use an empty string.
            'For example, .DatabaseName = ""
            .DatabaseName = ""
            .UserID = "USER"
            .Password = "pass"
        End With

        CrTables = crReportDocument.Database.Tables

        'Loop through each table in the report and apply the
        'LogonInfo information

      For Each CrTable In CrTables
          crtableLogoninfo = CrTable.LogOnInfo
          crtableLogoninfo.ConnectionInfo = crConnectionInfo
           CrTable.ApplyLogOnInfo(crtableLogoninfo)

        Next

        Dim sSingleIDforOracleProcTst As String
        'set parameter value to pass into CR BL file
        sSingleIDforOracleProcTst = sSglPBatchID
        'insert to parameter for businesslicenseLite report 
crReportDocument.SetParameterValue(0,sSingleIDforOracleProcTst)


        'CrystalReportViewer1.ReportSource = crReportDocument


        Dim sprintmsg As String = "Error on print test"

        Try

            crReportDocument.PrintToPrinter(1, True, 0, 0)
            '
        Catch ex As CrystalDecisions.ReportSource.EnterpriseLogonException
            MessageBox.Show(sprintmsg & ex.ToString)
        Catch ex As CrystalDecisions.CrystalReports.Engine.LogOnException
            MessageBox.Show(sprintmsg & ex.ToString)
        Catch ex As CrystalDecisions.CrystalReports.Engine.PrintException
            MessageBox.Show(sprintmsg & ex.ToString)
        Catch ex As Exception
            EventLog.LogException(ex)
        End Try