4 Replies Latest reply on Nov 6, 2019 8:15 AM by Aryan Anantwar

    Error to execute procedure using MSSQLAdapter (20.19.01.00)

    Natali Nogueira
      Share This:

      Hi,

       

      I am trying to execute a procedure with MSSQLAdapter. The status is success, but procedure result is and new register is not included.

      <query-executed>{call stp_Cadastro(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }</query-executed>

       

      REQUEST:

      <request-data>

        <sqlAdapterRequest>

          <targets>

            <target name="">

              <user-name>user</user-name>

              <password>****</password>

              <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>

              <url>jdbc:sqlserver://server-db;databaseName=Database;integratedSecurity=true;</url>

            </target>

          </targets>

          <procedure>

            <name>stp_CadastroIBD</name>

            <argument-sets>

              <argument-set>

                <argument>

                  <index>1</index>

                  <in-out>IN</in-out>

                  <data-type>VARCHAR</data-type>

                  <value>nanogueira</value>

                </argument>

                <argument>

                  <index>2</index>

                  <in-out>IN</in-out>

                  <data-type>VARCHAR</data-type>

                  <value>SCHEMA OU BD5</value>

                </argument>

                <argument>

                  <index>3</index>

                  <in-out>IN</in-out>

                  <data-type>VARCHAR</data-type>

                  <value>Oracle 12</value>

                </argument>

                <argument>

                  <index>4</index>

                  <in-out>IN</in-out>

                  <data-type>VARCHAR</data-type>

                  <value>TESTE</value>

                </argument>

                <argument>

                  <index>5</index>

                  <in-out>IN</in-out>

                  <data-type>VARCHAR</data-type>

                  <value>Corporativo</value>

                </argument>

                <argument>

                  <index>6</index>

                  <in-out>IN</in-out>

                  <data-type>VARCHAR</data-type>

                  <value>SERVER_BAO5</value>

                </argument>

                <argument>

                  <index>7</index>

                  <in-out>IN</in-out>

                  <data-type>VARCHAR</data-type>

                  <value>TST5</value>

                </argument>

                <argument>

                  <index>8</index>

                  <in-out>IN</in-out>

                  <data-type>NUMERIC</data-type>

                  <value>1</value>

                </argument>

                <argument>

                  <index>9</index>

                  <in-out>IN</in-out>

                  <data-type>VARCHAR</data-type>

                  <value>SCHEMA OU BD5</value>

                </argument>

                <argument>

                  <index>10</index>

                  <in-out>IN</in-out>

                  <data-type>NUMERIC</data-type>

                  <value>0</value>

                </argument>

                <argument>

                  <index>11</index>

                  <in-out>IN</in-out>

                  <data-type>VARCHAR</data-type>

                  <value>SERVER_BAO5</value>

                </argument>

                <argument>

                  <index>12</index>

                  <in-out>IN</in-out>

                  <data-type>NUMERIC</data-type>

                  <value>1</value>

                </argument>

              </argument-set>

            </argument-sets>

          </procedure>

        </sqlAdapterRequest>

      </request-data>

       

      RESPONSE:

      <sql-adapter-response>

        <metadata>

         <status>success</status>

        </metadata>

        <targets-output>

          <target-output name="sqlserver://server-db;databaseName=Database;integratedSecurity=true;">

            <target-metadata>

              <database-vendor>Microsoft SQL Server</database-vendor>

              <status>success</status>

            </target-metadata>

            <procedure-response name="stp_Cadastro">

              <response-sets>

                <response-set index="1">

                  <result-sets />

                  <response-set-metadata>

                    <update-count>0</update-count>

                    <out-param-count>0</out-param-count>

                    <status>success</status>

                  </response-set-metadata>

                </response-set>

              </response-sets>

              <procedure-metadata>

                <response-set-count>1</response-set-count>

               <query-executed>{call stp_Cadastro(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }</query-executed>

                <status>success</status>

                <execution-milliseconds>97</execution-milliseconds>

              </procedure-metadata>

            </procedure-response>

          </target-output>

        </targets-output>

      </sql-adapter-response>

       

       

      Thank you.

        • 1. Re: Error to execute procedure using MSSQLAdapter (20.19.01.00)
          Greg Michael

          Log files for the output would be helpful on both the MSSqlAdapter side as well as the CDP/Dev studio.  That said, what is the expected outcome or output?  Can you execute the same procedure outside of TSO?  What version of TSO and what Content version are you using?  What version is the database? What OS and version is the server running?  The key here is information,  If you think you've provided enough, we'll always ask for more!

          • 2. Re: Error to execute procedure using MSSQLAdapter (20.19.01.00)
            Aryan Anantwar

            Hi,

             

            If the stored procedure returns any output parameters then you have to add more <argument> for OUT type which will receive the results and you will be able to see that in adapter response.

             

            Regards,

            Aryan Anantwar

            1 of 1 people found this helpful
            • 3. Re: Error to execute procedure using MSSQLAdapter (20.19.01.00)
              Natali Nogueira

              Thank you for helping me.

              I am testing using query first and works.

              So, is there any doc explain how parameters must be passing?

              Thank you.

               

              <sql-adapter-response>

                <metadata>

                  <status>success</status>

                </metadata>

                <targets-output>

                  <target-output name="database-server;databaseName=DATABASE;integratedSecurity=true;">

                    <target-metadata>

                      <database-vendor>Microsoft SQL Server</database-vendor>

                      <status>success</status>

                    </target-metadata>

                    <statement-responses>

                      <statement-response>

                        <statement-metadata>

                          <response-set-count>0</response-set-count>

                          <status>success</status>

                        </statement-metadata>

                        <response-sets>

                          <response-set index="1">

                            <response-set-metadata>

                              <status>success</status>

                            </response-set-metadata>

                            <result-sets>

                              <result-set>

                                <row>

                                  <column name="COD_SGBD" label="COD_SGBD">1</column>

                                  <column name="NOME_SGBD" label="NOME_SGBD">SQL Server - 2000</column>

                                  <column name="NOME_TIPO_SGBD" label="NOME_TIPO_SGBD">SQL SERVER</column>

                                  <column name="IND_REG_ATIV" label="IND_REG_ATIV">1</column>

                                  <column name="DATA_MANT_REG" label="DATA_MANT_REG"></column>

                                  <column name="NOME_LOGN_USUA_MANT_REG" label="NOME_LOGN_USUA_MANT_REG">NULL</column>

                                </row>

                                <row>

                                  <column name="COD_SGBD" label="COD_SGBD">2</column>

                                  <column name="NOME_SGBD" label="NOME_SGBD">SQL Server - 2005</column>

                                  <column name="NOME_TIPO_SGBD" label="NOME_TIPO_SGBD">SQL SERVER</column>

                                  <column name="IND_REG_ATIV" label="IND_REG_ATIV">1</column>

                                  <column name="DATA_MANT_REG" label="DATA_MANT_REG"></column>

                                  <column name="NOME_LOGN_USUA_MANT_REG" label="NOME_LOGN_USUA_MANT_REG">NULL</column>

                                </row>

                              ...

                                <metadata>

                                  <query-executed>select * from [dbo].[TIBDSGBD];</query-executed>

                                  <execution-milliseconds>125</execution-milliseconds>

                                  <row-count>17</row-count>

                                  <column-count>6</column-count>

                                  <status>success</status>

                                </metadata>

                              </result-set>

                            </result-sets>

                          </response-set>

                        </response-sets>

                      </statement-response>

                    </statement-responses>

                  </target-output>

                </targets-output>

              </sql-adapter-response>

              • 4. Re: Error to execute procedure using MSSQLAdapter (20.19.01.00)
                Aryan Anantwar

                HI Natali,

                 

                Glad it worked.

                refer - SQL adapter parameterized statement request and response - Documentation for BMC Atrium Orchestrator Content 20.16.03 - …

                check XML sample of the SQL actor adapter request with a stored procedure and a missing index entry on the above document.

                 

                Regards,

                Aryan Anantwar

                1 of 1 people found this helpful