(SQL) SQL (2014 год)

Collection and processing information about your system.

This article/post contains many method to create list of installed program in your windows system and this article is conceived as shallow review of future of MS SQL Server and my personal SQL server experience, and the other side this article is expanding of my old article and opensource program from 2012 year WinDump - creation system snapshot by WMI., what is expanding of my earlier article and opensource program WinDump - save system state. And at common, my ancient article in this site was writing in 2003 year about Windows Installer and accounting of installed program.

Also this is my first article in English on SQL part of my blog SQL, to opposite of another part of my blog, for example ASP NET MVC programming that contains many english articles. But I work in SQL server since 1998 year and my first post about SQL Server I was created in 2000 year and now I can do difficulties as possible task in SQL Server, for example see this my video My project for refactoring huge database to prepare using it in Entity Framework, on the other side I work in in English since 1986 year as programmer IBM/360 in National Cosmic Agency of Ukraine - why I have nothing articles about SQL in English? When I had understanding how many separate edges is combine to one article/post I immediately decided to write this article.


In global view there are three way to investigate list of installed program:

1. "My" extension of VB.NET environment.

VB.NET is a special proprietary languages, human oriented and created by Microsoft for RAPID development, fast as possible. VB.NET is much older than C# and has many special addition comparing C# Difference between VB and C#, since 2002 year this language support special object MY (and early VB6 has similar functional as separate library). I don't want repeat my article from 2006 and 2012 year and you may see in this articles how to use "MY" library in VB.NET and we are immediately jump to WMI.



2. WMI function of Windows.

2.1 Call WMI programmatically.

My first program to call WMI programmatically through WSH (window script host) I publish in this blog in 2001 year (Скрипты WSH) and below I show how to call WMI by simplest as possible way.

This method use Microsoft .NET wrapper about WMI function System.Management.dll



