Any way to set ODBC connection string in Crystal Reports?

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
dtleahyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dtleahyAuthor Commented:
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
mlmccCommented:
>>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
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

dtleahyAuthor Commented:
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
dtleahyAuthor Commented:
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
dtleahyAuthor Commented:
...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
dtleahyAuthor Commented:
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
mlmccCommented:
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
mlmccCommented:
Glad i could help

mlmcc
0
qbjgqbjgConsultantCommented:
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
qbjgqbjgConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.