CR9 Changing Datasource Location on Rpt w/SQL Command 1

[JeffAnderson]

I created reports for a client on a local copy of their Oracle database. Some reports contains SQL commands and some did not. When I sent them to the client, there were no problems setting datasource location on those without SQL commands. However, when they try this on the reports with SQL commands there are problems.

When attempting to set datasource location, MY info appears in the connection dialog window. When he overwrites that with his correct connection info (service, user ID, password) he gets a message 'Logon failed'.
This is very consistent. All reports without SQL commands are fine -- with them this occurs.
I have been working with Crystal Decisions to no avail.

Does anyone know if there's a trick to setting datasource location on a report that uses SQL commands when there is no shared repository?

Thanks VERY much for any help in advance.

 

[FVTrainer]

I don't if this will help you out or not. I've only been able to play with command objects in the SQL Server environment. The following code succesfully modified the connection properties of a command object based on the SQL Server ODBC driver:

Set crDatabase = crRpt.Database
For Each crTable In crDatabase.Tables
Set crConnectionProps = crTable.ConnectionProperties
crTable.DllName = "crdb_odbc.dll"
With crConnectionProps
.DeleteAll
.Add "DSN", "Northwind2" 'This is the name of my odbc connection
.Add "Database", "Northwind" 'This is the name of my database on the sql server that the command object is going after
.Add "User ID", "green"
.Add "Password", "password"
End With

I know that if you use, say an OLEDB connection to a table rather than ODBC, the following lines are not appropriate:
crTable.DLLName = "some dll"
.DeleteAll

So the driver is critical in determining what you need to set in the ConnectionProperties property bag. Unfortunately, I don't have Oracle to test on.

 

[JeffAnderson]

Do you put this statement inside the SQL command before the query?

Thanks again.

 

[FVTrainer]

Oh, I'm sorry. I was assuming you were using VB to launch the reports. I think I was doing too much vb stuff yesterday and didn't read your post closely enough to see that you didn't mention anything about vb. Let's start over. How is your client running his report? Does the client have Crystal Reports on the his desktop?

 

[JeffAnderson]

Yes he does have CR9 on his desktop.

 

[FVTrainer]

Do the sql command reference a table or database to which the user does not have rights? That's the only thing that comes to mind.

 

[JeffAnderson]

Unfortunately, no. The reports I created that do not use SQL commands access the exact same set of tables, and they can be loaded without problem. It only occurs when trying to set datasource location for reports containing SQL commands.

 

[FVTrainer]

Have you tried having the client save the command object to their own repository? Does this step also fail?

 

[JeffAnderson]

Just wanted to let you know that I figured it out and to thank you for your help.

This was caused be two things, both related to the repository:

1. Some of the SQL commands had been connected to my repository. Even if I disconnected before sending them to the client, they still thought they were attached to my repository and would not work. I solved this by disconnecting them from my repository and re-naming them. Once I did that and sent to the client they worked fine.

2. Since my client wasn't adding these items to his repository, I didn't think to rename the SQL commands. I only had one in each report and by default they were named 'Command'. Even though he didn't put them in his repository, they ended up there anyway. Once the first report loaded, then second one was looking at the existing 'Command' object in his repository and trying to map its Commands fields to the 'Command' object in the repository. Since they were different, they did not match and there were mapping errors. I solved this by renaming the command in each report with a unique name and re-sending.

These two steps solved all of my problems.

Thanks again for trying...I appreciate the help!

 

[FVTrainer]

Thanks for the update. I haven't worked a lot with the repository so your experience will help me in the future.