Error deploying SQL database using powershell script

Legacy Uncategorised
1

Error while deploying a dacpac using Octopus.

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
Error
*** Could not generate deployment script.
Error
Unable to connect to target server.

Error
Script ‘C:\Octopus\Applications\Development\Database\7.0.0.0_2\Deploy.ps1’ returned non-zero exit code: 1. Deployment terminated.
Error
Running rollback conventions…
Error
Script ‘C:\Octopus\Applications\Development\Database\7.0.0.0_2\Deploy.ps1’ returned non-zero exit code: 1. Deployment terminated.
Fatal
The remote script failed with exit code 1

SQL server mixed mode authentication is enabled.
Named pipes and TCP/IP is also enabled.

2

Hi Pallavi,

Thanks for getting in touch. I know a lot of our customers use Octopus with DACPACs so we should be able to get you up and running. That said, I’ll need a bit more information from you to understand the problem better.

Looking forward to your reply!

Rob

3

Hi Rob,

Thank you for the community step for the dacpac deployment.
Previously I have been using a manual power shell script and resolved the error and have successfully deployed the dacpac now.:-)…

I tried using the community step suggested by you from the provided link but am facing below issue using the step.

Error ---------------------

Add-Type : Could not load file or assembly
’file:///C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll’
or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

4

Hi Pallavi,

Thanks for the reply. Can you tell me what version of Sql Server you’re using? I did some research and the initial information I found suggested that the DACPAC sqlproj file was targeting a different version of sql server than it was being deployed to. Is this a possibility?

If this doesn’t help, can you please attached the raw log from the failed deployment? This should help me troubleshoot the issue. The following page describes how to do this. http://docs.octopusdeploy.com/display/OD/Get+the+raw+output+from+a+task

Let me know how you go.

Rob

5

Hi Rob

My DACPAC sqlproj file is targeting a same version of sql server i.e 2012.
I have checked this from the properties of my dacpac project and set it to Microsoft SQL server 2012 already.

Following is the information requested .

Version of SQL - Microsoft SQL server 2012
Microsoft SQL Server Management Studio - 11.0.5058.0
Microsoft Analysis Services Client Tools - 11.0.5058.0

I have also attached the raw data file.
PFA.

ServerTasks-2457.log.txt (11 KB)

6

Hi Pavalli,

Thanks for the reply. I dug into this a bit deeper and I believe I’ve found the issue. Your log file states that the your server has PowerShell v1 and v2 on it and .NET v2 on it (CLR version). I did some googling and found that the Sql Server 2012 SDK assemblies require .NET v4 and thus you also need PowerShell v3 or newer as well. I’d recommend upgrading your server so you can take advantage of the community step.

The following Microsoft page covers all PowerShell Requirements and has links to download each version.
https://technet.microsoft.com/en-au/library/hh847769.aspx

That said, I’d recommending looking at the PowerShell script itself as it can help you trace through the process. Especially since you’ve already have some experience writing a similar script.

Hope this helps!

Rob