Solved

Any way to set ODBC connection string in Crystal Reports?

Posted on 2007-04-09
11
7,442 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

786 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