MS SQL Northwind & Pub databases | ‘sp_dboption’

Hi guys

Today I am going to upload two SQL scripts using which you could build Northwind & Pub databases against latest versions of MS SQL Server. The latest tested at my end is SQL Server 2014

I have packaged these scripts to avoid the error “Could not find stored procedure ‘sp_dboption’.” error that arises when Microsoft provided SQL scripts are executed against latest database technologies.

You may download the SQL scripts from this link

MD5: 5831113f5ec890986312d2ed532a13e2

SHA-1: 57484de9bea37c5e0b8ba84454e8093612d96df6

regards,

rajesh

C# A folder sync tool

Hi guys!

I’ve been busy with so many developments recently. Few projects for Oracle E-Business Suite R12 & few others for learning C#. I can say I’ve “learned” how to copy, paste, correct and use it with my projects to produce beautiful results!

My recent C# projects include a WPF solution that uses WMI classes to generate hardware details for entire Windows Domain (Linux boxes are not dealt) & today I’ve decided to build a small solution that could synchronize my C# projects with our file server.

This small project can,

  • Replicate source Directory, subdirectories & files to destination folder
  • While re-ran, check whether the destination files are older than the source files and, if yes, overwrite the old ones with newer ones.

Interested to try it yourself? here comes the code

(This project uses codes copied from both MSDN & http://www.stackoverflow.com. RecursiveFileProcessor is a MSDN example, while File exists snippet was copied from stackoverflow)

[code language=”csharp” gutter=”false”]
using System;
using System.IO;
using System.Collections;

//http://stackoverflow.com/questions/58744/copy-the-entire-contents-of-a-directory-in-c-sharp
//

namespace RecursiveFileProcessor
{
class Program
{
public static void Main(string[] args)
{
string sourceDirectory = @"D:\MyProjects";
string targetDirectory = @"\\network_share_location\MyProjects";
Copy(sourceDirectory, targetDirectory);
Console.WriteLine();
Console.WriteLine("Task Completed. Press any key to continue");
Console.ReadLine();

}

public static void Copy(string sourceDirectory, string targetDirectory)
{
DirectoryInfo diSource = new DirectoryInfo(sourceDirectory);
DirectoryInfo diTarget = new DirectoryInfo(targetDirectory);

CopyAll(diSource, diTarget);
}

public static void CopyAll(DirectoryInfo source, DirectoryInfo target)
{

Directory.CreateDirectory(target.FullName);

// Copy each file into the new directory.
foreach (FileInfo fi in source.GetFiles())
{

DateTime created = fi.CreationTime;
DateTime lastmodified = fi.LastWriteTime;

if (File.Exists(Path.Combine(target.FullName, fi.Name)))
{
string tFileName = Path.Combine(target.FullName, fi.Name);
FileInfo f2 = new FileInfo(tFileName);
DateTime lm = f2.LastWriteTime;
Console.WriteLine(@"File {0}\{1} Already Exist {2} last modified {3}", target.FullName, fi.Name, tFileName, lm);

try
{
if (lastmodified > lm)
{
Console.WriteLine(@"Source file {0}\{1} last modified {2} is newer than the target file {3}\{4} last modified {5}",
fi.DirectoryName, fi.Name, lastmodified.ToString(), target.FullName, fi.Name, lm.ToString());
fi.CopyTo(Path.Combine(target.FullName, fi.Name), true);
}
else
{
Console.WriteLine(@"Destination File {0}\{1} Skipped", target.FullName, fi.Name);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}

}
else
{
Console.WriteLine(@"Copying {0}\{1}", target.FullName, fi.Name);
fi.CopyTo(Path.Combine(target.FullName, fi.Name), true);
}

}

// Copy each subdirectory using recursion.
foreach (DirectoryInfo diSourceSubDir in source.GetDirectories())
{
DirectoryInfo nextTargetSubDir =
target.CreateSubdirectory(diSourceSubDir.Name);
CopyAll(diSourceSubDir, nextTargetSubDir);
}
}

}
}

[/code]

Thumb rules:

  1. Do not set your source and destination folders the same.

This project uses Framework 4.5 & I am sure versions above 2.0 should support the classes referred.

If you are new to .net developments, create a project with the name “RecursiveFileProcessor”, which will let you copy and paste the whole code as it is to your project.

Do let me know whether you enjoyed your new sync toy!

regards,

rajesh

C# (CSharp, C-Sharp) Windows Active Directory last logon

Hi guys

Please note, many threads were referred in order to compile the script attached below & all I did was re-arranging in order for better reading/formatting

(And I insure that the script works under following scenarios:

  1. You are a domain administrator
  2. You are administrating Windows 2003 onwards Windows domain
  3. Your puter is connected to the domain network ;)

)

Referenced websites/threads

  1. http://stackoverflow.com/questions/15775264/searching-for-lastlogon-attribute-of-user-in-multiple-domain-servers
  2. https://www.codeproject.com/kb/security/lastlogonacrossallwindows.aspx#_comments
  3. http://codebeautify.org/csharpviewer (for formatting the C# code)

How to test the code

Start Visual Studio (I am using 2013 Professional edition, you can use any of the community editions to test the scripts)

Create a new C# Console Application and name it llogon (else you need to change the namespace name “llogon” according to the name you have chosen for your new project.

Add the following references to your project

  1. “Framework -> System.DirectoryServices”
  2. “Browse and add -> C:\Windows\System32\activeds.tlb”

 

[code language=”csharp” gutter=”false”]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

//Added by Rajesh

//using System.Management;
//using System.Data;
using System.DirectoryServices;
using System.DirectoryServices.ActiveDirectory;
//http://stackoverflow.com/questions/33274162/the-namespace-of-iadslargeinteger
//ActiveDS namespace
using ActiveDs; // Namespace added via ref to C:\Windows\System32\activeds.tlb
//https://www.codeproject.com/kb/security/lastlogonacrossallwindows.aspx

namespace llogon
{
class Program
{
static void Main(string[] args)
{
// Get the root entry
DirectoryEntry rootDSE = new DirectoryEntry("LDAP://RootDSE");
string configurationNamingContext =
(string)rootDSE.Properties["configurationNamingContext"].Value;
string defaultNamingContext =
(string)rootDSE.Properties["defaultNamingContext"].Value;
Dictionary<string, Int64> lastLogons = new Dictionary<string, Int64>();
// Get all the domain controllers
DirectoryEntry deConfig = new DirectoryEntry("LDAP://" + configurationNamingContext);
DirectorySearcher dsConfig = new DirectorySearcher(deConfig);
dsConfig.Filter = "(objectClass=nTDSDSA)";
foreach (SearchResult srDomains in dsConfig.FindAll())
{
DirectoryEntry deDomain = srDomains.GetDirectoryEntry();
if (deDomain != null)
{
string dnsHostName =
deDomain.Parent.Properties["DNSHostName"].Value.ToString();
// Get all the users for that domain
DirectoryEntry deUsers =
new DirectoryEntry("LDAP://" + dnsHostName + "/" + defaultNamingContext);
DirectorySearcher dsUsers = new DirectorySearcher(deUsers);
dsUsers.Filter = "(&(objectCategory=person)(objectClass=user))";
foreach (SearchResult srUsers in dsUsers.FindAll())
{
DirectoryEntry deUser = srUsers.GetDirectoryEntry();
if (deUser != null)
{
// Get the distinguishedName and lastLogon for each user
// Save the most recent logon for each user in a Dictionary object
string distinguishedName = deUser.Properties["distinguishedName"].Value.ToString();
Int64 lastLogonThisServer = new Int64();
if (deUser.Properties["lastLogon"].Value != null)
{
IADsLargeInteger lgInt =
(IADsLargeInteger)deUser.Properties["lastLogon"].Value;
lastLogonThisServer = ((long)lgInt.HighPart << 32) + lgInt.LowPart;
}

// Save the most recent logon for each user in a Dictionary object
if (lastLogons.ContainsKey(distinguishedName))
{
if (lastLogons[distinguishedName] < lastLogonThisServer)
{
lastLogons[distinguishedName] = lastLogonThisServer;
}
}
else
{
lastLogons.Add(distinguishedName, lastLogonThisServer);
}
string readableLastLogon = DateTime.FromFileTime(lastLogonThisServer).ToString();
Console.WriteLine("User: " + distinguishedName + "Last logon: " +readableLastLogon);
}
}
Console.ReadLine();
}
}

}
}
}

[/code]

Try the code & if you are stuck somewhere, do let me through the comments. I am working on a WPF C# project for a simple Active Directory Reporter / Asset Management System using WMI. Stay tuned & I will soon post the entire solution here :)

regards,

rajesh

 

ASP.Net | Learn the basics of developing your first website using VB.Net or C#

You are here, seeking some information and we are more than happy to re-direct you to http://webproject.scottgu.com, a right place to start learning how to develop a website using Visual Studio IDE. Though the posts with scottgu.com mostly deal with VS 2005, almost everything they mentioned with their lessons are still applicable to the latest VS releases.
So give it a try, and you will be more than glad to visit one of the best beginners place.

regards,

rajesh

Visual Studio 2013 | HRESULT 0x80070002 exception

Scenario

A brand new installation of Visual Studio 2013

You are trying to create a new project/solution and immediately after choosing the type of the application (eg: Single page web application) an error raised saying referenced file cannot be found, followed by the error code HRESULT 0x80070002

Solution

Go to http://docs.nuget.org/consume/installing-nugget

Select the build of your Visual Studio NuGet client and move to the download page

Download the package (After closing down the Visual Studio, if yet open) and install the package

Restart Visual Studio and the error must have been gone by now.

Happy Programming guys ;)

