Automating a manual step that requires executing a Stored Procedure

Legacy Uncategorised
1

I have a script (stored procedure) that I want to run after all my steps have been successfully run. In other words, this will be the last step.

This script/proc will be executed on a different DB server other than what I have already used. Please tell me how to go about this and secondly, does this SQL server need to have a Tentacle agent installed?

2

Hi,

Thanks for getting in touch. You can definitely run a SQL script/stored proc as the final step of your deployment process. The easiest way to do this is to use our SQL - Execute Script community step template. This template only needs a connection string and a SQL script. For more information, see our step templates page. Lastly, you don’t need to have a Tentacle installed on your SQL server assuming the SQL server allows remote connections.

Hope this helps!

Thanks

Rob

3

Hi Rob,

Thank you for your reply. I will try the SQL-Execute Script template and will let you know if it worked or if I have any questions.

Regards,
Deepshikha

4

Hi Rob,

While adding the SQL- Execute Script step, what should be the value of the following options?

  • Run On : ‘Deployment Target’ OR ‘Octopus Server’
  • Runs on targets in Roles :

The DB Server that I want to execute the script on is not added as part of any environment. Should I add that server as a new Deployment Target? If I add a new deployment target, what should be the Target Type since the server has no Tentacle installed?

Thanks,
Deepshikha

5

Hi Deepshikha,

If your Octopus server can access the database, then you do not need to install a Tentacle on the database server. In that case you can set the Run on to Octopus Server and leave the Runs on targets in Roles blank. If not, then you would have to install a Tentacle on your database server and add it to your environment as a new listening Tentacle (or Polling if you configured it that way) and give it a role (e.g. db-server) and then set the Run on to Deployment Target and Runs on targets in Roles to db-server.

I hope that helps!

Thank you,
Henrik

6

Hi Henrik,

Thank you for your reply.

Regards,
Deepshikha

7

Hi Henrik,

When you say ‘Octopus Server’, are you referring to the app/db server on which Tentacle is installed?
I have 1 app server and 1 DB server on which Tentacle is installed and I am deploying my build to these 2 servers. The process steps that I already have for this auto deployment will also include a step that will execute a proc.(SQL-Execute Script) on another DB server. This DB server has no Tentacle installed.

Thank you,
Deepshikha

8

Hi Deepshikha,

When I say Octopus Server I mean the server where you installed the Octopus Server software, not servers that you have installed a Tentacle on or you are deploying to.

Thanks,
Henrik

9

Hi Henrik,

Thank you for the reply.

Regards,
Deepshikha

10

Hi Henrik,

I tried the SQL - Execute Script community step template. Unfortunately, I got the following error. I have attached a screenshot of the same.

You cannot call a method on a null-valued expression.

I have also attached a screenshot of the step template. If you can please help me with the same.

Thank you,
Deepshikha

Octo.png
Octo.png1056×388 63.1 KB

Step_Template.png
Step_Template.png1515×1858 195 KB

11

Hi Deepshikha,

Can you try to check and then uncheck the Continue On Error checkbox, it looks like it’s value hasn’t been set properly.

Thanks,
Henrik