7 Replies Latest reply on Aug 7, 2018 7:25 AM by Ted Namias

    SQL Error

    Gabriel Andrey
      Share This:

      Bonjour,

       

      I'm browsing trough the mtxagent.log and found that error:

      2017/02/21 10:23:12 Vision64Database                 I   [2872]  GeneralUpdate: SoftwareRelatedJob start

      2017/02/21 10:23:12 Vision64Database                 D   [2872]  Updating Installed Application Count

      2017/02/21 10:23:12 Vision64Database                 ERR [2872]  SQL Error: ORA-12704: non-concordance de jeux de caractères

      2017/02/21 10:23:12 Vision64Database                 ERR [2872]  Query: SELECT a.ApplicationName, a.ApplicationVersion, COUNT (a.ApplicationName) FROM Applications a INNER JOIN SoftwareInventory s ON ((s.Version IS NULL) AND (a.ApplicationVersion = '') AND (a.ApplicationName = Name)) GROUP BY a.ApplicationName, a.ApplicationVersion UNION SELECT a.ApplicationName, a.ApplicationVersion, COUNT (a.ApplicationName) FROM Applications a INNER JOIN SoftwareInventory s ON ((s.Version IS NOT NULL) AND (a.ApplicationVersion = s.Version) AND (a.ApplicationName = CONCAT (CONCAT (Name, ' '), Version))) GROUP BY a.ApplicationName, a.ApplicationVersion

      2017/02/21 10:23:12 Vision64Database                 D   [2872]  Dropping database connection

      2017/02/21 10:23:12 Vision64Database                 D   [2872]  Installed Application Count updated

      2017/02/21 10:23:12 Vision64Database                 I   [2872]  GeneralUpdate: SoftwareRelatedJob end

       

      I understand that a.ApplicationVersion = '' should be written a.ApplicationVersion IS NULL or a.ApplicationVersion = N'' and it would work.

       

      By the way my question is where that query is located or how I could find it ?

       

      Best regards to all

        • 1. Re: SQL Error

          Hi Andrey,

           

          I was able to located a .dll file with some of the similar code you are seeing above. I don't know if it is using the exact Query as seen.

           

          Here is the file and output I got when searching for the query in BCM files.

           

          • 2. Re: SQL Error
            ron sorrell

            If you run this against an SQL DB, do you still get a character mismatch error?

             

            SELECT a.ApplicationName, a.ApplicationVersion, COUNT (a.ApplicationName) FROM Applications a INNER JOIN SoftwareInventory s ON ((s.Version IS NULL) AND (a.ApplicationVersion = '') AND (a.ApplicationName = Name)) GROUP BY a.ApplicationName, a.ApplicationVersion UNION SELECT a.ApplicationName, a.ApplicationVersion, COUNT (a.ApplicationName) FROM Applications a INNER JOIN SoftwareInventory s ON ((s.Version IS NOT NULL) AND (a.ApplicationVersion = s.Version) AND (a.ApplicationName = CONCAT (CONCAT (Name, ' '), Version))) GROUP BY a.ApplicationName, a.ApplicationVersion

            • 3. Re: SQL Error
              Gabriel Andrey

              Yes

               

              ORA-12704: non-concordance de jeux de caractères

              12704. 00000 -  "character set mismatch"

              *Cause:    One of the following

                         - The string operands(other than an nlsparams argument) to an

                         operator or built-in function do not have the same character

                         set.

                         - An nlsparams operand is not in the database character set.

                         - String data with character set other than the database character

                         set is passed to a built-in function not expecting it.

                         - The second argument to CHR() or CSCONVERT() is not CHAR_CS or

                         NCHAR_CS.

                         - A string expression in the VALUES clause of an INSERT statement,

                         or the SET clause of an UPDATE statement, does not have the

                         same character set as the column into which the value would

                         be inserted.

                         - A value provided in a DEFAULT clause when creating a table does

                         not have the same character set as declared for the column.

                         - An argument to a PL/SQL function does not conform to the

                         character set requirements of the corresponding parameter.

              *Action:

               

              But that one work, one or the other bold line:

               

              SELECT a.ApplicationName, a.ApplicationVersion, COUNT (a.ApplicationName)

              FROM Applications a

              INNER JOIN SoftwareInventory s ON ((s.Version IS NULL)

                AND(a.ApplicationVersion = N'')

              --  AND(a.ApplicationVersion IS NULL ) 

                AND (a.ApplicationName = Name))

                GROUP BY a.ApplicationName, a.ApplicationVersion UNION SELECT a.ApplicationName, a.ApplicationVersion, COUNT (a.ApplicationName)

              FROM Applications a

              INNER JOIN SoftwareInventory s ON ((s.Version IS NOT NULL)

                AND (a.ApplicationVersion = s.Version)

                AND (a.ApplicationName = CONCAT (CONCAT (Name, ' '), Version)))

                GROUP BY a.ApplicationName, a.ApplicationVersion

              • 4. Re: SQL Error
                ron sorrell

                I assume your Oracle Database was setup for Unicode?  Which specific code was used?

                 

                 

                QReset - A Simplified AD Password Reset Solution

                Click the QReset icon below for a product overview video.

                 

                <http://goo.gl/o62kS2>

                 

                RON SORRELL

                RjR INNOVATIONS INC. | Connect your Business … all of IT

                A BMC Premier Partner and BMC Certified Support Provider for SDE/Remedy/Footprints

                Office: (613) 233 -1915 x 557 | Fax: (514) 372 -1919 | Email: rsorrell@rjrinnovations.com<mailto:rsorrell@rjrinnovations.com>

                • 5. Re: SQL Error
                  Gabriel Andrey

                  UTF8

                  • 6. Re: SQL Error
                    Julien Devienne

                    Hello,

                     

                    We are working on this on a case your partner opened at our support for you. As far as I can see it is triggered byt this action: "Updating Installed Application Count".

                    This probably implies a bug in the code but I'm waiting for confirmation (I doubt that there can be a workaround that would imply changing the type of the column here).

                     

                    Regards

                    • 7. Re: SQL Error
                      Ted Namias

                      Hi,

                       

                      This sql error, due to the missing N has been fixed in 12.6.00.003 build 180214o.

                       

                      What is surprising is that Oracle does not return always an error for such comparisons without N on NVARCHAR2 columns.

                       

                      Anyway as we are using NVARCHAR2 on Oracle, the recommandation is that each comparison with a string should be done with N.