A couple of weeks ago, I was asked by a colleague for help on querying data from a SQL server. In this particular case, the guy was trying to gather the names of servers and their patching groups from our corporate configuration database. While I am unable to present the exact information due to corporate confidentiality agreements, I thought that the process used to gather the information might be useful for someone.
In the following example, I have used the same process to gather information from the Northwinds Sample database. In order to better present the information, I will be breaking the different lines up into steps. It could be put together in a function to allow you to perform the same action against the database with various other queries.
The first thing we will do is to set up the connection string. I set this up in a separate variable for later readability. It also makes the lines more readable and easier to edit if you need to modify it a little bit later.
$connString = "data source=SQLServer,1433;Initial catalog=NorthWind;uid=NorthwindUser;pwd=NorthwindUser;"
If you are unfamiliar with connection strings, this line will tell the shell to connect to a server named “SQLServer” on port 1433. It will connect to the Northwind database using a username and password of “NorthwindUser.” (Security Note: It is recommended to use strong passwords on your database connections. That is to say that using an identical username/password combination is not recommended.) The next line will set up the SQL SELECT statement that will be used.
$QueryText = "SELECT DISTINCT c.CompanyName, c.ContactName, c.Phone FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID where o.CustomerID IS NOT null"
This statement will get the Company Name for all customers who have made an order. Using the default database, I started to list all the customers who did NOT have an order, but that was much less dramatic with only two records. This query will bring back quite a bit more.
Next piece will be to set up the SQL Connections. We will be using the SQLClient class in the System.Data namespace. First we will instantiate a new SqlConnection and SqlCommand object.
$SqlConnection = new-object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = $connString $SqlCommand = $SqlConnection.CreateCommand() $SqlCommand.CommandText = $QueryText
After instantiating the SqlConnection object, I have populated the ConnectionString property with the string we set up earlier. Next we created a new SqlCommand object using the CreateCommand() method of the SqlConnection object. I then added the $QueryText data to the CommandText property.
To get the data into something usable, and in a quick amount of time, I have set up a SqlDataAdapter to create an in-memory table that can easily be manipulated. The SqlDataAdapter will fill a dataset with the data that we are extracting from the SQL server. Another reason for using the Fill() method is that it will “open” and “close” the database connection for you as part of its normal operation. However, if the database connection is already open, you need to make sure that you also close it manually to save system resources.
$DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $SqlCommand $dataset = new-object System.Data.Dataset $DataAdapter.Fill($dataset)
When these last lines are run, Powershell will return a number which is the number of records that have been returned from the database. In my testing, this returned 89 records from the database.
Now the $dataset variable has one table with the information requested. To get to this information, you can access it by calling the first table in the dataset. Remember that the tables are numbered ordinally and start at “0.”
$dataset.Tables[0]
And you should get data returned that looks like this:
CompanyName ContactName Phone ----------- ----------- ----- Alfreds Futterkiste Maria Anders 030-0074321 Ana Trujillo Emparedados y helados Ana Trujillo (5) 555-4729 Antonio Moreno Taquería Antonio Moreno (5) 555-3932 Around the Horn Thomas Hardy (171) 555-7788 Berglunds snabbköp Christina Berglund 0921-12 34 65 Blauer See Delikatessen Hanna Moos 0621-08460 Blondesddsl père et fils Frédérique Citeaux 88.60.15.31 Bólido Comidas preparadas Martín Sommer (91) 555 22 82 Bon app' Laurence Lebihan 91.24.45.40 Bottom-Dollar Markets Elizabeth Lincoln (604) 555-4729 B's Beverages Victoria Ashworth (171) 555-1212 Cactus Comidas para llevar Patricio Simpson (1) 135-5555 Centro comercial Moctezuma Francisco Chang (5) 555-3392 Chop-suey Chinese Yang Wang 0452-076545 Comércio Mineiro Pedro Afonso (11) 555-7647 Consolidated Holdings Elizabeth Brown (171) 555-2282 Die Wandernde Kuh Rita Müller 0711-020361 Drachenblut Delikatessen Sven Ottlieb 0241-039123 Du monde entier Janine Labrune 40.67.88.88 Eastern Connection Ann Devon (171) 555-0297 Ernst Handel Roland Mendel 7675-3425 Familia Arquibaldo Aria Cruz (11) 555-9857 Folies gourmandes Martine Rancé 20.16.10.16 Folk och fä HB Maria Larsson 0695-34 67 21 France restauration Carine Schmitt 40.32.21.21 Franchi S.p.A. Paolo Accorti 011-4988260 Frankenversand Peter Franken 089-0877310 Furia Bacalhau e Frutos do Mar Lino Rodriguez (1) 354-2534 Galería del gastrónomo Eduardo Saavedra (93) 203 4560 Godos Cocina Típica José Pedro Freyre (95) 555 82 82 Gourmet Lanchonetes André Fonseca (11) 555-9482 Great Lakes Food Market Howard Snyder (503) 555-7555 GROSELLA-Restaurante Manuel Pereira (2) 283-2951 Hanari Carnes Mario Pontes (21) 555-0091 HILARION-Abastos Carlos Hernández (5) 555-1340 Hungry Coyote Import Store Yoshi Latimer (503) 555-6874 Hungry Owl All-Night Grocers Patricia McKenna 2967 542 Island Trading Helen Bennett (198) 555-8888 Königlich Essen Philip Cramer 0555-09876 La corne d'abondance Daniel Tonini 30.59.84.10 La maison d'Asie Annette Roulet 61.77.61.10 Laughing Bacchus Wine Cellars Yoshi Tannamuri (604) 555-3392 Lazy K Kountry Store John Steel (509) 555-7969 Lehmanns Marktstand Renate Messner 069-0245984 Let's Stop N Shop Jaime Yorres (415) 555-5938 LILA-Supermercado Carlos González (9) 331-6954 LINO-Delicateses Felipe Izquierdo (8) 34-56-12 Lonesome Pine Restaurant Fran Wilson (503) 555-9573 Magazzini Alimentari Riuniti Giovanni Rovelli 035-640230 Maison Dewey Catherine Dewey (02) 201 24 67 Mère Paillarde Jean Fresnière (514) 555-8054 Morgenstern Gesundkost Alexander Feuer 0342-023176 North/South Simon Crowther (171) 555-7733 Océano Atlántico Ltda. Yvonne Moncada (1) 135-5333 Old World Delicatessen Rene Phillips (907) 555-7584 Ottilies Käseladen Henriette Pfalzheim 0221-0644327 Pericles Comidas clásicas Guillermo Fernández (5) 552-3745 Piccolo und mehr Georg Pipps 6562-9722 Princesa Isabel Vinhos Isabel de Castro (1) 356-5634 Que Delícia Bernardo Batista (21) 555-4252 Queen Cozinha Lúcia Carvalho (11) 555-1189 QUICK-Stop Horst Kloss 0372-035188 Rancho grande Sergio Gutiérrez (1) 123-5555 Rattlesnake Canyon Grocery Paula Wilson (505) 555-5939 Reggiani Caseifici Maurizio Moroni 0522-556721 Ricardo Adocicados Janete Limeira (21) 555-3412 Richter Supermarkt Michael Holz 0897-034214 Romero y tomillo Alejandra Camino (91) 745 6200 Santé Gourmet Jonas Bergulfsen 07-98 92 35 Save-a-lot Markets Jose Pavarotti (208) 555-8097 Seven Seas Imports Hari Kumar (171) 555-1717 Simons bistro Jytte Petersen 31 12 34 56 Spécialités du monde Dominique Perrier (1) 47.55.60.10 Split Rail Beer & Ale Art Braunschweiger (307) 555-4680 Suprêmes délices Pascale Cartrain (071) 23 67 22 20 The Big Cheese Liz Nixon (503) 555-3612 The Cracker Box Liu Wong (406) 555-5834 Toms Spezialitäten Karin Josephs 0251-031259 Tortuga Restaurante Miguel Angel Paolino (5) 555-2933 Tradiçao Hipermercados Anabela Domingues (11) 555-2167 Trail's Head Gourmet Provisioners Helvetius Nagy (206) 555-8257 Vaffeljernet Palle Ibsen 86 21 32 43 Victuailles en stock Mary Saveley 78.32.54.86 Vins et alcools Chevalier Paul Henriot 26.47.15.10 Wartian Herkku Pirkko Koskitalo 981-443655 Wellington Importadora Paula Parente (14) 555-8122 White Clover Markets Karl Jablonski (206) 555-4112 Wilman Kala Matti Karttunen 90-224 8858 Wolski Zajazd Zbyszek Piestrzeniewicz (26) 642-7012
Now, this is great to be able to see this information on the screen, but most likely, you need to have this in a file. Enter the Export-CSV cmdlet which will export the data in a comma-separated file that you can manipulate in Excel.
$dataset.Tables[0] | Export-CSV customers_with_orders.csv
There are many applications that you could use this script for. And the possibilities for dealing with the data after you extract are numerous as well. Hopefully, this will help someone to gather information that they need from a database without having to necessarily load up SQL Management Studio or request the information from a SQL DBA. All you need for this is network connectivity and at least read access to the database.
Here are some resources for additional information on the .NET Framework objects that were used.
System.Data.SqlClient.SqlConnection
System.Data.SqlClient.SqlDataAdapter
System.Data.DataSet
Powershell Reflections 
Pingback: Episode 140 – Scripting Guy Ed Wilson « PowerScripting Podcast
Pingback: access sqlserver with different windows credentials in powershell « Just tinkering Blog