Changing Database Server - Posting Again.

[zwieback89]

Hi,

Whenever we develop the reports, we need to set the database location by specifying the database server and the table or view which we want to refer to.

I have come to a stage where there are a large number of reports - reports and sub-reports. Last two times, when I shipped to a server in UK, a person over there had to manually change the database server all over again (set Location method) because it pointed to a different server based in our office even though it had the same database. Similarly, when we send these reports to the customers where they have a local database server, the same thing has to be done then as well.

It really takes a lot of time to change the database server location in each and every report.

What I wanted to ask in this perpective was is there a way where we can eliminate this way of changing the database location and mention only the table name in the report. The server and the table name is provided in one place and then it is provided to the CR (not just one report but all the reports). Tables or views are the same.

I do not know how to approach this issue, but I do definitely want to get rid of changing the database server everytime we send it to some client site for demonstration coz' it really takes an awfully long time.

I use CR 8.0, and all the reports are viewed from the web. I click on a link, pass a person ID, and then the report opens in the browser window showing information for that Person ID.

Please do give some way of doing this.

Regards,
Padmaja.

 

[balves]

I try to remember to strip out the servername name in each table via Set Location before going live with reports. Then connect via VB or ASP to the live database in the code before running the report.

Another way to go is use ADO recordsets and pass the recordset to the report. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[zwieback89]

Hi Brian,

OK, What I can do is to go to each and every report. And within the Set Location, I strip off the Servername. Could you provide me with some sample code where I can see how to pass the Servername from the ASP or VB to the CR.

I tried using ASP where I had 1 asp file where I declared the server name in a variable. This is an ASP file called RptServerName.asp

rptServer = "CVWeb"
rptDatabase = "ClinicalVision1"

Then in the ASP file that opens the CR, I include

<!--#include file=RptServerName.asp-->
<!--#include file=RequiredSteps.asp-->
In between these two files, I pass my parameters from the ASP to the CR (Patient ID, Start Date, End Date) Which is as follows:

'SET THE PARAMETER VALUES TO THE CRYSTAL REPORT.

