VS2010, against SQL 2K8 R2 - login failure when adding subreport.

[Former Member]

I have tried a number of ways to get this to work. Is there a set of instructions that can be followed to render a CR with a subreport?

I can create CR reports that run. Can create subreports that run, but a CR containing a subreport will not run on the server. Fails with a variety of errors, depending on whether I am using XSDs, OLE-DB or DSN connection. I give up trying different methods and looking all over the internet for fixes.

What is the preferred method of connection? I am using stored procedures, and typically have no prblem with RC, but I have to adwit - this- is the first time that I have tried a subreport.

I just cannot beleive how difficult this is to push into a non-dev environment. Maybe I am missing something simple . . .

Dev: VS2010 on Vista. CR SP1. Server: Win 2K8 R2 Ent, SQL 2K8 R2

Thanks!

[former_member183750 · Accepted solution]

Preferably, use the same connection type (ODBC, OLEDB, etc.) for both the main and the subreport. Then you'd want to use code along the lines of:

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
crReportDocument = New CrystalReport1

CrConnectionInfo = New ConnectionInfo
With crConnectionInfo
.ServerName = "dbconn1"
.DatabaseName = "Pubs"
.UserID = "vantech"
.Password = "vantech"
crDatabase = crReportDocument.Database
crTables = crDatabase.Tables
End With

crDatabase = crReportDocument.Database
crTables = crDatabase.Tables
For Each crTable In crTables
crTableLogOnInfo = crTable.LogOnInfo
crTableLogOnInfo.ConnectionInfo = crConnectionInfo
crTable.ApplyLogOnInfo(crTableLogOnInfo)
Next

'Log on subreport

crSubreportDocument = crReportDocument.OpenSubreport("Ron")

crConnectionInfo = New ConnectionInfo
With crConnectionInfo
.ServerName = "Rcon1"
.DatabaseName = "Northwind"
.UserID = "vantech"
.Password = "vantech"
End With

crDatabase = crSubreportDocument.Database
crTables = crDatabase.Tables
For Each crTable In crTables
crTableLogOnInfo = crTable.LogOnInfo
crTableLogOnInfo.ConnectionInfo = crConnectionInfo
crTable.ApplyLogOnInfo(crTableLogOnInfo)
Next
CrystalReportViewer1.ReportSource = crReportDocument

As you do not provide any code you may have tried, it's hard to give you more than that. If the above does not help, lsettle on a type of connection you want to use and let's proceed from there. As it is (XSDs, OLE-DB or DSN connection) is a bit too expansive(?)...

Ludek

Follow us on Twitter http://twitter.com/SAPCRNetSup

Got Enhancement ideas? Try the [SAP Idea Place|https://ideas.sap.com/community/products_and_solutions/crystalreports]

[Former Member · Accepted solution]

Yep, I am using all OLE DB connections on all pieces (currently) but have tried a number of other ways. As near as I can tell, there are settings that are saved in the report that I cannot edit.

here is my code, which works on my development manhine, but not on the server:

Protected Sub RunReportSanitized(ByVal ThisReport As CoinBase.Report)

' Time to call the report:

Dim repFilePath As String = Server.MapPath("MyReport.rpt") ' File Path for Crystal Report

' Declare a new Crystal Report Document object and the report file into the report document

Dim repDoc As CrystalDecisions.CrystalReports.Engine.ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument()

repDoc.Load(repFilePath)

ApplyLogOnInfo(repDoc, "IP_Address", "DB_Name", "UN", "PW") ' Should do all tables and subreport tables??

repDoc.SetParameterValue("@RunDate", CoinBase.GetReportParameter(ThisReport.ReportLogID, "RunDate"))

repDoc.SetParameterValue("Param1", "Same text")

repDoc.SetParameterValue("Param2", "Some other report text")

repDoc.SetParameterValue("Param3", "Final text")

repDoc.SetParameterValue("@GUID", ThisReport.GUID)

Response.Buffer = False ' Stop buffering the response

Response.ClearContent() ' Clear the response content and headers

Response.ClearHeaders()

' Export the Report to Response stream in PDF format

repDoc.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, True, "My Report")

End Sub

Private Shared Sub ApplyLogOnInfo(ByVal report As ReportDocument, ByVal serverName As String, ByVal databaseName As String, ByVal userId As String, ByVal password As String)

'Recurse into each sub-report

For Each rptObject As Object In report.ReportDefinition.ReportObjects

If TypeOf rptObject Is SubreportObject Then

