Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

How do I change report DSN at runtime?

Hello,

I have some reports (some 2011 but most 9) that were created with a DSN named Dev for example.  When the reports are deployed to production, they are viewed via an ASP.NET application written in VB.NET (VS2012).  The production server has a DSN named Prod for example. The code I'm using to change the connection information "seems" to be working because if I write out the connection info before the code, it shows the Dev DSN connection information and if I write out the connection inf after the code, it shows the Prod DSN connection information.

The problem is that even though the connection information appears to be changing at runtime, when the report renders it shows data from the Dev database instead of production.

Here is the code I'm using to change the main report connection and the subreports, which seems to be working:

        Dim strPassword As String = System.Configuration.ConfigurationManager.AppSettings("ReportPassword")

        Dim strUserID As String = System.Configuration.ConfigurationManager.AppSettings("ReportUser")

        Dim strServer As String = System.Configuration.ConfigurationManager.AppSettings("ReportServer")

        Dim strDBName As String = System.Configuration.ConfigurationManager.AppSettings("ReportDatabase")

        Dim strDSName As String = System.Configuration.ConfigurationManager.AppSettings("ReportDSN")

        Dim myTable As CrystalDecisions.CrystalReports.Engine.Table

        Dim myLogin As CrystalDecisions.Shared.TableLogOnInfo

        Dim blDebugFlag As Boolean = False

        'This test is needed because the flag may/should not exist in the production web.config

        If Not System.Configuration.ConfigurationManager.AppSettings("debugFlag") Is Nothing Then

            blDebugFlag = System.Configuration.ConfigurationManager.AppSettings("debugFlag")

        End If

        'Modify the data source location for each table in the report (does not include subreports)

        strReturnData += Now.ToLongTimeString & "<br>"

        For Each myTable In Session("crpt").Database.Tables

            myLogin = myTable.LogOnInfo

            strReturnData += "<br><br>Table = [<b>" & myTable.Name.ToString & "</b>] - (Main report)" & _

              "<blockquote>" & _

              "<br>Original ServerName = [" & myLogin.ConnectionInfo.ServerName.ToString & "]" & _

              "<br>Original DatabaseName = [" & myLogin.ConnectionInfo.DatabaseName.ToString & "]" & _

              "<br>Original UserId = [" & myLogin.ConnectionInfo.UserID.ToString & "]"

            'check if a DSN or non-DSN connection

            If myTable.LogOnInfo.ConnectionInfo.DatabaseName = "" Or myTable.LogOnInfo.ConnectionInfo.ServerName = "Dev" Then

                myLogin.ConnectionInfo.ServerName = strDSName

                myLogin.ConnectionInfo.DatabaseName = ""

                strReturnData += "<br><br> ### DSN Connection ###"

            Else

                myLogin.ConnectionInfo.ServerName = strServer

                myLogin.ConnectionInfo.DatabaseName = strDBName

                strReturnData += "<br><br> *** Non-DSN Connection ***"

            End If

            myLogin.ConnectionInfo.Password = strPassword

            myLogin.ConnectionInfo.UserID = strUserID

            myTable.ApplyLogOnInfo(myLogin)

            ' Collect the login information after-the-fact

            strReturnData += "<br><br>Final ServerName = [" & myLogin.ConnectionInfo.ServerName.ToString & "]" & _

            "<br>Final DatabaseName = [" & myLogin.ConnectionInfo.DatabaseName.ToString & "]" & _

            "<br>Final UserId = [" & myLogin.ConnectionInfo.UserID.ToString & "]" & _

            "</blockquote>"

        Next

        'Iterate through subreports and make sure ConnectionInfo matches the main report

        'set the crSections object to the current report's sections

        Dim crSections As CrystalDecisions.CrystalReports.Engine.Sections = Session("crpt").ReportDefinition.Sections 'rpt.ReportDefinition.Sections

        Dim crReportobjects As ReportObjects, crSubReportobject As SubreportObject, crSubReportDocument As ReportDocument

        Dim crDatabase As Database, crTables As Tables

        Dim crtableLogoninfo As New TableLogOnInfo

        Dim crConnectioninfo As New ConnectionInfo

        'loop through all the sections to find all the report objects

        For Each crSection As CrystalDecisions.CrystalReports.Engine.Section In crSections

            crReportObjects = crSection.ReportObjects

            'loop through all the report objects to find all the subreports

            For Each crReportObject As CrystalDecisions.CrystalReports.Engine.ReportObject In crReportObjects

                If crReportObject.Kind = ReportObjectKind.SubreportObject Then

                    'you will need to typecast the reportobject to a subreport object once you find it

                    crSubreportObject = DirectCast(crReportObject, CrystalDecisions.CrystalReports.Engine.SubreportObject)

                    Dim mysubname As String = crSubreportObject.SubreportName.ToString()

                    'open the subreport object

                    crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

                    'set the database and tables objects to work with the subreport

                    crDatabase = crSubreportDocument.Database

                    crTables = crDatabase.Tables

                   

                    'loop through all the tables in the subreport and set up the connection info and apply it to the tables

                    For Each crTable As CrystalDecisions.CrystalReports.Engine.Table In crTables

                        'With crConnectioninfo

                        '    .ServerName = strDSName

                        '    .DatabaseName = ""

                        '    .UserID = strUserID

                        '    .Password = strPassword

                        'End With

                        'crtableLogoninfo = crTable.LogOnInfo

                        'crtableLogoninfo.ConnectionInfo = crConnectioninfo

                        'crTable.ApplyLogOnInfo(crtableLogoninfo)

                        myLogin = crTable.LogOnInfo

                        strReturnData += "<br><br>Table = [<b>" & crTable.Name.ToString & "</b>] - (Subreport - [" & mysubname & "])" & _

                          "<blockquote>" & _

                          "<br>Original ServerName = [" & myLogin.ConnectionInfo.ServerName.ToString & "]" & _

                          "<br>Original DatabaseName = [" & myLogin.ConnectionInfo.DatabaseName.ToString & "]" & _

                          "<br>Original UserId = [" & myLogin.ConnectionInfo.UserID.ToString & "]"

                        'check if a DSN or non-DSN connection

                        If crTable.LogOnInfo.ConnectionInfo.DatabaseName = "" Or crTable.LogOnInfo.ConnectionInfo.ServerName = "Dev" Then

                            myLogin.ConnectionInfo.ServerName = strDSName

                            myLogin.ConnectionInfo.DatabaseName = ""

                            strReturnData += "<br><br> ### DSN Connection ###"

                        Else

                            myLogin.ConnectionInfo.ServerName = strServer

                            myLogin.ConnectionInfo.DatabaseName = strDBName

                            strReturnData += "<br><br> *** Non-DSN Connection ***"

                        End If

                        myLogin.ConnectionInfo.Password = strPassword

                        myLogin.ConnectionInfo.UserID = strUserID

                        crtableLogoninfo = crTable.LogOnInfo

                        crtableLogoninfo.ConnectionInfo = myLogin.ConnectionInfo

                        crTable.ApplyLogOnInfo(crtableLogoninfo)

                        ' Collect the login information after-the-fact

                        strReturnData += "<br><br>Final ServerName = [" & myLogin.ConnectionInfo.ServerName.ToString & "]" & _

                        "<br>Final DatabaseName = [" & myLogin.ConnectionInfo.DatabaseName.ToString & "]" & _

                        "<br>Final UserId = [" & myLogin.ConnectionInfo.UserID.ToString & "]" & _

                        "</blockquote>"

                    Next 'crTable

                End If

            Next 'crReportObject

        Next 'crSection

