Fill factor explained

The Fill Factor specifies the % of fullness of the leaf level pages of an index. When an index is created or rebuilt the leaf level pages are written to the level where the pages are filled up to the fill factor value and the remainder of the page is left blank for future usage. This is the case when a value other than 0 or 100 is specified. For example, if a fill factor value of 70 is chosen, the index pages are all written with the pages being 70 % full, leaving 30 % of space for future usage.

You might choose a high fill factor value when there is expected little to no change on the underlying table. High fill factor value creates an index smaller in size and the queries on the underyling table can retrieve data with less disk I/O operations since there are less pages to read. But if you have an index that changes frequently, you want to have a lower value to keep some free space available for the new index entries. otherwise, if fill factor is large and there is little free space in the indexes, SQL Server would have to do a lot of page splits to fit the new values into the index pages.

the lower fill factor of your index, the larger the index size. if we take a fill factor of 50% increases index size to about 2 times the size of an index with fill factor of 100%.

With new data added to the table, the index page needs to have sufficient space to take the new entries. Where there is not enough space, a page split needs to take place, therefore impacting the performance.

Powershell and Active Directory: Moving user to another OU

Given a username passed as a parameter to the script, the code below will find the specified user in AD and move to the specified OU

$UserName = $args[0]

$root=[ADSI]''

$searcher = new-object System.DirectoryServices.DirectorySearcher($root)

$searcher.filter = "(&(objectClass=user)(sAMAccountName= $UserName))"

$User = $searcher.findone()

# Binding the user account to $AUser and the OU to move to to $MovetoOU
Write-Host $User.Properties.adspath

$ADSPath = $User.Properties.adspath

$MoveToOU = [ADSI]("LDAP://OU=NewOU,DC=mydomain,DC=com")

$AUser = [ADSI]("$ADSPath")


# Command to Do the actual move
$AUser.PSBase.moveto($MoveToOU)

Running PowerShell script with parameters

When running powershell from a command line and passing it one or more parameters, parameters passed in should be separated by a space and if any of the parameters contain spaces, they should be wrapped in double quotes:

C:\> .\MyPScript.ps1 "this is foirst parameter" "this is second"

And when accessing command line arguments from within the script, use args array with subscripts:

$param1=args[0]
$param2=args[1]

AD Security Groups and Creating Folders on a Share with Powershell

The below script is used to loop through all security groups in a particuler OU in Active Directory and create a folder on the share for each group and then, inside that folder, create a separate folder for each member of that group.

function CreateSubdirectory($path)
{
 New-Item -ItemType Directory -Path $path

}


foreach ($group in get-adgroup -searchbase "OU=MyOU,DC=mydomain,DC=com" -filter *){
  
 #create a subfolder $group.name
 $sharepath="\\SERVER\SHARE" + $group.name
 CreateSubdirectory $sharepath

 foreach ($member in ($group.member | sort)) 
 {
  #create a folder for each memeber $member.name
  CreateSubdirectory $sharepath + $member.name

 }
 }

Moving AD User to another OU using different credentials

In order to move Active Directory User to another OU you will need a service account with priviledges to move AD objects. Below is VBScript that worked for me:

Const ADS_SECURE_AUTHENTICATION = 1
sADDomain = "mydomain"
sADUser = "serviceuser"
sADPassword = "mypassword"

