Yet Another Database Login Problem (CR 8.0 Rant)

[Hexonx]

Got a database login problem in CR? Who doesn't, but here's a new one that happened to me recently.

My reports were originally bound to a proc in the database in our office. We were logging in to this database using SQL security at the time. The reports are distributed with a VB6 app and are configured at runtime to access our client's database on their network using SQL security.

Since the original design, the database in our office was moved to a different server. When I recently needed to modify the reports, I had to re-connect the reports to the new server, which I did via integrated security.

After sending the updated reports to the client, some of the people were receiving, "No rowset returned" error messages, but most people had no trouble. Remember that the app configures the reports with SQL security credentials and nowhere in the app is the database ever connected to via integrated security.

Well, after being stumped for a couple of weeks, I fired up SQL Profiler to see what the reports were actually sending to the database to cause the 'No Rowset' errors. The queries were correct, but I noticed that the LoginName was the user's domain name! Yes, for whatever reason, the reports were now connecting via integrated security! I never figured out how to explicitly tell CR what authentication method to use, but here's what I did: I opened the reports in CR designer, changed the driver from OLEDB to Native SQL and back again, forcing me to login to the database, which I did using SQL security. The reports now login to the database correctly.

And the helpful tip? Stay as far away from Crystal Reports as possible. There are too many other reporting tools on the market to have to tolerate such a horrible product as this.

 

[synapsevampire]

<chuckling>

It's the number one product integrated into VB applications, and the available resources and sample applications are mind boggling.

Consider that had you learrned how to properly use authentication the problem wouldn't have occured.

I'll agree that It does have lots of annoying "features", but in your case...

-k

 

[Hexonx]

synapsevampire,

I suppose by your chuckling that you've either solved this problem or never encountered it. Do you know how to determine which authentication method to use at runtime (SQL or integrated)? We're using DatabaseTable.SetLogonInfo method, which has always worked. I don't know why CR would use any other credentials to access the database, especially if the credentials provided are valid in the first place.

Almost all of the problems I've encountered with CR are involved with data access. I recently discovered the Active Data data source, whereby the reports are bound to a text schema file at design time, then bound to an ADO Recordset at runtime. I've built samples this way in my research, and helped another person build a production application using the same technique, and both appear to be successful over time. This technique solves the three main problems I experience with CR:

1. Accessing the database.
2. CR's interpretation of varchar resultset fields as a String, which has a limitiation of 255 characters.
3. CR's interpretation of varchar stored proc parameters as a String, which has a limitiation of 255 characters.

With Active Data, I go get the data myself and give it to CR. Furthermore, I can specify a character field to be a Memo at design time and avoid the 255-char limit. And as for the String parameter problem, it goes away altogether, since I execute the stored procedure myself.

 

[MColeman]

Hey, now!

I'm a fan of Crystal's data access prowess.

With one tool, I can report from just about
anything: windows event logs, my appointments in
my Outlook calendar, or even Windows file directory
listings.