Release Automation

Expand all | Collapse all

"Run SQL File on Microsoft(c) SQL server" action's behavior.

Jump to Best Answer
  • 1.  "Run SQL File on Microsoft(c) SQL server" action's behavior.

    Posted 09-16-2016 07:54 AM

    While trying to execute a SQL script using "Run SQL File on Microsoft(c) SQL server" it got failed on a step. The script contains two parts, first it was creating a new table(say "Table_new") and second it was altering the contents of another table(say "Table2"). My action failed stating that another table(Table2) name doesn't exists, and when I checked the Database the new table("Table_new) in first step was also not created. Does CA RA tool debug the complete script before execution or is it something else?



  • 2.  Re: "Run SQL File on Microsoft(c) SQL server" action's behavior.

    Broadcom Employee
    Posted 09-16-2016 09:44 AM

    Hi,

     

    I think what you're seeing is likely the most recent error in a sequence of several errors. Please have a look at this discussion. It may help you:

    a. figure out what is wrong; and

    b. if you feel an there is a feature/IDEA that should be requested.

     

    Discussion:

    Detailed SQL Script execution Logs 

     

    Kind regards,

    Gregg



  • 3.  Re: "Run SQL File on Microsoft(c) SQL server" action's behavior.

    Posted 09-16-2016 09:55 AM

    Hi Gregg,

     

    I have already checked that discussion, I'm not searching for error logs but what I wanted to know is how does the "Run SQL script" action works as it throws an error for second step mentioned in the script without even executing the first step.

     

    I hope I'm clear in asking my question.

     

    Thanks and Regards,

    Vipul Gupta



  • 4.  Re: "Run SQL File on Microsoft(c) SQL server" action's behavior.

    Broadcom Employee
    Posted 09-16-2016 10:45 AM

    I *think* it did execute the first step (unsuccessfully), moved on to the second step, and that is the most recent error which is what the agent/action is likely capturing and returning to you. Can you confirm?

     

    Thanks,

    Gregg



  • 5.  Re: "Run SQL File on Microsoft(c) SQL server" action's behavior.

    Posted 09-16-2016 10:57 AM

    I am not sure if what you said happened during the execution of action. I checked in nolio_all.log file for the steps that executed during action execution.

    Please find the screen shot of error logs attached here with.

     

    nolio_all.log for DB scripts execution using action.

     

    Thanks,

    Vipul



  • 6.  Re: "Run SQL File on Microsoft(c) SQL server" action's behavior.

    Broadcom Employee
    Posted 09-16-2016 12:30 PM

    Hi Vipul,

     

    Based on that screenshot it would appear that the ncsdata database had a Procedure (not table) created named spaENSCustSummary_Test_CA_RA1 created. And then it tries to alter a different procedure which generated a jdbc.SQLServerException. You are sure that it did not create the spaENSCustSummary_Test_CA_RA1 procedure? You'd have to go to the database, expand Programmability and expand Stored Procedures (as seen in the screenshot I attached).

     

    Thanks,

    Gregg

     



  • 7.  Re: "Run SQL File on Microsoft(c) SQL server" action's behavior.

    Posted 09-16-2016 02:09 PM

    Sorry for the confusion, they were stored procedures.

    We checked for it after execution of the script and the stored procedure was not created.

    I am unable to attach the files here else I would have shared the logs with you.

     

    Regards,

    Vipul



  • 8.  Re: "Run SQL File on Microsoft(c) SQL server" action's behavior.
    Best Answer

    Broadcom Employee
    Posted 09-16-2016 02:34 PM

    If the stored procedure isn't available then I wonder what happens when running the .sql file outside of RA. If it works outside of RA, and assuming you want assistance finding root cause, then you probably want to open a support issue and supply the agents\logs directory zipped when it has this problem as well as a copy of the .sql file (so that we can try to reproduce the problem and figure out root cause).  

     

    Regards,
    Gregg



  • 9.  Re: "Run SQL File on Microsoft(c) SQL server" action's behavior.

    Broadcom Employee
    Posted 09-26-2016 10:53 AM

    Hi,

     

    Where you able to verify if the SQL script runs correctly outside the RA agent.

    The agent basicly does the same as any other SQL tool. it connects to the SQL database server

    and runs the script.

    If you can run the sql script using a sql script utility please open a support case so we can investigate the log

     

    Regards

    Dirk



  • 10.  Re: "Run SQL File on Microsoft(c) SQL server" action's behavior.

    Broadcom Employee
    Posted 09-29-2016 02:52 AM

    Hi Vipul

    Did the answer from Gregg answered your question? If it did please mark it as the right answer. When your question is not answered or you still have additional questions please let us know.

    With Kind Regards

    Dirk



  • 11.  Re: "Run SQL File on Microsoft(c) SQL server" action's behavior.

    Broadcom Employee
    Posted 10-04-2016 03:06 AM

    Hi Viphal

    I believe your questions has been answered, I will mark this as the correct answer.

    When your question is not answered or you still have additional questions please let us know.

    With Kind Regards

    Dirk