无法加载动态生成的序列化程序集;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()
首先,关于这个问题的一些注意事项:
-
无法加载动态生成的序列化程序集。
序列化程序集用于通过Web服务进行通信
-
System.IO.FileLoadException
此错误不是来自您的代码,因为您没有加载文件。您正在尝试保存文件。两者都要求试图操作的装配具有
PERMISSION_SET
的EXTERNAL_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代码中循环获取
- 控制台应用程序(和Windows窗体)和SQLCLR之间的一个区别是控制台应用程序作为Windows登录运行。但是,在默认情况下,SQLCLR作为SQLSERVER NT服务的登录帐户运行。该帐户需要对文件夹进行写访问,或者您需要启用Impersonation以将安全上下文切换到正在运行存储过程的任何人,但这仅适用于Windows登录,而不适用于SQL Server登录。
spsnumber
。通过CURSOR
在T-SQL中获取该列表,然后在SPSNumber
中调用此进程。将军指出:
-
请不要将字符串连接到查询中,因为这会打开可能的SQL注入。相反,可以这样声明参数:
SqlParameter _SpsNumber = new SqlParameter("@SpsNumber", SqlDbType.Int); sqlCmdUpdateSlctPicking.Parameters.Add(_SpsNumber);
然后你更新
sqlCmdStrUpdate
以@SpsNumber
结束,而不是为每个项目更新sqlCmdUpdateSlctPicking.CommandText
,你只调用_SpsNumber.Value = strSpsNumber;
(尽管你可能需要做Int32.Parse(strSpsNumber)
,如果它抱怨)。 -
对于
dtTblInvoiceItem != null
和dtTblInvoiceItem.Rows.Count > 0
,不需要两个if
语句。把它们合并到if((dtTblInvoiceItem != null) && (dtTblInvoiceItem.Rows.Count > 0))