Dynamic data location VB6 1

[MikeTom]

I have a crystal report external ot in a dsr (tried both methods) with subreports launched from VB6. I can get the report to launch fine from the server it has been setup on. But when i programtically change the server to use it will not run. The database tables,views,stored procedure for the report are the same just on different servers and different database names. I have tried the odbc,oledb methods of connecting. The main problem seems to be if you report data source location is a Table called reportdata it sets it up to be MyDB.dbo.reportdata. And this is not changed if you logon to a different database name.

Has anyone had this problem, or does anyone know how to run report on serveral servers(different db names)by setting the logoninfo dynamically.

Thabks in advance

 

[vidru]

You can set the db at runtime:

Dim crxTables As CRAXDRT.DatabaseTables
Dim crxTable As CRAXDRT.DatabaseTable

Set crxTables = Report.Database.Tables
For Each crxTable In crxTables
crxTable.Location = Replace (crTable.Location,(crTable.Location, "OldDB", "NewDB")
crxTable.SetLogOnInfo DSN_OR_SERVER, DB_NAME, USER_ID, USER_PASS
End With
Next

-Dave

 

[tdong]

Hi Dave

What is the equivalent .SetLogOnInfo for CR9 I don't see that at all.

 

[vidru]

Starting with version 9.0, all of the connection info is in ConnectionProperties.

I don't have 9 yet, so I can't be more specific than that.

-dave

 

[IdoMillet]

It's still there in CR 9

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[FredHelm]

Ido,

How do you change the location of a SQL Server stored procedure?

In the past the format was DBName.DBOwner.StoredProcedure and now in version 9 only the stored procedure is returned from the Location property of the table object. The technical reference manual indicates that the SetLogOnInfo method is deprecated RDC method. So I changed the values in the ConnectionProperties but that does not seem to work.

Fred

 

[vidru]

Fred,

Have a look at page 14 of this PDF about ConnectionProperties and changing location at runtime:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_rdc9_connectionproperties.pdf.asp

You'll see that they're setting the Location to the fully qualified name (Database.Owner.ProcName).

-dave

 

[FredHelm]

Dave,

I have seen one of your other threads with a similar issue. I have tried the method you indicated in the PDF. When I try to display the value in the location property of the table my new location value is not reflected. Then I tried the setTableLocation method and I have still had no success. The question that I have for the function SetTableLocation is for a SQL Server Stored Procedure what values should I use for the subLocation and ConnectBufferString? According to the technical reference manual the function call is SetTableLocation( pLocation As String, pSubLocation AS String, pConnectBufferString As String) The example that I have is for Access which is SetTableLocation("xtreme.mdb","Customer","").

Could you paste your CR9 code which changes the Table Location and logins to the database (ie SetLogonInfo code)?

Thanks

Fred

 

[vidru]

Fred,

I don't have CR 9 here, but I'll run some tests with CR 9 vs. SQL Server stored procs in a few hours. I'll let you know what I find out. In the meantime, if you can give me some more info on what exactly you're trying to do, I can try to duplicate your exact scenario.

-dave

 

[FredHelm]

I have a SQL Server stored procedure with n number of varchar input parameters and returns 1 recordset. The Crystal Report connects to the database via ODBC. A separate ODBC is create for the Development database (pubs) and the Test database (pubsFred). My ASP code logs on to the table and sets the location and then assigns the parameter values to the report.

To replicate the environment, I created a PubsFred database which is a duplicate of the Pubs database with the modification of some data to determine that the setTableLocation worked correctly. Create a report based off of the following stored procedure:

Create Procedure dbo.TitleAuthors
(@au_lname varchar(25) = null)
As
Begin

Select Title, au_lname, au_fname
From Titles t
Inner join TitleAuthor ta On ta.title_id = t.title_id
Inner Join Authors a On a.au_id = ta.au_id
Where au_lname = @au_lname or @au_lname is null
Order By Title, au_Lname

End
GO

So normally when you change the table path it should be changed from Pubs.dbo.TitleAuthors to PubsFred.dbo.TitleAuthors. When you make the change don't forget to use the second ODBC DSN. Btw, the default database in the ODBC setup is Pubs for one and PubsFred for the other.

Here is a snippet of my ASP code (I have included only the code that I'm having difficulty with):

Dim DataSource
Dim DBName
Dim UserId
Dim Password

DataSource = "PubsODBC"
DBName = "Pubs"
UserId = "sa"
Password = "password"

'Create a reference to the tables collection of the main report
Set Tables = Session("oRpt").Database.Tables

For Each mnTable in Tables
With mnTable.ConnectionProperties
.Item("DSN") = DataSource
.Item("Database") = DBName
.Item("user ID") = UserId
.Item("Password") = Password
End With

' - 2 -
' SET THE LOCATION OF THE TABLE
' To set the location of the database file we set the location for each table
' using the DatabaseTable Object's Location property
'

sLocation = DBName & ".dbo." & Tables.item(1).Location

Response.Write "New Table Location: " & sLocation & "<br/>" & vbcrlf
mnTable.Location = sLocation
' mnTable.SetTableLocation sLocation, sLocation, ""
' mnTable.SetTableLocation sLocation, "", ""

Response.Write "Table Location: " & Tables.item(1).Location & "<br/>" & vbcrlf

Next

...


' - 2 -
' GET THE DATABASE'S STORED PROCEDURE PARAMETERS
' Create a variable and point it to the Stored Procedure Parameter
' in the report

set StoredProcParamCollection = Session("oRpt").ParameterFields

For Idx = 1 to StoredProcParamCollection.Count
' Create a variable and point it to the specific stored procedure
' that we want to work on

Set ThisParam = StoredProcParamCollection.item(cint(Idx))

' Save the new value for the Stored Procedure in the Store Procedure
FormField = "PROMPT" & CStr(Idx-1)

If Request.Form(FormField) <> "" then
ThisParam.AddCurrentValue(CStr( Request.form(FormField) ) )
Else
ThisParam.AddCurrentValue(CStr( "*" ))
End If

Next

Thank you in advance for taking the time in assisting with my problem.

 

[vidru]

Fred,

Set up my machine with the two db's and DSN's you described, with the same stored proc. Using VB6 and the CRAXDRT library, this code switches the report from the PubsFredODBC DSN and pubsFred databases to PubsODBC and pubs db.

Dim cApp As New CRAXDRT.Application
Dim cRpt As CRAXDRT.Report
Dim cTable As CRAXDRT.DatabaseTable

Set cRpt = cApp.OpenReport(App.Path & "\Fred.rpt")

For Each cTable In cRpt.Database.Tables
    cTable.ConnectionProperties("User ID") = "sa"
    cTable.ConnectionProperties("Password") = "test"
    cTable.ConnectionProperties("DSN") = "PubsODBC"
    'Works with either of the next two
    cTable.ConnectionProperties("Database") = "pubs"
    'cTable.ConnectionProperties("UseDSNProperties") = "true"
    cTable.Location = cTable.Name
Next
cRpt.ParameterFields(1).AddCurrentValue ("Tater")

Viewer.ReportSource = cRpt
Viewer.ViewReport

Hope that helps you out. I don't do much with ASP, but hopefully you can adapt this.

-dave

 

[FredHelm]

Thank you Dave for taking the time to provide me with working code. I have been able to adapt it to the ASP and now my ASP works as expected. Now I have to do the same thing for version 10 arghh...

Fred