无法加载动态生成的序列化程序集;SQLCLR存储过程中出现错误

本文关键字:存储过程 存储 SQLCLR 过程中 错误 程序集 动态 加载 序列化 | 更新日期: 2023-09-27 18:04:00

当我尝试从SQL Server 2008 R2执行c# CLR存储过程时,我得到以下错误:

Msg 6522,级别16,状态1,过程PrintShippingDocLabelsToPDF,第0行
.NET Framework在执行用户定义例程或聚合"PrintShippingDocLabelsToPDF"时发生错误:
系统。InvalidOperationException: 无法加载动态生成的序列化程序集。在某些托管环境中,程序集加载功能受到限制,请考虑使用预生成的序列化程序。

请参阅内部异常获取更多信息。——>

先。fileloadexcexception: LoadFrom(), LoadFile(), Load(byte[])和LoadModule()被主机禁用
System.IO.FileLoadException:

这是我的理解,这发生正是因为我试图写入网络共享上的文件。我已经查看了以下来源:https://msdn.microsoft.com/en-us/library/ms345106(v=sql.105).aspx,从CLR存储过程中访问Sql FILESTREAM,但我仍然迷路了。

我所需要做的就是在网络共享上编写几个从SSRS到xxx.pdf文件的报告(其中xxx代表自定义名称)。下面是这个功能的代码,我相当确定下面的部分是导致错误的原因。

// Create a file stream and write the report to it
using (FileStream stream = File.OpenWrite(strFilePath + strFileName))
{
    stream.Write(results, 0, results.Length);
} // END using (FileStream stream = File.OpenWrite(StoredProcedures.strFilePath + StoredProcedures.strFileName))

