Problem with TINYINT table values inside reports using ODBC

[Former Member]

We have a report which references a value of a TINYINT column. Let's assume it is called CCC.

The report utilises ODBC connection.

It works perfectly with old ODBC driver "SQL Server". Was used for last 10 years without any issues.

If we use a new ODBC driver - "SQL Native Client 11" or ODBC 13 - the report stops working.

We analysed the problem; can see that the error happens if CCC contains values greater than 127. So it appears that the problem is related to signed/unsigned range.

Even a simple function like WHERE CCC IN [1,2,200] throws an exception. If we remove 200 from the range, everything works perfectly.

We need help urgently. I can provide a trivial HelloWorld example demonstrating the problem.

Appreciated

Michael

[former_member11696 · Accepted solution]

Hi Michael and Brian,

I don't see the problem you guys see? I tried in both CR 2008 SP 7, CR 2016 SP 2 and CR for VS SP 20 ( beta ) and in all cases I get data.

On Windows 10.

Created the table from your script into my SQL 2008 Server.

Make sure you don't have an old crdb_odbc.dll, should be the same version as the Designer is.

And here's my ODBC connection properties:

I suggest you do some more debugging or updating to the latest CR patches...

Have a great day

Don

[Former Member]

Hi Don

it appears that SP19 has actually done the job! Fingers crossed. The test lab is busy testing now. Will keep you posted about the results on Monday. thanks!

[former_member11696]

Ah yes, that link always get configured with user info.

Go here:https://wiki.scn.sap.com/wiki/display/BOBJ

On the right side you'll see Crystal Reports and a link to download. use that one.

Don

[Former Member]

Hi Don

The problem (stops showing TINYINT column at 127 when using new ODBC driver version) can be still demonstrated using a trivial .Net app which uses the CR Viewer control. The app developed in VS 2015, using CR for VisualStudio 13.0.19 (this is SP19, we just checked it again).

It is a dev PC, Win7 x64, with local SQL 2012.

All you need to test it is the EXE, the Template, and a table in the database.

I am happy to allow you TeamView access to this PC, and let you control it whichever way you want.

An engineer can be here at any time, disregard the timezone (Sydney). Just give us 1h advance warning.

Much appreciated!

M

+61 410 60 70 20

[former_member11696]

M, we tested it using a remote SQL Server and it works using the latest CR SP's.

If you are using an earlier version of CR it will not be SQL 2008 or above aware, we updated our drivers so it was aware of the new Client dll's.

What is happening is because CR does not recognize the client it defaults to a different SQL generator and that can cause the problem

You can get the full builds from here if you don't have access to SMP downloads:

https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/patch_download/main_public.htm

Don

[Former Member]

Hi Don

the link gives me "403 You do not have the permissions to access this resource".

It appears that I don't have access to SMP downloads. How do I proceed from here?

Thanks

M

[former_member11696]

PS - Just tested with ODBC 13 driver and it works fine also.

[Former Member]

Hi Brian

[ a personal note: believe me I know how stupid and annoying things may appear, when trying to reproduce a problem reported by a customer 🙂 Your patience and desire to assist us is truly appreciated.]

We also observed that things work perfectly on Win10 when accessing a table on a LOCAL SQL server. But it still fails if we connect to SQL server located on another (also Win10) PC. Both SQLs are 2016. Same failure is observed when doing the opposite: accessing a table located on "good Win10 SQL PC" from another computer (checked with Win10 and 8.1).

So to summarize our observations so far: things MAY work when everything is on a single PC, but 100% fail for us when accessing a remote SQL server.

If I may ask you: please, give it another try.

In the meantime I'll double-check that we are running the latest SPs etc. Will report my findings.

Regards

M

[former_member292966]

Hi Michael,

I was able to duplicate the problem and it definitely looks like it's a Crystal problem with the new ODBC driver. I tested it in Excel and it worked fine. I also tested it with MS Query and I also saw all the numbers up to 255.

