SQL Script with Multiple Variables using DBUP ,but Setting the Variable in Octopus for the Nuget Package.

[Peter Blignaut]

I would like to run script with multiple variables using DBUP. 

I have create the CMD and the rest , but the problem comes with variable for a script.

 1) How will I declare the variable in my SQL SCRIPT ? 

****************MyTest Script *****************

Declare  @URL nvarchar(50) 

set @URL  =  $(myurl) 

insert into  LINKS  values  ( @URL ) 

** This is just simple example ** 

** This will work using sqlcmd  but not  DBUP. ** 

2) How will I pass the value and name of variable from  program.cs 

***********************************

var upgrader  = DeployChanges.To 

                      . SqlDatabase (DatabaseCon)

                      .WithScriptsEmbeddedInAssemble(Assemble.GetExecutingAssembly)

                       .WithVarible("myurl","Http://www.test.com")

                       .build();

** I have tried this but it fails , with SQL Error in the cmd. ** 

Again , It is for Setting up Base DB. But can be customize as require ...

End Goal is to Pass, Octopus Variables to your sql scripts , that is contained in your Nuget Package.   

If there is better or other way of doing this please let me know 

Thanks in advance 

[Jake Ginnivan]

You use $var$ for a variable in your SQL script. That should work.

Sent from my Windows Phone

---

From: Peter Blignaut
Sent: ‎17/‎04/‎2013 23:05
To: dbup-d...@googlegroups.com
Subject: SQL Script with Multiple Variables using DBUP ,but  Setting the  Variable in Octopus for the Nuget Package.

--
You received this message because you are subscribed to the Google Groups "DbUp Discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbup-discuss...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Altaf Balotrawala]

Hey Jake,

In continuation to Peter's question, I wanted to know how can I pass values to variables from Octopus Deploy instead of passing them in console app?

For eg: 

var upgrader  = DeployChanges.To 

                      . SqlDatabase (DatabaseCon)

                      .WithScriptsEmbeddedInAssemble(Assemble.GetExecutingAssembly)

                       .WithVarible("myurl",OctopusVariable)

                       .build();

We have multiple environments for which we pass values from octopus variables and use tokens/env. variables in SQL script.

On passing the octopus variable name, it was not replaced with its value and threw exception.

[Adam K]

Did you get an answer to this question?

If so, would you post it?

I'm going to need to do the same thing.  I was thinking of adding config settings to my console app that runs DbUp and then replacing those config settings using Octopus variables, but I thought I'd ask if there is an easier way before I start down that path.

[Jake Ginnivan]

​It is possible the variable substitution is broken. I can't see any tests for that functionality from a quick look. 

.WithVariable() should work, then use $variable$ in your script. 

What is the exception which was thrown?

---

From: dbup-d...@googlegroups.com <dbup-d...@googlegroups.com> on behalf of Adam K <adamk...@gmail.com>
Sent: Friday, 19 December 2014 11:02 AM
To: dbup-d...@googlegroups.com
Subject: Re: SQL Script with Multiple Variables using DBUP ,but Setting the Variable in Octopus for the Nuget Package.

 

For more options, visit https://groups.google.com/d/optout.

[Altaf Balotrawala]

I used the same approach as mentioned by you - adding config settings to my console app that runs DbUp and then replacing those config settings using Octopus variables

Regards,
Altaf B.

[Adam K]

Thanks for the quick response.  I'll start down that path as well.