Interesting Exceptions: C# - Cannot load dynamically generated serialization assembly
If you ever tried calling a webservice (among other things) from a SQLCLR procedure chances are pretty good that you've ran into the following exception:
Msg 6522, Level 16, State 1, Procedure Test, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "Test": 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: at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection) at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence) at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames) at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources) at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
For all intents and purposes I am going to assume you've got an understanding on how to write and deploy CLR procedures (You can learn more about SQLCLR procedures over here and here)
CLR Procedures that make use of webservices require a strongly typed wrapper class (a nice little performance optimization when working with XML serialization/deserialization, needed for SOAP requests/responses)
Notice the following setting (Generate serialization assembly) within your project properties page under the build tab (assuming you're using Visual Studio 2008)
When the serialization setting is set to auto/on and you're consuming a webservice in your project, a serialization assembly automatically gets generated in your output folder (e.g. SourceAssembly.XmlSerializers.dll) which you'll need to register in SQL like seen in the following snippet:
CREATE ASSEMBLY CLRProcedures FROM 'C:\demos\CSTruter.com\CLRSQL\bin\Release\SourceAssembly.dll' WITH PERMISSION_SET = UNSAFE GO CREATE ASSEMBLY CLRSerializer FROM 'C:\demos\CSTruter.com\CLRSQL\bin\Release\SourceAssembly.XmlSerializers.dll' WITH PERMISSION_SET = UNSAFE GO CREATE PROCEDURE Test AS EXTERNAL NAME [CLRProcedures].[CSTruter.com.StoredProcedures].[Test]
All of this works fine when we're consuming webservices, there is however another scenario where we'll get this exception when working with SQLCLR - which comes into play as soon as we use the XmlSerializer class.
When working with XML Types for example, the XmlSerializer class becomes quite useful in that instead of working with raw xml, we can rather work with a .net object like seen in the following snippet where we map our xml to a .net class.
[XmlRoot("tester")] public class tester { [XmlAttribute("name")] public string name { get; set; } }
Serialization will look something like this:
tester t = new tester(); t.name = "test"; using (StringWriter sw = new StringWriter()) { XmlSerializer xs = new XmlSerializer(typeof(tester)); xs.Serialize(sw, t); }
While deserialization will look something like this:
tester t = null; using (StringReader sr = new StringReader("some xml string")) { XmlSerializer xs = new XmlSerializer(typeof(tester)); t = xs.Deserialize(sr) as tester; }
Like with webservices we'll also need to supply SQL with a strongly type wrapper class, but unfortunately visual studio only seems to automatically generate the serialization assembly when consuming webservices.
In order to manually generate a serialization assembly Microsoft created a tool called sgen, which is available in the .net sdk (and a few other microsoft SDKs), by which we can generate this serialization assembly.
Firstly you need to add the XmlSerializerAssembly attribute above the class(es) that you're using for serialization e.g.
[XmlRoot("tester")] [XmlSerializerAssembly("SourceAssembly.XmlSerializers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null")] public class tester {
Secondly run the sgen tool.
sgen C:\demos\CSTruter.com\CLRSQL\bin\Release\SourceAssembly.dll
(generates SourceAssembly.XmlSerializers.dll)
Simply register the generated assembly in SQL and that should get rid of this exception.
Posted by - Christoff Truter
Date - 2011-06-19 22:13:34
Comments - 2
Date - 2011-06-19 22:13:34
Comments - 2
C#: Parsing Apache / IIS log files (NCSA/W3C) Part 1
Webserver log files (e.g. NCSA/W3C) contain all kinds of useful data - amount of visitors to a website, how pages are performing/underperforming, bandwidth consumption, missing pages etc.
There are a number of excellent applications out there that parse these log files and provides the webmaster with all kinds of sexy reports (containing funky graphs/grids about the unfortunate victims/visitors to our websites).
For example (feel free to add to the list):
http://www.weblogexpert.com
http://awstats.sourceforge.net
http://www.smartertools.com/smarterstats/web-analytics-seo-software.aspx
Some of these tools, like smarterstats (if memory serves me correctly) even feature a simple report builder which empowers a webmaster to build their own reports.
But even that amount of flexibility can prove to be a bit unreliable, observe the following extract of a NCSA format log:
crawl-66-249-72-249.googlebot.com - - [11/Apr/2011:00:01:47 +0200] "GET /robots.txt HTTP/1.1" 301 291 "-" "Googlebot-Image/1.0" 41.0.34.4 - - [11/Apr/2011:00:02:21 +0200] "GET /components/com_virtuemart/show_image_in_imgtag.php?filename=resized%2FPaddle_Colt_Gove_4c68c62b3111e_90x90.jpg&newxsize=70&newysize=80&fileout= HTTP/1.1" 200 5129 "-" "Mozilla/4.0 (compatible;)" 41.0.34.4 - - [11/Apr/2011:00:14:15 +0200] "GET /components/com_virtuemart/show_image_in_imgtag.php?filename=resized%2FLooper_Kydex_Bel_4ce4c8c4a74b1_90x90.gif&newxsize=70&newysize=80&fileout= HTTP/1.1" 200 3046 "-" "Mozilla/4.0 (compatible;)" 41.0.34.4 - - [11/Apr/2011:00:14:16 +0200] "GET /components/com_virtuemart/show_image_in_imgtag.php?filename=resized%2FESP_Baton_21__4c52fdff9221e_90x90.gif&newxsize=70&newysize=80&fileout= HTTP/1.1" 200 2041 "-" "Mozilla/4.0 (compatible;)" 41.0.34.4 - - [11/Apr/2011:00:14:37 +0200] "GET /components/com_virtuemart/show_image_in_imgtag.php?filename=resized%2FITP_A3_EOS_4bb9a8780aaef_90x90.jpg&newxsize=70&newysize=80&fileout= HTTP/1.1" 200 5761 "-" "Mozilla/4.0 (compatible;)" ool-18bc86a4.dyn.optonline.net - - [11/Apr/2011:00:17:28 +0200] "GET /index.php?page=shop.product_details&category_id=3&flypage=tpflypage.tpl&product_id=8&option=com_virtuemart&Itemid=10 HTTP/1.1" 200 7503 "http://www.google.com/url?sa=t&source=web&cd=12&ved=0CCYQFjABOAo&url=http%3A%2F%2Fwww.hailstormza.com%2Findex.php%3Fpage%3Dshop.product_details%26category_id%3D3%26flypage%3Dtpflypage.tpl%26product_id%3D8%26option%3Dcom_virtuemart%26Itemid%3D10&rct=j&q=esp%20baton&ei=bP2UTZvGH6OD0QHkn-XkCw&usg=AFQjCNGur0K_j-lqCC8VZX5Ftz5I-NnjuA" "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; WOW64; Trident/4.0; SearchToolbar 1.2; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.5.30729; InfoPath.2; .NET CLR 3.0.30729; .NET4.0C; SRS_IT_E8790476BD76555A36A199; SRS_IT_E8790476BD76555A31A090)"
In the preceding extract you might have noticed the page "show_image_in_imgtag.php" being called multiple times. The page in question (from virtuemart - a joomla shop web application) is used to dynamically output resized images to the browser.
If we're interested in an accurate amount of page views (not to be confused with hits), we'll need to exclude that file from our report (since its not actually a page view) - something we can't expect the reporting application to know (perhaps some apps support manual exclusion of files?).
Which obviously means that the structure of a website can influence the accuracy of our reports; Incidently the website where I took this extract from reported over 150,000 in page views using AWStats, but as soon as I manually excluded the "show_image_in_imgtag.php" page, views fell to under 10,000 - a rather horrible inflation of the actual stats don't you think?
Now I am not going to tell everyone to dump their reporting applications and to write their own, I am however going to give you a few clues on how to manually retrieve and interpret the raw data yourself.
The first thing we're going to have a look at is how to parse a log file.
Note that log files come in various formats, like seen in the following image (from IIS 7.5 Manager):
Instead of writing our own log parser(s) we can use a pre-written one like the "log parser 2.2" tool Microsoft created back in 2005 (it can be downloaded from here), the tool supports all of the formats seen in the image above (and even a few others).
In your project add a reference to the LogParser.dll - if you installed the above mentioned tool it will generally be located within your 32bit program files folder under the "Log Parser 2.2" folder.
Observe the following snippet:
using System; using System.Data; using MSUtil; public static Type[] types = new Type[] { Type.GetType("System.Int32"), Type.GetType("System.Single"), Type.GetType("System.String"), Type.GetType("System.DateTime"), Type.GetType("System.Nullable") }; public static DataTable ParseLog<T>(string query) where T : new() { LogQueryClassClass log = new LogQueryClassClass(); ILogRecordset recordset = log.Execute(query, new T()); ILogRecord record = null; DataTable dt = new DataTable(); Int32 columnCount = recordset.getColumnCount(); for (int i = 0; i < columnCount; i++) { dt.Columns.Add(recordset.getColumnName(i), types[recordset.getColumnType(i) - 1]); } for (; !recordset.atEnd(); recordset.moveNext()) { DataRow dr = dt.NewRow(); record = recordset.getRecord(); for (int i = 0; i < columnCount; i++) { dr[i] = record.getValue(i); } dt.Rows.Add(dr); } return dt; }
In order to use the ParseLog generic method (like seen in the preceding snippet), we need to pass the type of log we're attempting to parse and a SQL style query as parameter.
SQL style query? Well, that is the true power of the microsoft log parser tool, you're able to write queries against the parsed entries e.g.
// W3C DataTable dt = ParseLog<COMW3CInputContextClassClass>(@"select * from c:\logs\w3c\*.*"); // NCSA DataTable dt2 = ParseLog<COMIISNCSAInputContextClassClass>(@"select * from C:\logs\ncsa\*.*");
Awesome, so now we've got our logs all parsed and ready to go, everyone happy?
Well, not exactly, there is a few crucial shortcomings I need to point out...
As soon as we're working with custom fields everything goes a bit haywire, something I only noticed by accident.
The COMIISNCSAInputContextClassClass parser strictly requires the log file to be in NCSA format with an exact set of fields, no custom fields allowed and lines that don't conform gets excluded - makes sense since the log doesn't contain any column definitions (What happend to me was that the administrators of this webserver added two extra fields to the log files and all of a sudden the parser failed parsing lines).
The COMW3CInputContextClassClass parser handles things a bit better in that it actually supports custom fields (also due to the fact that the log file contains column definitions). Unfortunately there is a problem in the way it handles custom fields, observe the following extract:
#Software: Microsoft Internet Information Services 7.5 #Version: 1.0 #Date: 2011-05-24 11:49:35 #Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status time-taken 2011-05-24 11:49:35 ::1 POST /UIT/AppWebServices/LookupMruWebService.asmx - 5555 UIT\Administrator ::1 Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+6.1;+WOW64;+Trident/4.0;+SLCC2;+.NET+CLR+2.0.50727;+.NET+CLR+3.5.30729;+.NET+CLR+3.0.30729;+.NET4.0C;+.NET4.0E) 200 0 0 2387 #Software: Microsoft Internet Information Services 7.5 #Version: 1.0 #Date: 2011-05-24 13:04:55 #Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs-host sc-status sc-substatus sc-win32-status time-taken 2011-05-24 13:04:55 ::1 GET /blog/247/default.aspx - 5555 - ::1 Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+6.1;+WOW64;+Trident/4.0;+SLCC2;+.NET+CLR+2.0.50727;+.NET+CLR+3.5.30729;+.NET+CLR+3.0.30729;+.NET4.0C;+.NET4.0E) localhost:5555 401 1 2148074254 1640
Notice the #Fields rows, it contains a definition of the columns represented in each of its following rows, but oddly enough you will find that the last entry in the extract won't be parsed.
The reason being that the COMW3CInputContextClassClass parser takes the first #Fields line in the log file and assumes that it is an accurate representation for all its following rows (disregarding every other #Fields that might be different, like seen in the above extract).
Personally I think it would have made a lot more sense if the parser returned all W3C Logging Fields (ones that don't exist in the log returned as nulls) and simply use the #Fields line as reference to which column a field belongs - which means we're not going to need to exclude entries that are actually valid.
In part 3 we'll look at alternative ways of parsing log files, but in the next part of this post however we're going to look at how to interpret the parsed log entries - status codes, useragents, bots/spiders etc..
Posted by - Christoff Truter
Date - 2011-06-11 00:25:17
Comments - 1
Date - 2011-06-11 00:25:17
Comments - 1
First 1 2 3 4 5 6 7 8 9 10 Last / 65 Pages (129 Entries)