Azure Bicep部署外部SQL脚本报错，求嵌入部署解决方案

错误详情 #

New-AzResourceGroupDeployment : 22:53:14 - The deployment 'testSqlDeploymentScript' failed with error(s). Showing 1 out of 1 error(s).
Status Message: The provided script failed with the following error:
System.Management.Automation.ParseException: At line:2 char:17

• IF OBJECT_ID(N'[testTable]') IS NULL

• ~
  

Array index expression is missing or not valid.
at System.Management.Automation.ScriptBlock.Create(Parser parser, String fileName, String fileContents)
at System.Management.Automation.ScriptBlock.Create(ExecutionContext context, String script)
at System.Management.Automation.CommandInvocationIntrinsics.NewScriptBlock(String scriptText)
at Microsoft.PowerShell.Commands.InvokeExpressionCommand.ProcessRecord()
at System.Management.Automation.Cmdlet.DoProcessRecord()
at System.Management.Automation.CommandProcessor.ProcessRecord()
at , /mnt/azscripts/azscriptinput/DeploymentScript.ps1: line 310.

原代码 #

Bicep文件 #

param location string = resourceGroup().location 
@secure() 
param sharedAccessToken string = '' 
param addSqlFile string = loadTextContent('../sqlScript.sql')

resource deploySqlScript 'Microsoft.Resources/deploymentScripts@2023-08-01' = {
  name: 'deploy-sql-script'
  location: location
  kind: 'AzurePowerShell'
  properties: {
    azPowerShellVersion: '9.7'
    timeout: 'PT5M'
    retentionInterval: 'PT1H'
    supportingScriptUris: []
    arguments: '-accessToken ${sharedAccessToken}  -sqlScriptFile ${addSqlFile}'
    scriptContent: '''
        Param (
          [Parameter(Mandatory = $true)] $accessToken,
          [Parameter(Mandatory = $true)] $sqlScriptFile
        )
        
        Install-Module sqlserver -AllowClobber -Force -Scope CurrentUser
        Invoke-SqlCmd -ServerInstance $serverInstance -Database $databaseName -AccessToken "$accessToken" -InputFile $sqlScriptFile

    '''
  }
}

SQL脚本（sqlScript.sql） #

'
IF OBJECT_ID(N'[testTable]') IS NULL
BEGIN
    CREATE TABLE '[testTable]' (
        [testId] nvarchar(150) NOT NULL,
        [testVersion] nvarchar(32) NOT NULL
    );
END;
GO
'

问题分析 #

1. SQL脚本格式错误：脚本首尾多余的单引号会干扰PowerShell解析，且SQL语法中表名不需要用单引号包裹（'[testTable]'应改为[testTable]）。
2. 字符串转义冲突：直接将SQL脚本内容作为参数传递给PowerShell时，SQL中的N'[testTable]'会被PowerShell误解析为数组索引表达式（'被当作字符串边界，[testTable]被识别为数组），触发解析错误。
3. 未定义变量：PowerShell脚本中使用了$serverInstance和$databaseName但未在Bicep中定义对应参数，执行时会报错。

修复方案 #

1. 修正SQL脚本 #

去掉首尾单引号，修正表名格式：

IF OBJECT_ID(N'[testTable]') IS NULL
BEGIN
    CREATE TABLE [testTable] (
        [testId] nvarchar(150) NOT NULL,
        [testVersion] nvarchar(32) NOT NULL
    );
END;
GO

2. 修改Bicep部署脚本 #

添加必要参数，将SQL内容写入临时文件后再执行，避免转义问题：

param location string = resourceGroup().location 
@secure() 
param sharedAccessToken string = '' 
param serverInstance string // 添加SQL服务器实例参数
param databaseName string // 添加数据库名称参数
param sqlScriptContent string = loadTextContent('../sqlScript.sql')

resource deploySqlScript 'Microsoft.Resources/deploymentScripts@2023-08-01' = {
  name: 'deploy-sql-script'
  location: location
  kind: 'AzurePowerShell'
  properties: {
    azPowerShellVersion: '9.7'
    timeout: 'PT5M'
    retentionInterval: 'PT1H'
    supportingScriptUris: []
    arguments: '-accessToken ${sharedAccessToken} -serverInstance ${serverInstance} -databaseName ${databaseName} -sqlScriptContent ${sqlScriptContent}'
    scriptContent: '''
        Param (
          [Parameter(Mandatory = $true)] $accessToken,
          [Parameter(Mandatory = $true)] $serverInstance,
          [Parameter(Mandatory = $true)] $databaseName,
          [Parameter(Mandatory = $true)] $sqlScriptContent
        )
        
        # 安装SQL Server模块（如果需要）
        if (-not (Get-Module -ListAvailable -Name SqlServer)) {
            Install-Module SqlServer -AllowClobber -Force -Scope CurrentUser -SkipPublisherCheck
        }

        # 将SQL内容写入临时文件
        $tempSqlFile = Join-Path $env:TEMP "temp_script.sql"
        $sqlScriptContent | Out-File -FilePath $tempSqlFile -Encoding utf8

        # 执行SQL脚本
        Invoke-SqlCmd -ServerInstance $serverInstance -Database $databaseName -AccessToken $accessToken -InputFile $tempSqlFile

        # 清理临时文件
        Remove-Item $tempSqlFile -Force
    '''
  }
}

关键修改说明 #

• 添加serverInstance和databaseName参数，确保PowerShell脚本能正确定位目标数据库。
• 将SQL内容写入临时文件再执行，避免直接传递字符串导致的转义冲突。
• 优化模块安装逻辑，添加存在性检查并跳过发布者验证，提升部署稳定性。

内容的提问来源于stack exchange，提问作者albbla91