regards,

rajesh

Teaser | Visual Studio | VB.Net & Oracle Database

t1

Okay, my last few posts were all about VB.Net & utilities for Windows domain administrators. Doesn’t mean that I stopped being a Oracle bouy ;) I still earn my salary for the Oracle job I do everyday at work and once after managing to purchase a legal copy of Visual Studio 2013 Professional Edition, I am committed to develop few sample applications using VB.Net & Oracle database.

So, stay tuned

regards,

rajesh

VB.Net | Appending an existing XML file

VB.Net | Appending an existing XML file

XML is widely used by .net oriented programming. It is vast and learning XML could be requiring loads of dedication. I needed to add a XML file to hold certain configuration details that could be read with one of the VB.Net projects currently I am busy with. This project deals with running Oracle VirtualBox VM(Virtual Machines) headless using a Windows Service as Local System so that multiple administrators could log in and log off from the Windows Server that hosts Virtual machines without closing down the machines abrupt (This project is ongoing, half cooked)

Without wasting more time, let us see how to append an “existing” XML file using VB.Net

Create a Windows Form Project
Create a XML file (MyVMS.xml) using your favorite text editing tool and copy the below

[code language=”xml” gutter=”false”]
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<VM>
<MACHINE>
<MACHINE_ID>1</MACHINE_ID>
<VM_NAME>MyVM</VM_NAME>
<VM_SHUTDOWNMODE>poweroff</VM_SHUTDOWNMODE>
</MACHINE>
</VM>
[/code]
and copy the XML file to the project root folder

