通过PowerShell创建SQL Server存储过程失败问题求助

问题 #

通过PowerShell脚本远程创建并执行SQL Server存储过程时，对应的T-SQL脚本在SSMS中运行正常，但使用Invoke-Sqlcmd执行时提示无法加载Microsoft.SqlServer.BatchParser程序集或其依赖项。

相关代码与错误信息 #

T-SQL存储过程创建脚本

USE [master]
GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
GO

CREATE PROCEDURE [dbo].[sp_hexadecimal]
    (@binvalue varbinary(256),
     @hexvalue varchar (514) OUTPUT)
AS
BEGIN
    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)

    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
  
    WHILE (@i <= @length)
    BEGIN
        DECLARE @tempint int
        DECLARE @firstint int
        DECLARE @secondint int

        SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue, @i, 1))
        SELECT @firstint = FLOOR(@tempint / 16)
        SELECT @secondint = @tempint - (@firstint * 16)

        SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint + 1, 1) + SUBSTRING(@hexstring, @secondint + 1, 1)
  
        SELECT @i = @i + 1
    END 

    SELECT @hexvalue = @charvalue
END
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin
GO

CREATE PROCEDURE [dbo].[sp_help_revlogin]   
    (@login_name sysname = NULL)
AS
BEGIN
    DECLARE @name SYSNAME
    DECLARE @type VARCHAR (1)
    DECLARE @hasaccess INT
    DECLARE @denylogin INT
    DECLARE @is_disabled INT
    DECLARE @PWD_varbinary VARBINARY (256)
    DECLARE @PWD_string VARCHAR (514)
    DECLARE @SID_varbinary VARBINARY (85)
    DECLARE @SID_string VARCHAR (514)
    DECLARE @tmpstr VARCHAR (1024)
    DECLARE @is_policy_checked VARCHAR (3)
    DECLARE @is_expiration_checked VARCHAR (3)
    DECLARE @Prefix VARCHAR(255)
    DECLARE @defaultdb SYSNAME
    DECLARE @defaultlanguage SYSNAME     
    DECLARE @tmpstrRole VARCHAR (1024)
  
    IF (@login_name IS NULL)
    BEGIN
        DECLARE login_curs CURSOR FOR 
            SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
            FROM  sys.server_principals p 
            LEFT JOIN sys.syslogins l ON (l.name = p.name) 
            WHERE p.type IN ('S', 'G', 'U') 
              AND p.name <> 'sa'
            ORDER BY p.name
    END
    ELSE
        DECLARE login_curs CURSOR FOR 
                SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
                FROM  sys.server_principals p 
                LEFT JOIN sys.syslogins l ON (l.name = p.name) 
                WHERE p.type IN ('S', 'G', 'U') 
                  AND p.name = @login_name
                ORDER BY p.name
  
            OPEN login_curs 
            FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
            IF (@@fetch_status = -1)
            BEGIN
                  PRINT 'No login(s) found.'
                  CLOSE login_curs
                  DEALLOCATE login_curs
                  RETURN -1
            END
  
            SET @tmpstr = '/* sp_help_revlogin script '
            PRINT @tmpstr
  
            SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
  
            PRINT @tmpstr
            PRINT ''
  
            WHILE (@@fetch_status <> -1)
            BEGIN
              IF (@@fetch_status <> -2)
              BEGIN
                    PRINT ''
  
                    SET @tmpstr = '-- Login: ' + @name
  
                    PRINT @tmpstr
  
                    SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''')
                    BEGIN'
                    Print @tmpstr 
  
                    IF (@type IN ( 'G', 'U'))
                    BEGIN -- NT authenticated account/group 
                      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
                    END
                    ELSE 
                    BEGIN -- SQL Server authentication
                            -- obtain password and sid
                            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
  
                            EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                            EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
  
                            -- obtain password policy state
                            SELECT @is_policy_checked     = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
                            FROM sys.sql_logins 
                            WHERE name = @name
  
                            SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
                            FROM sys.sql_logins 
                            WHERE name = @name
  
                            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' 
                                            + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
  
                            IF ( @is_policy_checked IS NOT NULL )
                            BEGIN
                              SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
                            END
  
                            IF ( @is_expiration_checked IS NOT NULL )
                            BEGIN
                              SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
                            END
            END
  
            IF (@denylogin = 1)
            BEGIN -- login is denied access
                SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
            END
            ELSE IF (@hasaccess = 0)
            BEGIN -- login exists but does not have access
                SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
            END
            IF (@is_disabled = 1)
            BEGIN -- login is disabled
                SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
            END 
  
            SET @Prefix = '
            EXEC master.dbo.sp_addsrvrolemember @loginame='''
  
            SET @tmpstrRole=''
  
            SELECT @tmpstrRole = @tmpstrRole
                + CASE WHEN sysadmin        = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin'''        ELSE '' END
                + CASE WHEN securityadmin   = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin'''   ELSE '' END
                + CASE WHEN serveradmin     = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin'''     ELSE '' END
                + CASE WHEN setupadmin      = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin'''      ELSE '' END
                + CASE WHEN processadmin    = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin'''    ELSE '' END
                + CASE WHEN diskadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin'''       ELSE '' END
                + CASE WHEN dbcreator       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator'''       ELSE '' END
                + CASE WHEN bulkadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin'''       ELSE '' END
              FROM (
                        SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName],
                                sysadmin,
                                securityadmin,
                                serveradmin,
                                setupadmin,
                                processadmin,
                                diskadmin,
                                dbcreator,
                                bulkadmin
                        FROM sys.syslogins
                        WHERE (       sysadmin<>0
                                OR    securityadmin<>0
                                OR    serveradmin<>0
                                OR    setupadmin <>0
                                OR    processadmin <>0
                                OR    diskadmin<>0
                                OR    dbcreator<>0
                                OR    bulkadmin<>0
                            ) 
                            AND name=@name 
                  ) L 
  
                PRINT @tmpstr
                PRINT @tmpstrRole
                PRINT 'END'
            END 
            FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
        END
        CLOSE login_curs
        DEALLOCATE login_curs
        RETURN 0
    END

