Microsoft® SQL Server® 2012 Bible

798

Part V: Enterprise Data Management

LISTING 30-8 dept_birthdays.ps1

#dept_birthdays.ps1

#This script will extract information for employees by Department

#and write the results into text files named with the department name.

$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=MyServer\

MyInstance;Integrated Security=SSPI;Initial Catalog=AdventureWorks2008");

$cn.Open()

$q = "SELECT d.[DepartmentID]"

$q = $q + " ,d.[Name]"

$q = $q + " ,p.[FirstName]"

$q = $q + " ,p.[LastName]"

$q = $q + " ,e.[JobTitle]"

$q = $q + " ,e.[BirthDate]"

$q = $q + " ,e.[SalariedFlag]"

$q = $q + " FROM [AdventureWorks2008].[Person].[Person] p"

$q = $q + " INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e"

$q = $q + " ON p.[BusinessEntityID] = e.[BusinessEntityID]"

$q = $q + " INNER JOIN [AdventureWorks2008]

.[HumanResources].[EmployeeDepartmentHistory] dh"

$q = $q + " ON p.[BusinessEntityID] = dh.[BusinessEntityID]"

$q = $q + " INNER JOIN [AdventureWorks2008].[HumanResources].[Department] d"

$q = $q + " ON dh.[DepartmentID] = d. [DepartmentID]"

$q = $q + " WHERE p.[PersonType] = 'EM'"

$q = $q + " AND dh.[EndDate] IS NULL"

$q = $q + " ORDER BY d.DepartmentID, p.LastName"

$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)

$cmd.CommandTimeout = 0

$dr = $cmd.ExecuteReader()

$did = ""

while ($dr.Read()) {

$DepartmentID = $dr.GetValue(0)

$Name = $dr.GetValue(1)

$FirstName = $dr.GetValue(2)

$LastName = $dr.GetValue(3)

$JobTitle = $dr.GetValue(4)

$BirthDate = $dr.GetValue(5)

$SalariedFlag = $dr.GetValue(6)

if ($DepartmentID -ne $did) {

$r = """DepartmentID"",""Name"",""FirstName"",""LastName"

$r = $r + """,""JobTitle"",""BirthDate"",""SalariedFlag"""

$f = $Name + ".txt"

$r | out-file $f -append -encoding ASCII

c30.indd 798c30.indd 798 7/31/2012 9:46:24 AM7/31/2012 9:46:24 AM

http://www.it-ebooks.info