question

Winniezhang-3472 avatar image
0 Votes"
Winniezhang-3472 asked LimitlessTechnology-2700 answered

Powershell script create a dac tore Microsoft.SqlServer.Management.Dac.DacStore.

I want to deploy dacpac to Azure database using powershell scripts.

Firstly, I tried the sample scripts in the page below:
https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/deploy-a-data-tier-application?view=sql-server-ver15
However, the error shows:
New-Object : Cannot find type [Microsoft.SqlServer.Management.Common.ServerConnection]: verify that the assembly containing this type is loaded.

After that, i got an assemblies list below:
$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps140"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
$item = Get-ItemProperty $sqlpsreg
$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}

$assemblylist =
"Microsoft.SqlServer.Management.Common",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.Instapi ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.SqlTDiagM ",
"Microsoft.SqlServer.SString ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum",
"Microsoft.SqlServer.Management.Dac",
"Microsoft.SqlServer.Management.Dac.DacStore",
"Microsoft.SqlServer.Management.DacEnum",
"Microsoft.SqlServer.Management.Utility"

foreach ($asm in $assemblylist)
{
$asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}


However, it still shows the error:
New-Object : Cannot find type [Microsoft.SqlServer.Management.Dac.DacStore]: verify that the assembly containing this type is loaded.


windows-server-powershellazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered Winniezhang-3472 commented

Have you installed all required components/assemblies? By the error message not.
See
https://docs.microsoft.com/en-us/sql/tools/download-sql-feature-packs?view=sql-server-ver15

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I checked that the assemblies are there.
191729-image.png



The error now changed to below:
Exception calling "Connect" with "0" argument(s): "Failed to connect to server XXXX"
At C:\Winnie\Smart_Current\RebuildFilterData\PowershellTest\Testimport.ps1:4 char:1
+ $serverConnection.Connect()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : ConnectionFailureException

The server is a azure SQL database. I could connect to it using SSMS. However, when I try to load the dacpac to deploy the dacpac to the server.
It threw error on the line:
$dacStore = New-Object Microsoft.SqlServer.Management.Dac.DacStore($serverConnection)

Question is whether we could use dacpac to deploy a database structure to Azure SQL database.


0 Votes 0 ·
image.png (212.2 KiB)
LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered

Hello Winniezhang

The DAC library was deprecated starting with SQL 2012. You should be able to use the SMO libraries or other .NET libraries to create the connection string at least. However you may need to refactor your script a little for some of your commands

Reference: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/cc879339(v=sql.110)?redirectedfrom=MSDN#data-tier-applications



--If the reply is helpful, please Upvote and Accept as answer--

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.