A colleague of mine recently asked me the best way to run queries against SQL Server from PowerShell. The scenario is that a software company can’t always install modules on customer systems, and when that software could be run from any number of hundreds of servers in a site, it’s usually best to work with “what’s in the box.” So, when working with SQL Server data, ADO.NET is still our friend. It runs natively in PowerShell without any special add-ins.
The tool developers use most often to manage data in SQL Server is ADO.NET. Since PowerShell is built on the .NET Framework, ADO.NET is always available to you, and can be a very powerful tool in meeting some of the “regularly scheduled ad-hoc requests” you’ll receive as a DBA.
ADO.NET is a set of libraries included with the Microsoft .NET Framework that can help you communicate with various data stores from .NET applications. It contains two groups of objects that you can use to manage data, connected objects and disconnected objects.
The connected objects allow you to work with SQL Server (or another data source) using an active connection to your source. Here’s a list of the objects you’ll use on a regular basis.
Connection Object – provides a connection to your data source, including all the properties necessary to manage that connection, such as login, password, timeout values, etc.
Command Object – represents a query against your database, a call to a stored procedure, or a direct request to return the contents of a specific table.
DataReader Object – returns your query results as quickly as possible.
Transaction Object – allows you to group a number of changes to your database and treat them as a single unit of work. The Connection object has a BeginTransaction method that you can use to create Transaction objects
Parameter Object – allows you to specify parameters for stored procedures or parameterized queries.
DataAdapter Object – acts as a bridge between your database and the disconnected objects in the ADO.NET object model.
The disconnected objects in ADO.NET essentially provide an in-memory database, allowing you to work with sets of data without going back to the data source when you have multiple uses for a set of data in your application. The DataAdapter object is used to populate these objects initially.
DataSet Object – the container for a number of DataTable objects. This is the object the DataAdapter’s Fill method populates.
DataTable Object – allows you to examine data through collections of rows and columns. The DataSet contains one or more DataTable objects based on the resultset of the Command executed in the DataAdapter Fill method.
DataRow Object – provides access to the DataTable’s Rows collection.
DataColumn Object – corresponds to a column in your table.
Constraint Object – defines and enforces column constraints.
DataRelation Object – defines the relations between DataTables in the DataSet object.
DataView Object – allows you to examine DataTable data in different ways.
So, you can use the Connection object to connect to SQL Server. Once connected, you can define your query (or call your stored procedure) using the Command object, using Parameter objects as necessary to provide your stored procedure arguments with the necessary data. Then you can run your command using either the DataReader object (if results are large) or the DataAdapter object (if results can fit in memory). If results are in memory, the DataAdapter Fill method fills a DataSet object. The DataSet’s Tables collection contains DataTable objects, and the DataTable’s Rows collection contains DataRow objects. While iterating through the DataRow objects, the DataRow’s Columns collection contains the Item objects that you’ll use in your script to do whatever work is necessary.
When managing a set of SQL Server instances I use an XML file containing the instances I choose to manage. Here’s an example of the type of file I use.
<?xml version=”1.0″ standalone=”yes”?>
The DataSet object has a method called ReadXML which loads an XML file into a DataTable object. It’s then easy to iterate through the DataTable and connect with each of my servers to perform whatever work is required. The following code will load the XML listed above into a DataTable.
$ds = new-object "System.Data.DataSet" "dsServers"
$dtServer = new-object "System.Data.DataTable" "dtServers"
$dtServer = $ds.Tables
The output of this script looks like this:
Query SQL Server with ADO.NET
Normally we’d use the Invoke-SQLCmd cmdlet, but this requires that the snap-ins shipped with SQL Server are installed on the system where the cmdlet is used. While this is the best way to get data, sometimes it’s not practical. (I’m often at client sites where I can’t install software on servers easily.) ADO.NET is built into the .NET Framework, and it’s always available, so I frequently use it to query the servers I work with.
The first thing we need to do is to create a new SqlConnection object, using a standard SQL connection string. Then we’ll create a DataSet object, and put our query into a string variable. We pass the SqlConnection object and the query string as parameters to a new DataAdapter object, and invoke the DataAdapter’s Fill method. Each resultset from the query is loaded into a DataTable within the DataSet, and we can parse each DataTable for the data we’re after.
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=SQLTBWS\INST01;Integrated Security=SSPI;Initial Catalog=AdventureWorks"); $ds = new-object "System.Data.DataSet" "dsPersonData" $q = @" SELECT TOP 25 [ContactID],[FirstName],[LastName],[EmailAddress],[Phone] FROM [AdventureWorks].[Person].[Contact] "@ $da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn) $da.Fill($ds) $dtPerson = $ds.Tables
The $dtPerson object now contains a DataTable with the results from the query.
Now, let’s say you’ve got stored procedures you want to run, and they don’t return any results, but they do have parameters you need to provide. Not a problem. In this case, after you create the SqlConnection object you need to define a SqlCommand object, giving it the name of the stored procedure and the SqlConnection object as arguments. The SqlCommand object has a set of properties, one of which is the type, which you set to “StoredProcedure”. For each parameter you need to create a SqlParameter object, set its type and value, and add it to the Parms collection of the SqlCommand object. Once that’s done, invoke the ExecuteNonQuery method of the SqlCommand object and you’re done.
$ds = new-object System.Data.SqlClient.SqlCommand("[Analysis].[insLogicalDisk]", $cn)
$ds.CommandType = 'StoredProcedure'
$parm = new-object System.Data.SqlClient.SqlParameter ("@Name", [System.Data.SqlDbType]::VarChar, 30)
$parm.Value = $_.Name
$ds.Parameters.Add($parm) | Out-Null
Now, one of the really great things about SQL Server since its creation is the bulk copy utility (originally using the bcp utility, or by implementing the Bulk API in application programs.) This utility allows data to be loaded rapidly into SQL Server and is a critical part of many ETL processes. SQL Server currently provides this ability through the SQLBulkCopy method, but the source must be a DataTable object.
To get a PowerShell object containing data I want to load into SQL Server into a DataTable object can be challenging, but Marc van Orsouw, a PowerShell MVP, created a function called Out-DataTable, that does just that. Here’s a link to the function: https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd
I use this function to create a DataTable with the data to load, ensuring that the object columns exactly match the data structure of the destination table. Once that’s done I create a SqlBulkCopy object, set the object’s DestinationTableName property to the name of the table to be loaded, and invoke the WriteToServer method. This is the fastest way to get data into SQL Server.
$logdsk = gwmi -query "select * from Win32_LogicalDisk where DriveType=3" -computername $svr | select Name, FreeSpace, Size
$dtable = $logdsk | Out-DataTable
$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
$bc.DestinationTableName = "dbo.LogicalDisk"
Now, I mentioned earlier about the Invoke-SQLCMD cmdlet. This is by far the easiest way to get data in and out of SQL Server in PowerShell. Set a string variable to the query you want, and use the cmdlet. If you’re getting results back, assign the invocation of the cmdlet to a variable, and those results are returned as a PowerShell object.
$q = @' SELECT [Name] ,[FreeSpace] ,[Size] FROM [ServerAnalysis].[dbo].[LogicalDisk] GO '@ invoke-sqlcmd -ServerInstance 'SQLTBWS\INST01' -Database 'ServerAnalysis' -Query $q
Besides communicating directly with SQL Server, there are other data formats commonly used.
PowerShell supports a number of cmdlets supporting comma-separated values files. Export-CSV creates a CSV file containing the values in the PowerShell object you send to it. Import-CSV allows you to re-create objects from the CSV strings. ConvertTo-CSV and ConvertFrom-CSV cmdlets convert .NET Framework objects to CSV strings. Use the parameters for these cmdlets to specify delimeters, encoding, headers, etc. to get them to provide your desired results.
Similarly a significant amount of data is stored in XML files, and PowerShell has strong XML support. ReadXml method reads data only, or both data and schema into a DataSet from an XML document. Export-Clixml creates an XML-based representation of an object. Import-Clixml imports a CLIXML File and creates corresponding objects. ConvertTo-XML creates an XML-based representation of an object, and Select-XML finds text in an XML string or document.
Hopefully we’ve given you enough of an idea on how to work with data in PowerShell that you can start building the scripts you’ll find useful in your business environment. Once you start putting some of these scripts into an automated process, like SQL Server Agent, you’ll find you have more time to be more effective in your job.