Add the namespaces to your new project

[code language=”vb” gutter=”false”]
Imports System.Xml
Imports System.Data
[/code]

As I said in the beginning, we will be adding data to an “existing” XML file, hence the XML files must have a root element, for example

[code language=”xml” gutter=”false”]
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<VM>
</VM>
[/code]

Drag a button to your forms1.vb design view
Drag another button to your forms1.vb design view
Drag a DataGridView to your forms1.vb design view

Now we will try to add one more “MACHINE” node into the existing XML file using VB.Net code block

[code language=”vb” gutter=”false”]
Public Class Form1

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim xmldoc As New XMLDocument()
Try
xmldoc.Load("MyVMS.xml")
Dim e1 As XmlElement = xmldoc.CreateElement("MACHINE")
Dim e2 As XmlElement = xmldoc.CreateElement("MACHINE_ID")
e2.InnerText = "2"
Dim e3 As XmlElement = xmldoc.CreateElement("VM_NAME")
e3.InnerText = "Linux"
Dim e4 As XmlElement = xmldoc.CreateElement("VM_SHUTDOWNMODE")
e4.InnerText = "savestate"

e1.AppendChild(e2)
e1.AppendChild(e3)
e1.AppendChild(e4)

xmldoc.DocumentElement.AppendChild(e1)
xmldoc.Save("MyVMS.xml")

Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim xmlFile As XmlReader
xmlFile = XmlReader.Create("MyVMS.xml", New XmlReaderSettings())
Dim ds As New DataSet
ds.ReadXml(xmlFile)
DataGridView1.DataSource = ds.Tables(0)
DataGridView1.Columns(0).HeaderText = "Id"
DataGridView1.Columns(1).HeaderText = "VM Name"
DataGridView1.Columns(2).HeaderText = "Shutoff Mode"

End Sub
End Class
[/code]

Now, as you click on the first button, a new node for “MACHINE” will be inserted using the static information attached & by activating the second button, your data grid view should populate the MACHINE information already stored within the XML file.

Pretty simple & easy, right? You may rush ahead and say thanks to my dearest friend and ONE of the best .Net programmers Aziz Marafi / @azizane , who had coded majority of the block above for the “FileMyster” project.

Please post your comments and queries and I may learn few more things with you!

regards,

rajesh

