I get the following error when I attempt to execute a C# CLR stored procedure from SQL Server 2008 R2:
Msg 6522, Level 16, State 1, Procedure PrintShippingDocLabelsToPDF, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "PrintShippingDocLabelsToPDF":
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer.Please see inner exception for more information. --->
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
It is my understanding that this is happening precisely because I am trying to write to a file on a network share. I have reviewed the following sources: https://msdn.microsoft.com/en-us/library/ms345106(v=sql.105).aspx, Accessing Sql FILESTREAM from within a CLR stored procedure, but I am still lost.
All I need to do is write several reports from SSRS to xxx.pdf files on a network share (where xxx represents a custom name). The code for this functionality is below and I am fairly certain that the below part is causing the error.
// 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))
Here is the full procedure. Can someone give me a pointer, please? I am working on a tight deadline. As a temporary solution, perhaps it is possible to write these reports to a temp table and then use a SSIS package to move them to a folder. If that is possible can someone give me a pointer on how I would store these files (varbinary(MAX these files are approximately 60KB-150KB in size)?
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()