Dim subReport As SubreportObject = TryCast(rptObject, SubreportObject)

ApplyLogOnInfo(report.OpenSubreport(subReport.SubreportName), serverName, databaseName, userId, password)

End If

Next

Edited by: MarkVick on Oct 19, 2011 9:12 PM

Edited by: Ludek Uher on Oct 19, 2011 1:10 PM

[Former Member · Accepted solution]

Eh, can't get the code to show up right - not sure why that button/tags isn't working either . . .

[former_member183750 · Accepted solution]

Post continued here

'Set the connection information on each table

For Each table As CrystalDecisions.CrystalReports.Engine.Table In report.Database.Tables

Dim tinfo As TableLogOnInfo = TryCast(table.LogOnInfo.Clone(), TableLogOnInfo)

tinfo.ConnectionInfo.ServerName = serverName

tinfo.ConnectionInfo.DatabaseName = databaseName

tinfo.ConnectionInfo.UserID = userId

tinfo.ConnectionInfo.Password = password

table.ApplyLogOnInfo(tinfo)

If table.Location.IndexOf("."c) > 0 Then

table.Location = databaseName & table.Location.Substring(table.Location.IndexOf("."c))

Else

table.Location = table.Location

End If

Next

End Sub

Found the ApplyLogOnInfo on the internet, but it doesn't help.  the error I am getting is this:

Server Error in '/App' Application.

-

---

Logon failed.Error in File MyReport {B500DA9B-F620-43CB-BF42-13C9F9FC87F5}.rpt:

Unable to connect: incorrect log on parameters.

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: Logon failed.Error in File MyReport {B500DA9B-F620-43CB-BF42-13C9F9FC87F5}.rpt:

Unable to connect: incorrect log on parameters.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[COMException (0x8004100f): Logon failed.

Error in File MyReport {B500DA9B-F620-43CB-BF42-13C9F9FC87F5}.rpt:

Unable to connect: incorrect log on parameters.]

CrystalDecisions.ReportAppServer.Controllers.DatabaseControllerClass.SetTableLocation(ISCRTable CurTable, ISCRTable NewTable) +0

CrystalDecisions.CrystalReports.Engine.Table.set_Location(String value) +747

App.Render.ApplyLogOnInfo(ReportDocument report, String serverName, String databaseName, String userId, String password) in C:\Users\Mark\Documents\Visual Studio 2010\Projects\App\Reports\Render.aspx.vb:238

App.Render.ApplyLogOnInfo(ReportDocument report, String serverName, String databaseName, String userId, String password) in C:\Users\Mark\Documents\Visual Studio 2010\Projects\App\Reports\Render.aspx.vb:224

App.Render.MyReportReport(Report ThisReport) in C:\Users\Mark\Documents\Visual Studio 2010\Projects\App\Reports\Render.aspx.vb:159

App.Render.Page_Load(Object sender, EventArgs e) in C:\Users\Mark\Documents\Visual Studio 2010\Projects\App\Reports\Render.aspx.vb:21

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

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

-

---

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.237

I am going to give the above a try as well. Hopefully you see something obvious wrong here . . . Thanks again!

[former_member183750 · Accepted solution]

Re. the formatting. There is a 2300 (+-) limit on a post. You loose formatting after that.

In any case, try the code I provided. If that does not work there is a utility that will write out the logon code required by the report for you:

[1553921 - Is there a utility that would help in writing database logon code?|http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes_boj/sdn_oss_boj_bi/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/scn_bosap/notes%7B6163636573733d36393736354636443646363436353344333933393338323636393736354637333631373036453646373436353733354636453735364436323635373233443330333033303331333533353333333933323331%7D.do]

As long as the same database connection type is used in both main and subreport, you can use it to logon both. Else if it is different, save out the subreport and run the utility to see how the subreport logs on.

- Ludek

[Former Member · Accepted solution]

I think the code you provided HELPS. But it is not working in all cases. Here is the deal:

I have the reports desigmed within Visual Studio. If I preview the report, I can change the parameters, and all is rendering as expected. The report with subreports is all working.

However, for production, I want to render the reports on the SQL Server. So when I run the web site locally, from within Visual Studio, when I click the button to render the report, it calls a web page on the SQL Server that generates a PDF via a fully qualified URL. This then streams to the client and opens up as a PDF. This works fine. My URL is "http://InternetIP/App/Reports/Render.ASPX" and I pass a GUID on the querystring to ID the instance of a report that I want to run. Again, this all renders fine when running locally on my dev machine.

