I have a Crystal XIr2 Report that reports on data from a SQL Server VIEW. Using the Database expert I created a connection to that view & inserted the fields. (Actually this report, as are about 75 others, are being upgraded from CR V7 but the same concept applies).

The problem I'm encountering is that the DataBase Expert is creating a fully qualified link to my test database that I can't seem to change at run time. The other reports that report on data from tables don't seem to have this problem.

When using the Show SQL Query option in CR is shows the Select statement as I'd expect. but the From Clause shows:  FROM   "TESTDB"."dbo"."FullPallet" "FullPallet"

For my other reports that report on tables only the From clause would show something similar to:  FROM   "Table1l" "Table1"

Since this app is deployed to multiple customers their servers and DBs will have varying names so at run time i send a connection string that works for all the other reports expect the ones that report on VIEWS. The connection string is thus: crReport.Database.Tables(1).SetLogOnInfo SqlServerName$, SqlCatalogName$, "UserID", "Password"

If i connect my app to a different DB on the same SQL server the report will find the TestDB & try to report on it. Obviously producing erroneous data. If i connect to a different SQL Server that has no TestDB, it produces an error that it can not find my TestDB.

I also tried to replace the report's SQL Statement by sending my own to it using:

crReport.SQLQueryString = "SELECT FullPallet.ShipOrder, FullPallet.SeqNum, FullPallet.Description FROM FullPallet FullPallet"

(FullPallet is the name of the View)

and it still fails on looking for the TestDB.

BTW - the reports are being called from a VB6 app, if that helps any.

So any help would be greatly appreciated.

Edited by GHustis - 16 Nov 2012 at 10:58am

Dell,
Your reply caused me to have a ton of questions. I did a little reading on the command idea and I think I will pursue it.
However my immediate concern is to do one thing and that is to point my reports to an SQL Server & DataBase that the app is pointing to. I have nearly 100 reports written in CR7 called by a VB6 app that I am converting to CR XIr2 to run on Windows 7. (a .NET conversion will come later where I'll probably take up the Command issue).
Since I have to do this as fast as possible I want to make as few changes as needed to get this done.
From my research on this It seems as though I need to set the Location of the Server & DB in code, but am having trouble figuring out how to do this.
All the examples i find on the net show me how to set the location of an MS Access DB.
Initially i was using just this one line of code to set it and it seemed to work fine except for a few reports, even if there were multiple tables in the report:
crReport.Database.Tables(1).SetLogOnInfo SqlServerName$, SqlCatalogName$, "UserID", "Password"
Where crReport =CRAXDRT.Report
I found another example to loop through all the tables in the report & set each table's Logon info, It too didn't solve my problem.
Since this app is at multiple customers where their Servers & DB's are named differently, I need to be able to pass this information to the report.
Thanks for any help you can provide.
Gary

 

Dell,
You just gave me the path to my solution. I opened the report in CR7 & in the Set Location window I removed the reference to the Database name leaving only the Table name (in this case a View) and saved it. I then opened & saved it in CR XI. The reference to the database name is removed & my SetLogOnInfo now directs the report to the database named there.
Thanks for your Sage advice, even if you weren't aware of what advice you were giving.
Gary.