Problem with changing database runtime...

[Former Member]

Hi:)

<br><br>

We are developing a ASP.NET solution where we are using crystal report .net.<br>

But we have ran into one problem, namely changing the reports database at runtime. Our code for that is below.<br>

It works locally, but when running it on the production server we got the error "database login error".<br>

If we call it with databasename, server, integratedSecurity = true on the production server, then it will ONLY WORK <br>

if we also provide a username and password. <br>

What is the meaning with that? I can't understand why we should provide username and password when <br>

integrated Security is set to true? Is this a bug or.... With integrated security it should take the logged in username and password from windows.

<br><br>

Sincerly Jan, Denmark<br>

<br><br>

Private Function AendreRapportDB_NY(ByVal Report As Object, ByVal Dbnavn As String, ByVal userid As String, ByVal passw As String, ByVal Server As String, ByVal integratedSecurity As Boolean, ByVal DSN As String) As Boolean<br>

Dim oCRDb As CrystalDecisions.CrystalReports.Engine.Database = myReportDocument.Database<br>

Dim oCRTables As CrystalDecisions.CrystalReports.Engine.Tables = oCRDb.Tables<br>

Dim oCRTable As CrystalDecisions.CrystalReports.Engine.Table<br>

Dim oCRTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo<br>

Dim oCRConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo()

oCRConnectionInfo.ServerName = Server<br>

oCRConnectionInfo.DatabaseName = Dbnavn<br>

oCRConnectionInfo.IntegratedSecurity = integratedSecurity<br>

oCRConnectionInfo.UserID = userid<br>

oCRConnectionInfo.Password = passw<br>

oCRConnectionInfo.Type = ConnectionInfoType.SQL<br>

For Each oCRTable In oCRTables<br>

Dim nvp As CrystalDecisions.Shared.NameValuePair2<br>

For Each nvp In oCRTable.LogOnInfo.ConnectionInfo.Attributes.Collection<br>

If String.Compare(nvp.Name, "QE_LogonProperties", True) = 0 Then<br>

Dim dca As DbConnectionAttributes = nvp.Value<br>

Dim nvp1 As NameValuePair2<br>

For Each nvp1 In dca.Collection<br>

Select Case UCase(nvp1.Name)<br>

Case "Database DLL"<br>

nvp1.Value = "crdb_odbc.dll"<br>

Case "DSN"<br>

nvp1.Value = DSN<br>

Case "DATABASE"<br>

nvp1.Value = Dbnavn<br>

Case "USEDSNPROPERTIES"<br>

nvp1.Value = "True"<br>

End Select<br>

'Current.Response.Write("2." & nvp1.Name & " = " & nvp1.Value & "<br>")

Next<br>

Else<br>

Select Case UCase(nvp.Name)<br>

Case "QE_DatabaseName"<br>

nvp.Value = Dbnavn<br>

Case "QE_DatabaseType"<br>

nvp.Value = "ODBC (RDO)"<br>

Case "QE_SQLDB"<br>

nvp.Value = True<br>

End Select<br>

'Current.Response.Write("1." & nvp.Name & " = " & nvp.Value & "<br>")

End If<br>

Next<br>

<br><br>

oCRTableLogonInfo = oCRTable.LogOnInfo<br>

oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo<br>

oCRTable.ApplyLogOnInfo(oCRTableLogonInfo)<br>

<br><br>

Dim crReportObjects As ReportObjects<br>

Dim crSection As Section<br>

For Each crSection In myReportDocument.ReportDefinition.Sections<br>

crReportObjects = crSection.ReportObjects<br>

'Next<br>

Dim crReportObject As ReportObject<br>

For Each crReportObject In crReportObjects<br>

If (crReportObject.Kind = CrystalDecisions.Shared.ReportObjectKind.SubreportObject) Then<br>

Dim crSubreportObject As SubreportObject = crReportObject<br>

Dim crSubreportDocument As ReportDocument<br>

crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)<br>

Dim crDatabase As CrystalDecisions.CrystalReports.Engine.Database<br>

Dim crTables As CrystalDecisions.CrystalReports.Engine.Tables<br>

Dim aTable As CrystalDecisions.CrystalReports.Engine.Table<br>

crDatabase = crSubreportDocument.Database<br>

crTables = crDatabase.Tables<br>

For Each aTable In crTables<br>

oCRTableLogonInfo = aTable.LogOnInfo<br>

oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo<br>

aTable.ApplyLogOnInfo(oCRTableLogonInfo)<br>

Next<br>

'Response.Write(crSubreportObject.SubreportName)<br>

End If<br>

Next<br>

Next<br>

Next<br>

End Function<br>

Edited by: Jan Tovgaard on Nov 24, 2009 9:24 AM