However, if I log on locally to the sorver and try to run it from there, it does not work - I get the login prompts. And I am logged in as an Administrator!

I see that the connection information in that utility is VERY SPECIFIC, so I will try that tomorrow. Is there some code I can run right before I call the "repDoc.ExportToHttpResponse" and just see what the current login parameters are? I am assuming at that point, i could see something missing somewhere.

It is almost is if ON THE SERVER ONLY, the login commands are ignored, but only when I am executing the web page on the server. Maybe there is something about the server itself, but when I call the server from wy dev box, it works. This is VERY strange.

In the end, I will be callling my SQL Server from a hosted web site, which is why I am designing this to work with an InternetIP address in the first place.

Thanks again for your help - we are getting closer, but you have given me a few more things to try for sure. Thanks! I will post the results in the next day or two. Hopefully I can work on this some more soon!

[former_member183750 · Accepted solution]

The runtime may also cause logon errors. E.g.; runtime differences (CR, DB client and other) should also be considered. Run the [Modules|https://smpdl.sap-ag.de/~sapidp/012002523100006252802008E/modules.zip] utility and check if the CR, DB, VS++ runtimes are different.

- Ludek

[Former Member · Accepted solution]

The code created by the prior utility does not work when running locally. I am going to go back to my original code and then compare the loaded DLLs.

When it works, this is where I am at: I have tried so many combinations of things - I thnk I have it narrowed down pretty well!

Here is the line that is throwing the error, it is the line the generates the PDF, basically the last line of code to execute in VB before CR takes over and does its thing:

boReportDocument.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, True, "The Name of the PDF File to be Rendered")

Here is the error it is throwing:

Logon failed.

Details: [Database Vendor Code: 18456 ]

Failed to export the report.

Error in File CoinAnalysis {11B86E68-E25E-4FD4-90C4-FACFE065CE0C}.rpt:

Unable to connect: incorrect log on parameters.

Perhaps this is the wrong method to call? I think that this line of code is something I have used for years. so perhaps it is no longer supported in the newer CR code? This is the first time I have used this all in Visual Studio 2010. However, all reports that don't havve subreports are working just fine still.

Thanks again! Will post results of DLL comparison when I get that completed, but that app looks pretty hard to decipher . . . .

[Former Member · Accepted solution]

I am confused by the DLL matcher. To compare the DLLs, I would run the CR on my local destop. This would render in Inetpub, I believe. Hawever, when I attempt to render the report on the sorver, there is no such process generated.

Is there another way to compare the DLLs without attempting a comparison of active DLLs between loaded EXEs?

[Former Member · Accepted solution]

Here is what I have installed.

Development machine:

Vista Home SP2 (64-bit)

SAP Crystal Reports, version for Visual Studio 2010

SAP Crystal Reports runtime engine for .NET Framework 4 (64-bit)

Server:

Windows Server 2008 R2 Enterprise (64-bit)

SAP Crystal Reports runtime engine for .NET Framework 4 (64-bit)

Is there a way to know which DLLs I shouild be finding installed and where? Then I could compare them directly!

[former_member183750 · Accepted solution]

Modules will only work on 32 bit apps - my assumption that you were using 32 bit runtime...

Look up Database Vendor Code: 18456 in your databse documentation.

- Ludek

[former_member183750 · Accepted solution]

I'd like to take this back a step or tow and do the following:

1) Open the report in the CR designer.

2) Go to Database | Set Location.

3) For the main report, expand the properties node. SQLNCLI10.dll should be the dll this report is using

4) Repeat for the subreport.

5) If the above check out, save out the subreport

6) Delete the subreport from the main report

7) Run the main report on it's own - this should work

😎 Run the subreport on it's own - does it work?

I know you said in your original post that these work on their own and only stop working once yo insert a report as a subreport, but give the above a try.

- Ludek

[Former Member · Accepted solution]

Yeah but it is even worse than that:

I started with a report that contained 5 subreports, and it all works on the dev machine just fine. I think something is goofy on the server only, because it is there that they report containing subreports does not run.

In fact, everything works on my dev machine. I mean, I am very close to installing Visual Studio 2010 on my server to see if that helps! That is the only difference I can see.

I will stub all of this out and get back with the results though - probably not till tomorrow or the next day. It will be good to verify if the subs work on their own on the server - that I have NOT tried.

