将 Azure bacpac 文件导入本地数据库时出错，EXTERNAL 附近的语法不正确 [英] Error Importing Azure bacpac file to local db error incorrect syntax near EXTERNAL

问题描述

从 azure bacpac 文件导入 db 到本地 sql server 2016 时，出现以下错误.

When importing db fro azure bacpac file to local sql server 2016 I'm geting the following error.

Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'EXTERNAL'.
Error SQL72045: Script execution error.  The executed script: CREATE EXTERNAL DATA SOURCE [BoxDataSrc]
    WITH (
    TYPE = RDBMS,
    LOCATION = N'MYAZUREServer.database.windows.net',
    DATABASE_NAME = N'MyAzureDb',
    CREDENTIAL = [SQL_Credential]
    );

(Microsoft.SqlServer.Dac)

(Microsoft.SqlServer.Dac)

推荐答案

我今天遇到了同样的问题.由于WITH(TYPE = RDBMS)"仅适用于 Azure SQL DB，因此在尝试将 bacpac 导入本地 SQL Server 2017 时出现错误.由于这篇文章，我确实找到了解决方案:

I ran into this same issue today. Since "WITH(TYPE = RDBMS)" is only applicable to Azure SQL DB, we get the error when attempting to import the bacpac into SQL Server 2017 on-premise. I did find a solution thanks to this article:

https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/08/16/editing-a-bacpac-file/

这里重写了相关步骤:

1. 复制 bacpac 文件(为了安全以防出错).
2. 将文件扩展名更改为 zip，然后将其解压缩到一个文件夹中.令人惊讶的是，bacpac 实际上只是一个 zip 文件，而不是专有且难以进入的东西.

3. 找到 model.xml 文件并编辑它以删除如下所示的部分:

1. Make a copy of the bacpac file (for safety in case of errors).
2. Change the file extension to zip, then decompress it into a folder. Surprisingly, a bacpac is actually just a zip file, not something proprietary and hard to get into.

3. Find the model.xml file and edit it to remove the section that looks like this:

<Element Type="SqlExternalDataSource" Name="[BoxDataSrc]">
    <Property Name="DataSourceType" Value="1" />
    <Property Name="Location" Value="MYAZUREServer.database.windows.net" />
    <Property Name="DatabaseName" Value="MyAzureDb" />
    <Relationship Name="Credential">
        <Entry>
            <References Name="[SQL_Credential]" />
        </Entry>
    </Relationship>
</Element>

如果您有多个这种类型的外部数据源，您可能需要为每个数据源重复步骤 3.我只有一个.

If you have multiple external data sources of this type, you will pobably need to repeat step 3 for each one. I only had one.

现在您需要重新生成 model.xml 的校验和，以便 bacpac 不会认为它被篡改(因为您只是篡改了它).创建一个名为 computeHash.ps1 的 PowerShell 文件并将此代码放入其中.

Now you need to re-generate the checksum for model.xml so that the bacpac doesn't think it was tampered with (since you just tampered with it). Create a PowerShell file named computeHash.ps1 and put this code into it.

$modelXmlPath = Read-Host "model.xml file path"
$hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")
$fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)
$hash = $hasher.ComputeHash($fileStream)
$hashString = ""
Foreach ($b in $hash) { $hashString += $b.ToString("X2") }
$fileStream.Close()
$hashString

运行 PowerShell 脚本，并为其提供解压缩和编辑的 model.xml 文件的文件路径.它将返回一个校验和值.

Run the PowerShell script and give it the filepath to your unzipped and edited model.xml file. It will return a checksum value.

复制校验和值，然后打开 Origin.xml 并替换现有的校验和，在看起来像这样的行的底部:

Copy the checksum value, then open up Origin.xml and replace the existing checksum, toward the bottom on the line that looks like this:

<Checksum Uri="/model.xml">9EA0F06B282D4F42955C78A98822A31AA0ED0225CB131B8759379055A482D01F</Checksum>

保存并关闭 Origin.xml，然后选择所有文件并将它们放入一个新的 zip 文件中，并将扩展名重命名为 bacpac.

Save and close Origin.xml, then select all the files and put them into a new zip file and rename the extension to bacpac.

现在您可以使用这个新的 bacpac 导入数据库而不会出现错误.它对我有用，对你也有用.

Now you can use this new bacpac to import the database without getting the error. It worked for me, it could work for you, too.

这篇关于将 Azure bacpac 文件导入本地数据库时出错，EXTERNAL 附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！