Well today, I started the fun filled adventure of managing data from/to a LinkedServer.

During this learning curve, I found plenty of information about how to query from and instert into a Linked server but most of them utilized the OpenQuery TSQL Statement to operate.

One failure of the concepts is to use the inline statement to access the Linked Server.

In my situation, i was linking to a MySQL database offsite. Below you will find the MSSQL statement patterns to used when utilizing query statements against the Linked Server.

Select:

Select {cols}
From {LinkedServer}...{table}
[Conditions]


Insert:

Insert Into {LinkedServer}...{table} [cols,...]
Values ([cols,...])


There are more commands you can use but the point is that the developer/dba is utilizing only the 'server' and 'table' portions of the source information. This being, as described by Microsoft, as '{server}.{database}.{schema}.{table}' is simply '{linkedserver}...{table}'. At least for MySQL, they do not broadcast schema information through general access this is mostly handled by the login credentials instead, as i understand the framework.

In summary, instead of building god-aweful string concatenations with double/triple/etc single quotes to try and get the Execute style expression to execute against the remote server, just use the dot system already in place. Will allow you to operate on the database/tables in the same way as you maybe/are comfortable with.

I have been researching for the last couple of weeks a way to acquire a maintenance plan job history, success or failure.

After alot of searching and finding that no one has or is not sharing the ability to do this, I decided to start hacking away at the MS SQL 2005/2008 core databases to find the relationship.

After much digging, there are a total of 4 tables you need to have access to, whether through a proxy account of dbo schema account.

Tables in question:
- msdb..sysmaintplan_log
- msdb..sysmaintplan_logdetail
- msdb..sysmaintplan_subplans
- msdb..sysjobs (optional: gives the SQL Agent Job name)

SQL Script as i built it, but you will probably modify it to your needs:


SELECT
mpld.server_name,
j.name,
mpl.start_time,
mpl.end_time,
mpl.succeeded,
mpld.error_number,
mpld.error_message,
mpld.line1 + char(10) + char(13) +
mpld.line2 + char(10) + char(13) +
mpld.line3 + char(10) + char(13) +
mpld.line4 + char(10) + char(13) +
mpld.line5 as details
FROM msdb..sysmaintplan_log mpl
inner join msdb..sysmaintplan_logdetail mpld on mpl.task_detail_id = mpld.task_detail_id
inner join msdb..sysmaintplan_subplans mpsp on mpl.subplan_id = mpsp.subplan_id
inner join msdb..sysjobs j on mpsp.job_id = j.job_id
where mpl.start_time between dateadd(day, -7, '5/2/2011') and '5/2/2011'
order by mpl.end_time desc


As you can see, this only shows the logdetail entries from (5/2/2011 -7days) to 5/2/2011, which is in effect a range from 4/25/2011 00:01 (or 00:00:001) to 5/1/2011 00:00.

A style that currently is not advertised and probably under-pronounced on resume's is the ability to translate a old code-base into a up-to-date framework (whether it is a .Net framework or simply taking ForTran 77 and converting it to GNU-C). In either case, the process it the same.

Forensic Translation is the ability of the programmer to read another language, deduce the methodology being incorporated and then successfully translating it to the new code-base. This process could take as little as 1 week or as long as several years.