This is not ideal method of using WMI, because it's absurd to serialize hierarchical WMI data to plain text. But this is console utility is a step for more useful WMI function as SQL CLR Assemblies (we will showing it below).


   1:  Module Module1
   2:      Sub Main()
   3:          Dim WMI1 As New Wmi
   4:          Dim WmiNamespace As String = "\\XEON\ROOT\CIMV2"
   5:          Dim WmiRSClass As String = "WIN32_BIOS"
   6:          Console.WriteLine("WmiExplorer test")
   7:          While True
   8:              Console.WriteLine("Please Input WmiNamespace (like \\XEON\ROOT\CIMV2) or Enter to exit")
   9:              WmiNamespace = Console.ReadLine()
  10:              If WmiNamespace.Trim.Length = 0 Then Exit Sub
  11:              Dim Response As String = WMI1.Connect(WmiNamespace)
  12:              Console.WriteLine(Response)
  13:              If Response <> "Invalid namespace " Then
  14:                  While True
  15:                      Console.WriteLine("Please Input WmiClass (like WIN32_BIOS) or Enter to exit")
  16:                      WmiRSClass = Console.ReadLine()
  17:                      If WmiRSClass.Trim.Length = 0 Then Exit Sub
  18:                      Dim Result As String
  19:                      Result = WMI1.BindClass(WmiRSClass)
  20:                      Console.WriteLine(Result)
  21:                      If Result.StartsWith("Has") Then
  22:                          Console.WriteLine(WMI1.ListProperty())
  23:                      End If
  24:                  End While
  25:              End If
  26:          End While
  27:      End Sub
  28:  End Module

   1:  Public Class Wmi
   2:      Dim ServerClass As Management.ManagementClass
   3:      Dim Scope As Management.ManagementScope
   4:      Dim Instances As Management.ManagementObjectCollection
   5:   
   6:      Function Connect(WmiNamespace As String) As String
   7:          Try
   8:              Scope = New Management.ManagementScope(WmiNamespace)
   9:              Scope.Connect()
  10:              If Scope.IsConnected Then Return "OK" Else Return "Not found"
  11:          Catch ex As Exception
  12:              Return ex.Message
  13:          End Try
  14:      End Function
  15:   
  16:      Function BindClass(WmiRSClass As String) As String
  17:          Try
  18:              ServerClass = New Management.ManagementClass(WmiRSClass)
  19:              ServerClass.Get()
  20:              Instances = ServerClass.GetInstances()
  21:              Return "Has " & ServerClass.Properties.Count & " properties"
  22:          Catch ex As Exception
  23:              Return ex.Message
  24:          End Try
  25:      End Function
  26:   
  27:      Function ListProperty()
  28:          Dim Out1 As New Text.StringBuilder
  29:          Try
  30:              For Each Instance As Management.ManagementObject In Instances
  31:                  Out1.AppendLine(Instance.ToString)
  32:                  Out1.AppendLine()
  33:                  Dim InstProps As Management.PropertyDataCollection = Instance.Properties
  34:                  Dim Prop As Management.PropertyData
  35:                  For Each Prop In InstProps
  36:                      Dim Name As String = Prop.Name
  37:                      Dim Val As Object = Prop.Value
  38:                      Out1.AppendLine("Property: " + Name)
  39:                      If Val Is Nothing Then
  40:                          Out1.AppendLine("   Value: <null>")
  41:                      Else
  42:                          Dim Str1 As New Text.StringBuilder
  43:                          If Val.ToString = "System.UInt16[]" Then
  44:                              Array.ForEach(Of UInt16)(Val, Sub(One) Str1.Append(One.ToString & ","))
  45:                              Out1.AppendLine("   Value: " + Str1.ToString(0, Str1.Length - 1))
  46:                          ElseIf Val.ToString = "System.String[]" Then
  47:                              Array.ForEach(Of String)(Val, Sub(One) Str1.Append(One.ToString & ","))
  48:                              Out1.AppendLine("   Value: " + Str1.ToString(0, Str1.Length - 1))
  49:                          Else
  50:                              Out1.AppendLine("   Value: " + Val.ToString())
  51:                          End If
  52:                      End If
  53:                  Next
  54:              Next
  55:              Return Out1.ToString
  56:          Catch ex As Exception
  57:              Out1.AppendLine(ex.Message)
  58:              Return Out1.ToString
  59:          End Try
  60:      End Function
  61:  End Class
  62:   

2.2 Two main GUI program exploring Windows WMI.

My first post about WMI I wrote in 2001 year Скрипты WSH and now WmiExplorer is a good stable OpenSource program. You may download this program from https://wmie.codeplex.com/ and learn all system objects through universal WMI interface.



Second good GUI program is Msinfo32:



This is good programs, but this is only GUI interface to WMI. It has no oportunity to export data to SQL Server to strong processing this data. But one way to export data is exists msinfo32 /nfo.

2.3 Processing NFO data exported from Msinfo32 as XML.

Data from Msinfo32 naturally is exported by XML and has extension .NFO:

Since 2000 year I wrote a lot of various articles/posts about processing XML in special section of my blog, best program to working with XML is Altova, but you may download and install to you system XML Notepad from https://xmlnotepad.codeplex.com/ and see or exit your NFO-file.



But how to process this XML file? At common, there are two ways, first way is import this data to SQL Server and father processing data inside SQL Server.

