Database Name hardcoded in report causing problems

[Spuriousbugfix]

I am using Crystal 9 and MS SQL Server 2000 and the activeX viewer to launch my reports in the web using ASP.
All of my reports use stored procedures.
The majority of these reports use a Command to call the sp and the following code to set the log on details in the web.

Set CRTables = Session("oRpt").Database.Tables

For CRTable in CRTables
CRTable.SetLogOnInfo cstr(ODBC),cstr(Database),cstr(User),cstr(Pass)
Next

This is all working fine.
However I have been given a stack of reports to use in the web which do NOT have a command in them and just call the stored procedure so the database name is hardcoded in the call to the stored proc.
My above code does not seem to work with this and errors because it is looking for the same database the report was using when it was created.
(We have a number of reports that need to run against 5 different databases).

Any help with this would be appreciated as I can not set the location of some of the reports for unknown reasons (Crystal errors).

 

[Spuriousbugfix]

Forgot to say I am using ODBC.
If I do the following
Set CRTables = Session("oRpt").Database.Tables

For each CRTable in CRTables
CRTable.SetLogOnInfo cstr(ODBC),cstr(Database),cstr(User),cstr(Pass)
alert CRTable.Location
Next

It is showing me the correct database. i.e. Live

However when the report appears I get this error

Error Occurred Reading Records: Failed to open a rowset. Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'Test.dbo.spRep_meals'.

So it is still looking for the sp in the Test Database.

 

[vidru]

Try something like this:

For each CRTable in CRTables
CRTable.SetLogOnInfo cstr(ODBC),cstr(Database),cstr(User),cstr(Pass)
CRTable.Location = CRTable.Name
alert CRTable.Location
Next

The Location property probably still holds the entire schema, while the Name property usually only contains the actual name of the procedure. I've got a FAQ demonstrating this for VB6/CR 8.5-9 that outlines the procedure for changing the db at runtime: faq768-5374

-dave