By: Mason Prewett

This article is Part 2 of our previous Key2 article, How to Execute and Store Transact-SQL (T-SQL) Query Results in a PowerShell Array.

In this article, I will cover:

Getting a SQL Server result set from a stored procedure
- The stored procedure has a required parameter
Iterating through the rows of data returned from SQL Server
Using column values from the SQL server result set to create – and uniquely name – text files

Note: All of these operations will be done in PowerShell.

PowerShell Code

#Variables - details of the connection, stored procedure and parameters
$connectionString = "server=ServerName;database='DatabaseName';trusted_connection=true;";
$storedProcedureCall = "exec dbo.GetFileNames @param1";
$param1Value = "SomeValue";

#SQL Connection - connection to SQL server
$sqlConnection = new-object System.Data.SqlClient.SqlConnection;
$sqlConnection.ConnectionString = $connectionString;

#SQL Command - set up the SQL call
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$sqlCommand.Connection = $sqlConnection;
$sqlCommand.CommandText = $storedProcedureCall;
$parameter = $sqlCommand.Parameters.AddWithValue("@param1",$param1Value);

#SQL Adapter - get the results using the SQL Command
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter 
$sqlAdapter.SelectCommand = $sqlCommand
$dataSet = new-object System.Data.Dataset
$recordCount = $sqlAdapter.Fill($dataSet) 

#Close SQL Connection
$sqlConnection.Close();

#Get single table from dataset
$data = $dataSet.Tables[0]

#File creation variables
$folderLocation = "C:\NewFiles\";

#Loop through each row of data and create a new file
#The dataset contains a column named FileName that I am using for the name of the file
foreach($row in $data)
{ 
    $fullFileName = $folderLocation + $row.FileName + ".txt"
    $newFile = New-Item $fullFileName -ItemType file
}

#Variables - details of the connection, stored procedure and parameters

$connectionString = "server=ServerName;database='DatabaseName';trusted_connection=true;";

$storedProcedureCall = "exec dbo.GetFileNames @param1";

$param1Value = "SomeValue";

#SQL Connection - connection to SQL server

$sqlConnection = new-object System.Data.SqlClient.SqlConnection;

$sqlConnection.ConnectionString = $connectionString;

#SQL Command - set up the SQL call

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand;

$sqlCommand.Connection = $sqlConnection;

$sqlCommand.CommandText = $storedProcedureCall;

$parameter = $sqlCommand.Parameters.AddWithValue("@param1",$param1Value);

#SQL Adapter - get the results using the SQL Command

$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter

$sqlAdapter.SelectCommand = $sqlCommand

$dataSet = new-object System.Data.Dataset

$recordCount = $sqlAdapter.Fill($dataSet)

#Close SQL Connection

$sqlConnection.Close();

#Get single table from dataset

$data = $dataSet.Tables[0]

#File creation variables

$folderLocation = "C:\NewFiles\";

#Loop through each row of data and create a new file

#The dataset contains a column named FileName that I am using for the name of the file

foreach($row in $data)

{

$fullFileName = $folderLocation + $row.FileName + ".txt"

$newFile = New-Item $fullFileName -ItemType file

}

PowerShell Code Explained

SQL Server Connection String – In the initial Variables code section (at the top), enter your server and database information in the connection string.
- You must include trusted_connection=true or you will get an error saying “Login Failed for user” for Windows authentication. Windows authentication will use the credentials of the user executing the script to authenticate to the database.
- If you want to provide the username and password in the connection string, use this syntax: Server=myServerAddress;Database=myDataBase;User Id=myUsername;
  Password=myPassword;”.
- For more information on SQL Server connection strings, see this page: https://www.connectionstrings.com/sql-server.

Stored Procedure Call – Again in the Variables code section, enter your stored procedure call in the $storedProcedureCall variable and the parameter value in the $param1Value variable.
- If the stored procedure is in a different schema than dbo, simply replace dbo with your schema name.
- The stored procedure parameter name (@param1, in this case) has to match the parameter name that is defined in the stored procedure or you will get an error. This is not case-sensitive. This is necessary in both of the following lines of code:
  - $storedProcedureCall = exec dbo.GetFileNames @param1;
  - $parameter = $sqlCommand.Parameters.AddWithValue(“@param1”,$param1Value);

DataSet Tables – This line of code, $data = $dataSet.Tables[0], may not make much sense at a glance, but this is necessary for the System.Data.Dataset data type that is returned from the System.Data.SqlClient.SqlDataAdapter.Fill method. Multiple tables could be returned to a DataSet variable. In our case, it is just one, so we indicate that we want the first (and only) table in the set at the 0 position of the array ($dataSet.Tables[0]).

Iterating Through the Data – The $row variable in the foreach statement represents the row of data for each iteration. To access the column values of each row, simply use this syntax: $row.ColumnName.
- In my example, I am accessing a column named “FileName” with the statement $row.FileName to create a file with this value as the name.

Suppressing Output (Pro Tip)

This next tactic applies to all PowerShell and not just this example. The Powershell code above has many cases where an operation is preceded with a variable being equal to the call. This is done because some operations will automatically display output based on the data type that is returned from the operation. By simply storing the results in a variable, it will suppress the output of the results. Here are examples of this being done in this code:

$parameter = $sqlCommand.Parameters.AddWithValue(“@farm”,$farmParameter);
- Will display the parameter details without the $parameter =
$recordCount = $sqlAdapter.Fill($resultSet)
- Will display the record count returned from the Fill method without the $recordCount =
$newFile = New-Item $fullFileName -ItemType file
- Will display the new file details without the $newFile =

Test out the difference on your own by removing the $variable = before each of these three operations. It will automatically output details that are likely not desired. The technique that I have demonstrated will result in no output at all, but the variables collected could easily be used for output if desired.

Did you find this to be useful?

Did you find this article to be useful? Did you put any of the code into practice? If so, or if you have any questions, please leave us a comment below! We’d love to hear from you.

Keep your data analytics sharp by subscribing to our mailing list!

Thanks for reading! Keep your knowledge sharp by subscribing to our mailing list to receive fresh Key2 content around data analytics, business intelligence, data warehousing, and more!

Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.

6 Comments

Durga Prasada Rao Gaddam on June 26, 2021 at 8:33 pm

I found this is very useful. Explained very well about the steps
Lucian on August 13, 2021 at 11:09 am

Very usefull and great layout and explanation. I used this to get installed services on all servers. Saved me some time coding the basics. Thanks.
- Key2 Consulting on August 17, 2021 at 3:15 pm
  
  Hi Lucian,
  
  We are glad you found this useful! Thanks for reading.
  
  – Key2 Consulting
Leslie on November 29, 2021 at 9:47 pm

I found this very helpful. Thank you so much!
- Key2 Consulting on December 6, 2021 at 3:35 pm
  
  Hi Leslie,
  
  That’s great to hear and thanks for reading!
Suraj on February 28, 2022 at 9:27 am

Do we same instruction for Oracle Database.

PowerShell – How To Iterate Through Rows of Data Returned From a SQL Stored Procedure

PowerShell Code

PowerShell Code Explained

Suppressing Output (Pro Tip)

Did you find this to be useful?

Keep your data analytics sharp by subscribing to our mailing list!

Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.

6 Comments

Recent Posts

Categories