How do you get "setlocation" to work ? (CR 8.5)

[purrepirre]

I am using CR 8.5

I'm trying to change the database my reports are using from my local SQLServer test-database to the SQLServer at my customer.

In a best case senario this could be done dynamicly in code

The code i have tried so far (see below) seems to do nothing, no error but the reports still use the same db as before.

CODE
///////////////////////////////////

 Dim CRReport As CRAXDRT.Report
 Dim CRapp As CRAXDDRT.Application
 Dim param As CRAXDDRT.ParameterFieldDefinition
 Dim crTable As CRAXDRT.DatabaseTable

 Set CRapp = New CRAXDDRT.Application
 
Set CRReport = CRapp.OpenReport(REPORT_PATH & "\Notes.rpt", 1)
    
CRReport.Database.Tables.Item(1).SetLogOnInfo &quot;<Server Name>&quot;, &quot;<DB_Name>&quot;, &quot;<DBUser Name>&quot;, &quot;<Passw>&quot;

/////////////////////////////

I am not using the RDC-designer, would that change anything?
(at this point I would prefer to keep on not using the designer)

I am also using a stored procedure as my only datasource

Thankful for any Help

 

[redmondson92]

Here's an abbreviated example (this assumes you could have more than 1 datasource per report, thus the for...next thru the tables collection):

Set crxReport = crxApp.OpenReport(mstrReportPath)
Set crxDatabase = crxReport.Database
Set crxDatabaseTables = crxDatabase.Tables

For Each crxDatabaseTable In crxDatabaseTables
strDSN = "MyDSN"
strDataBase = "DBName"
strUserName = "sa"
strPassWord = ""

Call crxDatabaseTable.SetLogOnInfo(strDSN, strDataBase, strUserName, strPassWord)

strLocation = "DBName.dbo.Proc(sp_MyStoredProc;1)"

Call crxDatabaseTable.SetTableLocation(strLocation,"" , "&quot

next

 

[purrepirre]

almost there!

When I use your code I set the name of the SQLServer as strDSN, however the LogonServerName property of crxDatabaseTable refuses to change.

The other properties (DatabaseName, Username) work fine (only if you set strDSN="" though)

If I use a report made in the designer (i.e change "Set crxReport = crxApp.OpenReport(mstrReportPath)" to "Set crxReport = New CR_Report" )

everything seems to work fine!

 

[redmondson92]

Have you updated the datasource(DSN) to point to the new database? You said you set it to the "name of the SQL Server". In our case we assume there is a ODBC datasource that points to the correct DB, and the name of that DSN is the variable "MyDSN".

 

[redmondson92]

addendum...
last line should be "the name of that DSN in the variable strDSN"

 

[purrepirre]

I'm using a OLE DB connection to SQLServer at the moment and would prefer not to use a ODBC connection. From what I have read, putting the name of the SQLServer in the "Servername" property in SetLogOnInfo should work.

Further findings:
If I use the automation server objects (CRPEAuto.Application & CRPEAuto.Databasetable) instead of the CRAXDrt objects this code works fine:

crpTable.SetLogOnInfo "PROLIANT", "KIM_Copy", "sa", ""
crpTable.Location = "KIM_Copy.dbo.Proc(usp_GetInformationRowReport;1)"

I'm sort of new to CR but I have a hunch that using the automationserver objects isn't an optimal choice with regards to future development (feel free to inform me if this isn't correct).

So now I have 3 ways to solve my problem:
1. Remake the reports into .dsr files
2. Use the automationserver objects
3. Use a ODBC connection

I would still like to know though, if there is a way to make my first approch work.

 

[redmondson92]

I wouldn't recommend the automation server - I believe Seagate recommends the RDC, and other comments in this site have indicated this as well.

As far as why it's not working, I don't have any help. One thing to check - when you save your .rpt file, do you have "save with data" checked?

 

[Guest_imported]

Purrepirre, please try this:
1. Open your Crystal Report
2. Look for "Convert Database Drive" usually under Database (Crystal Report 7.0)
3. Change Pdsodbc.dll(ODBC) to PdsSQL.dll(SQL Server)
4. Save your report

Then go to your app:
CRReport.Database.Tables.Item(1).SetLogOnInfo &quot;<Server Name>&quot;, &quot;<DB_Name>&quot;, &quot;<DBUser Name>&quot;, &quot;<Passw>&quot;

CRReport.Database.Tables(1).SetLogOnInfo strServerName, strDB, strUID, strPWD

Notice: ServerName now is the name of the server, NOT ODBC_DSN or ServerAlias
Hope this will work!