Set session(&quot;ParamCollection&quot = Session(&quot;oRpt&quot.Parameterfields
Set Param1 = session(&quot;ParamCollection&quot.Item(1)
PatientID = Request.Form(&quot;PatientID&quot
Call Param1.SetCurrentValue (cint(PatientID), 7)

'set the start date
Set session(&quot;ParamCollection&quot = Session(&quot;oRpt&quot.Parameterfields
Set Param2 = session(&quot;ParamCollection&quot.Item(2)
SDate = Request.Form(&quot;DateFrom&quot
Call Param2.SetCurrentValue (cstr(SDate), 12)

'set the end date
Set session(&quot;ParamCollection&quot = Session(&quot;oRpt&quot.Parameterfields
Set Param3 = session(&quot;ParamCollection&quot.Item(3)
EDate = Request.Form(&quot;DateTo&quot
Call Param3.SetCurrentValue (cstr(EDate), 12)

session(&quot;oRpt&quot.RecordSelectionFormula = &quot;{web_Patient_Test_Results_Cardiac_Function.patient_id} = {?PatientID} and {web_Patient_Test_Results_Cardiac_Function.test_date} in DateTime ({?StartDate}) to DateTime ({?EndDate})&quot;

'Could you show me to how to pass the ServerName from this ASP page to the CR ?
'What changes should I make in the CR in order to catch the servername ?
'If in the future, I need to pass not only server name, but also database name (tables being the same), then what do I do ? What are changes are needed in the CR ?

<!--#include file=MoreRequiredSteps.asp-->
<!--#include file=SmartViewerActiveX.asp-->

I really hope to hear more on this. I have being trying hard to get this right, but not without any luck.

Thanks,
Padmaja.

 

[balves]

You need to call .LogonServer or .SetLoginInfo before you open the report on the web. It will establish a connection to the database.

For .LogonServer:

userid = &quot;myusername&quot;
password = &quot;mypassword&quot;

session(&quot;oApp&quot.LogonServer &quot;P2SODBC.DLL&quot;, &quot;Server name&quot;, &quot;database name&quot;, CStr(userid), CStr(password)

In the Crystal web sample apps, this code gets run after &quot;AlwaysRequired&quot; but before &quot;MoreRequired&quot;

There are examples on the Crystal Web site. If you're using the .RPT way of doing things, download a file called simpxmps.zip. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[zwieback89]

Thanks for the tip, Brian.

In the CR, in the Set Location, I see this in the text box beside the label &quot;table&quot; : Pubs.dbo.titles

So instead of typing Pubs.dbo.titles, I should only have titles in this text box. Is this right ? This is what I exactly did.

My 2nd question is since my the users who are viewing this report would not have CR installed in their machine except in the web server, would typing P2SODBC.DLL cause any problems ?

My 3rd question is:

When I mention the values in variable names like this:

'provide the server and the database values.
rptServer = &quot;CVWeb&quot;
rptDatabase = &quot;ClinicalVision1&quot;
rptUserid = &quot;sa&quot;
rptPassword = &quot;&quot;

session(&quot;oApp&quot.LogonServer &quot;P2SODBC.DLL&quot;, CStr(rptServer), CStr(rptDatabase), CStr(rptUserid), CStr(rptPassword)

I get the following error:

Error Type:
Seagate Crystal Reports ActiveX Designer (0x80047288)
Server has not yet been opened.
/Testfiles/SimpleSetLogonInfo.asp, line 60

where line 60 points to

session(&quot;oApp&quot.LogonServer &quot;P2SODBC.DLL&quot;, CStr(rptServer), CStr(rptDatabase), CStr(rptUserid), CStr(rptPassword)

And if I try to use the values instead like this:

session(&quot;oApp&quot.LogonServer &quot;P2SODBC.DLL&quot;, &quot;CVWeb&quot;, &quot;ClinicalVision1&quot;, CStr(rptUserid), CStr(rptPassword)

then also I get the above error pointing to this line written above.

You mentioned about opening the reports the report way. What did you mean by that ? I did not understand that question.

I am using the Web Component Server which is installed in the web server. And I have several links whereby when I click on them, then the corresponding reports open with the parameters passed from ASP page.

Hope to hear from you soon.
Regards,
Padmaja.

 

[balves]

1. Leave dbo.Titles, which is the name of the table.

2. What the user gets on his/her PC is the Active-X viewer. The first time the user runs a report off of the web server, he/she will get a certificate from Seagate and the SmartViewer Active-X component will be downloaded. After it is successfully downloaded, it will show up in ..\Downloaded Program Files directory (depending on Win 95/95/ME or NT) and show up in IE under Internet Options -> Settings -> View Objects.

The DLLs, like P2SODBC.DLL, are installed on the web server to run the report on the web server. The output report is sent back to the viewer on the client's PC.

3. If you're using the Crystal skeleton app, the &quot;AlwaysRequired&quot; file creates Session(&quot;oRpt&quot and then opens the report. In ASP, you can put an

If Err Then
Response.Write &quot;The previous statement - xyz failed&quot;
End If

Perhaps your problem is creating the oRpt object or opening the report before you get to .LogonServer.

Also, just to be safe, I would go to the web server and using those parameters, try connecting to the database with Access or some other tool. Make sure &quot;sa&quot; with no password is connecting to the server with the database name. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[zwieback89]

Hi Brian,

Please ignore the earlier questions. My page as follows works now though not completely:

<%
reportname = &quot;title.rpt&quot;
<!--#include file=RequiredSteps.asp-->

'provide the server and the database values.
rptServer = &quot;CVWeb&quot;
rptDatabase = &quot;ClinicalVision1&quot;
rptUserid = &quot;sa&quot;
rptPassword = &quot;&quot;

' Set the location
set crtable = session(&quot;oRpt&quot.Database.Tables.Item(1)
crtable.SetLogonInfo CStr(rptServer), CStr(rptDatabase), cstr(rptUserid), cstr(rptPassword)

'to test whether or not connection is made to the server
response.write crtable.testconnectivity

<!--#include file=MoreRequiredSteps.asp-->
<!--#include file=SmartViewerActiveX.asp-->

testconnectivity returns a value of true. So i guess the connection is right. Am I right ? ( I stripped off the servername and the database name from the Set Location Command in CR)

But somehow this connection is not working right when I pass parameters from ASP pages to the CR reports.

I request for the titleID from the previous page. ( I try to test whether or not this ASP page is able to catch the TitleID and it does. So there is no error here.)

And I pass it to the CR in the following way:

' Set the location
set crtable = session(&quot;oRpt&quot.Database.Tables.Item(1)
crtable.SetLogonInfo CStr(rptServer), CStr(rptDatabase), cstr(rptUserid), cstr(rptPassword)

'to test whether or not connection is made to the server
response.write crtable.testconnectivity


'Set the Title ID parameter
Set session(&quot;ParamCollection&quot = Session(&quot;oRpt&quot.Parameterfields
Set Param1 = session(&quot;ParamCollection&quot.Item(1)
Title = Request.Form(&quot;cmbTitle&quot
Response.Write title
Call Param1.SetCurrentValue (cstr(Title), 12)
session(&quot;oRpt&quot.RecordSelectionFormula = &quot;{titles.title_id} = {?TitleID}&quot;

I create a Parameter TitleID in the CR of String Data type.

In the Report->Select Expert->Record Selection->

I enter the following formula:
{titles.title_id} = {?TitleID}

But now when I view the report, I see the following error even though crtTable.testconnectivity returns true and the title ID is being passed:

Error detected by database DLL.
-2147192179
Seagate Crystal Reports ActiveX Designer

Does this connection works when we try to pass parameters to the CR from ASP pages ?

Hope to hear more on this.

Regards, Padmaja.