CODEWITHSUNDEEP
sql-server-2008powershell
Moose
Moose

Reputation: 755

Fetch SQL data?

I want to fetch data from a table when I run a PowerShell script. I have written the following script. For some reason it is only displaying field counts and not the actual data.

My script:

Add-PSSnapin sqlserverprovidersnapin100
Add-PSSnapin sqlservercmdletsnapin100

$db =  get-content c:\users\riteshthakur\desktop\test.txt | select -index 0

$table = get-content c:\users\riteshthakur\desktop\test.txt | select -index 1

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=.;Database=$db;Integrated Security=True"
$SqlConnection.Open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select * from $table"
$SqlCmd.Connection = $SqlConnection

$x = $SqlCmd.ExecuteReader()
Write-output $x

$SqlConnection.Close()

Upvotes: 0

Views: 72

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200203

Once you created the reader you need to actually read the data:

$x = $SqlCmd.ExecuteReader()
while ($x.Read()) {
  $x.GetValue(0)
  $x.GetValue(1)
  ...
}
$x.Close()

Or you could use an SQLDataAdapter instead of a reader:

$SqlCmd = $SqlConnection.CreateCommand()
$SqlCmd.CommandText = "SELECT * FROM $table"
$SqlAdapter = New-Object Data.SqlClient.SqlDataAdapter $SqlCmd
$tbl = New-Object Data.DataTable
$SqlAdapter.Fill($tbl)
$tbl | Format-Table -AutoSize

Upvotes: 1

Related Questions