But for now, I need to create some simple "single" reports to cover for the complex ones that are not currently working. I did not think this would take so long for me to figure out. but I about give up, you know?

Thanks again!

[Former Member · Accepted solution]

Oh darn - forgot to mention! Was working on reports yerterday and VS 2010 just died. The windows "let me help you with your problem" thing choked up that there is a Service Pack 1 for VS, which I downloaded and installed. I wonder if that fixed anything? Could that have anything to do with my issue? I will test that right now!

[former_member183750 · Accepted solution]

Hello Mark

Frustrating for me, can't imagine how it is for you...

Can you please have a look at this MS Blog and see if it sheds a bit of light on the error?

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

One other step we can do before installing .NET on that server. Download an eval of CR 2008 Designer, install that on the server and try to run the reports in the CR designer. This often gives better errors or at least an indication of what may be wrong. An Eval of CR 2008 can be downloaded from here:

http://www.sap.com/solutions/sapbusinessobjects/sme/freetrials/index.epx

I think it will be a faster install \ uninstall than VS2010...

- Ludek

[Former Member · Accepted solution]

I have given up on subreports entirely. As soon as I add a second table to a report, it will not run on the server. So I have decided to punt any type of complex reporting (which is temporary, once I get basic functionality up and running I will have to find a workable reporting solution) - all of my reports will run off of a single table of data per report only.

However, sometimes this is not even working. Again, everything works on the dev machine (including a report with 5 sureports) but when pushed to the production environment, I get a login error on a simple report, which only has three fiields:

Server Error in '/MyApp' Application.

-

---

Logon failed.Error in File ReportName {14CE5A6C-C618-4E4F-98C0-829FA175B01C}.rpt:

Unable to connect: incorrect log on parameters.

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: Logon failed.Error in File CoinHistory {14CE5A6C-C618-4E4F-98C0-829FA175B01C}.rpt:

Unable to connect: incorrect log on parameters.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

The MS blog was no use - this is all occuring within CR, and there are no codes. The error reporting coming out of CR is simply pathetic. There is NO WAY that I am not applying the login information incorrectly. If I were, then why does everything work on Dev?

I am about to give up - I will try the demo install, which I hate doing to my Production server, but what choice do I have? Which one do I install exactly? Do I want "SAP Crystal Server 2011" or "SAP Crystal Reports 2008" or 2011? There are a dozen downloads there!

Thanks.

[former_member183750 · Accepted solution]

"SAP Crystal Reports 2008" or 2011. Either one will do fine.

- Ludek

[Former Member · Accepted solution]

I noticed that the refernces in Visual Studio for the Crystal assemblys all say "v2.0.50727" - is that referring to the version of .Net?

It doesn't seem like any have 4.0 - not sure if that was an issue or not.

[Former Member · Accepted solution]

Installed Crystal Reports 2008 on the server and opened one of the reports that is not working there.

I get this error: "This report was created with a version of Crystal Reports which is later than the version you are running. Some features may not be supported."

Do I need to download 2011 instead?

I will play with this one a bit and see if I can get anything to render. Thanks!

[Former Member · Accepted solution]

OK! Now we are getting somewhere!

On the server, when I open each report, it complains about being the wrong version, then I View Sample, which prompts me to enter all of the login information for the DB. Them the report opens. If I then SAVE the report, it will run from my web site.

Some reports complain that the default printer is missing as well.

Questions:

1. Why isn't the database info being saved with the reports, and then when I specify it during my rendering process, how come THAT version doesn't stick (in my VB code, I mean)?

2. Not having ANY printers on the server, why do some complain about that? Why not just select the default printer, or all complain? I am not sure how I would have specified on some and not others. Strange, you know?

3. Which version of what do I have installed where? I thought I had installed the same CR version on both my dev and prod machines. Is there a way I can check and then synchronize them? I want to use the newest version for sure. I should have gotten 2011, I guess! I am using VS 2010 against SQL 2008.

4. Since I have to do this "report fixing" on the server, will I have to pay for this software after 30 days, or however long the trial is? I plan to continue making reports from time to time, after I finish these.

Now I will try this with the original complex report containing 5 subreports, but it is looking good, for sure!

Thanks!

[former_member183750 · Accepted solution]

Re. CR 2008 complaining about version; inconsequential for our purposes.

1. Why isn't the database info being saved with the reports, and then when I specify it during my rendering process, how come THAT version doesn't stick (in my VB code, I mean)?

- A pretty big security gap if all the database info was saved(?).

