Skip to Content
avatar image
Former Member

Problem with TINYINT table values inside reports using ODBC

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

8 Answers

  • Best Answer
    Apr 04, 2017 at 05:56 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 04, 2017 at 05:04 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 04, 2017 at 06:27 PM

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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Apr 05, 2017 at 03:09 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Apr 06, 2017 at 05:07 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Apr 07, 2017 at 12:37 PM

    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!

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 31, 2017 at 05:24 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

      cr-dotnet.png (101.6 kB)
  • Apr 04, 2017 at 05:52 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded