In this level we will look at ways of getting data into and out of SQL Server. There are many different ways to get data out and a few to get data in, so let’s look at them in some detail. SMO has a couple of ways to retrieve data from SQL Server. We will specifically look at the Database object and a cmdlet Invoke-Sqlcmd or Invoke-SqlCmd2. To get data in, we are going to leverage a PowerShell script that uses SMO to put data into a predefined table in SQL Server.
Using SMO to Execute TSQL
There are a few mechanisms to get data out of SQL Server using PowerShell and SMO. One of the first ones is to execute a query within the SMO space. The methods exist on the SMO.Database object. The methods are called ExecuteNonQuery and ExecuteWithResults. The names are pretty self-explanatory, but we’ll cover them separately.
ExecuteNonQuery
ExecuteNonQuery is used to execute TSQL and return no results. This could be a simple INSERT statement, UPDATE statement or any other statement that is not meant to return data to the client. It is executed within the context of a database since this method is on the Database object. That means if you have a query INSERT INTO dbo.Table1 (col1) VALUES ('Ben') it will look in the database from which you are executing the method to the determine what table dbo.Table1 actually refers to. If the table is not found, then an error will be generated. In Listing 10.2 you will see code for executing ExecuteNonQuery to insert a row into a table called dbo.Table1.
The database MyDB and the table dbo.Table1 were created in Level 9. To recreate them, you can run the code in Listing 10.1.
IF DB_ID('MyDB') IS NULL
CREATE DATABASE MyDB;
GO
USE MyDB;
GO
IF OBJECT_ID('dbo.Table1') IS NOT NULL
DROP TABLE dbo.Table1;
GO
CREATE TABLE dbo.Table1
(
Table1Id INT NOT NULL ,
Name VARCHAR(50) NULL
);
GOListing 10.1 Recreate the database and table
Note that you may need to edit Listings 10.2 and 10.3 to replace localhost with localhost\[name of instance] if you are using an named instance of SQL Server.
# If you have SQL 2012 then you can use Version=11.0.0.0 # If you have SQL 2014 then you can use Version=12.0.0.0 $version = "12.0.0.0" Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=$version, Culture=neutral, PublicKeyToken=89845dcd8080cc91" $instance = "localhost" $dbname = "MyDB" $class = "Microsoft.SqlServer.Management.Smo" $server = New-Object –TypeName "$class.Server" –Args $instance $db = $server.Databases[$dbname] $db.ExecuteNonQuery(“INSERT INTO dbo.Table1 (Table1Id, Name) VALUES (1, 'BoboBob')”) # There should now be a row in the table with Name = BoboBob
Listing 10.2 Using ExecuteNonQuery
ExecuteWithResults
ExecuteWithResults is used to execute TSQL and return results. When the database object returns results, it does so in the object of a System.Data.DataSet in .NET. Inside the DataSet object there are Table objects referenced by DataSet.Tables[index]. If your TSQL returns multiple result sets then there will be multiple tables in the DataSet object. In this way you can use the context of the database and return one or more result sets to be used by the rest of the script. This will be more evident as we see additional scripts later in this level. In Listing 10.3 you will see the code for using ExecuteWithResults to return the data in dbo.Table1 table.
# If you have SQL 2012 then you can use Version=11.0.0.0 # If you have SQL 2014 then you can use Version=12.0.0.0 $version = "12.0.0.0" Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=$version, Culture=neutral, PublicKeyToken=89845dcd8080cc91" $instance = "localhost" $dbname = "MyDB" $class = "Microsoft.SqlServer.Management.Smo" $server = New-Object –TypeName "$class.Server" –Args $instance $db = $server.Databases[$dbname] $ds = $db.ExecuteWithResults(“SELECT * FROM dbo.Table1”) $dt = $ds.Tables[0] $dt | ft Table1Id, Name -Auto # You should see the row in the table.
Listing 10.3 Using ExecuteWithResults
PowerShell Options of Execution
In PowerShell there are other ways to get data out of SQL Server without using SMO. We will talk about two ways. The second will aid you in another example in this level, when we look at getting data into SQL Server. The two techniques we will cover here are Invoke-Sqlcmd and Invoke-Sqlcmd2. One of these is a cmdlet from the “sqlps” module and the other is a function written by Chad Miller to return data in a DataTable instead of as rows of data. Let’s look closer at the usage of each.
Invoke-Sqlcmd
Invoke-Sqlcmd is a cmdlet to which we have access when we use import-module sqlps in the PowerShell console. This cmdlet will return a set of row objects from the query that is executed. It is important to note that this way still allows you to use foreach to get to each row in the result set. The drawback of this cmdlet is that it is more difficult to combine the rows into a single object for subsequent reuse. The result is simply a collection of rows. Listing 10.4 shows how this is used and the type of output that comes back.
# To gain access to the cmdlet Invoke-Sqlcmd you need to load the module sqlps Import-Module sqlps –DisableNameChecking $instance = "localhost" $dbname = "MyDB" $results = Invoke-SqlCmd –ServerInstance $instance –Database $dbname –Query “SELECT * FROM dbo.Table1” $results | ft Table1Id, Name -Auto # You should see the rows in the table.
Listing 10.4 Using Invoke-Sqlcmd
Invoke-Sqlcmd2
Invoke-Sqlcmd2 comes to us in the form of a script that we dot-source in order to gain access to it through the console. The reason to use this function is to allow you to return the results as a DataTable or DataRow. In Listing 10.5 you will see the way this is called and the options you have when using this method of execution. The reason you would use this method is to return a single object that contains all the rows in a DataTable object that can then be used to put the results back into a database table for storage or further manipulation. Calling this function is the same as calling a cmdlet. In Listing 10.5 you will see at the top the URL to get the script. The script needs to be downloaded and saved into a file called Invoke-Sqlcmd2.ps1 in the current directory.
# To gain access to the Function Invoke-Sqlcmd2 you need to dot source the file Invoke-Sqlcmd2.ps1 # you can get the file at # https://gallery.technet.microsoft.com/scriptcenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894 . .\Invoke-Sqlcmd2.ps1 $instance = "localhost" $dbname = "MyDB" $results = Invoke-SqlCmd2 –ServerInstance $instance –Database $dbname –Query “SELECT * FROM dbo.Table1” –As DataTable $results | ft Table1Id, Name -Auto # You should see the rows in the table.Listing 10.5 Using Invoke-Sqlcmd2
.NET Execution
For those of you that have used .NET or programmed in another object-oriented language, there is a way you can get data out using .NET and the classes available there. You essentially use the System.Data namespace and the System.Data.SqlClient namespace to create the objects you need to execute things. In the System.Data namespace there are the following classes; System.Data.DataSet and System.Data.DataTable. I will also show you the use of a System.Data.DataAdapter. With these classes you essentially are going to create a SqlClient.SqlConnection and a SqlClient.SqlCommand to start the process and then the DataAdapter or DataReader to get the data into a DataSet or DataTable. Listing 10.6 shows how this is done and the results coming from .NET execution.
$ServerInstance = "localhost"
$Database = "MyDB"
$ConnectionTimeout = 30
$Query = "SELECT * FROM dbo.Table1"
$conn = new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance, $Database, $ConnectionTimeout
$conn.ConnectionString = $ConnectionString
$conn.Open()
$cmd = New-Object system.Data.SqlClient.SqlCommand($Query, $conn)
$ds = New-Object System.Data.DataSet
$da = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.Fill($ds)
$conn.Close()
$ds.Tables[0] | ft Table1Id, Name –AutoListing 10.6 Using .Net to get data out
Putting Data Into SQL Server
So as we’ve seen, using SQL PowerShell to get data out of our SQL Server is relatively straightforward. Sometimes you’ll need to get data out for use in automation, but other times you will want to pull data out of SQL Server and put it into another SQL Server for storage or monitoring. Whether you are using data from SMO objects or from a TSQL query that produces results, being able to store it in a table for later use or analysis is a good thing. In the next part of this article you will see how you can get data into SQL Server
Write-DataTable.ps1
Write-DataTable.ps1 is a script downloadable from this location: https://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae. This script will need to be downloaded and saved in a file called Write-DataTable.ps1 in the current directory. This script contains a function called Write-DataTable and when dot-sourced allows you to add data to a table from a DataTable object. There are things you need to know about how this process works. First, the importing of data in the DataTable object does not rely on the column names but the order of the columns. When you have 3 columns in the DataTable you should have 3 columns in the table in the SQL Server database. It is just like a bulk insert command (i.e. it uses the BulkCopy object) so you can think of it as a mini SSIS package to move data in. In Listing 10.7 you will see the process of taking data from SQL Server and putting in another table.
# To gain access to the Function Invoke-Sqlcmd2 you need to dot source the file Invoke-Sqlcmd2.ps1
# you can get the file at
# https://gallery.technet.microsoft.com/scriptcenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894
# Create a table as a clone to Table1
# IF object_id('Table2') IS NOT NULL DROP TABLE Table2;
# CREATE TABLE dbo.Table2 (Table2Id int NOT NULL, Name varchar(50) )
. .\Invoke-Sqlcmd2.ps1
. .\Write-DataTable.ps1
$instance = "localhost"
$dbname = "MyDB"
$results = Invoke-SqlCmd2 –ServerInstance $instance –Database $dbname –Query “SELECT * FROM dbo.Table1” –As DataTable
Write-DataTable –ServerInstance $instance –Database $dbname –TableName “dbo.Table2” –Data $results
$newtable = Invoke-SqlCmd2 –ServerInstance $instance –Database $dbname –Query “SELECT * FROM dbo.Table2” –As DataTable
$newtable | ft Table2Id, Name -Auto
# You should see the rows in the table.Listing 10.7 Writing data to SQL with Write-DataTable
In Listing 10.8 you will see the process of creating a DataTable object and putting data into it and then using Write-DataTable to put data into the other table. The new table must be created with the TSQL in the top of Listing 10.7. The table dbo.Table2 will be used as the data destination. In Listing 10.8 there is TSQL that needs to be run to create the table dbo.Table3 for use in the script.
# Create a new table to hold the data called Table3
# IF object_id('Table3') IS NOT NULL DROP TABLE Table3;
# CREATE TABLE dbo.Table3 (Table3Id int NOT NULL, Name varchar(50), EmailAddress varchar(128) )
. .\Invoke-SqlCmd2.ps1
. .\Write-DataTable.ps1
$instance = "localhost"
$dbname = "MyDB"
$dt = New-Object –TypeName System.Data.DataTable –Args “ImportData”
$colTable3Id = New-Object System.Data.DataColumn "Table3Id", ([int])
$colName = New-Object system.Data.DataColumn "Name", ([string])
$colName.MaxLength = 50
$colEmail = New-Object system.Data.DataColumn BobColumn, ([string])
$colEmail.MaxLength = 128
$dt.Columns.Add($colTable3Id)
$dt.Columns.Add($colName)
$dt.Columns.Add($colEmail)
$row = $dt.NewRow()
$row.Table3Id = 50
$row.Name = "Ben Miller"
$row.BobColumn = "email@hotmail.com"
$dt.Rows.Add($row)
$row = $dt.NewRow()
$row.Table3Id = 51
$row.Name = "Kalen Delaney"
$row.BobColumn = "different_email@hotmail.com"
$dt.Rows.Add($row)
Write-DataTable –ServerInstance $instance –Database $dbname –TableName “dbo.Table3” –Data $dt
$newtable = Invoke-SqlCmd2 –ServerInstance $instance –Database $dbname –Query “SELECT * FROM dbo.Table3” –As DataTable
$newtable | ft Table3Id, Name, EmailAddress -Auto
# You should see the rows in the table.Listing 10.8 Writing data to SQL with Write-DataTable with custom DataTable
If you notice in the Listing 10.8 there is a column called BobColumn and it still receives data even though BobColumn is not the column name in Table3. It shows that order matters, not names of the columns.
Summary
This level shows that you can get data out of SQL Server and get data back in. Your challenge is to take this information and apply it into your needs to get data back in SQL Server for purposes that help your organization. Remember the key is that you can use built-in functions or functions that you import like Invoke-SqCmd2 or Write-DataTable. You can use results that come out of functions or you can create a custom DataTable with columns with custom data.