PowerShell脚本

Start-Job -RunAs32 -ArgumentList [My arguments] -Name "Job name" -ScriptBlock {
    Invoke-Sqlcmd -Query "[THE T-SQL query above]" -ServerInstance "TheCorrectDBServer"
    Invoke-Sqlcmd -Query "EXEC sp_help_revlogin"
}

Wait-Job -Name "Job name"
Receive-Job -Name "Job name"

错误信息

[TEST-SQL-Logins_Revs-Manu] $ powershell.exe -NonInteractive -ExecutionPolicy Bypass -File *********************.ps1

Id     Name            PSJobTypeName   State         HasMoreData     Location             Command                  

--     ----            -------------   -----         -----------     --------             -------                  

1      TEST-SQL-Log... BackgroundJob   Running       True            localhost            ...                       

1      TEST-SQL-Log... BackgroundJob   Failed        True            localhost            ...                       

Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=15.100.0.0, Culture=neutral,PublicKeyToken=***************' or one of its dependencies. The system cannot find the file specified.At *********************.ps1:*** char:1+ Receive-Job -Name "TEST-SQL-Log....."+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Invoke-Sqlcmd], FileNotFoundException

    + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.SqlServer.Management.PowerShell.GetScriptComma

   nd

    + PSComputerName        : localhost

Build step 'PowerShell' marked build as failureFinished: FAILURE

解决方案 #

• 安装对应版本的SMO组件：Invoke-Sqlcmd依赖SQL Server Management Objects(SMO)，Microsoft.SqlServer.BatchParser是SMO的核心组件。需在运行PowerShell的机器上安装与报错版本匹配的SMO包，可通过官方安装包或NuGet命令Install-Package Microsoft.SqlServer.SqlManagementObjects完成。
• 匹配运行架构：脚本使用了-RunAs32强制32位模式运行，需确保安装的SMO是32位版本；若机器和SQL Server支持64位，可直接去掉-RunAs32参数，改用64位模式运行Job。
• 手动加载程序集：若已安装SMO仍报错，可手动指定程序集路径加载，示例：

  Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\150\SDK\Assemblies\Microsoft.SqlServer.BatchParser.dll"
  

  注意路径中的版本号需与报错中的版本一致（如150对应SQL Server 2019）。
• 替换为新版SqlServer模块：旧版Invoke-Sqlcmd（来自SQLPS模块）兼容性差，建议安装新版SqlServer模块：

  Install-Module -Name SqlServer -Force
  

  之后使用该模块的Invoke-Sqlcmd，其对程序集依赖的处理更完善。

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