2. Not having ANY printers on the server, why do some complain about that? Why not just select the default printer, or all complain? I am not sure how I would have specified on some and not others. Strange, you know?

- Depends on how the report is constructed and what options is uses. E.g.; these reports are not consistent in their printer settings. However, this should not impact database connections...

3. Which version of what do I have installed where? I thought I had installed the same CR version on both my dev and prod machines. Is there a way I can check and then synchronize them? I want to use the newest version for sure. I should have gotten 2011, I guess! I am using VS 2010 against SQL 2008.

- As I mentioned at the begining of this post, that really is not of any importance at this time.

4. Since I have to do this "report fixing" on the server, will I have to pay for this software after 30 days, or however long the trial is? I plan to continue making reports from time to time, after I finish these.

- What ever you have to do in the designer, you will have to do in code. So, you have a choice:

a) do the code correctly

b) obtain a valid license if you want to keep the eval longer than 30 days

- Ludek

[Former Member · Accepted solution]

1. Security gap to have the connection information in the report? Seriously? It is already in the application that opens the report! Then to generate a report, I have to pass that same information at run-time. Heck - to develop the report, I have to supply the login information (which is them stored with the report, since I don't have to supply it again.). So I supply the login info three seperate times in three seperate locations. Now THAT is a security gap, in my opinion - better to use the encrypted connection string from within .NET that is in the configuration file, no?. Anyway, the report is on a web server - it isn't like I am delivering a clear text RPT file to the end user to open in some client app, or anything like this. Storing the SQL login info in the CR file is NOT a security risk, and even if it was - that is exactly what the report server app is doing on the server when I edit them there anyway (to get them to ultimately work).

2. Not sure on this printer issue, but I am not selecting any printers for sure. I am generating to PDF, so I am not sure how CR is coming up with this one. Not a big deal. Always want (and have) print to PDF.

3. Clearly the version of CR is important. Otherwise it would not show me the error message when I open the RPT files on the server saying "this file was created in a newer version of CR." See 4A below.

4. So in tho end, you recommend that the CR reports will work if I A) use the code correctly, or B) buy a valid license for Crystal Reports for the server, so I can edit the file there to supply login info:

A) Since my code is NOT changing between Dev and Prod, and since the only change between Dev and Prod to get CR to work is that I open the CR file on the server, add login info, clear printer issues and hit save; this indicates to me that the source code is not a problem. Would you not agree? I believe I started this thread with "what is the recommended VB code that runs a report?"

B) I was under the impression that my Visual Studio 2010 license cleared me to create CR reports, and this is why CR is packaged with Visual Studio (and has been for years). If this is not the case, them I guess I need to find another reporting solution. I know that Reporting Services comes free with SQL Server, for example. If not, what does it cost for me to own an application that I use to open each RPT file once, add login info and hit save?

And if the code is incorrect, I am happy to alter it.. I just don't understand why everything works on Dev, but not in Production, usless I edit the RPT on the server with the full blown Crystal product. Then my code that did not work, suddenly does. This indicates to me that the file on the server was altered to work somehow.

Am I understanding all of this correctly?

[former_member183750 · Accepted solution]

I believe we're going way off on a tangent and I do not want to go in that direction. I really think the best thing to do at this point is to create a phone case here:

http://store.businessobjects.com/store/bobjamer/en_US/pd/productID.98078100?resid=S6I@hgoHAkEAAGsiyV...

If your issue is determined to be a bug, you will get a refund.

Alternatively, perhaps sopmebody else can pipe in and help. Umm, just to head off the next post I see coming down, having a peek at [this|] blog may be a good idea.

- Ludek

[zeljko_mrcic · Accepted solution]

Hi to all...

I have problem with subreport logon...

I have app running on one pc with SQL2005Express installed on that machine. Reports are working fine, with logoninfo or without it, with setting logon details for each subreport or with no setting.

Over mapped drive other users are starting that same app and they get log on error on reports with subreports. Reports with no subreports are working fine. Been everywhere, tried everything....

Subreports and main report are SQLNCLI...installed that on all machines starting app...

Tried to change provider in code....no luck.

Any suggestions?

[former_member183750 · Accepted solution]

This post was marked as "Answered". Please create a new Discussion. Add in more info, e.g.; version of CR - be precise (e.g.; CR 12 is not precise), version of .NET. Add more details re.; Over mapped drive.

This being a mapped driver, consider permission issues. Add the report with saved data to your new Discussion.

- Ludek