Browse Data & Parameter flds no longer fully populated after upgd SQL 2005

[Former Member]

I upgrade our SQL 2000 Server last weekend to SQL 2005 (sp2).

Now our Crystal reports that access SQL data are not working correctly. Parameter Fields are showing little to no data, where before the upgrade we were seeing many more rows in the list.

Digging further, I looked at the fields used for the dynamic LOV. If I use Browse Data on those fields I am seeing the same lack of data. One "Browse Data" that would typically return 80 items now returns 3 items. In that report the data field is DateTime. In another report, the field is string.

I am also seeing an extra check for User Name and password before getting to the Enter values screen for the parameter.

I am using Crystal Developer 11.0.0.1994. It is happening on multiple PCs. I tried with both the original SQL ODBC Driver and the newer SQL Native Client ODBC driver, with the same result.

I've looked at this for 3 days with no luck. Any ideas?

[Former Member · Accepted solution]

have the field types altered (either in type or length) in SQL Server?

[Former Member · Accepted solution]

It was an in-place upgrade of SQL 2000 to SQL 2005. I did check, and no changes occured to either type or length.

In the case of one Crystal report, I see a "List of Values failure: fail to get values" error.

In some ways, this seems like a security issue of some type.

Keep up the ideas, folks, I appreciate all suggestions!

[Former Member · Accepted solution]

It could be!

What 'rights' do you have to the db?

may require a dedicated 'crystal report logon' to the db with read-only rights....

[Former Member · Accepted solution]

Full SysAdmin rights.

I use a dedicated ODBC connection and have tried both "SQL Server" odbc driver and "SQL NativeClient" (2005) odbc driver.

If I go to Crystal, to the Field Explorer, to the one of the fields, right-click and "Browse Data..." I get two results (I expect 82 results and have seen that in the past.) If I paste the query from Crystal, Database, "Show SQL Query.." to SQL Server Management Studio, I see the full, correct results.

In one example the field browsed is called "PeriodEndDate" (DateTime). A dynamic Parameter Field was created accessing this field. Here is the security curiosity...refreshing now asks for the user login each time "Prompt for new parameter values" is selected. It used to ask only the first time. The Crystal: Data Explorer" shows the current connection logged in ok.

The two issues may or may not be related. Certainly if #1 were fixed, dynamic parameters would return the correct results. Even if it asked for the password each time.

1.) "Browse Data" returning a smaller result than normal.

2.) "Parameter Prompt" asks for Server Name, Database Name, User Name, and Password on

each refresh selecting "Prompt for new parameter values".

[Former Member · Accepted solution]

Well, I'm giving up on "Why" this happened. I have a solution, of sorts.

I had some previous minor revisions of this report (2.1) versus the latest 2.8 version. No changes to the datasource or formulas or parameters between the two, just text and formatting changes. Yet this older revision works. So I made a copy and brought the text/formatting up to the current level.

I still have the constant prompting for a password i.e.

- "Parameter Prompt" asks for Server Name, Database Name, User Name, and Password on

each refresh selecting "Prompt for new parameter values".

[Former Member]

have you tried using the newer OLE DB (ADO) Provider as opposed to ODBC ?

regards,

dom

[Former Member]

I have and it didn't help the problem. Thanks for the sugguestion though.