Missing columns on view in Report Creation Wizard

[Former Member]

Hi all,

I'm having a strange problem with the Report Creation Wizard... When I select two views that I have in my database [ Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) ] the one has all columns displayed in it, but the other only has a subset of the columns the view actually has.... If I remove the one view with all columns displaying, leaving only the one that has the subset, then that view displays all the columns as it should. Why might this be happening, and more importantly, how to fix it? I need both views in my report, and link them together via a data field, which is one of the missing ones when I have the two views together in the Report Creation Wizard. (screen shots attached)

My CR version is CR Developer 2013, SP1 (14.1.1.1036)

Thanks!

Will

[former_member183750 · Accepted solution]

Hi Dennis

Weird issue, my thoughts:

1. How are you connecting to the data source?   ( ODBC, OLE DB, JDBC )

2. If connecting via ODBC / OLEDB, what is the driver / provider used?

I am asking the above questions because strange issue may arise when using the SQL Server ODBC driver, that was created for very old version of SQL Server.

When using MS SQL Server 2008, or 2012, the suggestion is to use the Native ODBC Driver for MS SQL Server corresponding to the MS SQL Server version you are connecting to. In this case, MS SQL Server 2012.

Remember:

For OLE DB use:

MS SQL 2005 - OLE DB Provider

MS SQL 2008 - SQL Native 10

MS SQL 2012 - SQL Native 11

MS SQL 2013 - SQL Native 11

For ODBC use:

MS SQL 2005 - SQL Native

MS SQL 2008 - SQL Native 10

MS SQL 2012 - SQL Native 11

MS SQL 2013 - SQL Native 11

And if after that, you still see the same issue then I will suggest to test it outside of Crystal Reports. See how sqlconn32 does.

And finally, if all fail, then use a command object in which you write your own SQL Query that joins the 2 views to retrieve the desired data set.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow me on Twitter

[Former Member · Accepted solution]

Hi Ludek,

The properties for the Data Source I'm using in CR show the provider as "SQLNCLI11", and the database DLL as "crdb_ado.dll"... So I'd say it's using the OLE DB (ADO) driver with SQL Server Native Client 11 provider. (Any easier way to get CR to tell you the nature of the DB connection?)

Tested it with the sqlconn32 util you provided, it can connect successfully with a ODBC connection I set up using SQL Server Native Client 11, and I could get all the col's of the view in question... I will attach the log to this thread.

So now what?

[Former Member · Accepted solution]

Looks like one can't add add'l attachments past the first post, so here's a link with the output...

sqlconn32 output

[former_member183750 · Accepted solution]

What happens if you try to connect CR via ODBC?

- Ludek

[Former Member · Accepted solution]

Unfortunately, the same thing...

BUT - getting out the highlighter and marking all of the col's that DO display, and then looking at the ones that are missing, I see the missing ones are all of type / length "nvarchar, 0"

So for some weird reason, when I pull other views in with the problematic view, I don't see any of the "nvarchar, 0" data-type columns... Surely this is a clue??

[Former Member · Accepted solution]

I also verified the other two views, they do NOT have any columns of type "nvarchar, 0" so that must be why they are not missing any columns...

[Former Member · Accepted solution]

...and some more info - looking at the view structure in SQL Server, where sqlconn32 shows "nvarchar, 0", the column type is actually defined in SQL Server as "nvarchar(max)" (see nchar and nvarchar (Transact-SQL) for more info)

[DellSC · Accepted solution]

These fields don't show up in on the Linking tab because Crystal won't let you make joins on varchar(max) fields.  I would be that they show up in the Field Explorer though.

-Dell

[Former Member · Accepted solution]

Do you know why that is? (Data stored my be too big for Crystal to handle?)

Unfortunately, this is bad for me, because it is a vendor-created view that I am using (and the underlying table the data comes from has all textual data in a nvarchar(max) field as well....)

[DellSC · Accepted solution]

Nvarchar(max) are treated as character blobs - in most SQL you can't join tables on a blob.  However, there is a way around this.  You can write a Command, which is a SQL select statement that will pull ALL of the data for the report.  In the command you can use the syntax of your database to cast nvarchar(max) fields to a shorter nvarchar field so that crystal doesn't see it as a blob.

There are a couple of best practices to use when using commands:

1.  DO NOT join a command with other tables or commands.  When you do this Crystal will not be able to push the join down to the database, so it will pull all of the data into memory and join it there.  This can significantly slow down a report.

2.  DO NOT use the Select Expert to filter data.  Instead, put ALL of the filters in the Where clause of the query.  If you use the Select Expert, once again Crystal will not be able to push the join down to the database, so it will pull all of the data into memory and filter it there.

3.  Parameters that are used to filter data MUST be created in the Command Editor.  Parameters that have been created in the Command Editor have some additional internal properties that parameters created in the report don't have and the Command Editor can't "see" parameters that were created in the report.  The Command Editor gives you some very limited properties that you can set when creating a parameter.  However, after you use the param in the command, you will be able to edit it in the report in order to set more properties.

-Dell

[DellSC]

It looks like you have SP1 of Crystal 2013.  I would try upgrading to SP5.  Here's information about how to do that: 

-Dell

[Former Member]

Hi Dell,

Thanks for the upgrade tip  - I went to the SAP CR 2013 Eval page (Thank You | SAP) and I was able to download 2013 SP4 (not SP5) from there. Upon removing 2013 SP1 and reinstalling with SP4, the problem remains... So not the fix.

Will

[DellSC]

Perhaps can help with this.

-Dell