Solved

Any way to set ODBC connection string in Crystal Reports?

Posted on 2007-04-09
11
7,480 Views
Last Modified: 2008-01-09
I have a Crystal Reports (8.5) report that I am trying to build, using 3 databases (at least that is how Crystal refers to 3 different stored procedures.) I have not tried this before...

Everything works fine with 2 different parametrized stored procedures. As it turns out, both of those use the same parameter (in case that has any relevance.)

When I add any field from the 3rd stored procedure (that does not happen to have any parameters, in case that has any relevance), problems occur. Using the SQL Server ODBC, I got "connection is busy with results for another hstmt" error. I did some reading, and found this tidbit:

"You can avoid this error message by switching to the SQL Server 2005 ODBC driver, 'SQL Native Client' and enabling the MARS (Multiple Active Result Sets feature)."

and this:

"To use MARS with ODBC the connection string addition is “MARS_Connection=yes”, or you can a connection attribute as follows:
    SQLSetConnectAttr(hdbc, SQL_COPT_SS_MARS_ENABLED,SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);
before calling SQLDriverConnect or SQLBrowseConnect. "

So, I re-created the ODBC connection using "SQL Native Client", which is half of what is needed to enable MARS. I can see that I need to modify my connection string, which I can do programmatically within VB, but I don't see a way to do it within Crystal Reports application. Is there a way? If not, even using the newly created ODBC SQL Native Client, Crystal still throws up with the error:

"Connection is busy with results for another command"

Any ideas? I'll upgrade Crystal to v.11 (XI) if I have to. I bought v.9.0, and had so many problems I went back to 8.5, but God only knows, maybe they have finally fixed some of the bugs in Crystal by now. Seems to me the new Crystal versions are putting more and more emphasis on web reporting, which I don't need - yet. But if v.11 does a better job with JPG images referenced by path from a database, I would gladly jump on the v.11 bandwagon. But, I digress... My problem right now is the inability to gets MARS support within Crystal, so I can test the reports at design time.

Another thought is to restructure the source data, maybe dump it all into a temp table, and read it from there. But I just hate to see all that repeating data in so many fields! It's just not right! {in my best Kevin Meany voice}

Thanks!

Dennis
0
Comment
Question by:dtleahy
  • 5
  • 4
  • 2
11 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 18879780
CR XI allows images to be referenced by the path stored in the database.

Crystal has never liked using multiple data sources except through subreports.

mlmcc
0
 

Author Comment

by:dtleahy
ID: 18879917
Hi Michael, and thanks for the quick reply.

I know when I tried (a long time ago) to put more than one "database" (database object really, usually SQL Server stored procs or parameterized Access queries) into a report, I hit a brick wall. I actually thought it could not be done, and had not tried since then. But when I tried it recently, it looked like (other than throwing up a whining/complaining dialog) it was going to work.

So, if you have a report that has a certain data set, for example dynamic data that goes in a report header, and a different (big) data set that goes in a detail section, do you typically write one stored proc or query with all the merged data (with all the header info repeating throughout the data set), or do you typically use a subreport?

>CR XI allows images to be referenced by the path stored in the database.
I did read something about it on their site. However, I have learned to be skeptical. Several years ago, they listed (for Crystal 9) PNG right along with JPG as a supported image type. But after I tried, failed, and went to support, they admitted that PNG files at designtime could be embedded, but no support for dynamic PNG files at runtime. I got past that. JPGs are fine for what I'm doing, but it leaves me a bit skeptical to any claim they make.

For this application, I am storing just the filename in the database, e.g. "Bullfrog.JPG" and need to get the rest of the path at runtime, on the specific machine. So, on one machine, the full path might be "D:\Bobs Machine\Nested Folder1\Nested folder Z\Bullfrog.JPG". Have you tried XI, and do you know if I can concatenate in the path with a DB fields holding the image filename, at runtime?

Thanks again for your assistance. I really appreciate it!

Dennis
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 500 total points
ID: 18880104
>>So, if you have a report that has a certain data set, for example dynamic data that goes in
It really depends on the data.  In your case since the report header only runs once, I would probably use a subreport for that part.  Subreports tend to slow the report generation so i wouldn't use a subreport in the details section unless absolutely necessary.

