0

I am trying to pull data from a SQL database using PowerShell.

This is the SQL of what I am trying to do

USE dbfile SELECT * FROM table.marker WHERE table.marker = 0 ORDER BY table.sessionid

How do I put this in to PowerShell?
My level of understanding for PowerShell is not bad but this is just out of my current knowledge zone and I just don't have the time resource to figure it out the old fashioned way..

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Gawainuk
  • 149
  • 1
  • 10
  • 1
    Use [Invoke-Sqlcmd](https://learn.microsoft.com/en-us/sql/powershell/invoke-sqlcmd-cmdlet) or see [How do you run a SQL Server query from PowerShell?](https://stackoverflow.com/questions/8423541/how-do-you-run-a-sql-server-query-from-powershell) – ShanayL Nov 02 '17 at 16:49

1 Answers1

1

Firstly you'd need a connection string somewhere in your powershell script such as:

$connectionstring= "SERVER=servername; database=databasename; user id=username;password=password

Then you'd need to append the query to a string something like:

$myQuery = 
@"
USE dbfile
SELECT *
FROM table.marker
WHERE table.marker = 0
ORDER BY table.sessionid
"@

Then you'd need to open an SQL connection with your query, create an SQL adapter with a dataset - something like:

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionstring
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $myQuery
$command.Connection = $connection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$myDataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($myDataSet)

Then you can call the dataset wherever you want using 

$myDataset.Tables[0].rows (etc)...
dan6657
  • 117
  • 2
  • 11