Just to see how far the problem ran, I tried it in Crystal 2008 and on another machine using CR4VS 13.0.2000.0 with the same results. Let's see what Business Objects will do.

Thanks,

Brian

[vitaly_izmaylov]

Tested on CR 2013 and CR 2016 with both drivers on two different machines, but did not reproduce the issue.

It only throws an error if we use out of range numbers like

WHERE CCC IN [1,2,256],

but it is by design because 256 is out of range. Any values less than 256 works fine.

What is the exact ODBC Driver version number, what is the version of the SQL Server and the version number of CR Designer?

The question is for both of you, Michael and Brian.

[Former Member]

Hi all,

I am following this case because it happens in my PC too.

Version: Crystal reports*: 2008 (12.1.0.892 - Product type: Full)

SQL: SQL Server 2012 (11.0.3156.0)

Microsoft. Net Framework 4.0.3031942000

if I set report selection formula as

{TESTREPORT.Num}=255

The report will result nothing.

Note: the database is resided in local PC.

Hope it help you guys replicating the problem.

Thanks for your attention.

[Former Member]

Hi Vitaly Izmaylov,

I wonder if my illustrations help you to reproduce the problem.

Thanks for your time and effort.

PS:

My PC: Windows 7 professional SP1.

Local database.

[former_member292966]

Hi Michael,

Can you test something? In another program that uses ODBC such as Access, can you create a query that uses the same WHERE clause and see if that works?

I want to isolate whether the problem is with the ODBC driver or Crystal. If it fails in Access then the problem is with the new ODBC driver.

If it works in Access then the problem is in Crystal and we can take a closer look at that.

What version so SQL Server are you using?

What version of Crystal are you using?

Thanks,

Brian

[Former Member]

Hi Brian

thanks for looking into it.

[do you have an email address which I can use to send you some attachments?]

I can trivially demonstrate the problem:

1. Create a table in your DB, using the following script:

create table TESTREPORT(Num tinyint , NumValue varchar(32))

go

declare @i tinyint

set @i = 0

while (@i <= 255)

begin

insert into TESTREPORT(Num, NumValue) values (@i, 'Num-'+convert(varchar(4), @i));

if (@i = 255)

break;

select @i = @i + 1

end;

2. Create ODBC data source, using either ODBC 13 driver or Native Client 11 driver. Call it TESTREPORT

3. go to ftp.pacom.com, user=pacom pwd=public. Take a report template inside ODBC folder. The template references TESTREPORT DSN. It simply prints the table.

(in case you cannot use FTP: grab the template from https://www.dropbox.com/sh/ywnx7qlkmzwvixz/AAAUG6py4lmW2P3jP8MbjeWva?dl=0)

4. Preview.

You should see that the "Num" column stops once it reaches 127. The attached bitmap shows it.testreport.png

If you however change TESTREPORT DSN to use old "SQL Server" driver, the report will be generated correctly.

This problem is a real showstopper. Any help/advise is truly appreciated. Feel free to contact me on michaelz@pacom.com or even call directly on +61 410 60 70 20

Regards

Mtestreport.png

[Former Member]

Just in case you are not allowed to use FTP: the same template is in there: https://www.dropbox.com/sh/ywnx7qlkmzwvixz/AAAUG6py4lmW2P3jP8MbjeWva?dl=0

[former_member292966]

Hi Michael,

I ran the tests and I was able to see all the values up to 255 in both cases. I noticed in the screenshots it looks like you're using an older version of Crystal. What version of Crystal are you using and what version of SQL Server. I'll try the tests in the same environment and try to duplicate your problem.

Thanks,

Brian

[Former Member]

Hi Brian

For this specific test, we used CR 2008 Template Designer, running with SQL 2008 R2 server and SQL ODBC 13 driver.

We have observed the same problem when viewing the same report from a simple app, using a more recent CR .Net package. The details are in the attached image. cr-dotnet.png

Thanks for looking into it!

Regards

M