Depending on the amount of data being used in the report header, I might consider a single joined query.

>>For this application, I am storing just the filename in the database, e.g. "Bullfrog.JPG"
I don't know if you can add the path at run time.  Is there a particular reason you can't set the path when you install the report and put the files in the report or application directory or a subdirectory under it?

Here is some information on it
http://www.businessobjects.com/global/flash/products/reporting/dynamic_image_location.asp

mlmcc
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:dtleahy
ID: 18880186
Hi Michael,

I have seen that Flash movie, and it kind of left me with the same question. Basically, they are using the Formula Editor and placing a hardcoded path into a formula. That didn't give me a warm fuzzy. I suppose I can always make the active path part of the query/stored proc, use 2 fields (path, and filename) in the Formula Editor, and allow the formula editor to concatenate the path and filename. Besides, it is still an improvement, because with the versions that I have (8.5 and 9.0) even a full path in a database field will not swap the image. I think I'm going to spring for v.11 and try it.

As for the original problem, it sounds like making the page header (and perhaps the report header) into a subreport will allow the users the most flexibility while remaining dynamic.

Thanks again for your time and expertise!

Dennis
0
 

Author Comment

by:dtleahy
ID: 18880195
a comment for anyone else reading the thread:

I said "...because with the versions that I have (8.5 and 9.0) even a full path in a database field will not swap the image."

It is possible, however, to swap the image as an OLE object in those older versions. I have done it. It's just not very intuitive or report-developer friendly.

Dennis
0
 

Author Comment

by:dtleahy
ID: 18880300
...sorry, forgot to respond to this:

>>Is there a particular reason you can't set the path when you install the report and put the files in the report or application directory or a subdirectory under it?

I'm trying to sell an application to a number of clients, with a normal installation routine that will allow them to install the application to any legitimate drive and path.  It just seems like poor form to force the user to put their image files in one specific directory, and even worse to force them to use a specific drive letter. In the Crystal version I am using (8.5) there is no Crystal equivalent to the VB "App.Path" in the Formula Editor. I guess the only way to know if I can make the path a field returned from the stored proc, and whether it can be concatenated at runtime to swap images will be to try it. Business Objects policy is to offer no pre-sales support, such as my question. For $195, they will allow me to call them once on the phone. Remember the good ol days when software companies offered unlimited free technical support? A fuzzy memory at best...

(I'm downloading a copy now. When I know the answer, I'll post it.)

Dennis
0
 

Author Comment

by:dtleahy
ID: 18880716
OK, it works. Now this info is buried in an ODBC topic...

Well, if someone wants dynamic images on reports, wants to save just filenames in their DB and supply the Path at runtime, it can be done with CrystalReports XI.

In the stored procedure (or paramaterized query), declare a string parameter to hold the Path.
(That would be  

@paramImagePath varchar(255)

or something similar in SQL Server.) Add that parameter to the SELECT statement's fields, aliased (using the AS keyword.)  For example:

SELECT strFName, strLName, @paramImagePath AS strImagePath

(There might be better SQL to do this, all I'm doing is passing in a Path string as a parameter, that then gets passed back out in the field data of the records...)

Then, follow the example in the movie in Michael's (mlmcc) posted link to format the Graphic in Crystal's Formula Workshop. But, instead of the hardcoded path, use the strImagePath field and concatenate the path field with the filename field.

FWIW...

Dennis
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 18882070
My comment on putting the images in a known location was made in case you were supplying the images even if different to the various clients.  

There is a trial version of CR XI available
http://www.businessobjects.com/products/reporting/crystalreports/eval.asp

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 18882073
Glad i could help

mlmcc
0
 

Expert Comment

by:qbjgqbjg
ID: 24445879
There is a way to set MARS on for the ODBC connection using regedit to add the connection string to the ODBC.INI. BUT, it still would not work with the older versions of Crystal. Crystal XI seems to work ok with it.
0
 

Expert Comment

by:qbjgqbjg
ID: 24452376
One more comment on this. In sql server 2000, mars is not needed if you have all of the corrections. It will handle it, so with an older crystal, you might be better off with SQL Server 2000 as long as you have the latest service packs.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question