Saturday, December 31, 2011

Exporting All Installed Applications in Windows

When I helped someone to sell her laptop on internet I needed first to look at what applications were installed in this Windows 7. But, having this list in a file was not an easy task, therefore I searched on internet to get a way to do this.

Finally, I used at that moment the command line "wmic" which also allows you to export this list in few formats, always remember first to open your command line window and type the command as shown in the image.


As you can see it's very simple just don't forget to specify the destination path and the format you want your output file (the options are: HTML, Table, XML and more). Once you got your file open it and you will see something like this.


If you want to have this file in a more comfortable scheme just do what is said here and it will look like this.



Ready to be used.


Friday, December 30, 2011

Problem Copying Large Path Files

Some time ago I saw one problem as a consequence of a project, this consisted on moving all directories from one network device to an external drive as a backup policy. But, during the copying process happened problems with certain directories and files, an unknown error actually.

However, I got the impression that this problem could be produced by the large length of the paths of this directories or files and yes, that was the reason.

As a proof of this, I created a large path for a directory that just contains one text file, as you can see "D:\Large Named Directory" has a lot of similar inner directories, having the last one the "Large Named Textfile.txt" file. Pay attention, because it's not possible to create more directories inside the last one, because Windows has a limit... like everything in life.

Now, the image showed before represents the directories in the network drive, and we are going to copy them to a new directory located in the "local drive" which is represented by "D:\Another Large Directory to Copy Directory" directory, but when we do this simple procedure, we get this error message.


All of this because the file in the first location had a full path with less than 256 characters, however, in the new location it would have needed a full path with more than 256 characters which is not allowed by Windows. So, what you have to do in this situation is changing the path directory destination to the shortest name to not exceed the 256 path length limit.

 

Thursday, December 29, 2011

Speeding Up your Queries with XPath

Finally I'm posting again, the time has been very tough with me... but I'm here again with one subject that I was thinking to post long time ago.

This is something that I found really curious when I was helping someone, this person asked me why in certain part of an stored procedure the programmer used the text() function to improve its execution time.

Actually, I didn't have idea at that moment why that function gave that kind of benefit, I mean, we had this:


CREATE PROCEDURE [dbo].[SP_Test]
@xml xml
AS

Insert   Into dbo.temp_table

Select   Id1 =          D.Item.value('Id1[1]','bigint'),
         Id2 =          D.Item.value('Id2[1]','int'),
         Text1 =        D.Item.value('Text1[1]','varchar(50)'),
         Text2 =        D.Item.value('Text2[1]','varchar(50)'),
         Id3 =          D.Item.value('Id3[1]','int'),
         Text3 =        D.Item.value('Text3[1]','int'),
         Text4 =        D.Item.value('Text4[1]','int'),
         Text5 =        D.Item.value('Text5[1]','int'),
         Id4 =          D.Item.value('Id4[1]','varchar(10)')    
From     @xml.nodes('/Node1/Node2') as D(Item)

And this procedure was changed to this:

CREATE PROCEDURE [dbo].[SP_Test]
@xml xml
AS

Insert   Into dbo.temp_table

Select   Id1 =          D.Item.value('(Id1/text())[1]','bigint'),
         Id2 =          D.Item.value('(Id2/text())[1]','int'),
         Text1 =        D.Item.value('(Text1/text())[1]','varchar(50)'),
         Text2 =        D.Item.value('(Text2/text())[1]','varchar(50)'),
         Id3 =          D.Item.value('(Id3/text())[1]','int'),
         Text3 =        D.Item.value('(Text3/text())[1]','int'),
         Text4 =        D.Item.value('(Text4/text())[1]','int'),
         Text5 =        D.Item.value('(Text5/text())[1]','int'),
         Id4 =          D.Item.value('(Id4/text())[1]','varchar(10)')    
From     @xml.nodes('/Node1/Node2') as D(Item)

What did we get using text() function? Well, we got an execution time of 5 seconds instead of the original 2 minutes and 23 seconds for getting text values from XML. Pretty cool right?.

Doing some research I realized that using text() function is also using XPath instead of using the UDX (extended operator) query functions that Microsoft SQL Server has. If you look at the execution plan of the query you will see that not using XPath functions makes you use more UDX functions that could hit considerably the performance.