Before processing data we can import data as XML fields of SQL server:


   1:  CREATE TABLE [dbo].[Xeon-msinfo32](
   2:      [i] [int] IDENTITY(1,1) NOT NULL,
   3:      [XML] [xml] NULL,
   4:   CONSTRAINT [PK_Xeon-msinfo32] PRIMARY KEY CLUSTERED 
   5:  (
   6:      [i] ASC
   7:  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   8:  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   9:  GO
  10:   
  11:  INSERT INTO [dbo].[Xeon-msinfo32](Xml)  
  12:  SELECT * FROM  OPENROWSET(BULK 'F:\xeon-nfo.NFO', SINGLE_BLOB) x;

Aftr that we can process data by xml Data Type Methods and XQuery Functions, for example by this function.


   1:  DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
   2:  SELECT @XML = [XML] FROM [dbo].[Xeon-msinfo32]
   3:  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
   4:  SELECT text
   5:  FROM OPENXML(@hDoc, 'MsInfo/Category/Data/')
   6:  --WITH (text nvarchar(100))
   7:  where not(text is null)
   8:  EXEC sp_xml_removedocument @hDoc


For more details about processing XML inside SQL please see my articles How to parse XML SOAP inside MS SQL and other in section XML.

2.4 Processing NFO data as JSON.

Second (alternative) way to processing NFO data is transform XML file to JSON, for example with this online service https://www.freeformatter.com/.



After that we may processing JSON data inside MongoDB. I have some details articles about MongoDB, for example MongoDB - noSQL-database for irregular JSON data, but this method of data processing is developing very fast and now we are have another tools, than previous year.

But the common logic remains the same, firstly need to prepare config file and start MongoDB as service.



After that we may load Json to MongoDB



and processing data as JSON. Json selector little bit similar to SQL selector, but strange for SQL base logic - SQL to Mongo mapping, SQL to MongoDB Mapping And I recommended you for creating query to JSON to use commercial query builder from 3T.



2.5 Observing all WMI class by Powershell.

In previous section we are see how to call WMI by special GUI program, in this section we are continue executing WMI, but in the command line. At common, we can call WMI directly by command WMIC (like in first screen above to install MongoDB) or through universal interface Powershell.

In this section we call WMI by Powershell and firstly we will observe all most important WMI class related to collection information about computer environment and installed software (with prefix Win32_). This is common description of this command PowerShell Get-WmiObject


Get-WMIObject -List| Where{$_.name -match "^Win32_"} | Sort Name > F:\Win32_All.txt

For further processing this interesting data we will import this data to SQL server. The simplest way to import text data is Sql Server Import Export Wizard (Download SQL Server Data Tools (SSDT) if you don't have installed it).

This wizard is not correctly separate one column from another in text file and I usually import all text row to one field "Column 0", I usually correct in this wizard only one parameters - length of "Column 0" field.



Next step is create clear data after importer store raw text to SQL Server. This task can be create by this script.


   1:  CREATE TABLE [dbo].[Win32](
   2:      [i] [int] IDENTITY(1,1) NOT NULL,
   3:      [Name] [nvarchar](50) NULL,
   4:   CONSTRAINT [PK_Win32] PRIMARY KEY CLUSTERED 
   5:  (
   6:      [i] ASC
   7:  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   8:  ) ON [PRIMARY]
   9:  GO
  10:   
  11:  Insert Win32
  12:  SELECT left([Column 0],36) FROM [tst1].[dbo].[Win32_All]
  13:  where [Column 0] like 'Win32_%' and 
  14:  not [Column 0] like 'Win32_PerfRawData_%' and
  15:  not [Column 0] like 'Win32_PerfFormattedData__%'

2.6 Extending SQL server opportunities by SQL CLR Assemblies to call WMI.

In this step we are expanding my Сommon template of table value SQL CLR Assembly to insert body of code executing WMI pattern (from section 2.1 Call WMI programmatically). But firstly need to add reference to System.Management.dll to project and to MS SQL.



   1:  CREATE ASSEMBLY [System.Management]
   2:  FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
   3:  WITH PERMISSION_SET = UNSAFE
   4:  GO

I done small refactoring code of part 2.1 Call WMI programmatically. from console utility to Table Value SQL CRL template:


   1:  Imports System
   2:  Imports System.Data
   3:  Imports System.Data.SqlClient
   4:  Imports System.Data.SqlTypes
   5:  Imports Microsoft.SqlServer.Server
   6:  Partial Public Class Wmi
   7:   
   8:      <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read,
   9:                                              Name:="GetWmiInfo",
  10:                                              FillRowMethodName:="NextRow",
  11:                                              TableDefinition:="Prop Nvarchar(250), Val Nvarchar(1000)")>
  12:      Public Shared Function GetWmiInfo(WmiNamespace As SqlString, WmiClass As SqlString) As Collections.IEnumerable
  13:          Dim X As New Data.DataTable
  14:          X.Columns.Add("Prop", GetType(SqlString))
  15:          X.Columns.Add("Val", GetType(SqlString))
  16:   
  17:          If WmiClass.ToString.Trim.Length = 0 Or WmiNamespace.ToString.Trim.Length = 0 Then
  18:              X.Rows.Add("Error", "Request is empty")
  19:              Return X.Rows
  20:              Exit Function
  21:          End If
  22:   
  23:          Dim WMI1 As New WmiWrapper
  24:          Dim Response As String = WMI1.Connect(WmiNamespace.ToString)
  25:          If Response <> "Invalid namespace " Then
  26:              Dim Result As String
  27:              Result = WMI1.BindClass(WmiClass.ToString)
  28:              Console.WriteLine(Result)
  29:              If Result.StartsWith("Has") Then
  30:                  WMI1.ListProperty(X)
  31:                  Return X.Rows
  32:                  Exit Function
  33:              End If
  34:          Else
  35:              X.Rows.Add("Error", "Invalid namespace")
  36:              Return X.Rows
  37:              Exit Function
  38:          End If
  39:          Return X.Rows
  40:      End Function
  41:   
  42:      Public Shared Sub NextRow(Obj As Object,
  43:                               <Runtime.InteropServices.Out()> ByRef Prop As SqlString,
  44:                               <Runtime.InteropServices.Out()> ByRef Val As SqlString)
  45:          Try
  46:              Dim X As Data.DataRow = TryCast(Obj, DataRow)
  47:              Prop = X("Prop").ToString
  48:              Val = X("Val").ToString
  49:          Catch ex As Exception
  50:              Prop = "Error"
  51:              Val = ex.Message
  52:          End Try
  53:      End Sub
  54:  End Class

   1:  Imports System
   2:   
   3:  Public Class WmiWrapper
   4:      Dim ServerClass As Management.ManagementClass
   5:      Dim Scope As Management.ManagementScope
   6:      Dim Instances As Management.ManagementObjectCollection
   7:   
   8:      Function Connect(WmiNamespace As String) As String
   9:          Try
  10:              Scope = New Management.ManagementScope(WmiNamespace)
  11:              Scope.Connect()
  12:              If Scope.IsConnected Then Return "OK" Else Return "Not found"
  13:          Catch ex As Exception
  14:              Return ex.Message
  15:          End Try
  16:      End Function
  17:   
  18:      Function BindClass(WmiRSClass As String) As String
  19:          Try
  20:              ServerClass = New Management.ManagementClass(WmiRSClass)
  21:              ServerClass.Get()
  22:              Instances = ServerClass.GetInstances()
  23:              Return "Has " & ServerClass.Properties.Count & " properties"
  24:          Catch ex As Exception
  25:              Return ex.Message
  26:          End Try
  27:      End Function
  28:   
  29:      Function ListProperty(ByRef DT As Data.DataTable) As Data.DataTable
  30:          Try
  31:              For Each Instance As Management.ManagementObject In Instances
  32:                  Dim InstProps As Management.PropertyDataCollection = Instance.Properties
  33:                  Dim Prop As Management.PropertyData
  34:                  For Each Prop In InstProps
  35:                      Dim Name As String = Prop.Name
  36:                      Dim Val As Object = Prop.Value
  37:                      If Val Is Nothing Then
  38:                          DT.Rows.Add(Name, DBNull.Value)
  39:                      Else
  40:                          Dim Str1 As New Text.StringBuilder
  41:                          If Val.ToString = "System.UInt16[]" Then
  42:                              Array.ForEach(Of UShort)(Val, Sub(One) Str1.Append(One.ToString & ","))
  43:                              DT.Rows.Add(Name, Str1.ToString(0, Str1.Length - 1))
  44:                          ElseIf Val.ToString = "System.String[]" Then
  45:                              Array.ForEach(Of String)(Val, Sub(One As String) Str1.Append(One.ToString & ","))
  46:                              DT.Rows.Add(Name, Str1.ToString(0, Str1.Length - 1))
  47:                          Else
  48:                              DT.Rows.Add(Name, Val.ToString())
  49:                          End If
  50:                      End If
  51:                  Next
  52:              Next
  53:              Return DT
  54:          Catch ex As Exception
  55:              DT.Rows.Add("Error", ex.Message)
  56:              Return DT
  57:          End Try
  58:      End Function
  59:  End Class

And test this code by this command:


   1:  select * from [dbo].[GetWmiInfo]('\\XEON\ROOT\CIMV2','WIN32_BIOS')

All working fine!



2.7 Collect to SQL Server all information with SQL CLR Assembly GetWmiInfo.

In finished step we will collecting all information about computer to table by script below.


   1:  CREATE TABLE [dbo].[XEON-prop](
   2:      [i] [int] IDENTITY(1,1) NOT NULL,
   3:      [Class] [nvarchar](50) NULL,
   4:      [Prop] [nvarchar](50) NULL,
   5:      [Val] [nvarchar](250) NULL,
   6:   CONSTRAINT [PK_XEON-prop] PRIMARY KEY CLUSTERED 
   7:  (
   8:      [i] ASC
   9:  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  10:  ) ON [PRIMARY]
  11:  GO

   1:  truncate table [tst1].[dbo].[XEON-prop]
   2:  DECLARE @ONE nvarchar(50)
   3:  DECLARE All_WMI_CLASS CURSOR  
   4:      FOR SELECT [NAME] FROM [dbo].[Win32]
   5:   
   6:  OPEN All_WMI_CLASS 
   7:   
   8:  FETCH NEXT FROM All_WMI_CLASS INTO @ONE
   9:  WHILE @@FETCH_STATUS = 0  BEGIN  
  10:   
  11:      Insert [tst1].[dbo].[XEON-prop]([Class],[Prop],[Val])
  12:      SELECT RTRIM(@ONE),* FROM [dbo].[GetWmiInfo]('\\XEON\ROOT\CIMV2',RTRIM(@ONE))
  13:      FETCH NEXT FROM All_WMI_CLASS INTO @ONE
  14:  END
  15:   
  16:  CLOSE All_WMI_CLASS
  17:  DEALLOCATE All_WMI_CLASS

This script had working in my computer about one hour and as result I have more many million records with information about my computer.



2.8 Process information about installed program, collection by GetWmiInfo

Request below collect more than millions records directly to SQL Server.


   1:  select * from [dbo].[GetWmiInfo]('\\XEON\ROOT\CIMV2','Win32_SoftwareElement')


But how to process it and getting useful info from this bid data?

This is a best way to process this big data:


   1:  SELECT  distinct ROW_NUMBER() OVER (Order by M.Val, P.Val,N.Val) as I, N.Val as [Name], P.Val as [Path], M.Val as [Manufacturer], convert(datetime, left(D.val,8), 111) as [InstallDate]
   2:    FROM [tst1].[dbo].[XEON-prop] As N
   3:    join [tst1].[dbo].[XEON-prop] as P On N.i+2=P.I 
   4:    join [tst1].[dbo].[XEON-prop] as M On N.i-1=M.I
   5:    join [tst1].[dbo].[XEON-prop] as D On N.i-4=D.I
   6:    where N.Prop='Name' and P.Prop='Path' and M.Prop='Manufacturer' and D.Prop='InstallDate'
   7:    and not M.Val like 'Microsoft%'


As a result this is a list of my NonMicrosoft SoftwareElement in my computer XEON, Microsoft list more than 20 times bigger NonMicrosoft SoftwareElement in my computer XEON. (Both this list is updated in 2017).

2.9 Getting information from one class WMI by Powershell or WMIC

We may process information about one interesting class by powershell or WMIC and after that processing this plain text by the way in 3.3 Collect information about installed program directly from Registry by PowerShel.


   1:  wmic /output:F:\programlist.txt product get name, version
   2:  Get-WmiObject -Class Win32_Product


But this is not interesting way, if you use my SQL CLR Assembly


   1:  select * from [dbo].[GetWmiInfo]('\\XEON\ROOT\CIMV2','Win32_SoftwareElement')

because it store 1,5 millions of records about your software DIRECTLY TO SQL SERVER.



3. Directly inspection of windows registry.

3.1 Observing by registry editor.

Registry with important information about installed program may export tp standard plain text (".INI"-format) by building GUI Regedit.exe. Also there are many third party utilities to export registry.



3.2 Observing registry programmatically through VB-wrapper "My"

If you want process data from registry the best way is use VB.NET extension library "MY", details pattern please see in WinDump - снимок состояния системы с помощью WMI.



3.3 Collect information about installed program directly from Registry by PowerShell

In this section I show the simplest way to build list of installed program and below I publish lists of programs from some of my working computers. As first step need to upload registry key to plain text file. There are many ways to do this task, one of the way is use PowerShell. Type to cmd.exe "Powershell" and after that type this command:


Get-ItemProperty HKLM:\Software\Wow6432node\Microsoft\Windows\CurrentVersion\Uninstall\* | Select-Object DisplayName, DisplayVersion, Publisher, Size, InstallDate, UninstallString > F:\Installed-program.txt

After that you may proceed second step - to uploading this plain text to SQL DB. There are many way to do this task, for example using internal DB for OpenOffice, but I usually use MS SQL. The simplest way to upload plain text to DB is MS SQL Export/Import wizard. In this wizard need only select a source file as plain text, a destination MS SQL Server and the length of field (need set at least 500 chars). Db need to create manually before import data.



And last step is processing data in SQL server. It contains two substeps. (1) Transfer data from raw table to table with key, with filtering and split data to two column. In strong sound this operation is incorrect because imported rows after importing not has rowindex, and by strong way need firstly add from beginning of each row key with row number. But in practically SQL server not mix uploaded rows.

This is raw data, uploaded by wizard:



And this is first processing of data.


   1:  CREATE TABLE [dbo].[X22](
   2:      [i] [int] IDENTITY(1,1) NOT NULL,
   3:      [Name] [nvarchar](50) NULL,
   4:      [Val] [nvarchar](500) NULL,
   5:   CONSTRAINT [PK_X22] PRIMARY KEY CLUSTERED 
   6:  (
   7:      [i] ASC
   8:  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   9:  ) ON [PRIMARY]
  10:  GO
  11:   
  12:  Insert XEON
  13:  select  left([Column 0],15), SUBSTRING([Column 0] ,18,len([Column 0])-17) from [dbo].[Xeon-Installed-program]
  14:  where Len([Column 0])>16

After this step data is strongly structured by SQL rules.



And from this point we have opportunity to any create any list of our installed program, but firstly we must reverse rows to columns with considering order of row repeating. In common, reverse from row to column is most wide task of SQL server and I wrote many posts about this task, for example Выполняем разворот строк в столбцы в MS SQL и PostgreSQL. This is only one interesting technique to use MS SQL Server, if you have interest to MS SQL Server read my article about recursive CTE procedures Хранение в MS SQL сетевой топологии и отборы маршрутов рекурсивными CTE-процедурами..

But in this task our SQL command to reverse row to column will be simple:


   1:  select  A.VAL as DisplayName,
   2:  B.VAl as DisplayVersion,
   3:  C.VAL as Publisher,
   4:  D.VAL as Size,
   5:  E.VAL as InstallDate,
   6:  F.VAL as UninstallString        
   7:    from [tst1].[dbo].[X22] as A 
   8:    join  [tst1].[dbo].[X22] as B on A.I+1=B.I and B.Name='DisplayVersion'
   9:    join  [tst1].[dbo].[X22] as C on A.I+2=C.I and C.Name='Publisher'
  10:    join  [tst1].[dbo].[X22] as D on A.I+3=D.I and D.Name='Size'
  11:    join  [tst1].[dbo].[X22] as E on A.I+4=E.I and E.Name='InstallDate'
  12:    join  [tst1].[dbo].[X22] as F on A.I+5=F.I and F.Name='UninstallString'
  13:    where A.Name='DisplayName' 
  14:    and not (len(A.VAL)=0 and len(B.VAl)=0 and len(C.VAL)=0 and len(D.val)=0 and len (E.VAL)=0 and len(F.VAL)=0)
  15:    and C.VAL <>' Microsoft Corporation'


Structured data is possible to processing by any way, for example this SQL script creates table for publishing list of installed program in your system to html.


   1:    drop table #tmp1
   2:  create table #tmp1(
   3:  [i] [int] IDENTITY(1,1) NOT NULL,  
   4:  DisplayName Nvarchar(200), 
   5:  DisplayVersion Nvarchar(200),
   6:  Publisher Nvarchar(200),
   7:  Size Nvarchar(200),
   8:  InstallDate Nvarchar(200),
   9:  UninstallString Nvarchar(200)
  10:  )
  11:  ;
  12:  Insert  #tmp1
  13:  select  A.VAL as DisplayName,
  14:  B.VAl as DisplayVersion,
  15:  C.VAL as Publisher,
  16:  D.VAL as Size,
  17:  E.VAL as InstallDate,
  18:  F.VAL as UninstallString        
  19:    from [tst1].[dbo].[X22] as A 
  20:    join  [tst1].[dbo].[X22] as B on A.I+1=B.I and B.Name='DisplayVersion'
  21:    join  [tst1].[dbo].[X22] as C on A.I+2=C.I and C.Name='Publisher'
  22:    join  [tst1].[dbo].[X22] as D on A.I+3=D.I and D.Name='Size'
  23:    join  [tst1].[dbo].[X22] as E on A.I+4=E.I and E.Name='InstallDate'
  24:    join  [tst1].[dbo].[X22] as F on A.I+5=F.I and F.Name='UninstallString'
  25:    where A.Name='DisplayName' 
  26:    and not (len(A.VAL)=0 and len(B.VAl)=0 and len(C.VAL)=0 and len(D.val)=0 and len (E.VAL)=0 and len(F.VAL)=0)
  27:    and C.VAL <>' Microsoft Corporation'
  28:    order by DisplayName
  29:    ;
  30:    select  '<tr><td>' + str(i)
  31:    + '</td><td>'+  DisplayName 
  32:    + '</td><td>'+ DisplayVersion
  33:    + '</td><td>'+ Publisher 
  34:    + '</td><td>'+ Size
  35:    + '</td><td>'+ InstallDate 
  36:    + '</td><td>'+ UninstallString  
  37:    + '</td></tr>'      
  38:   from #tmp1
  39:   order by i

This script return table for installed program in my computer, that I named X22 or XEON or Black. Also for fast installation in new place I stored in my site list of main program of my working environment (created by the same way), for example - (2013 years) My working environment in Burgas or Моя девелоперская среда 2006.





Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23
Link to this page: //www.vb-net.com/ListOfInstalledProgram/index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>