Linking to SQL Server, Index Missing

[ottman2]

I'm using Crystal 8.0 and I connecting to SQL Server 2000 database. I want to link more than one table, but the indexes are not showing up in Crystal. From what I have searched, it seems indexes are not supported using ODBC? Is there any way to get the indexes to work with SQL Server?

 

[synapsevampire]

Assuming that indexes aren't being used because Crystal doesn't demonstrate them, or because of ODBC is incorrect.

If your concerned with performance, the biggest pitfall is in the construction of the record selection formulas, which is how you pass SQL to the database.

Here's my FAQ on that:

faq767-3825

And you don't need indexes to show in order to join tables either, so I'm not sure what your concern is.

-k

 

[ottman2]

My goal is to link them, I thought you needed a index to link fields, now I know better. I have not been able figure out the rules or logic of linking fields. Sometimes I get a error saying the field is not indexed..

 

[synapsevampire]

Using SQL Server or Access?

I haven't encountered that error myself, but I've not been on SQL Server very often lately.

Unfortunately your version is no longer supported by Crystal, there were many service packs, perhaps you can make sure that you have the latest.

You might also try a different connectivity, I believe that Crystal had a native connection for SQL Server back then.

-k

 

[fredp1]

The only time i've seen that a error about index fields and SQL server, is when the tables are in different db's.
(I generally report of the one db, or use a view in SQL2k to point to a table in another db)

When the tables are in the same db, then CR essentially doesn't care about the indexes.
Its up to SQL Servers' Query optimiser to determine the best way to retrieve the data. (it may decide not to use a index)
CR 8.5
SQL Server 2k


Cheers
Fred

 

[ottman2]

I am using SQL2k and Pervasive. That would explain it, thanks.

 

[synapsevampire]

A good trick for going across multiple databases is to LINK (not import) the tables into an Access database, then build the query in Access and use the query as the datasource for Crystal.

-k