sDestOU = "LDAP://OU=My Group,DC=mydomain,DC=net"
Set objRootDSE = GetObject("LDAP:")
Set objSysInfo = CreateObject("ADSystemInfo")
Set objDestOU = objRootDSE.OpenDSObject(sDestOU, sADDomain & "\" & sADUser, sADPassword, ADS_SECURE_AUTHENTICATION)

Set objUser = objRootDSE.OpenDSObject("LDAP://" & objSysInfo.UserName, sADDomain & "\" & sADUser, sADPassword, ADS_SECURE_AUTHENTICATION)

objDestOU.MoveHere "LDAP://" & objUser.distinguishedName, vbNullString
If Err.Number = 0 Then
 MsgBox "User moved successfully."
Else
 MsgBox "Error " & Err.Number & ": " & Err.Description
 Err.Clear
End If

Running Powershell script with spaces in the name from VBScript

Running Powershell script from VBScript is rather simple:

Set objShell=CreateObject("Wscript.Shell)
sCmd="C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe C:\test.ps1"
objShell.Run sCmd, 1, true

But what to do when the Powershell script file has spaces in the name, like C:\My Path\test.ps1?

Theanswer is to use ampersand and single quotes:

Set objShell=CreateObject("Wscript.Shell)
sCmd="C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe " & Chr(34) & "& 'C:\script with spaces.ps1'"  & Chr(34) 
objShell.Run sCmd, 1, true

or

Set objShell=CreateObject("Wscript.Shell)
sPSPath="C:\script with spaces.ps1"
sCmd="C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe " & Chr(34) & "& '" & sPSPath & "'"  & Chr(34) 
objShell.Run sCmd, 1, true

Active Directory container vs. organizational unit

Active directory container is a built-in container that comes with AD and they cannot be altered to suit your organizational needs, without altering the AD schema. (Examples: Computers and Users containers).

An OU or Organizational Unit is a special purpose container created by the user and to which administrators can apply group policy. Group policy cannot be applied to a container. An organizational unit is a subdivision within an Active Directory into which you can place users, groups, computers, and other organizational units. You can create organizational units to suit your organization's functional or business structure. Each domain can implement its own organizational unit hierarchy.

Add an array value to the Registry using Powershell

The code below initializes array $a and creates a key named "MyArray" of type REG_MULTI_SZ in registry path HKEY_CURRENT_USER\Control Panel\Desktop\Test and sets its value to an array

$a = ("val1", "val2", "val3")

$RegPath= "Registry::HKEY_CURRENT_USER\Control Panel\Desktop\Test"

Set-itemProperty $RegPath -name "myArray" -value $a -type MultiString

Generating comma separated lists with SELECT statement

I needed to retrieve a two column data set from a database where the first column would contain the last name of the author and second column - a comma separated list of all the book titles for that particular author.

It was rather simple to generate a list of author ids and list of title ids usign SUBSTRING function and FOR XML

SELECT a.au_id, 
SUBSTRING(  (  SELECT ( ',' + title_id)  
FROM titleauthor ta   
WHERE a.au_id = ta.au_id  
ORDER BY a.au_id, ta.au_id  FOR XML PATH('') ), 3, 1000)
FROM authors a
GROUP BY a.au_id

To generate a list of actual full names and titles, all i had to do is add a join to titles table:

SELECT a.au_lname + ', ' + a.au_fname, SUBSTRING(  (  SELECT ( ',' + '''' + t.title + '''')  
FROM titles t
INNER JOIN titleauthor ta ON t.title_id=ta.title_id  
WHERE a.au_id = ta.au_id  
ORDER BY  a.au_lname + ', ' + a.au_fname  FOR XML PATH('') ), 2, 1000)
FROM authors a
GROUP BY a.au_id,a.au_lname + ', ' + a.au_fname

Strange occurrence when returning Datatable from a function

I was trying to retrieve some data from a SQL Server database using Powershel using the following function:

function GetLocationData($Name)
{
$SqlConnection = New-Object system.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=ServerName;Database=MyDB;User Id=dbuser;Password=pwd;"

$SqlCommandText = "SELECT Field1, Field2 FROM TableName WHERE [Name]='" + $Name + "'"
$SqlConnection.Open()

$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$Reader = $SqlCmd.ExecuteReader()

$Datatable = New-Object System.Data.DataTable 
$DataTable.Load($Reader)  

# Close the database connection 
$sqlConnection.Close() 

return $Datatable
}

When I would call it from my script

$dt = New-Object System.Data.DataTable
$dt=GetLocationData $item.name 
Write-Host "row count is " +  $dt.Rows.Count

It would not return anything and if I tried accessing a field

Write-Host $dt.Rows[0].Field1

I would get an error "Cannot index into null array"


The issue is that function would be returning a child collection of the table object instead of the table itself. What helped me is using the comma operator to wrap the rows collection in an array so that when the array is unrolled leaving me with the original rows collection.

So all I had to do to fix this issue is put a comma in the return statement right before datatable object:

function GetLocationData($Name)
{
$SqlConnection = New-Object system.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=ServerName;Database=MyDB;User Id=dbuser;Password=pwd;"


$SqlCommandText = "SELECT Field1, Field2 FROM TableName WHERE [Name]='" + $Name + "'"
$SqlConnection.Open()

$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$Reader = $SqlCmd.ExecuteReader()

$Datatable = New-Object System.Data.DataTable 
$DataTable.Load($Reader)  

# Close the database connection 
$sqlConnection.Close() 

return ,$Datatable
}

Windows Live Essentials 2011 uninstall from Windows 7

I have been struggling with creating an uninstall package for Windows Live Essentials 2011 on Windows 7 platform.

Finally I found a list of uninstallers that need to be run in order to clear out all of Windows Live Essentials:

Live Essentials {FE044230-9CA5-43F7-9B58-5AC5A28A1F33}
Microsoft SQL Server 2005 Compact Edition {F0B430D1-B6AA-473D-9B06-AA3DD01FD0B8}
D3DX10    Microsoft   {E09C4DB7-630C-4F06-A631-8EA7239923AF}
Live Remote Service   {E02A6548-6FDE-40E2-8ED9-119D7D7E641F}
Live Remote Client    {DF6D988A-EEA0-4277-AAB8-158E086E439B}
Live Writer Resources {DDC8BDEE-DCAC-404D-8257-3E8D4B782467}
Live MIME IFilter     {DA54F80E-261C-41A2-A855-549A144F2F59}
Live Communications Platform {D45240D3-B6B3-4FF9-B243-54ECE3E10066}
Live Photo Common     {D436F577-1695-4D2F-8B44-AC76C99E0002}
MSVCRT_amd64    Microsoft  {D0B44725-3666-492D-BEF6-587A14BD9BD9}
Live UX Platform      {CE95A79E-E4FC-4FFF-8A75-29F04B942FF2}
Live Mail    {C66824E4-CBB3-4851-BB3F-E8CFD6350923}
Live Writer  {AAAFC670-569B-4A2F-82B4-42945E0DE3EF}
Live Mesh    {A0C91188-C88F-4E86-93E6-CD7C9A266649}
Live Movie Maker  {92EA4134-10D1-418A-91E1-5A0453131A38}
MSVCRT    Microsoft  {8DD46C6A-0056-4FEC-B70A-28BB16A1F11F}
Mesh Runtime  {8C6D6116-B724-4810-8F2D-D047E6B7D68E}
Microsoft Silverlight  {89F4137D-6C26-4A84-BDB8-2E5A4BB71E00}
Live Remote Client Resources  {847B0532-55E3-4AAF-8D7B-E3A1A7CD17E5}
Live Messenger {80956555-A512-4190-9CAD-B000C36D6B6B}
Live Messenger Companion Core  {78A96B4C-A643-4D0F-98C2-A8E16A6669F9}
Live UX Platform Language Pack {6A05FEDF-662E-46BF-8A25-010E3F1C9C69}
Live SOXE  {682B3E4F-696A-42DE-A41C-4C07EA1678B4}
Bing Bar Platform   {65C0025A-2CDE-43C5-82D0-C7A56EF0DB39}
Live Remote Service Resources2   {656DEEDE-F6AC-47CA-A568-A1B4E34B5760}
Live Language Selector  {5EB6F3CB-46F4-451F-A028-7F6D8D35D7D0}
Messenger Companion   {50816F92-1652-4A7C-B9BC-48F682742C4B}
Live PIMT Platform   {4CBABDFD-49F8-47FD-BE7D-ECDE7270525A}
Live Family Safety   {46A5FBE9-ADB3-4493-A1CC-B4CFFD24D26A}
Live Photo Gallery   {34F4D9A4-42C2-4348-BEF4-E553C84549E7}
Live Mesh ActiveX Control for Remote Connections {2902F983-B4C1-44BA-B85D-5C6D52E2C441}
Live SOXE Definitions  {200FEC62-3C34-4D60-9CE8-EC372E01C08F}
Junk Mail filter update {1F6AB0E7-8CDD-4B93-8A23-AA9EB2FEFCE4}
Live ID Sign-in Assistant  {1B8ABA62-74F0-47ED-B18C-A43128E591B8}
Live Installer   {0B0F231F-CE6A-483D-AA23-77B364F75917}
Bing Bar   {08234a0d-cf39-4dca-99f0-0c5cb496da81}

I also needed to package separately Windows Live Photo gallery and Movie Maker and create an uninstall just for those two:

Live Essentials {FE044230-9CA5-43F7-9B58-5AC5A28A1F33}
Microsoft SQL Server 2005 Compact Edition {F0B430D1-B6AA-473D-9B06-AA3DD01FD0B8}
D3DX10    Microsoft  {E09C4DB7-630C-4F06-A631-8EA7239923AF}
Live Communications Platform  {D45240D3-B6B3-4FF9-B243-54ECE3E10066}
Live Photo Common {D436F577-1695-4D2F-8B44-AC76C99E0002}
MSVCRT_amd64 Microsoft  {D0B44725-3666-492D-BEF6-587A14BD9BD9}
Live UX Platform  {CE95A79E-E4FC-4FFF-8A75-29F04B942FF2}
Live Movie Maker  {92EA4134-10D1-418A-91E1-5A0453131A38}
MSVCRT    Microsoft {8DD46C6A-0056-4FEC-B70A-28BB16A1F11F}
Live UX Platform Language Pack {6A05FEDF-662E-46BF-8A25-010E3F1C9C69}
Live SOXE {682B3E4F-696A-42DE-A41C-4C07EA1678B4}
Live PIMT Platform {4CBABDFD-49F8-47FD-BE7D-ECDE7270525A}
Live Photo Gallery {34F4D9A4-42C2-4348-BEF4-E553C84549E7}
Live SOXE Definitions {200FEC62-3C34-4D60-9CE8-EC372E01C08F}
Live ID Sign-in Assistant  {1B8ABA62-74F0-47ED-B18C-A43128E591B8}
Live Installer {0B0F231F-CE6A-483D-AA23-77B364F75917}

As it turned out the above was not enough to uninstall Photo Gallery, I needed two more Product GUIDs, one for PhotGallery and one for Photo Common, so in total the were 2 GUIDs for Photo Gallery and 2 GUIDs for Photo Common. Below are the extra GUIDs:

Live Photo Gallery {3336F667-9049-4D46-98B6-4C743EEBC5B1}
Live Photo Common {A9BDCA6B-3653-467B-AC83-94367DA3BFE3}

Conditional T-SQL using CASE WHEN

Let's say you need to retrieve data from one table and whether or not each record contains data in the second table, and instead of data from the second table you want to display "yes" or "No". Your best bet is to use CASE WHEN in your SELECT:

SELECT DISTINCT c.CustomerID as [Customer ID], c.CompanyName as [Company Name], 
CAST(CASE WHEN o.CustomerID IS NULL THEN 'No' ELSE 'Yes' END as varchar) as [Has Orders]  
FROM Customers as c 
LEFT JOIN Orders as o ON c.CustomerID=o.CustomerID

The resulting data would look like this:

Customer ID	Company Name	Has Orders
OCEAN	Océano Atlántico Ltda.	Yes
OLDWO	Old World Delicatessen	Yes
OTTIK	Ottilies Käseladen	Yes
PARIS	Paris spécialités	No
PERIC	Pericles Comidas clásicas	Yes

How to select a comma separated list and use it in WHERE IN clause

First create a user-defined function that retrieves a comma separated list of IDs from one table using COALESCE built-in function, sort of like I did here

How to combine several rows in one row, separated by commas

CREATE FUNCTION [dbo].[GetListOfIDs] ( )
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @RES VARCHAR(8000)
  SELECT @RES = COALESCE(@RES + ',','') + ID
  FROM Table1
  RETURN (@RES)
END

Then create a stored procedure that will retrieve all the data from another table, based on input list:

CREATE PROCEDURE [dbo].[usp_GetAllData]
@list varchar(500)
as
begin
declare @SQL varchar(600)
set @SQL = 'SELECT * FROM TABLE2 WHERE ID IN ('+ @list +')'
EXEC(@SQL)
end       

Then you will need one more function to tie it all together - use user-defined function to retrieve the list of IDs and then execute stored procedure and pass it the list:

CREATE PROCEDURE usp_RunProcedure
AS
BEGIN
DECLARE @ids varchar(600)  
SELECT @ids = dbo.GetListOfIDs()

EXEC dbo.usp_GetAllData @list=@ids
END

SQL Server 2005 Agent XPs disabled issue

Today when I got to work I got an e-mail sent to me through Database mail, notifying me that a number of SQL scheduled jobs failed. That's the message that I found in the logs:

[298] SQLServer Error: 15281, SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

When I logged in to database server, I saw that next to SQL Server Agent there is a note (Agent XPs disabled).

Agent XPs is an option to enable the SQL Server Agent extended stored procedures on this server. When this option is not enabled, the SQL Server Agent node is not available in SQL Server Management Studio Object Explorer.


The possible values are:

0, indicating that SQL Server Agent extended stored procedures are not available (the default).

1, indicating that SQL Server Agent extended stored procedures are available.


This setting can be changed without server stop or restart.

Aparently something happened, probably related to the Security policy, that changed the option from 1 to 0. So to resolve the issue I ran the code below to re-enable Agent XPs option by setting it back to 1.

USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Agent XPs', 1
GO
RECONFIGURE
GO

Another option is to re-enable it through SQL Server Surface Area Configuration tool.

Changing desktop wallpaper on Windows 7 programmatically

All the programmatic wallpaper changes from previous versions of Windows do not work on either Windows 7 or Vista. I have tried many different ways and many technologies untill i found a way that worked on Windows XP, Windows Vista, and Windows 7.

I created a dll using C#. Here is the C# code for it:

[DllImport("user32.dll", CharSet = CharSet.Auto)]
private static extern Int32 SystemParametersInfo(UInt32 action, UInt32 uParam, String vParam, UInt32 winIni);
private static readonly UInt32 SPI_SETDESKWALLPAPER = 0x14;
private static readonly UInt32 SPIF_UPDATEINIFILE = 0x01;
private static readonly UInt32 SPIF_SENDWININICHANGE = 0x02;

public void SetWallpaper(string path, ref string err)
{
    try
    {
        if (File.Exists(path))
        {
            SystemParametersInfo(SPI_SETDESKWALLPAPER, 3, path, SPIF_UPDATEINIFILE | SPIF_SENDWININICHANGE);
             err = string.Empty;

         }
         else
         {
              throw new Exception("Path '" + path + "' does not exist!");
         }
      }
      catch (Exception ex)
      {
           err = ex.Message;
      }

}

The function above accepts a file path of a wallpaper image as an argument along with a variable passed by reference that will return an error message in case of a failure. An empty string will be returned in case of a success.

Find all tables containing particular column

There are two ways to retrieve all the tables containing a particuler column

1) using INFORMATION_SCHEMA

SELECT * FROM INFORMATION_SCHEMA.COLUMNS As i WHERE i.column_name LIKE '%MyField%'

2) using SYS.TABLES

SELECT * FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%MyField%'

If you know the exact column name, then use = instead of LIKE to reduce the number of unrelated rows return

Displaying data by hour

I needed to display record count hour by hour for a 24 hour period, displaying just the hour and record count for that hour, without the date part but ordered not from 0 to 23 but from current hour, i.e. if it is currently 9:28am, then the data should be displayed from 9am today up to 8am tomorrow. Here is what I came up with:

SELECT CONVERT(VARCHAR(8),MyDateField,101) + ' ' + CONVERT(VARCHAR(2),[MyDateField],108) as TDate,    
DATEPART(hh,MyDateField) AS Hour,
COUNT(*) AS NumberOfRecordsPerHour    
FROM MyTable
WHERE DATEDIFF(hh, MyDateField, GETDATE())<= 24  
GROUP BY CONVERT(VARCHAR(8),MyDateField,101) + ' ' + CONVERT(VARCHAR(2),MyDateField,108), DATEPART(hh,MyDateField)  
ORDER BY TDate ASC