{"id":32,"date":"2013-10-30T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/t-sql-command-line-options-part-3-ps-w-net"},"modified":"2026-03-18T21:50:19","modified_gmt":"2026-03-18T21:50:19","slug":"t-sql-command-line-options-part-3-ps-w-net","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2013\/10\/30\/t-sql-command-line-options-part-3-ps-w-net\/","title":{"rendered":"T-SQL Command Line Options – Part 3 – PS w\/ .NET"},"content":{"rendered":"

Powershell and .NET Objects<\/h2>\n

Now that we\u2019ve covered SQLCMD.exe with the command prompt I\u2019d like to dive into the present and future of Windows command line, Powershell. One of the great things about Powershell is that for every task there are always multiple ways to tackle it. In this blog series I will be covering three different methods of establishing a SQL Server connection and executing a query. Of those three ways there are different situations where you might be forced to pick one over the other but we\u2019ll get into that as I cover each.<\/p>\n

Why this method?<\/h2>\n

I think all DBAs will agree that, given the option between command prompt and Powershell, you should always use Powershell. Powershell has all of the capabilities as command prompt plus so much more. So, now that you\u2019ve chosen Powershell and verified that you have it installed; why would we use .NET?<\/p>\n

Powershell exposes the entire .NET framework to the script writer so, naturally, you can use the exact same .NET objects to build your SQL connection, execute queries, and retrieve the results. There are, however, other methods of using Powershell to interact with SQL Server, as mentioned above.<\/p>\n

Recently I needed to have a T-SQL script file executed on an entire active-active topology of SQL Server. The scripts were related to peer-to-peer replication, I could not predict how the code would change by the developers in the future, it had to be run outside of the individual node\u2019s SSDT dacpac deployments, and it had to snap into their continuous integration seamlessly. They already had a requirement to run their deployment agents on Server 2008 R2 or above so I could assume I had access to Powershell 2.0 but couldn\u2019t count on the deployment agent having any of the SQL modules installed. So, in an effort to write re-usable code that was flexible and didn\u2019t impose any new requirements on the change management team, I chose to use .NET. This method does not require anything that is not native to Windows Server 2008 R2 or higher.<\/p>\n

How to use it<\/h2>\n

First off, I highly recommend using the Windows Powershell ISE which comes prepackaged with Windows 7 and 8. If you are running Windows Server or just don\u2019t want to use the ISE then the normal Powershell console will work just fine as well.<\/p>\n

We are going to start off by establishing our variables. The timeout settings seen below are optional but I like to include them in case the defaults need adjustment at a future date.<\/p>\n

$ConnectionTimeOutInSeconds = 30;\n$CommandTimeOutInSeconds = 30;\n$server = 'localhost\\SQL2008R2';\n$database = 'master';<\/code><\/pre>\n
Next we will; instantiate the SQLConnection object, set the connection string, and open the connection.<\/p>\n
# establish connection\n$Conn=New-Object System.Data.SQLClient.SQLConnection \"Server=$server;Database=$database;Trusted_Connection=Yes;Connection Timeout=$ConnectionTimeOutInSeconds;\";\n$Conn.Open();<\/code><\/pre>\n
We will then instantiate the SqlCommand object and set the command timeout.<\/p>\n
# instantiate command object\n$DataCmd = New-Object System.Data.SqlClient.SqlCommand;\n$DataCmd.CommandTimeout = $CommandTimeOutInSeconds;<\/code><\/pre>\n
This next snippet will extract the T-SQL text from a file and load it into the $MyQuery variable<\/p>\n
# extract T-SQL\n$sqlFile = 'C:\\Fake_Test_File.sql';\n$MyQuery = (get-content ($sqlFile));<\/code><\/pre>\n
Alternatively, you may also hard code the query like so.<\/p>\n
$MyQuery = \"SELECT 'Don<\/code>'t forget to subscribe to the SQLHammer.com RSS feed.'\";<\/code><\/pre>\n
Next we will set the command object\u2019s connection property and execute. This first example is for a non-query such as DML commands.<\/p>\n
# associate command to connection\n$DataCmd.Connection = $Conn;\n
execute script<\/h1>\n$DataCmd.ExecuteNonQuery();<\/code><\/pre>\n
This second example is to execute a query and print the results to the console.<\/p>\n
$reader = $Conn.ExecuteReader(\"SELECT Name, CostRate FROM Production.Location\")\nwhile ($reader.Read())\n{\n   $name = $reader.GetValue(0);\n   $cRate = $reader.GetValue(1);\n   Write-Host $name,\"(\",$cRate,\")\"\n}<\/code><\/pre>\n
Finally, we collect the garbage.<\/p>\n
# clean up\n$Conn.Close();\n$Conn.Dispose();<\/code><\/pre>\n
Full script \u2013 non-query<\/h3>\n
##############################################\n
Set Variables<\/h1>\n##############################################\n\n
$ConnectionTimeOutInSeconds = 30;\n$CommandTimeOutInSeconds = 30;\n$server = 'localhost\\SQL2008R2';\n$database = 'master';<\/p>\n\n
##############################################\n
Execute scripts<\/h1>\n##############################################<\/p>\n\n
establish connection<\/h1>\n$Conn=New-Object System.Data.SQLClient.SQLConnection \"Server=$server;Database=$database;Trusted_Connection=Yes;Connection Timeout=$ConnectionTimeOutInSeconds;\";\n$Conn.Open();\n\n
instantiate command object<\/h1>\n$DataCmd = New-Object System.Data.SqlClient.SqlCommand;\n$DataCmd.CommandTimeout = $CommandTimeOutInSeconds;\n\n
extract T-SQL<\/h1>\n$sqlFile = 'C:\\Fake_Test_File.sql';\n$MyQuery = (get-content ($sqlFile));\n\n
associate command to command text<\/h1>\n$DataCmd.CommandText = $MyQuery;\n\n
associate command to connection<\/h1>\n$DataCmd.Connection = $Conn;\n\n
execute script<\/h1>\n$DataCmd.ExecuteNonQuery();\n\n
clean up<\/h1>\n$Conn.Close();\n$Conn.Dispose();<\/code><\/pre>\n
Full script \u2013 query read<\/h3>\n
##############################################\n
Set Variables<\/h1>\n##############################################\n\n
$ConnectionTimeOutInSeconds = 30;\n$CommandTimeOutInSeconds = 30;\n$server = 'localhost\\SQL2008R2';\n$database = 'master';<\/p>\n\n
##############################################\n
Execute scripts<\/h1>\n##############################################<\/p>\n\n
establish connection<\/h1>\n$Conn=New-Object System.Data.SQLClient.SQLConnection \"Server=$server;Database=$database;Trusted_Connection=Yes;Connection Timeout=$ConnectionTimeOutInSeconds;\";\n$Conn.Open();\n\n
instantiate command object<\/h1>\n$DataCmd = New-Object System.Data.SqlClient.SqlCommand;\n$DataCmd.CommandTimeout = $CommandTimeOutInSeconds;\n\n
extract T-SQL<\/h1>\n$sqlFile = 'C:\\Fake_Test_File.sql';\n$MyQuery = (get-content ($sqlFile));\n\n
associate command to command text<\/h1>\n$MyQuery = \"SELECT <\/code>'Don't forget to subscribe to the SQLHammer.com RSS feed.<\/code>'\";\n\n
associate command to connection<\/h1>\n$reader = $Conn.ExecuteReader($DataCmd.CommandText);\nwhile ($reader.Read())\n{\n   $name = $reader.GetValue(0);\n   $cRate = $reader.GetValue(1);\n   Write-Host $name,\"(\",$cRate,\")\"\n}\n\n
clean up<\/h1>\n$Conn.Close();\n$Conn.Dispose();<\/code><\/pre>\n
What\u2019s next?<\/h2>\n
In the next part of this blog series we will look into how to use the SQLPSX community built Powershell module.<\/p>\n
Navigation<\/h2>\n