下面是完整的程序。谁能给我点指示吗?我的工作期限很紧。作为临时解决方案,也许可以将这些报告写入临时表,然后使用SSIS包将它们移动到文件夹中。如果这是可能的,有人能给我一个指针,我将如何存储这些文件(varbinary(最大这些文件大约60KB-150KB的大小)?

public static void PrintShippingDocLabelsToPDF()
{
    // Put your code here  
    using (SqlConnection sqlConnTestDB = new SqlConnection("context connection=true"))
    {
        string sql4ConnectionString = "connection string";
        string sqlConnStrTestDB = sql4ConnectionString;
        // Select all unprinted SPSNumbers
        // If IsValidatedByWms == 0 --> SPS Not printed
        // If IsValidatedByWms == 1 --> SPS is printed
        string sqlCmdSlctTblPicking = @"SELECT SPSNumber, IsValidatedByWms AS LabelPrintStatus 
                                      FROM TestDB.dbo.tblPickingSlip WHERE IsValidatedByWms = 0";
        //SqlConnection sqlConnTestDB = new SqlConnection(sqlConnStrTestDB);
        SqlCommand sqlCmdSlctPicking = new SqlCommand(sqlCmdSlctTblPicking, sqlConnTestDB);
        // Select all SPSNumbers that have invoices against them
        // SPS number will be coming from tblPicking
        // If SPSNumber is NULL --> No invoice against SPS
        // If SPSNumber is NOT NULL --> There is an invoice against the SPS
        string sqlCmdStrSlctTblInvoiceItem = @"SELECT DISTINCT SPSNumber 
                                              FROM TestDB.dbo.tblInvoiceItem
                                              WHERE SPSNumber IS NOT NULL ";
        SqlCommand sqlCmdSlctTblInvoiceItem = new SqlCommand(sqlCmdStrSlctTblInvoiceItem, sqlConnTestDB);
        DataTable dtPicking = new DataTable();
        SqlDataAdapter sqlPickingAdapter = new SqlDataAdapter(sqlCmdSlctPicking);
        sqlPickingAdapter.Fill(dtPicking);
        DataTable dtTblInvoiceItem = new DataTable();
        SqlDataAdapter sqlTblInvoiceItemAdapter = new SqlDataAdapter();
        // Update print status of printed lables, labels only print for SPSNumbers that have invoices against them
        string sqlCmdStrUpdate = @"UPDATE TestDB.dbo.tblPickingSlip 
                                   SET IsValidatedByWms = 1
                                   WHERE SPSNumber = ";
        SqlCommand sqlCmdUpdateSlctPicking = new SqlCommand(sqlCmdStrUpdate, sqlConnTestDB);
        string strSpsNumber = null;  // keep track of the SPSNumber
        // Inspect the Picking table
        foreach (DataRow row in dtPicking.Rows)
        {
            if (Convert.ToInt32(row["LabelPrintStatus"]) == 0)
            {
                // a label has not been printed for the associated SPSNumber
                // check if the particualr SPSNumber has an assocaited invoice
                strSpsNumber = row["SPSNumber"].ToString();                    
                // add SPSNumber to query that selects all SPSNumbers that
                // have invoices against them
                string sqlCmdStrSlctTblInvoiceItem2 = sqlCmdStrSlctTblInvoiceItem + "AND SPSNumber = '" + strSpsNumber + "'";
                sqlCmdSlctTblInvoiceItem.CommandText = sqlCmdStrSlctTblInvoiceItem2;
                sqlTblInvoiceItemAdapter.SelectCommand = sqlCmdSlctTblInvoiceItem;
                sqlTblInvoiceItemAdapter.Fill(dtTblInvoiceItem);
                // Inspect tblInvoiceItem and print all SPSNumbers that have invoices against them 
                if (dtTblInvoiceItem != null)
                    if (dtTblInvoiceItem.Rows.Count > 0)
                    {
                        foreach (DataRow r in dtTblInvoiceItem.Rows)
                        {                                
                            // Write the report to the ExportFilePath
                            //WriteReport(strSpsNumber);
                            string ExportFilePath = @"''testsrv'EXPORT'";  // locaiton where PDF reports will be written.
                            string ReportPath = @"/xxx/Report1";  //  Path to report on modabackupsql reportserver
                            string FileExtentionPDF = @".pdf";
                            PrintShippingDocLabelPDF.REService2005.ReportExecutionService _re;  //  proxy class for the report execution for 
                            // Report arguments 
                            string report = ReportPath;
                            string historyID = null;
                            string deviceInfo = null;
                            string format = @"PDF";
                            Byte[] results;
                            string encoding = String.Empty;
                            string mimeType = String.Empty;
                            string extension = String.Empty;
                            PrintShippingDocLabelPDF.REService2005.Warning[] warnings = null;
                            string[] streamIDs = null;
                            string strFilePath = ExportFilePath;  // location for writing PDF labels generated from executed reports
                            string strFileName;  // the name of pdf labels generated from executed reports
                            _re = new PrintShippingDocLabelPDF.REService2005.ReportExecutionService();
                            _re.Credentials = System.Net.CredentialCache.DefaultCredentials;
                            // Prepare Render arguments
                            PrintShippingDocLabelPDF.REService2005.ExecutionInfo ei = _re.LoadReport(report, historyID);
                            PrintShippingDocLabelPDF.REService2005.ParameterValue[] parameters = new PrintShippingDocLabelPDF.REService2005.ParameterValue[1];
                            // add the spsnumber as the report parameter
                            parameters[0] = new PrintShippingDocLabelPDF.REService2005.ParameterValue();
                            parameters[0].Name = "spsnumber";
                            parameters[0].Value = strSpsNumber;
                            strFileName = strSpsNumber + FileExtentionPDF;
                            // set the execution parameters
                            _re.SetExecutionParameters(parameters, "en-us");
                            // render the report
                            results = _re.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
                            // Create a file stream and write the report to it
                            using (FileStream stream = File.OpenWrite(strFilePath + strFileName))
                            {
                                stream.Write(results, 0, results.Length);
                            } // END using (FileStream stream = File.OpenWrite(StoredProcedures.strFilePath + StoredProcedures.strFileName))
                            // Set the IsValidatedByWms associated with SPSNumber to 1
                            // to indicate that the report has been printed.
                            //sqlConnTestDB.Open();
                            sqlCmdUpdateSlctPicking.CommandText = sqlCmdStrUpdate + "'" + strSpsNumber + "'";
                            sqlCmdUpdateSlctPicking.ExecuteNonQuery();
                            //sqlConnTestDB.Close();
                        }  // END foreach (DataRow r in dtTblInvoiceItem.Rows)
                        dtTblInvoiceItem.Clear();
                    } // if (dtTblInvoiceItem.Rows.Count > 0)
            }   //  END if (Convert.ToInt32(row["LabelPrintStatus"]) == 0)
        }  // END foreach (DataRow row in dtPicking.Rows)
    }  // END using (SqlConnection sqlConnTestDB = new SqlConnection("context connection=true"))
} // END public static void PrintShippingDocLabelsToPDF()

无法加载动态生成的序列化程序集;SQLCLR存储过程中出现错误

首先,关于这个问题的一些注意事项:

  • 无法加载动态生成的序列化程序集。

    序列化程序集用于通过Web服务进行通信

  • System.IO.FileLoadException

    此错误不是来自您的代码,因为您没有加载文件。您正在尝试保存文件。两者都要求试图操作的装配具有PERMISSION_SETEXTERNAL_ACCESS,但加载和保存不是同一件事。

  • LoadFrom(), LoadFile(), Load(byte[])和LoadModule()已被主机禁用。

    这很可能是由于没有将程序集的PERMISSION_SET设置为EXTERNAL_ACCESS。这需要将DB设置为TRUSTWORTHY ON(不好),或者根据您签署组件的密钥创建登录,并授予该登录EXTERNAL ACCESS ASSEMBLY权限。

出于测试目的,您可以执行以下操作,看看它是否可以工作。将程序集部署为SAFE之后,运行以下命令:

  • ALTER DATABASE [{db_name}] SET TRUSTWORTHY ON;
  • ALTER ASSEMBLY [{assembly_name}] WITH PERMISSION_SET = EXTERNAL_ACCESS;

并尝试再次运行存储过程。你至少应该走远一点。然而,鉴于代码的当前形式,既然你说它已经作为控制台应用程序工作,我认为你最好保持这种方式并从xp_cmdshell运行它,或者如果还没有启用,从SQL代理作业的命令步骤运行它。

我认为这个可以在SQLCLR中工作,但是它需要首先进行一些重组。

    不要在。net代码中循环获取spsnumber。通过CURSOR在T-SQL中获取该列表,然后在SPSNumber中调用此进程。
  • 控制台应用程序(和Windows窗体)和SQLCLR之间的一个区别是控制台应用程序作为Windows登录运行。但是,在默认情况下,SQLCLR作为SQLSERVER NT服务的登录帐户运行。该帐户需要对文件夹进行写访问,或者您需要启用Impersonation以将安全上下文切换到正在运行存储过程的任何人,但这仅适用于Windows登录,而不适用于SQL Server登录。

将军指出:

  • 请不要将字符串连接到查询中,因为这会打开可能的SQL注入。相反,可以这样声明参数:

    SqlParameter _SpsNumber = new SqlParameter("@SpsNumber", SqlDbType.Int);
    sqlCmdUpdateSlctPicking.Parameters.Add(_SpsNumber);
    

    然后你更新sqlCmdStrUpdate@SpsNumber结束,而不是为每个项目更新sqlCmdUpdateSlctPicking.CommandText,你只调用_SpsNumber.Value = strSpsNumber;(尽管你可能需要做Int32.Parse(strSpsNumber),如果它抱怨)。

  • 对于dtTblInvoiceItem != nulldtTblInvoiceItem.Rows.Count > 0,不需要两个if语句。把它们合并到if((dtTblInvoiceItem != null) && (dtTblInvoiceItem.Rows.Count > 0))