Solved

Any way to set ODBC connection string in Crystal Reports?

Posted on 2007-04-09
11
7,381 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now