Report issue with SQL

[fcoreyesv]

Hello,

We have 300 Crystal Reports files which need to be converted to use SQL server.

For some reason when the report is modified to use the SQL driver "pdsSQL.dll" the report does not work when we call it from our VB application.

We found out that Reports created to use Access converted to SQL do not work and they need to be created from scratch in order to work. I am guessing that reports created to use Access or ODBC connections somehow "keep" information in the report that "interferes" with the new connection to SQL.

Does someone knows if there is a workaround on this issue?
I do not want to recreate 300 reports. by the way I am using Crystal Reports 8.0 (I know is old )

Any comments will be appreciated.

Jose F

 

[synapsevampire]

I'm unaware of this issue, did Crystal tech support tell you this?

What connectivity is used for Access, File or ODBC?

If they use File, perhap there's a 2 step process required, change to ODBC to Access, then change to ODBC for SQL Server.

-k

 

[fcoreyesv]

The reports use MSAccess with a ODBC.

When the reports are converted and relinked they work fine. The problem comes when the application needs to change the name of the server to connect with at run time. We get the error "Server has not yet been opened" or something like that.

We tried to change the registry settings of the ODBC Connection at run time and it works but when the report is used in a computer with no network and the program tries to connect to the "Local" MSDE server it fails.

The only solution we found is to recreate all the reports using the SQL driver and it works. I just do not want to create 300 reports if there is a way to make the conversion using the Crystal report tools to change the database driver and the relink process.

 

[synapsevampire]

It does sound like a strange environment, that each system has a local MSDE database.

So if you use the Database->Set Location and point to the new database in Crystal itself, does it work?

If so, you may be able to use a batch processing product to change this, or even write code.

-k

 

[IanWaterman]

It could be that the SQL statement in the report is retaining the original database name. for example it may look something like


SELECT "Table Name"."Column Name", ......

FROM "CD_db_sample"."dbo"."Table Name" "Table Name"

"CD_db_sample"."dbo". is the database ID which will not be the same as your Live application.

Try removing these by opening report, in main tool bar

Database -> Set Alias

Click on any field and then set alias. If it has a database name similar to above the just delete from window, repeat for all fields.

If this works you have a long and tedious job to do, repeating for every selected filed in 300 reports. In versions CR9 and above it is easier as you can remove name at table level.

YOu can also invest in tools like Rpt Inspector which I think has a tool which can do this job automatically.

Alternatively, try creating a test enviroment with the same name and ODBC with same name as your live environment and point reports there. Then when you run against live everyting should be the same and connect OK.

Ian