VB.Net | Get IP address of a computer against hostname | Fun projects

Credits: http://stackoverflow.com/questions/19713868/returning-ipv6-instead-of-ipv4-in-vb

How to?

Open Visual Studio IDE (We are using VS 2012 Express and the .Net framework is 4.5 by default, hence if few things do not work at your setups, please cross check the specs we specified) and create a Windows form application. We’ll be mostly doing our samples using forms, a better mechanism to test stuffs against console applications (Minimum from our experiences)

drag a button to the form

drag a listbox to the form

& do not change any property for any of the above items

right click on the form and select view code, once the code page opens
Ctrl+A (Select all and delete)

copy and paste the below code block (replacing the original content of the code page)

[code language=”vb” gutter=”false” wraplines=”true”]
Imports System.Net

Public Class Form1
Public Function getip(ByVal lst As ListBox) As ListBox
Dim hostEntry = Dns.GetHostEntry("hostname")
Dim addressList As New List(Of IPAddress)

For Each address In hostEntry.AddressList
If address.AddressFamily = Sockets.AddressFamily.InterNetwork Then
lst.Items.Add(address)
End If
Next
Return lst

End Function

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
getip(ListBox1)
End Sub
End Class
[/code]

Now replace the “hostname” with some actual hostnames and execute the application by pressing F5. Click the button over the form you are done! Please note, this sample is restricted to fetch IPV4 addresses.

VB.Net | Active Directory | Windows Form Application for listing users & details in a grid view

Have you read our previous post about finding last logon date time value for a domain member computer or user yet? If not, please read about it here

Now, we are going to provide you a decent (Warning: Amateurish code blocks possible) Windows forms based application that could list all your domain users and few of their details including last logon date time values.

run

Above image demonstrates the run time interface of the application. Application has a search textbox, that could accept values before and after the user details are populated.

Before the population, the users will be limited to matching string, after population, the list could be refined against the combination of keystrokes

Mandatory References as seen with the below image

ref

You can download the solution from here

regards,

rajesh

VB.net | Active Directory | Get last logon for computer/user account

Ever wondered how to fetch the last logon details for a domain member computer or user? Using .Net Microsoft has made it pretty easier for the developers to populate the active directory attributes to desired data repositories, however getting the last logon date time value still remains a complex stuff (for beginners like us), especially when your domain consist of multiple domain controllers and they are spread across different geographical areas and subnets :)

After dwelling for long a while, I came across a C# code snippet @ http://www.codeproject.com/Articles/19181/Find-LastLogon-Across-All-Windows-Domain-Controlle that was mostly built against http://www.rlmueller.net/Last%20Logon.htm

I had to copy the codes those were split into multiple blocks for proper explanations, re-arrange and then using the online C# to VB.Net converters, convert to readable VB.Net coding. Again, there were few mismatches and I was able to figure them out through stackoverflow & tech forums posts.

Without going on with what and how I did it, here comes the complete coding. Please note, this is a console application solution, and you should add the references as seen with the below image in order to successfully call the methods and types

Ref

[code language=”vb” gutter=”false” wraplines=”true”]
Imports System.DirectoryServices
Imports ActiveDs

Module Module1