The major variable in this process is source informations direct usage of declarations. For the most part, the bracket languages (Java, C/C++/C#, AdaXX, etc) tend to have the ability to utilize an object that otherwise would be declared but is not. This process enables the reduction of resources for the compiler to have to declare for operation. Although, this is find great and dandy for stream-lining the execution process, this severly hampers debugging as well. When it comes to translating it the translator had best understand this concept or they will be spinning their wheels in some deep MUCK.

Knowledge of the two languages helps in this process. I personally am a VB.Net programmer, and since .Net 2.0, the VB language has grown to include some generics (albeit not as powerful of generics as C/C++/C#) and enables us to cleanly attach to events or create them (Delegates). As my background includes these languages: BasicA, Ansi-C/C+, MS-C/C++/C#, ForTran 77, Ada95, QBasic, VB4-6/.Net, Java 1.0+, SQL, HTML/ASP.Net/PHP, VB/Java-script and several small languages, i have a good grasp on the way different languages are structured and how they utilize their naming conventions. Usually i just have pick up the different data types, control structures (if...else, for...loop, case, etc) and i am pretty much good to go. As you see above, i have a wide-range of knowledge, not expert but intermediate, of alot of languages which allows me to be able to read the languages and be able to decipher what is going on at that specified line. On the vice, this also allows me to pick and choose the language for the situation.

Best Tool to Use:
Everyone has heard (more like read on forums) the battles between how the C framework is better than the VB framework. And this is much like the argument between the Republicans and Democrats in the US, Labor and Democrats in UK, etc. Everyone has their own view point and rightfully so they are right in their own mind. To be an effective Forensic Translator Programmer (FTP), these view-points will do nothing for you. They will actually hinder your development environment. In my past, those that stuck strictly with one language, actually gave them self a handicap. They can only rely on one experience to lead them through the development of a project. They dont have the ability to effectively read another languages methodology, and least of all they end up turning off other programmers of other languages due to their closed mindedness. FTP's must always adhere to the "Devils Advocate" approach: how would this work in another languages, how much more efficient would this line of code be in this language, what are the Pro's & Con's of translating this segment from a Do..Loop to a Do Until...Loop, what are the effects of this segment being consolidated into a Method versus having it copied into 5 methods to use, and so on.

Link: http://msdn.microsoft.com/en-us/library/bb384936.aspx

Intro:

While translating a C# library, i ran into a segment of code that i didnt recognize. After doing some research found out that the same design can be implemented in VB.Net (VB 2k8). One catch to the whole implementation is that the Extension development is only possible in a module. As well, there are some limitations to the design of the extension.

C# style

Syntax:

Scope ReturnType MethodName('this' ParentType parameter, ParamType para1, ...)
{
....
}

When designing this in C# the structure is:
- Scope : Public, Shared, Private, Friend, etc
- ReturnType : Object or Type
- MethodName : any name that is consistent with the naming convention of the compiler,
- 'this' : the internal pointer to the object that will have the extension,
- ParentType : the object/type that will be utilizing this extension,
- parameter : the internal reference to the 'this' object for operation within the extension method.
- ParamType : the object/type that will actually be passed to the method
- para1 : the parameter variable name to be used within the method

All that being said, here is a live example from a Silverlight Library that i have been de-engineering for educational purposes only (Silverlight Menu).

Declaration:

public static Point TransformFromRootVisual(this UIElement element)
{
try
{
MatrixTransform globalTransform = (MatrixTransform)element.TransformToVisual(null);
return globalTransform.Matrix.Transform(_zero);
}
catch { }
return _zero;
}

Usage:

Point p = this.TransformFromRootVisual();

VB 2k8 style

VB 2k8 has a similar declaration statement, but varies in how each part is incorporated. I will keep the same declaration format names for consistency and comparison.

Syntax:

<Extension()> _
Scope Method MethodName(parameter As ParentType, para1 As ParamType, ...) As ReturnType

End Method

Explanation:
- <Extension()> : Attribute to precede the Method declaration, much like all other Attributes, except this Attribute is Method (Sub/Function) specific and is not available for use with any other declaration type.
- Scope : Private, Public, Friend, etc.
- Method : Sub or Function
- MethodName : Any method name that is consistent with the nameing convention of the compiler
- parameter : any parameter name that is consistent with the naming convention of the compiler.
- ParentType : The object/type that this Extension method will be attached to.
- param1 : Any parameter name that is consistent with the naming convention of the compiler, this parameter will actually be a passed value. This is an optional part of the Extension declaration.
- ParamType : The object/type that the parameter will be defined.
- ReturnType : the object/type that will be returned once all operations are completed inside the method.

As you can see there isn't a whole lot of difference between the C# and VB 2k8 format. yes there are some structural and design implementation difference but in either case they result in the same outcome.

Here is an example of the VB 2k8 equivalent of the C# example:
Extension Declaration:

<extension ()> _
Public Function TransformFromRootVisual(ByVal element As UIElement) As Point
Dim p As New Point

Try
Dim globalTransform As MatrixTransform
globalTransform = CType(element.TransformToVisual(Nothing), MatrixTransform)

p = globalTransform.Matrix.Transform(_zero)
Catch ex As Exception
p = _zero
End Try

Return p
End Function

Usage:

Dim p As Point = Me.TransformFromRootVisual()

Summary:

Although in C# you dont have the availability of putting certain code in a module to make it "globally" available, to the whole namespace in which it is declared, you need to place it in a class that is static and not instantiatable.

On the flip side, in VB 2k8 you need to explicitely define a module within the namespace in which you want it used. The caveat is that Extension(s) for VB 2k8 are restricted to only Module level methods and not Class level. Which for some mind-sets restricts the ability to co-locate in consolidated locations.

The key notes:
1) Include System.Runtime.ComilerServices
2) make sure the first parameter is the Object/Type you want the method to be associated with. If you want it to operate on Object types, then make the first parameter of type Object, but be aware that if you do so, the Extension method will be extended out to all objects that inherit type Object within the namespace.

Recently, i ran into a situation where i needed to daisy-chain several usercontrols together. The basic thought was similar to most sites "New User Data Entry" forms. You fill one out, and then you goto the next one to fill more incriminating information and then the last form you fill out, you basically sign your life away and your first born.

Problem i ran into was the fact that i could not achieve this effect without a severe overload of page resources (i.e. Multi-View/View relationship) and even then it was sadly sketchy and buggy.

After doing some research and some serious code management (thats what i call it ) I managed to achieve a relatively small method, through VB but should be easily translatable since it is small, to get the effect i needed.


Friend Shared Sub NextForm(ByRef par As Control, ByRef curr As Control, ByVal [new] As String)
'remove current control (curr) from parent (par)
par.Controls.Remove(curr)
'load control into cntrl object
Dim cntrl As Control = par.Page.LoadControl([new])
'add control to the parent
par.Controls.Add(cntrl)
End Sub


Minus the comments and method declaration, this is a 3-liner. For my instance i put it in the residing page and as i performed my logic inside the usercontrols i simply called the page class and then the method.

Below is an example of how i used it in the code.

Data.NextForm(Me.Parent, Me, "content/" & ProdType.Material.ToString & ".ascx")

- Data is the ASPX page
- Me.Parent is the usercontrols containing control (for my instance, it was a placeholder)
- Me is the usercontrol
- "content/" & ProdType.Material.ToString & ".ascx" is the string representation of the location of the usercontrol.

If i remember right LoadControl can any of the different url representations. I use a referential level, for portability if i am lucky and it needs to be implemented elsewhere.