Redirecting a database at runtime for a report in asp

[NordicMan]

I am using crystal reports 7 inmy asp site.My reports are working fine however....
After development, it is time to move the reports to the production server. Currently, because of the reports, we have three different sites because of the way crystal reports works (ie. a report is linked directly to a database
during design).When we release a new report, it has to be coppied to all three sites, then opened with crystal and then point the database to the correct database.However, if I can get the reports to receive a connection(real time), to dynamically change the database, then we could
have one site, and reduce maintenance.According to samples and documentation, this should be possible:

GIVEN MY SOURCECODE:

Set session ("oApp&quot = Server.CreateObject "Crystal.CRPE.Application&quot
Path = Request.ServerVariables("PATH_TRANSLATED&quot

While (Right(Path, 1) <> &quot;\&quot; And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
wend

Set Session(&quot;oRpt&quot = Session(&quot;oApp&quot.OpenReport(path & &quot;Kevin.rpt&quot;, 1)

session(&quot;oRpt&quot.MorePrintEngineErrorMessages = False
session(&quot;oRpt&quot.EnableParameterPrompting = False

' Create Database object
set CRDatabase = Session(&quot;oRpt&quot.Database
'Database Table collection

Set CRDatabasetables = CRDatabase.tables
NTables = CRDatabasetables.Count

strServer = &quot;MyServer&quot;
strdb = &quot;MyDatabase&quot;

For I = 1 To NTables
Set CRDatabasetable = CRDatabasetables.Item(CInt(I))
CRDatabasetable.SetLogOnInfo strServer, strdb, session (&quot;gbldbuid&quot, session(&quot;gbldbpwd&quot
Report.Database.Tables.ItemI).Location= &quot;&quot;&quot;&quot; & strdb & &quot;.dbo.ClinicalStaffMeetingRetrieveForReport&quot;
Next

set StoredProcParamCollection = CRDatabase.Parameters
Set session(&quot;Param&quot = StoredProcParamCollection
StoredProcParamCollection.item(1).value = Session(&quot;ProgramID&quot
StoredProcParamCollection.item(2).value = Request.Form(&quot;BeginDate&quot
StoredProcParamCollection.item(3).value = Request.Form(&quot;EndDate&quot

Session(&quot;oRpt&quot.ReadRecords

Seems pretty straint forward. If effect, this should change the database the report points to. The problem is, it isn't.It still goes to the existing development database and retrieves the information. My asp calls a stored procedure and passes it parameters. I have lot's with subreports, but if I can get the database to change, the rest is elementary.

I am using cr version 7 and ms sqlserver 7. Is there something in the report that needs to be set?

Any ideas??
NordicMan

 

[FowlerConsulting]

I see this all the time.

Set the DSN the same in PROD as you do in QA and DEV.
BUT configure the data source of the DSN to be their respective database instances.

Steven Fowler
info@fowlerconsulting.com

http://www.fowlerconsulting.com

 

[NordicMan]

Mr. Fowler,

Thanks for your response. We are accessing Sql Server natively. There is no DSN name

We have one report that is linked directly to tables. If I change the server name and db name using the following
CRDatabasetable.SetLogOnInfo session(&quot;gbldbserver&quot, session(&quot;gbldbname&quot, session(&quot;gbldbuid&quot, session(&quot;gbldbpwd&quot
it works.

When I use Stored Procedures it doesn't.

Using SetLogOnInfo, the first parameter either receives the server name or a DSN. It doesn't appear to like it when I use a server name instead of a DSN for a stored procedure.


Any other advice?

Sincerely,

Kevin Masek
KWorks, LLC
kmasek@execpc.com

 

[FowlerConsulting]

Do you mean you are trying to change the sp source on the fly?

please clearify.

Steven Fowler
info@fowlerconsulting.com

http://www.fowlerconsulting.com

 

[NordicMan]

Yes. The stored procedure exists on both servers. According to the documentation, I should be able to change the source server using SetLogOnInfo.

All reports except one execute one or many stored procedures. That seems to be my problem. However, it is supposed to be possible.

Kevin Masek
KWorks, LLC
kmasek@execpc.com