> What I need to do is change the Server\Database at run-time for a report
> which has been developed using SQLServer 2000 Stored Procs. I am using the
> OLE DB drivers. My client app gets the connection to server , I have a ADO
> Connection object I can send to the Report (runtime) but I have no idea of
a
> way to do this. I am using the Viewer control to display the report to the
> user.

> Any assistance would be very helpful.

> To change database name, you need to set DatabaseTable.Location property
by
> specifying fully qualified object name (i.e.,

> > Ok, I'm a bit lost here. I've tried loads of code to achieve this but
none
> > have worked.

> > What I need to do is change the Server\Database at run-time for a report
> > which has been developed using SQLServer 2000 Stored Procs. I am using
the
> > OLE DB drivers. My client app gets the connection to server , I have a
ADO
> > Connection object I can send to the Report (runtime) but I have no idea
of
> a
> > way to do this. I am using the Viewer control to display the report to
the
> > user.

> > Any assistance would be very helpful.

> > You don't send an ADO connection object to report. Instead, you set
> > connection properties on CR report object so that CR can retrieve data
for
> > itself.

> > To change database name, you need to set DatabaseTable.Location property
> by
> > specifying fully qualified object name (i.e.,
> databasename.dbo.objectname).

> > > Ok, I'm a bit lost here. I've tried loads of code to achieve this but
> none
> > > have worked.

> > > What I need to do is change the Server\Database at run-time for a
report
> > > which has been developed using SQLServer 2000 Stored Procs. I am using
> the
> > > OLE DB drivers. My client app gets the connection to server , I have a
> ADO
> > > Connection object I can send to the Report (runtime) but I have no
idea
> of
> > a
> > > way to do this. I am using the Viewer control to display the report to
> the
> > > user.

> > > Any assistance would be very helpful.

> Private Sub SetTableLocations(AReport As CRAXDRT.Report)

> 'change any underlying data table

> Dim ATable As CRAXDRT.DatabaseTable
> Dim ASection As CRAXDRT.Section
> Dim ASubReport As CRAXDRT.SubreportObject
> Dim TheSubReport As CRAXDRT.Report
> Dim WorkStr As String
> Dim MyLoop As Integer, iVar As Integer

> Dim CDatabase As crystalDatabase
> Dim LogonDatabase As crystalDatabase

> Dim OriginalTableLocation As String
> 'Dim NewTableLocation As String

>     If PrimaryDatabase.OldTableSource = "" And
> SecondaryDatabase.OldTableSource = "" And PrimaryDatabase.TableSource = ""
> And SecondaryDatabase.TableSource = "" Then
>         'no table location settable
>         Exit Sub
>     End If

>     For Each ATable In AReport.Database.Tables

>         If Not IsOriginalTableLocation Then

>             On Error Resume Next

>             'get the old table location by using the new table location as
> key
>             ATable.Location = TableLocations.Item(ATable.Location)

>             On Error GoTo 0

>         End If

>         OriginalTableLocation = ATable.Location

>         If PrimaryDatabase.OldTableSource <> "" And
> PrimaryDatabase.TableSource <> "" And InStr(1, ATable.Location,
> PrimaryDatabase.OldTableSource, vbTextCompare) > 0 Then
>             Set CDatabase = PrimaryDatabase
>         Else
>             If SecondaryDatabase.OldTableSource <> "" And
> SecondaryDatabase.TableSource <> "" And InStr(1, ATable.Location,
> SecondaryDatabase.OldTableSource, vbTextCompare) > 0 Then
>                 Set CDatabase = SecondaryDatabase
>             End If
>         End If

>         If chkUseSQLDataSource.Value = 1 And SQLDataSource.DatabaseName <>
> "" Then
>             Set LogonDatabase = SQLDataSource
>         Else
>             Set LogonDatabase = CDatabase
>         End If

>         If Not (CDatabase Is Nothing) Then
>             'need to replace table location

>             If chkUseSQLDataSource.Value = 1 And

>             If LogonDatabase.UserName = "" And LogonDatabase.Password = ""
> Then
>                 ATable.SetLogOnInfo LogonDatabase.DataSourceName,
> LogonDatabase.DatabaseName
>             Else
>                 ATable.SetLogOnInfo LogonDatabase.DataSourceName,
> LogonDatabase.DatabaseName, LogonDatabase.UserName, LogonDatabase.Password
>             End If
>         End If
>         Set CDatabase = Nothing

>         Set LogonDatabase = Nothing

>         On Error Resume Next

>         'delete old record of matching old table location against new
table
>         'location if any record actually exists, thus error resume next
>         'stores the original table location with new table location as key
>         'the same table may be reused - however, same table location
should
>         'always have the same corresponding new location, 1 to 1 match
>         'thus the error resume next

>         TableLocations.Remove ATable.Location

>         TableLocations.Add OriginalTableLocation, ATable.Location

>         On Error GoTo 0

>     Next

>     'set table location for any subreport
>     For Each ASection In AReport.Sections
>         For iVar = 1 To ASection.ReportObjects.Count
>             If ASection.ReportObjects(iVar).Kind = crSubreportObject Then
>                 Set ASubReport = ASection.ReportObjects(iVar)
>                 'Set TheSubReport = AReport.OpenSubreport(ASubReport.Name)
>                 Set TheSubReport = ASubReport.OpenSubreport

>                 Call SetTableLocations(TheSubReport)

>                 Set TheSubReport = Nothing
>                 Set ASubReport = Nothing
>             End If
>         Next iVar
>     Next

> End Sub

> > So , If  set the location for the main report  -do I have to set it for
> the
> > subreports?. The stored proc doesn't change, only the database and
server.
> > Also, does this mean it will not prompt me to login - I have already
> > established a connection using my client, I don't want the user to have
to
> > login again when the report runs. Have you got any sample code which
> > demonstrates this as I have not been able to get it working.

> > > You don't send an ADO connection object to report. Instead, you set
> > > connection properties on CR report object so that CR can retrieve data
> for
> > > itself.

> > > To change database name, you need to set DatabaseTable.Location
property
> > by
> > > specifying fully qualified object name (i.e.,
> > databasename.dbo.objectname).

> > > > Ok, I'm a bit lost here. I've tried loads of code to achieve this
but
> > none
> > > > have worked.

> > > > What I need to do is change the Server\Database at run-time for a
> report
> > > > which has been developed using SQLServer 2000 Stored Procs. I am
using
> > the
> > > > OLE DB drivers. My client app gets the connection to server , I have
a
> > ADO
> > > > Connection object I can send to the Report (runtime) but I have no
> idea
> > of
> > > a
> > > > way to do this. I am using the Viewer control to display the report
to
> > the
> > > > user.

> > > > Any assistance would be very helpful.

> I am surprised there is not an easy way to open a RPT file, supply the
> servername/database or active connection properties and let the report
> run...after all its only the server/database that has changed - having to
> change every section of the report just to run the report on the live
server
> seems very antiquated.  I hope they address this shortfall in future
> versions....version 10 maybe...

> > Yes, you need to change location property subreports as well. Some
sample
> > code to get you started. Obviously some of the code won't make sense to
> you.
> > But you should be able to get the general idea.

> > Private Sub SetTableLocations(AReport As CRAXDRT.Report)

> > 'change any underlying data table

> > Dim ATable As CRAXDRT.DatabaseTable
> > Dim ASection As CRAXDRT.Section
> > Dim ASubReport As CRAXDRT.SubreportObject
> > Dim TheSubReport As CRAXDRT.Report
> > Dim WorkStr As String
> > Dim MyLoop As Integer, iVar As Integer

> > Dim CDatabase As crystalDatabase
> > Dim LogonDatabase As crystalDatabase

> > Dim OriginalTableLocation As String
> > 'Dim NewTableLocation As String

> >     If PrimaryDatabase.OldTableSource = "" And
> > SecondaryDatabase.OldTableSource = "" And PrimaryDatabase.TableSource =
""
> > And SecondaryDatabase.TableSource = "" Then
> >         'no table location settable
> >         Exit Sub
> >     End If

> >     For Each ATable In AReport.Database.Tables

> >         If Not IsOriginalTableLocation Then

> >             On Error Resume Next

> >             'get the old table location by using the new table location
as
> > key
> >             ATable.Location = TableLocations.Item(ATable.Location)

> >             On Error GoTo 0

> >         End If

> >         OriginalTableLocation = ATable.Location

> >         If PrimaryDatabase.OldTableSource <> "" And
> > PrimaryDatabase.TableSource <> "" And InStr(1, ATable.Location,
> > PrimaryDatabase.OldTableSource, vbTextCompare) > 0 Then
> >             Set CDatabase = PrimaryDatabase
> >         Else
> >             If SecondaryDatabase.OldTableSource <> "" And
> > SecondaryDatabase.TableSource <> "" And InStr(1, ATable.Location,
> > SecondaryDatabase.OldTableSource, vbTextCompare) > 0 Then
> >                 Set CDatabase = SecondaryDatabase
> >             End If
> >         End If

> >         If chkUseSQLDataSource.Value = 1 And SQLDataSource.DatabaseName
<>
> > "" Then
> >             Set LogonDatabase = SQLDataSource
> >         Else
> >             Set LogonDatabase = CDatabase
> >         End If

> >         If Not (CDatabase Is Nothing) Then
> >             'need to replace table location

> >             If chkUseSQLDataSource.Value = 1 And
> SQLDataSource.DatabaseName
> > <> "" Then
> >                 ATable.Location = Replace(ATable.Location,
> > CDatabase.SQLReplacementSource, SQLDataSource.SQLReplacementSource, , ,
> > vbTextCompare)
> >             Else
> >                 ATable.Location = Replace(ATable.Location,
> > CDatabase.OldTableSource, CDatabase.TableSource, , , vbTextCompare)
> >             End If

> >             If LogonDatabase.UserName = "" And LogonDatabase.Password =
""
> > Then
> >                 ATable.SetLogOnInfo LogonDatabase.DataSourceName,
> > LogonDatabase.DatabaseName
> >             Else
> >                 ATable.SetLogOnInfo LogonDatabase.DataSourceName,
> > LogonDatabase.DatabaseName, LogonDatabase.UserName,

> >         Set LogonDatabase = Nothing

> >         On Error Resume Next

> >         'delete old record of matching old table location against new
> table
> >         'location if any record actually exists, thus error resume next
> >         'stores the original table location with new table location as
key
> >         'the same table may be reused - however, same table location
> should
> >         'always have the same corresponding new location, 1 to 1 match
> >         'thus the error resume next

> >         TableLocations.Remove ATable.Location

> >         TableLocations.Add OriginalTableLocation, ATable.Location

> >         On Error GoTo 0

> >     Next

> >     'set table location for any subreport
> >     For Each ASection In AReport.Sections
> >         For iVar = 1 To ASection.ReportObjects.Count
> >             If ASection.ReportObjects(iVar).Kind = crSubreportObject
Then
> >                 Set ASubReport = ASection.ReportObjects(iVar)
> >                 'Set TheSubReport =

> >                 Call SetTableLocations(TheSubReport)

> >                 Set TheSubReport = Nothing
> >                 Set ASubReport = Nothing
> >             End If
> >         Next iVar
> >     Next

> > End Sub

> > > So , If  set the location for the main report  -do I have to set it
for
> > the
> > > subreports?. The stored proc doesn't change, only the database and
> server.
> > > Also, does this mean it will not prompt me to login - I have already
> > > established a connection using my client, I don't want the user to
have
> to
> > > login again when the report runs. Have you got any sample code which
> > > demonstrates this as I have not been able to get it working.

> > > > You don't send an ADO connection object to report. Instead, you set
> > > > connection properties on CR report object so that CR can retrieve
data
> > for
> > > > itself.

> > > > To change database name, you need to set DatabaseTable.Location
> property
> > > by
> > > > specifying fully qualified object name (i.e.,
> > > databasename.dbo.objectname).

> > > > > Ok, I'm a bit lost here. I've tried loads of code to achieve this
> but
> > > none
> > > > > have worked.

> > > > > What I need to do is change the Server\Database at run-time for a
> > report
> > > > > which has been developed using SQLServer 2000 Stored Procs. I am
> using
> > > the
> > > > > OLE DB drivers. My client app gets the connection to server , I
have
> a
> > > ADO
> > > > > Connection object I can send to the Report (runtime) but I have no
> > idea
> > > of
> > > > a
> > > > > way to do this. I am using the Viewer control to display the
report
> to
> > > the
> > > > > user.

> > > > > Any assistance would be very helpful.

> I have had lots of problems run time changing the database but use cr
> 7/8/8.5 successfully for about 3 years now, using the following method.

> - use the pdsmon driver (active data (ADO)) and design the report using
some
> test connection, but with exactly the same fields as the live version (we
> mostly use a select top 10 * from tblxxx as sql).

> in runtime use something like this:

> Set rsReport = New ADODB.Recordset

> Set myReport = New cryReportname

> strSql = "Select * From tblPrinttable where PrtId=" & gvSessionId

> rsReport.Open strSql, dbCon, adOpenStatic, adLockReadOnly

> If rsReport.EOF Then

> Msg "Nothing to print", vbOKOnly + vbInformation

> Err.Raise 32755

> End If

> myReport.ParameterFields.Item(1).SetCurrentValue gvSessionId

> myReport.ParameterFields.Item(2).SetCurrentValue "testvalue"

> myReport.Database.SetDataSource rsReport

> myReport.SelectPrinter Printer.DriverName, Printer.DeviceName,
Printer.Port

> myReport.PaperOrientation = crPortrait

> AantKopie = IIf(Printer.Copies = 0, 1, Printer.Copies)

> myReportAntw.PrintOut False, AantKopie

> We never let crystal reports mess with our databases, especially when we
use
> a lot (sometimes we use up to 24) of subreports. We use this method on SQL
> server 7,2000 and even Access databases.

> Hope this helps you.

> Regards,

> Skyliner Software bv

> Roy van Manen.

> > A bit long-winded but I'll give it a go, nothing has worked so far.

> > I am surprised there is not an easy way to open a RPT file, supply the
> > servername/database or active connection properties and let the report
> > run...after all its only the server/database that has changed - having
to
> > change every section of the report just to run the report on the live
> server
> > seems very antiquated.  I hope they address this shortfall in future
> > versions....version 10 maybe...

> > > Yes, you need to change location property subreports as well. Some
> sample
> > > code to get you started. Obviously some of the code won't make sense
to
> > you.
> > > But you should be able to get the general idea.

> > > Private Sub SetTableLocations(AReport As CRAXDRT.Report)

> > > 'change any underlying data table

> > > Dim ATable As CRAXDRT.DatabaseTable
> > > Dim ASection As CRAXDRT.Section
> > > Dim ASubReport As CRAXDRT.SubreportObject
> > > Dim TheSubReport As CRAXDRT.Report
> > > Dim WorkStr As String
> > > Dim MyLoop As Integer, iVar As Integer

> > > Dim CDatabase As crystalDatabase
> > > Dim LogonDatabase As crystalDatabase

> > > Dim OriginalTableLocation As String
> > > 'Dim NewTableLocation As String

> > >     If PrimaryDatabase.OldTableSource = "" And
> > > SecondaryDatabase.OldTableSource = "" And PrimaryDatabase.TableSource
=
> ""
> > > And SecondaryDatabase.TableSource = "" Then
> > >         'no table location settable
> > >         Exit Sub
> > >     End If

> > >     For Each ATable In AReport.Database.Tables

> > >         If Not IsOriginalTableLocation Then

> > >             On Error Resume Next

> > >             'get the old table location by using the new table
location
> as
> > > key
> > >             ATable.Location = TableLocations.Item(ATable.Location)

> > >             On Error GoTo 0

> > >         End If

> > >         OriginalTableLocation = ATable.Location

> > >         If PrimaryDatabase.OldTableSource <> "" And
> > > PrimaryDatabase.TableSource <> "" And InStr(1, ATable.Location,
> > > PrimaryDatabase.OldTableSource, vbTextCompare) > 0 Then
> > >             Set CDatabase = PrimaryDatabase
> > >         Else
> > >             If SecondaryDatabase.OldTableSource <> "" And
> > > SecondaryDatabase.TableSource <> "" And InStr(1, ATable.Location,
> > > SecondaryDatabase.OldTableSource, vbTextCompare) > 0 Then
> > >                 Set CDatabase = SecondaryDatabase
> > >             End If
> > >         End If

> > >         If chkUseSQLDataSource.Value = 1 And

> > >         If Not (CDatabase Is Nothing) Then
> > >             'need to replace table location

> > >             If chkUseSQLDataSource.Value = 1 And
> > SQLDataSource.DatabaseName
> > > <> "" Then
> > >                 ATable.Location = Replace(ATable.Location,
> > > CDatabase.SQLReplacementSource, SQLDataSource.SQLReplacementSource, ,
,
> > > vbTextCompare)
> > >             Else
> > >                 ATable.Location = Replace(ATable.Location,
> > > CDatabase.OldTableSource, CDatabase.TableSource, , , vbTextCompare)
> > >             End If

> > >             If LogonDatabase.UserName = "" And LogonDatabase.Password
=
> ""
> > > Then
> > >                 ATable.SetLogOnInfo LogonDatabase.DataSourceName,
> > > LogonDatabase.DatabaseName
> > >             Else
> > >                 ATable.SetLogOnInfo LogonDatabase.DataSourceName,
> > > LogonDatabase.DatabaseName, LogonDatabase.UserName,
> LogonDatabase.Password
> > >             End If
> > >         End If
> > >         Set CDatabase = Nothing

> > >         Set LogonDatabase = Nothing

> > >         On Error Resume Next

> > >         'delete old record of matching old table location against new
> > table
> > >         'location if any record actually exists, thus error resume
next
> > >         'stores the original table location with new table location as
> key
> > >         'the same table may be reused - however, same table location
> > should
> > >         'always have the same corresponding new location, 1 to 1 match
> > >         'thus the error resume next

> > >         TableLocations.Remove ATable.Location

> > >         TableLocations.Add OriginalTableLocation, ATable.Location

> > >         On Error GoTo 0

> > >     Next

> > >     'set table location for any subreport
> > >     For Each ASection In AReport.Sections
> > >         For iVar = 1 To ASection.ReportObjects.Count
> > >             If ASection.ReportObjects(iVar).Kind = crSubreportObject
> Then
> > >                 Set ASubReport = ASection.ReportObjects(iVar)
> > >                 'Set TheSubReport =
> AReport.OpenSubreport(ASubReport.Name)
> > >                 Set TheSubReport = ASubReport.OpenSubreport

> > >                 Call SetTableLocations(TheSubReport)

> > >                 Set TheSubReport = Nothing
> > >                 Set ASubReport = Nothing
> > >             End If
> > >         Next iVar
> > >     Next

> > > End Sub

> > > > So , If  set the location for the main report  -do I have to set it
> for
> > > the
> > > > subreports?. The stored proc doesn't change, only the database and
> > server.
> > > > Also, does this mean it will not prompt me to login - I have already
> > > > established a connection using my client, I don't want the user to
> have
> > to
> > > > login again when the report runs. Have you got any sample code which
> > > > demonstrates this as I have not been able to get it working.

> > > > > You don't send an ADO connection object to report. Instead, you
set
> > > > > connection properties on CR report object so that CR can retrieve
> data
> > > for
> > > > > itself.

> > > > > To change database name, you need to set DatabaseTable.Location
> > property
> > > > by
> > > > > specifying fully qualified object name (i.e.,
> > > > databasename.dbo.objectname).

> > > > > > Ok, I'm a bit lost here. I've tried loads of code to achieve
this
> > but
> > > > none
> > > > > > have worked.

> > > > > > What I need to do is change the Server\Database at run-time for
a
> > > report
> > > > > > which has been developed using SQLServer 2000 Stored Procs. I am
> > using
> > > > the
> > > > > > OLE DB drivers. My client app gets the connection to server , I
> have
> > a
> > > > ADO
> > > > > > Connection object I can send to the Report (runtime) but I have
no
> > > idea
> > > > of
> > > > > a
> > > > > > way to do this. I am using the Viewer control to display the
> report
> > to
> > > > the
> > > > > > user.

> > > > > > Any assistance would be very helpful.