”’ &lt;summary&gt;
”’ You should able to easily convert this console application to windows form application
”’ with least efforts
”’ Original code was posted with http://www.codeproject.com/Articles/19181/Find-LastLogon-Across-All-Windows-Domain-Controlle
”’ using C#, I converted most of the C# Codes using online converter(s)
”’ and ammended at few places, as the code block provided with the project were not fetching decent results
”’ Rajesh Thampi / 15-Jan-2015 | w7bugs at gmail dot com | windows7bugs.wordpress.com
”’ &lt;/summary&gt;
”’ &lt;remarks&gt;&lt;/remarks&gt;
Sub Main()
‘Get the root of the directory data tree on a directory server.
Dim rootDse As New DirectoryEntry(&quot;LDAP://rootDSE&quot;)
‘Dictionary object to hold the records retrived by the search
Dim lastLogons As Dictionary(Of String, Int64) = New Dictionary(Of String, Int64)
‘Local variable for holding formatted last logon values in datetime format
Dim llogon As DateTime = Nothing

‘for User last logon
‘Dim TargetUsername As String = &quot;george&quot; ‘ -&gt; Pass sAMAccountName
‘Dim objType As Integer = 805306368 ‘-&gt; 805306368 User | 805306369 Computer

‘for Computer
Dim TargetUsername As String = &quot;JOSE-KSP$&quot; ‘ -&gt; Pass sAMAccountName
Dim objType As Integer = 805306369 ‘-&gt; 805306368 User | 805306369 Computer

‘Loop through all available domain controllers
For Each dsDC As DirectoryServices.ActiveDirectory.DomainController In _
DirectoryServices.ActiveDirectory.Domain.GetCurrentDomain.DomainControllers
‘Print the individual domain controller name
‘Debug.Write(dsDC.Name &amp; vbCrLf) ‘ Uncomment for print
‘Get the Entry details for domain controller
Dim dirEntry As DirectoryEntry = New DirectoryEntry(&quot;LDAP://&quot; + dsDC.Name + &quot;/&quot; + DirectCast(rootDse.Properties(&quot;defaultNamingContext&quot;).Value, String))
‘Define searcher for the objects of interest
Dim dirObjects As DirectorySearcher = New DirectorySearcher(dirEntry)
‘Define a large integer to hold the COM object translated value
‘ActiveDS COM object is a MUST reference, yet to find another way to deal with highpart, lowpart values obtained from &quot;lastlogon&quot;
Dim lastLogonThisServer As Int64 = New Int64()
‘Set up the filter for object to be returned through findall method
dirObjects.Filter = &quot;(&amp;(sAMAccountType=&quot; &amp; objType &amp; &quot;)(sAMAccountName=&quot; &amp; TargetUsername &amp; &quot;))&quot;
‘ Loop through the records found
For Each objRecords In dirObjects.FindAll()
‘Get the directory entry, this will return all the attributes associated with the object (Computer/User)
Dim dirObj As DirectoryEntry = objRecords.GetDirectoryEntry()
If Not dirObj Is Nothing Then
‘ Get the distinguishedName and lastLogon for each user

Dim distinguishedName As String = dirObj.Properties(&quot;distinguishedName&quot;).Value.ToString()

Try

If Not dirObj.Properties(&quot;lastLogon&quot;).Value Is Nothing Then
Dim lgIntas As ActiveDs.LargeInteger = dirObj.Properties(&quot;lastLogon&quot;).Value
Dim lngHigh As Long = lgIntas.HighPart
Dim lngLow As Long = lgIntas.LowPart
lastLogonThisServer = (lngHigh * (2 ^ 32) – lngLow)

End If

Catch ex As Exception
Debug.Write(ex.Message)
End Try
‘Different date time formats you can play around with
‘Dim format As String = &quot;MMM ddd d HH:mm yyyy&quot; -&gt; Jan Thu 15 08:11 2015
‘Dim format As String = &quot;dd/MMM/yyyy HH:mm tt&quot; -&gt; 15/Jan/2015 08:36 AM
‘Debug.Write(distinguishedName &amp; &quot;;&quot; _
‘&amp; DateTime.FromFileTime(lastLogonThisServer).ToString(format, Globalization.CultureInfo.InvariantCulture) &amp; vbCrLf)

‘Save the most recent logon for each user in a Dictionary object
‘How it works
‘lastLogons dictionary object has two parts, key and corresponding value
‘Prior adding a new record, using ContainsKey call we can check the array for existing keys
‘With this example, the key is &quot;distinguishedName&quot;
‘If the distinguishedName as key found

If lastLogons.ContainsKey(distinguishedName) Then
‘We compare the latest fetched logon date integer value against the key value of the dictionary object
If lastLogons(distinguishedName) &lt; lastLogonThisServer Then
‘If the existing value is smaller than the new value &quot;lastLogonThisServer&quot; holds
lastLogons(distinguishedName) = lastLogonThisServer
‘We will update the existing key value with recent lastLogonThisServer value
End If
Else
‘We will add a new entry to the dictionary object
lastLogons.Add(distinguishedName, lastLogonThisServer)
End If
End If
Next
Next
‘Now we will loop through the dictionary object and fetch the details
‘For a single user/computer the dictionary object will not have more than one entry
For Each kvp As KeyValuePair(Of String, Int64) In lastLogons
Dim v1 As String = kvp.Key
Dim v2 As DateTime = DateTime.FromFileTime(kvp.Value).ToString()
‘ llogon = v2
Dim format As String = &quot;dd/MMM/yyyy HH:mm tt&quot;
Debug.Write(&quot;Distinguished Name :&quot; &amp; v1 &amp; &quot;; Last logged :&quot; &amp; v2.ToString(format) &amp; vbCrLf)

Next

End Sub

End Module
[/code]

You may download the solution from https://drive.google.com/file/d/0B-3iVeOMTCbWWFliTGN1NmNxMjQ/view?usp=sharing

regards,

rajesh