Problem CR10/CRXI connecting by ODBC on SQL2005

[MarnickTelenet]

We have a lot of rpt-reports that are made based on an ODBC-connection to SQL2000-server.

We migrated to SQL2005-server an now a lot of reports give the error: "The database table <stored procedure> cannot be found."

It has nothing to do with the version of CR. We have the same problem in CR10 and in CRXI.

We think it has something to do with de ODBC-driver that CR uses to connect to SQL2005. If we reset the ODBC-connection to SQL2000, the report works. As soon as we set the ODBC-connection to SQL2005 the problem is back.

We MUST migrate to SQL2005, but we can't: not because of a problem with our application but because of a problem between Crystal Reports and SQL2005-server.

 

[IanWaterman]

Are the database names in SQL2000 and SQL2005 the same?

If not you will need to remove names from SQL query. If you look in SQL statement you will see something like

Select --- list of fields
From SQL2000datebasename.dbo.tablename...

remove go to Datebase -> set data source location
Click on + sign by tablename
Click Properties
Double click
Overridden Qualified Table Name:
and type in name as appears in Table name.

Repeat for all tables

Select statement will now look like

Select --- list of fields
From tablename...

Report should now be transferable to SQL2005.

Ian

 

[MarnickTelenet]

Ian,

Thank you for this information, but the reports are not based on tables. They are based on a stored-procedure and in the SQL-statement in CR we see:

{CALL "DatabaseName"."dbo"."MyStoredProcedure";1(parameters)}

If we run this SQL-statement from the query-analyser, it works. Although, in CR we get the message: "The database table <stored procedure> cannot be found." and we can't find why?

 

[IanWaterman]

The same rules apply to Stored Procedures, or are the database names the same in both SQL 2000 and SQL 2005.

alternatively in Database -> Set database location

Open connection in lower pane to SQL2005 and see if Stored Procedures are listed. If yes highlight SP in both panes and click update. If you can not see SPs in lower pane then there is something wrong with you ODBC connection.

I assume you have gone to File Options and under the database tab you have checked Stored Procedures.

Ian

 

[MarnickTelenet]

When I try to update the connection crystal reports gives the error: "Data object cannot be opened/accessed."

I'm sure the stored-procedure is in the SQL2005-database.

We can select the SP to use this in the report. But later, when we reopen the report and want to retrieve data or want to verify database it looks like CR does not find the SP and I dont' know why??? Then we get the error "The database table <stored procedure> cannot be found.".

Database in SQL2000: it works.
Same Database in SQL2005: it does not work.

Almost hopeless...

 

[IanWaterman]

Earlier you said

If we run this SQL-statement from the query-analyser, it works.

Have you run in both SQL 2000 and 2005.

Unfortunately, my background is mainly Oracle so can not help much with SQL Server.

Is the ODBC name the same for both connections. Have you tried just repointing the sql2000 odbc to SQL2005, and trying to trick the report that way.

Ian