?
Solved

Crystal 2008 Set datasource location for SQL command reports

Posted on 2011-05-10
17
Medium Priority
?
1,099 Views
Last Modified: 2012-06-22
Hello,

I have a few Crystal reports for the Lawson ERP which were written on a MS SQL database.  The reports are a combination of tables and SQL commands.  I need to update the datasource to point to an Oracle 10g data base.   I can use the Set Datasource Location to easily map the tables.  Does anyone know of a way to update the SQL commands without recreating command?   Does anyone know of a way to view the SQL command on a report without connecting to the orginal data source?  I tried to use the export report definition command but the SQL is not listed in the report definition.  
0
Comment
Question by:jrbledsoe001
  • 5
  • 4
  • 4
  • +1
17 Comments
 
LVL 4

Expert Comment

by:musalman
ID: 35735030
To view SQL Command,
Database > Show SQL Query

Update Data source of SQL Command is same as Procedure...
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 35735072
I just tried one.

Use the SET DATASOURCE LOCATION
Create the connection to the new data source
Select the database for the command
Select the new database
Click UPDATE

mlmcc
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 35738540
For musalman and mlmcc, Lawson queries are treated as "commands" in Crystal Reports, but aren't really the same thing. They're actually create outside of Crystal entirely.

@jrbledsoe001 - do your reports use Lawson Queries or actual SQL Commands? Also, if you're using tables combined with Lawson Queries, you're going to get horrible performance.  The queries either need to be complete, including parameters, or you should rewrite the reports to use SQL Commands or tables entirely. Also, no, you cannot view the contents of a SQL Command or Lawson Query without first connecting to it's data source.

~Kurt
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 

Author Comment

by:jrbledsoe001
ID: 35742926
Hi Kurt,  

You must work on Lawson to know about dme queries (Lawson OLEDB connector).  These specific reports are not using the Lawson OLEDB connector they are written in MS SQL using SQL command parameters.  My goal is to connect the reports using SQL command statements to an Oracle database.  I'll try the suggestion from mlmcc and post an update.  

A couple of the reports are using tables and SQL commands - this was a short cut due to a time pressure.  Thanks for the advice to rewrite these as SQL statements.  The oracle db is gargantuan and the last thing I need are poor performing reports.  Thanks again!!!

jrbledsoe001 (aka Joanna_Bledsoe@dell.com)  
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 35743193
Hi Joanna,

Yes, I've had a few clients with Lawons and LBI. In fact I recently had a project where we had to convert DME queries to use either simply tables (where possible) or SQL commands.  So many of the reports performed so badly prior to the conversion, because they had parameters outside the queries, joins to other queries, joins to tables, etc..  It's great seeing a report that takes 10+ minutes running in seconds now!

In general, the reason you shouldn't have tables connected to a data source like a command or query is because the command is treated as an external data source. It must execute in entirety before any joins, Crystal Reports record selection criteria or parameters that exist outside the command can be processed.  When using a command, it's best practice to have everything you need, including parameters and selection criteria, inside the command.

~Kurt
0
 

Author Comment

by:jrbledsoe001
ID: 35760290
mlmcc - your solution worked perfectly to update my sql command statements in crystal
0
 

Author Comment

by:jrbledsoe001
ID: 35760297
kurt - I would like to network with you since i do lbi too.  would you send me your e-mail address?    
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35760472
Joanna - In general posting of email addresses in question is not allowed.  You can post it in your profile.
Please post it there.  This is mainly for your protection since there are people who scrape these pages looking for email addresses and then start sending spam.

mlmcc
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 35761351
My email is in my profile as is my linkedin.com profile. I'd be happy to network with you.

~Kurt
0
 

Author Comment

by:jrbledsoe001
ID: 35762523
mlmcc  -  thank you for pointing out the profile section for me.  i am a newbie to the forum.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35762605
No problem.  You post most anything you want there so long as it is moral and legal.

Most of us who post our addresses put a twist on it like spelling out AT or DOT.

mlmcc
0
 
LVL 4

Expert Comment

by:musalman
ID: 35762784
Dear All

I have advised the same thing , What was wrong in my suggestion ???
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 35764415
@musalman - Your reply instructed the OP to use Show SQL Query, which ins't where you set the location, nor is it necessary in this case. You also didn't explicitly tell her how to set the location, as mlmcc did. mlmcc's answer was the most technically correct.

~Kurt
0
 
LVL 4

Expert Comment

by:musalman
ID: 35776141
If you read Part of the question
"Does anyone know of a way to view the SQL command on a report without connecting to the orginal data source?"

I answered this ...

Secondly, He wrote
"I can use the Set Datasource Location to easily map the tables. "

that's why I suggested her that Update Sql Command is similar to update Table Datasource...
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35777941
As far as I know you cannot show a SQL command without connecting to the database.
I tried in the Database Expert by trying to edit the command.  It wanted to connect to the database
I tried the SHOW SQL QUERY - I got prompted for the parameters then it wanted the database.
Perhaps if there is data stored with the report it would work.

As to the second part, since she knew how to do that it either didn't work or she was doing something wrong

mlmcc
0
 
LVL 4

Expert Comment

by:musalman
ID: 35780034

see - I just created a report by using SQL, and just saw this Sql As i explained, Perhaps i am wrong ...

In 2nd process, if it did not work is another thing,,,but my answer was enough to understand... Perhaps I am wrong...
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 35780135
You saw the SQL because you were logged in to the database.  You had to be in order to create it.  You cannot see the SQL in Show SQL Command without connecting to the database. If you completely close Crystal Reports, open it again and attempt to view the SQL you'll be prompted to login again (assuming you aren't using some sort of trusted pass through authentication).

In the second, you just weren't explicit enough.  
Update Data source of SQL Command is same as Procedure...

Open in new window

doesn't mean anything to anybody who hasn't ever set location before or used a stored procedure as a data source.  It doesn't even mean "set location", since you might be talking about changing the code within the actual SQL itself.

You need to be explicit with your answers.

~Kurt  
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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. …
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 video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Suggested Courses

616 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