9 Replies Latest reply on Feb 1, 2018 11:49 PM by PAWAN KAUSHIK

    ARERR 552 SQL Operation failed invalid column name.

    PAWAN KAUSHIK

      Hi All,

       

      I am on Remedy 9.1.03 with ITSM 9.1.03 on Windows environment. I am facing an error related to SQL while i am trying to updating or creating any incident. error screen shot is below. I have basic knowledge of SQL hence request you to please help me to resolve the same.

       

      Thanks in advance!

      SQL error.JPG

        • 1. Re: ARERR 552 SQL Operation failed invalid column name.
          Mark Walters

          For some reason the server is issuing some SQL that includes INC121 as a column name but it does not exist.  It's a bit odd as that looks like a field value rather than a field name which I would expect if there was damage to the table structure.  Enable server SQL/API/filter logging and reproduce the error so that you can see exactly what the SQL is and the workflow may help explain what's happening.

          • 2. Re: ARERR 552 SQL Operation failed invalid column name.
            PAWAN KAUSHIK

            Hi Mark,

             

            I checked the same and found error in SQL and API logs as below:

             

            <SQL > <TID: 0000000297> <RPC ID: 0000013908> <Queue: Fast  > <Client-RPC: 390620   > <USER: pawan                                    > <Overlay-Group: 1     > /* Tue Nov 21 2017 15:32:24.2900 */

            org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'INC121'.

                at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231) ~[na:na]

                at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[na:na]

                at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660) ~[bundlefile:na]

             

            at java.lang.Thread.run(Unknown Source) [na:1.8.0_144]

                at com.bmc.arsys.serverrpc.nio.ARThreadFactory$ARWorkerThread.run(ARThreadFactory.java:74) [bundlefile:9.1.03-SNAPSHOT]

            Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'INC121'.

                ... 139 common frames omitted

            • 3. Re: ARERR 552 SQL Operation failed invalid column name.
              Mark Walters

              That's the error, it's the actual SQL referencing INC121 that we need to see - it will be a few lines earlier in the log.

              • 4. Re: ARERR 552 SQL Operation failed invalid column name.
                Ravindrakumar Rodge

                You can try to search in the logs using the keyword as 'INC121'. As @Mark Walters stated the SQL query would be just above the error.

                 

                Thanks.

                 

                Ravi

                • 5. Re: ARERR 552 SQL Operation failed invalid column name.
                  Ravindrakumar Rodge

                  Most of the time these type of errors are resolved by finding out orphan columns in the database. Removing overlay corruption is the useful KB for this.

                  KnowledgeArticle - BMC

                   

                  If you find the field that is reference, try to rename or remove it and then check again. If you fail to find the field in the logs then you can do some fake change in base development mode so that it will update the structure in cache and then try again.

                   

                  Regards,

                  Ravi

                  • 6. Re: ARERR 552 SQL Operation failed invalid column name.
                    PAWAN KAUSHIK

                    Hi,

                     

                    I am unable to make a search with the query provided in this KB artical. here is the logs lines:

                    --> Passed -- perform actions
                    <FLTR> <TID: 0000000297> <RPC ID: 0000013908> <Queue: Fast  > <Client-RPC: 390620   > <USER: pawan                                    > <Overlay-Group: 1     >     0 : Push Fields -> "ABYD:ManagedRequest"
                    <FLTR> <TID: 0000000297> <RPC ID: 0000013908> <Queue: Fast  > <Client-RPC: 390620   > <USER: pawan                                    > <Overlay-Group: 1     >           <deferred to phase 2>
                    <FLTR> <TID: 0000000297> <RPC ID: 0000013908> <Queue: Fast  > <Client-RPC: 390620   > <USER: pawan                                    > <Overlay-Group: 1     > /* Tue Nov 21 2017 15:32:24.2710 */ <Filter Level:0 Number Of Filters:200> Checking "P:COL:CreateWOrklogEntryOnAssignedGroupChangeOnIncident" (500)
                    <FLTR> <TID: 0000000297> <RPC ID: 0000013908> <Queue: Fast  > <Client-RPC: 390620   > <USER: pawan                                    > <Overlay-Group: 1     >--> Passed -- perform actions
                    <FLTR> <TID: 0000000297> <RPC ID: 0000013908> <Queue: Fast  > <Client-RPC: 390620   > <USER: pawan                                    > <Overlay-Group: 1     >     0 : Set Fields
                    <SQL > <TID: 0000000297> <RPC ID: 0000013908> <Queue: Fast  > <Client-RPC: 390620   > <USER: pawan                                    > <Overlay-Group: 1     > /* Tue Nov 21 2017 15:32:24.2710 */ BEGIN TRANSACTION
                    <SQL > <TID: 0000000297> <RPC ID: 0000013908> <Queue: Fast  > <Client-RPC: 390620   > <USER: pawan                                    > <Overlay-Group: 1     > /* Tue Nov 21 2017 15:32:24.2710 */ SELECT assigned_group FROM HPD_Help_Desk WHERE Incident_Number = INC121
                    <SQL > <TID: 0000000297> <RPC ID: 0000013908> <Queue: Fast  > <Client-RPC: 390620   > <USER: pawan                                    > <Overlay-Group: 1     > /* Tue Nov 21 2017 15:32:24.2900 */

                    org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'INC121'.

                    • 7. Re: ARERR 552 SQL Operation failed invalid column name.
                      PAWAN KAUSHIK

                      the SQL query which is reflecting in logs is :

                      SELECT assigned_group FROM HPD_Help_Desk WHERE Incident_Number = INC121

                      in this SQL query single quotes are missed. i tried by search with SQL server management studio tool. if i am searching with the below SQL query then it is working fine:

                       

                      SELECT assigned_group FROM HPD_Help_Desk WHERE Incident_Number = 'INC121'

                       

                      Kindly provide your valuable suggestions to resolve this problem.

                      • 8. Re: ARERR 552 SQL Operation failed invalid column name.
                        Ravindrakumar Rodge

                        It looks like the Set Field is running on a view in the database.

                         

                        Can you try to do some fake changes on the HPD:HelpDesk Form and save it. This will recreate the view with correct fields details.

                         

                        OR

                         

                        Open the P:COL:CreateWOrklogEntryOnAssignedGroupChangeOnIncident" filter and correct the qualification by adding quotes.

                         

                        Thanks.

                         

                        Regards,

                        Ravi

                        1 of 1 people found this helpful
                        • 9. Re: ARERR 552 SQL Operation failed invalid column name.
                          PAWAN KAUSHIK

                          Hey Raghu,

                           

                          That's correct ! i reviewed the same filter and checked that that filed is not there which i had mapped in set field action through SQL. now it is working fine after disabling that filter