Best method for using Crystal Reports with SQL Server?

I wanted to know the best method to have Crystal Reports pull data from SQL Server without causing great strain on an existing network?

I saw a method of which a stored procedure was accessed through the connection and was considering doing that again.  I'm much more familiar with Access Reports and am wondering why can't I just make something similiar to the Access queries in my SQL Server machine?  I guess a View is similiar to Access' queries in that its a view of the data and that may be quite faster.

I am currently under the impression that the only way I'm going to get a SQL Server query to show up through an ODBC connection is having it as a stored procedure and that I really couldn't run queries except through Query Analyzer.  Since this part of the question relates to SQL Server - I am asking it here even though it involves Crystal Reports also.  Please correct my faulty reasoning!  Thank you.

LVL 1
stephenlecomptejrAsked:
Who is Participating?
 
TommyTupaConnect With a Mentor Commented:
Let me start off by saying that I can't really speak for the network side of things, but I'll throw in my two cents because views and ODBC will not be your least intensive data retrieval methods.  

From the SQL Server and CPU side of things, your least intensive methods will be stored procedures accessed via ADO.

Views are good but slower than stored procedures as the execution plan will need to be generated each time the view is accessed.  Stored Procedures do not need execution plans regenerated and allow for formulas and other logic thus assuing processing is being done on the server where it belongs.  In about two weeks of practice you'll find Stored Procedures easier to write than MS Access views, and much more powerful.  

Micrososft Data Links (ADO) as opposed to ODBC also has benefits such as improved connection pooling. To create a Microsoft Data Link, first create a text file and then rename to mylinkname.udl.  Double-click on the link, select SQL Server as your connection type and continue.  These are more transporable and offer other benefits.
0
 
andrewbleakleyConnect With a Mentor Commented:
I use views from sql server to generate crystal reports. I can save a lot of time that way and I havn't found any significant network strain yet.
make sure whatever method you use you only return the exact fields and rows you need so that unwanted data isn't being sent.
You are right views are the same as queries in access.
your last paragraph is a bit confusing, can you try it again I am sure a solution can be found for you.
0
 
SaxonWicaConnect With a Mentor Commented:
You can try creating stored procedure with parameters. And you can also try using simple selection formula.

From my past experiences, using view and selection formula on crystal repot will do the following : The crystal report will pull all the data generated by view command, then only get some of the intended data. You can notice this by reading progress on the bottom right of your Crystal Report designer. This happens a lot when you're using a bit complex selection formula. But when crystal report recognizes your selection formula as a simple one, it will include it on the sql syntax that crystal report made as "WHERE" command. (You can view the commands from Database menu -> Show SQL Query).

This won't happen on stored procedure with parameters, and of  course, this will reduce the strain on your network dramatically.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.