Changing the database for a command in Crystal 9

[tfernand]

Hi All,
So I created a report in Crystal 9 using the "Command" feature that lets u enter complex SQLs for report generation. I created this using a development database. Now, its time to migrate it over to the production database. So i try using "Set Datasource Location" but it doesn't seem to work. It gives me an error saying it can't find some database tables in the destination or something to that effect (Can't recall the exact error message). How do you go about doing this in organized manner. We plan on using "Commands" extensively in our application.

Please help,
Appreciatively,
-- Tony

 

[mrmtek]

try using the following
data base name.dbo.table name

 

[tfernand]

thanks mikalm, I am not using VisualBasic or any other program to interface with Crystal. I am directly using Crystal reports. I'm guessing the solution u have provided works for a programming language interface with Crystal. Thanks anyways for the input

 

[GJParker]

You will need to edit the command to point to your live database,.

Database Expert, right mouse click on the command, edit command.

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[tfernand]

thanks for the post Gary. How do you get it to point to a different database. When one uses tables, we use the Set Datasource Location to change the database that the report accesses. How do u do the same thing for a command ?

 

[vidru]

As Mikalm suggested, try using the fully qualified database name in the command:

SELECT <fields>
FROM dbName.dbo.TableName
WHERE <condition>

-dave

 

[bdreed35]

What type of connection are you using? ODBC, OLEDB, Native, etc

I typically use ODBC. I set up a ODBC DSN that points to a specific server, or instance of SQL Server.
When I point to that DSN from within Crystal, I have access to whatever databases I have permissions on.

Now, if I want to change from development to say production, I will set up or use a different ODBC DSN that point to the Production SQL Server..

The point I am getting at is that if your new database is on a differnt server, you need to acces that through a differnt connection, not by changing the command itself.

Also, the datasource name is not going to change. You need to check the properties of it by expanding the nodes under the datasource in the upper pane of the Set datasource Dialog Box. Check out this thread782-806964 for more info on that.

~Brian

 

[tfernand]

Brian,
I am using ODBC. I guess I'm not explaining this right . Let me try this again.

So I created a report in Crystal 9 using the "Command" feature that lets u enter complex SQLs for report generation. I created this report to run against a development database. So I created a ODBC DSN for this dev database and used it to "Add Command" and everything works fine. Now, its time to migrate it over to the production database.
So I create a ODBC DSN for this prod database and I try to use the "Set Datasource Location" functionality to make the report use this new connection. However, it doesn't let me do this. In the "Set Datasource Location" window, I highlight the "Command" in the "Current data source" pane and highlight the "Add Command" in the "Replace With" pane of the production ODBC DSN and then click on update. When I do this it comes up with a blank Add Command window. So I cut and paste the SQL into this window and click on OK. However, this does not create the Command in the new prod database DSN connection.

This has worked fine when using reports that do not use commands i.e. I've developed a report using a dev database ODBC DSN and when time to migrate to production I would use "Set Datasource Location" and point it to the production database DSN and click on update.

Hope that explains it better. Question is what is the best way to go about doing this in an organized manner?

thanks and sorry for the long post.

 

[GJParker]

tfernand

I don't think it's possible to change the DSN for a command, you will need to use an OLE-DB connection. This will allow you to use the fully qualified database name in your command as mentioned above.



Gary Parker
Systems Support Analyst
Manchester, England

 

[simpelli]

I've run into the same issue using an Informix Database. We have 34 different reports, most of which require complex SQL, so we use the command object behind the reports.

My "quickie" workaround is to open my services file and comment out the service for the current (development) connection. I open the report and select "Verify Database" from the Database menu. Since the service information cannot be found, I am prompted for a connection. I then choose the new connection (production) that I want for the report. Do a Save As.. and I now have a production copy.

I'm guessing that this could work with DSN by changing the development DSN name, then selecting the production DSN? (I haven't tried it, but its' worth a shot)

-SI

 

[ddnh]

tfernand,

were you able to solve this issue? I have the exact same problem. Did you try simpelli's solution?

 

[ddnh]

nevermind, I found it finally...

http://support.businessobjects.com/library/kbase/articles/c2012566.asp