Software Metering Console View – Part 4

After yesterday’s query, I know many of you are asking what more can be done to this query?

First off – notice that the language file is still a number and not at language name, my original idea was to show you how to translate that into a name but it is really one big case statement. Here is a lite version:

select Case v_MeteredProductRule.LanguageID
 when ‘1029’  then ‘Czech’
 when ‘1033’  then ‘English (United States)’
 when ‘65535’  then ‘Any’
 else ‘unknown’
end as ‘Language’
from  v_MeteredProductRule

So instead I will move on to my phase 2 plan.

If I was to add in a case statement in to cover all languages the query would increase by over 175! Based on this table http://www.microsoft.com/globaldev/reference/winxp/xp-lcid.mspx

 

sw4

MICROSOFT WILL NOT SUPPORT THESE UPDATES!

Have said the prerequisite warning, keep in mind that MS might during service package or version upgrade might delete all objects from the db, therefore I recommend having a backup of all your custom queries.

A useful option is to use a SQL function particularly if it will be used by many SQL queries.

Using our case statement above as the base for the function, this is what we end up with.

CREATE function Lang (@LangID varchar(100))
returns varchar (255)
as
Begin
 select @langID = Case @Langid
 when ‘1029’  then ‘Czech’
 when ‘1033’  then ‘English (United States)’
 when ‘65535’  then ‘Any’
 else ‘unknown’
 end
 Return @Langid
end

and our final query would look like this:

 

sw4b

SELECT
 MPR.ProductName AS ‘Name’,
 MPR.OriginalFileName AS ‘Original File Name’,
 MPR.FileName AS ‘File Name’,
 MPR.FileVersion AS ‘Version’,
 dbo.Lang(MPR.LanguageID) as ‘Language’,
 MPR.SourceSite AS ‘Site’,
 case MPR.ApplyToChildSites
 when ‘1’ then ‘True’
 else ‘False’
 end as ‘Apply to Child Site’,
 case MPR.Enabled
 when ‘1’ then ‘True’
 else ‘False’
 end as ‘Enabled’,
 MPR.SecurityKey as ‘Rule ID’
FROM
 v_MeteredProductRule MPR

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *