Or my alternate SEO-friendly title, “Dynamically load the proper SQL Server SMO Trace library in C#”.
I just posted a version of ClearTrace that will determine whether you have SQL Server 2005 or SQL Server 2008 installed and dynamically load the proper SMO library. Previously I had one version of ClearTrace for each version of SQL Server (YUCK!). I’m pretty happy with how this works even though I’m not very happy with SMO.
The method names are the same between the two versions of SMO but they aren’t both derived from the same interface. That made it a little difficult write a provider since I couldn’t strongly type anything. The methods for an object are called using InvokeMethod. Fortunately I only needed to call the InitializeAsReader method that way. After that I was able to cast the TraceFile object to an IDataReader and manipulate it just like any other data reader. My proof of concept code looks like this:
Object[] fileName = new object[] { @"\\hpserver\Development\trace\CDC10.trc" }; // Type t = Type.GetType("Microsoft.SqlServer.Management.Trace.TraceFile,
Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91"); Type t = Type.GetType("Microsoft.SqlServer.Management.Trace.TraceFile,
Microsoft.SqlServer.ConnectionInfoExtended, Version=10.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91");Object o = t.InvokeMember(null,
BindingFlags.DeclaredOnly |
BindingFlags.Public | BindingFlags.NonPublic |
BindingFlags.Instance | BindingFlags.CreateInstance, null, null, null);t.InvokeMember("InitializeAsReader",
BindingFlags.DeclaredOnly |
BindingFlags.Public | BindingFlags.NonPublic |
BindingFlags.Instance | BindingFlags.InvokeMethod, null, o, fileName);IDataReader t2 = (IDataReader)o;
while (t2.Read())
Console.WriteLine(t2.GetString(t2.GetOrdinal("EventClass")));t2.Close();
(Please remove the carriage returns in the middle of the string. I just put that in so it wouldn’t wrap. I’m sure there’s some C# guru looking at my code with the same disdain I have for people new to complex SQL statements. Take pity on a humble DBA. And I’m open to suggestions on better ways to do this.)
The downside is that I lose some accuracy on error messages. If the InitializeAsReader method fails you get the “Exception has been thrown by the target of an invocation” error message which really isn’t very helpful. That statement is wrapped in a try catch so I can provide a better error message.
Take it for a spin and let me know what you think. Now that’s I’ve got the code back in shape I should be doing more development on this in the future.