1

I am writing a Powershell script that extracts data via the SQLPS module, executing a query directly to the SQL Server. If I do a plain

Select <column A> from <table B>

I get the column listed as well, as stated like this:

Column A
--------
Value C

Here I wish to only retrieve the Value C, for storing it as a variable.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Mark Jenster
  • 503
  • 3
  • 6
  • 18
  • Why "Value C" only? Is it because it's the 2nd row? Because of another column value? – artm Mar 07 '16 at 09:13
  • Possible duplicate of [Powershell SQL SELECT output to variable](http://stackoverflow.com/questions/22714531/powershell-sql-select-output-to-variable) – sagi Mar 07 '16 at 09:13
  • "Value C" is just bad naming. It's just one value. The select statement will differ a bit, but the thing is that I want just the value listed, instead of the column name as well, when I pass it on to somewhere else e.g. a text file. A >> in PS will pass both into a file. – Mark Jenster Mar 07 '16 at 09:28
  • This may be help for your queries http://stackoverflow.com/questions/23221532/remove-column-header-from-sql-server-query-result – Gopalakrishnan Mar 07 '16 at 09:44

1 Answers1

1

If you are not bound to use this SQLPS module then this might be a easier way to do it:

$connection = new-object System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=TestDB;Integrated Security=True");
$connection.Open()

$query = "SELECT [A] FROM [dbo].[Tablename]"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($query, $connection)
$cmd.CommandTimeout = 0

$executeReader = $cmd.ExecuteReader()

while ($executeReader.Read()) { 
    $Name = $executeReader.GetValue(0)
    //Do what you desire with the resultset.
    $Name + "`r`n" >> D:\PathToResultFolder\result.txt
}

$executeReader.Close()
$connection.Close()

Also I read and think that this should be handled outside of the Query as it is not normal for a Query to not show column-names.

if-trubite
  • 159
  • 9