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?
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.
Detailed SQL Script execution Logs
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,
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?
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.
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).
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.
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).
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
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
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.