[former_member183750 · Accepted solution]

Hello Jan:

You don't mention version of CR you are using and other info that will be good to have;

Any SP applied for your version of CR?

Version of .NET used?

Database?

Database connection type?

OS you are deploying to?

I've also just competed an articel on how best to troubleshoot these types of issues. It is in the process of being published, but I am attaching it here. See if any of the info there helps you out.

Ludek

[Former Member · Accepted solution]

The problem happens in the GETSQLSTATEMENT line. Check out my code.

Any SP applied for your version of CR?

i installed CRRuntime_12_2_mlb.exe on the developer machines. Which is crystal report runtime service pack 2.

Version of .NET used?

Framework 3.5 is installed on the server

Database?

SQL server

Database connection type?

ODBC

OS you are deploying to?

Windows server 2003.

[former_member183750 · Accepted solution]

I would not consider the runtime to be equivalent to the actual SP. SP 2 is here;

https://smpdl.sap-ag.de/~sapidp/012002523100009038092009E/cr2008win_sp2.exe

Next, question; can you change the connection in the CR designer?

Also, see [this|https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/b021e47e-be1d-2b10-c6b2-efa9db3abd6b] article on integrated security and Crystal Reports.

See if you can simplify your code and use the code provided in the above article.

Ludek

[Former Member · Accepted solution]

Why is the runtime not equivalent to the actual service pack for the crystal report 2008 program?

The runtime package i have installed is:

Crystal Reports 2008 Fix Pack 2.2 - Redist Install

The newest package service pack for the crystal report program is :

Crystal Reports 2008 SP2 - Fix Pack 2.2

Both are package 2.2.

The reason why we are so interested in getting it to work with the redistrutable package, is that not all our customers

wants the whole crystal report program installed on their server.

It is not working. I have read many articles and tried for days now. Nothing seem to work.

The SQL SERVER where the database is, is on a different location that the webserver where the webapplication lies.

[former_member183750 · Accepted solution]

There is a difference between SP 2 and runtime for SP 2. Don't really want to go into details here. Suffice to say that on your development computer you must install SP 2. Then find another computer to test the runtime distribution on.

Suggest you create a phone case and discuss with a technician. Phone cases can be created here;

http://store.businessobjects.com/store/bobjamer/DisplayProductByTypePage&parentCategoryID=&categoryI...

Ludek

[Former Member · Accepted solution]

I get this error, nomather how i do it.

It fails when i use the getsqlstatement

We have bought the crystal report 2008 program, will there not be free support then. The link you gave me costs 200 dollars.

Server Error in '/EGBoligWeb' Application.

-

---

Failed to open the connection.Failed to open the connection.

brugere4 {7AA8C71E-9C93-4D7A-9F96-C6B746A725CF}.rpt

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: Failed to open the connection.Failed to open the connection.

brugere4 {7AA8C71E-9C93-4D7A-9F96-C6B746A725CF}.rpt

Source Error:

Line 185: Dim sql As String = "", rapport_sql As String = ""

Line 186: Dim obj As Object = Nothing

Line 187: rapport_sql = myReportClientDocument.RowsetController.GetSQLStatement(obj)

Line 188: sql = AendreSqlStr(rapport_sql, kode_sql, "")

Line 189: If InStr(sql, "'SYN'='SYN'") <> 0 Then

Source File: C:\inetpub\wwwroot\EGBoligWeb\App_Code\EG_Crystal.vb Line: 187

Stack Trace:

[COMException (0x800002f4): Failed to open the connection.

Failed to open the connection.

brugere4 {7AA8C71E-9C93-4D7A-9F96-C6B746A725CF}.rpt]

CrystalDecisions.ReportAppServer.Controllers.RowsetControllerClass.GetSQLStatement(ISCRGroupPath GroupPath, String& Reserved) +0

EGDI.Utils.Web.EG_Crystal.SaetSql(String kode_sql) in C:\inetpub\wwwroot\EGBoligWeb\App_Code\EG_Crystal.vb:187

CRYSTAL_TEST.Page_Load(Object sender, EventArgs e) in C:\inetpub\wwwroot\EGBoligWeb\CRYSTAL_TEST.aspx.vb:23

System.Web.UI.Control.OnLoad(EventArgs e) +99

System.Web.UI.Control.LoadRecursive() +50

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

-

---

Version Information: Microsoft .NET Framework Version:2.0.50727.4200; ASP.NET Version:2.0.50727.4016

[former_member183750 · Accepted solution]

Keep in mind that if this proves to be an issue with CR, your case will be refunded. Other than that, start testing from small and simple to bigger and more complicated. E.g.; a new report, one table, one field, our sample app. If that does not help and you can not et a phone case, let's see if somebody else has any ideas.

Ludek