MS SQL Connection string syntax in variable set

I have added a SQL connection string to the variable ‘ConnectionString’ into a variable set

Original SQL connection string:
<add name="xxxContext" connectionString="Data Source=DBserv;Initial Catalog=dev;Integrated Security=False;MultipleActiveResultSets=True; User Id=user_login; Password=password;" providerName="System.Data.SqlClient" />

Connection String i made for Octopus variable set:
"DataSource=#{DatabaseServer}; Initial Catalog=#{Database}; Integrated Security=False; MultipleActiveResultSets=True; User ID=user_login; Password=#{login_password};" providerName="System.Data.SqlClient"

However the above string resulted in several errors:
*** Unrecognized command line argument 'Initial'.

*** Unrecognized command line argument 'Catalog=dev;'.

Do i need to enclose each argument in double quotes ?
Would you have an example connection string that i could edit for my requirements?

Hi Alan,

Thanks for reaching out! Could you please follow the below instructions and send me a full deployment log? That should help me see what’s going on here.

1) Add these 2 variables to your project http://docs.octopusdeploy.com/display/OD/Debug+problems+with+Octopus+variables

2) Create a new release (so the new variables take effect) and deploy it. If possible skip as many steps as you can and only leave step we are troubleshooting in order to avoid the noise in the log.

3) Send us the raw log of that deployment http://docs.octopusdeploy.com/display/OD/Get+the+raw+output+from+a+task

Thanks,
Dalmiro

Thanks Dalmiro, I have emailed the log.

Regards

Alan

Hi Alan,

The value of the name property of your connection string in your config file has to match the name of the Octopus variable.

In your case your Connection string name in your config file is "xxxContext", but in Octopus the variable that holds the connection string is called ConnectionString.

The following example (which I just tested) should work:

In the config file

 <connectionStrings>
    <add name="DBConnectionString" connectionString="" />
  </connectionStrings>

In Octopus
see attached screenshot which has a variabled called “DBConnectionString” which matches the name of my connection string above

Hope that helps!
Dalmiro

attaching image

Thank you very much Dalmiro, Could i please have the full value of the DBConnectionString.

Alan

Hi Alan,

I used the same test value you provided:

"DataSource=#{DatabaseServer}; Initial Catalog=#{Database}; Integrated Security=False; MultipleActiveResultSets=True; User ID=user_login; Password=#{login_password};" providerName="System.Data.SqlClient"

Thanks Dalmiro, I had changed quite a lot in the redacted version so it was difficult to tie up the values.

Could I use xxx_www_login_password as a variable name?

I get the error below, Could this be because the DatabaseServer variable value is a 10.x.x.x IPaddress and it doesn’t like the format, perhaps I should supply a hostname?
*** Error parsing connection string: Keyword not supported: ‘datasource’…

Alan

Apologies Dalmiro, A variable set was still associated with the project.

So is it working now ? :slight_smile:

Lol, Almost it seems to be interpreting the connection in a strange way as below:

Locating SqlPackage Tool now…Deploying database now…deployed
*** Unrecognized command line argument ‘Catalog=dev;’.
*** Unrecognized command line argument ‘Integrated’.
*** Unrecognized command line argument ‘Security=False;’.
*** Unrecognized command line argument ‘MultipleActiveResultSets=True;User’.
*** Unrecognized command line argument ‘ID=user_login;Password=********; providerName=System.Data.SqlClient’.

Perhaps I should put quotes around each parameter?

Alan

First you need to check how the config file looks like after the variable is replaced. Does it look as expected? Once you nail that, then you should test it with your application