Last week I posted about using PowerShell and SQL Server together, and I used a SQLDataAdapter and populated a DataTable with the results. This approach is fine if what you're doing returns a reasonably small resultset, but what if you're returning millions of rows. In this case a DataTable isn't too practical, and a forward-only DataReader is the best way to go.
In my company we have some legacy applications which need data from a large relational database. The preferred format is a comma-separated data file with the columns quoted (to handle whatever imbedded data might be found). I needed to supply separate text files, with subscriber lists, with the list name as the file name of the text file, and the first row a list of the column names of the data.
The first thing I need to do is open a connection to the database.
#extract_subs.ps1
#This script will extract information for subscribers
#and write the results into text files named with the list name.
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=MyServer/MyInstance;Integrated Security=SSPI;Initial Catalog=Subscribers");
$cn.Open()
Once the connection is open I build a SqlCommand object with the query to be processed. (To prevent collisions with the application using the database, and because dirty reads are acceptable to this process, I use the NOLOCK hint.) The results are ordered by list name and email address - the list name order is important because I'm creating separate files based on that list name. I also need to set the CommandTimeout property of the SqlCommand object to 0 to prevent PowerShell from timing out waiting for results - as I mentioned, there are millions of rows coming back.
$q = "SELECT List,"
$q = $q + " EmailAddr,"
$q = $q + " FullName,"
$q = $q + " UserID,"
$q = $q + " CompanyName,"
$q = $q + " Address1,"
$q = $q + " CityName,"
$q = $q + " StateProvince,"
$q = $q + " PostalCode,"
$q = $q + " Country,"
$q = $q + " Telephone,"
$q = $q + " FROM .[dbo].[SubList] WITH (NOLOCK)"
$q = $q + " ORDER BY [List], [EmailAddr]"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.CommandTimeout = 0
Now I can use the ExecuteReader method of the SqlCommand object to return a SqlDataReader object. I'll also initialize a variable I'll use to know when the List name changes, so I can write a header row to the output file. I then use a while loop to iterate through the results. The DataReader Read() method returns true if it has data and false if it is done, so using that method in the while condition returns a row and tests for the end of the data.
$dr = $cmd.ExecuteReader()
$l = ""
while ($dr.Read()) {
There are a number of methods to return column data from a DataReader, but GetValue returns a datatype appropriate to the data returned, so I use that method pretty consistently. I return that data to variables for clarity.
$List = $dr.GetValue(0)
$EmailAddr = $dr.GetValue(1)
$FullName = $dr.GetValue(2)
$UserID = $dr.GetValue(3)
$CompanyName = $dr.GetValue(4)
$Address1 = $dr.GetValue(5)
$CityName = $dr.GetValue(6)
$StateProvince = $dr.GetValue(7)
$PostalCode = $dr.GetValue(8)
$Country = $dr.GetValue(9)
$Telephone = $dr.GetValue(10)
Now that I have the row data in variables I test to see if the List name changed. If it did I build a header row. I then use the new List name and concatenate the ".txt" extension onto it for the output file name, then pipe the header row to the out-file cmdlet. I specify -append in case the file from the previous run still exists (it shouldn't), and I specify -encoding ASCII because by default PowerShell will create a Unicode format file. I also set my list change variable to the name of the list.
if ($List -ne $l) {
$r = """List"",""EmailAddr"",""FullName"",""UserID"
$r = $r + """,""CompanyName"",""Address1"
$r = $r + """,""CityName"",""StateProvince"",""PostalCode"
$r = $r + """,""Country"",""Telephone"""
$f = $List + ".txt"
$r | out-file $f -append -encoding ASCII
$l = $List
}
Now I build the row data into its comma-separated string and write out the data using the out-file cmdlet.
$r = """" + $List + """,""" + $EmailAddr + ""","""
$r = $r + $FullName + """,""" + $UserID + ""","""
$r = $r + $CompanyName + """,""" + $Address1 + ""","""
$r = $r + $CityName + """,""" + $StateProvince + """,""" + $PostalCode + ""","""
$r = $r + $Country + """,""" + $Telephone + """"
$f = $List + ".txt"
$r | out-file $f -append -encoding ASCII
}
The last thing we need to do is to close the DataReader and the Connection.
$dr.Close()
$cn.Close()
There are many different ways to accomplish a task like this. PowerShell gives us one more tool in the toolbox to get tasks done.
Allen
Cross-posted from SQLBlog! -
http://www.sqlblog.com