Here is what gets printed after this code when the debug flag is True:

   Table = [usp_MainReportProcedureName] - (Main report)

      Original ServerName = [Dev]

      Original DatabaseName = [Dev]

      Original UserId = [UserName]

      ### DSN Connection ###

      Final ServerName = [Prod]

      Final DatabaseName = []

      Final UserId = [UserName]

   Table = [usp_SubreportProcedureName] - (Subreport - [subrptName.rpt])

      Original ServerName = [Dev]

      Original DatabaseName = [Dev]

      Original UserId = [UserName]

      ### DSN Connection ###

      Final ServerName = [Prod]

      Final DatabaseName = []

      Final UserId = [UserName]

Here is the export code that renders the report:

        'Export to new format

        Dim crExportOptions As New ExportOptions

        Dim crDiskFileDestinationOptios As New DiskFileDestinationOptions

        Dim fname As String = ""

        Dim gFileId As New Guid

        Try

            'Filename for temporary report

            fname = System.Configuration.ConfigurationManager.AppSettings("DocumentsLocation") + gFileId.ToString + ".pdf"

            crDiskFileDestinationOptios.DiskFileName = fname

            crExportOptions = Session("crpt").ExportOptions

            Session("crpt").ExportOptions.DestinationOptions = crDiskFileDestinationOptios

            Session("crpt").ExportOptions.ExportDestinationType = ExportDestinationType.DiskFile

            Session("crpt").ExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat

            Session("crpt").Export()

            'Display and Cleanup

            Response.ClearContent()

            Response.ClearHeaders()

            If blDebugFlag = False Then

                'Display the report

                Response.ContentType = "application/pdf"

                Response.WriteFile(fname)    'writes out PDF if created

            Else

                'Display connection information for debugging

                '*** DEBUG CODE *** for use in TEST ENVIRONMENTS ONLY

                Response.Write(strReturnData)

            End If

        Catch ex As Exception

            Session("error") = ex.ToString

            'Add the location of the error

            Session("error") = "[Address: " & Request.Url.ToString & "] " & Session("error")

            Response.Redirect("ErrorEvents.aspx")

        Finally

            Response.Flush()

            'Response.Close()  'Prevents pdf's from rendering in Chrome

            System.IO.File.Delete(fname)

            GC.Collect()


        End Try


I'm wondering if there is some additional code needed after .ApplyLogOnInfo or if there are some other parameters needed when exporting after the connection info has been changed or if there is something else I'm missing.


Thanks for your help.

Former Member
replied

Hi Dave,

It could be as your logging shows:

      Original ServerName = [Dev]

      Original DatabaseName = [Dev]

      Original UserId = [UserName]

      ### DSN Connection ###

      Final ServerName = [Prod]

      Final DatabaseName = []

      Final UserId = [UserName]

After you set it to the Production DB the database name is empty. Should this not have the same name [Dev] or renamed to [Prod]? In either case it should not be empty.

If this is MS SQL Server that must have a name

And I would not Use .Refresh at all, this has all sorts of timing issues, it could be you are resetting the DB back to the DEV DB.

Rather than .Verify use TestDatabase as Ludek first suggested.

What happens if you stop the DEV DB completely? CR has the DSN info embedded in the RPT file so it has enough info to connect, renaming the DSN is not enough to validate which DB it is connecting to.

And of course the usual, install the MS SQL Server Native 10 client on both your DEV and App/IIS Server and update the reports to use the updated client.

You can do it in code but requires RAS and ReplaceConnection API to do this.

And Crlogger may give you more info, search for it, there is a Kbase article on how to enable.

Don

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question