March 25, 2016 at 2:28 pm
Hi,
I'm new to using PowerShell to process a SQL Query. The query that I'm trying to process is from the Project Server ProjectWebApp database. I came across the following code that seems well structured and runs my SQL Query just fine:
#Read Projects From the ProjectWebApp Database, based on the SQL Query selection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server=DBServerName;Database=ProjectWebApp;Integrated Security=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$TargetDataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($TargetDataSet)
$SqlConnection.Close()
The SQL Query in the $SqlQuery variable works perfectly, when I run this script and provides me with the Dataset needed in $TargetDataSet.
However, I would like to use the SQL Switch -h-1 which I understand would suppress the inclusion of the Column Header and the line of dashes below the header, which currently is included in the dataset output.
Does anyone know the syntax that would allow me to run this SQL Query with the column header suppression switch (-h-1)?
Alternatively, is there another way to structure the code so that I can run this switch to suppress the headers?
My dataset only returns a single column of values, but I need it to be without the headers. I attempted to remove the header in the SQL Query, but everything I tried there was unsuccessful. So if anyone knows how to remove headers using a SQL Query, I guess that would be helpful as well.
Cheers,
Wayne
March 25, 2016 at 7:34 pm
The switch you are referring to is for sqlcmd. Being that your code outputs a table (dataset) there is no true way of removing it just with a switch.
You could do it by piping the dataset variable to Format-Table and use -HideTableHeader (or whatever it is called) but you would loose the ability to send that object somewhere else; unless the purpose is to just output it to the console.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 25, 2016 at 10:36 pm
Hi Shawn,
Thanks very much for providing feedback on my issue. I didn't realize I was using a process that was not compatible with using a Switch like the one I have been trying to use. Thanks for the clarification on that. I certainly was spinning my wheeels in trying to get that to work.
As an explanation, the original script I found went part of the way to doing what I wanted. It used a Text File that was manually created to list entries that are updated in a Project Server Lookup Table. This process worked just fine.
However, what I am trying to do is generate that list directly from the Project Server database and then continue with the Lookup Table update process in the same PowerShell script.
The remainder of the code that updates the Lookup Table (and works just fine, with a Text file source), is as follows:
# Update the Lookup Table entries based on the values in the Target Dataset.
foreach($Value in $TargetDataSet.Tables[0])
{
$GUID = [System.Guid]::NewGuid()
$LookupRow = $LookupTable.LookupTableTrees.NewLookupTableTreesRow()
$LookupRow.LT_STRUCT_UID = $GUID
$LookupRow.LT_UID = $lookupTableGuid.LT_UID
$LookupRow.LT_VALUE_TEXT = $Value
$LookupRow.LT_VALUE_SORT_INDEX = ($Count ++)
$LookupTable.LookupTableTrees.AddLookupTableTreesRow($LookupRow)
}
In this version of the code, I changed the 'foreach' code to 'foreach($Value in $TargetDataSet.Tables[0])' so that it reads from the dataset, whereas the original was from coming from a Text File. This worked, except for the column header issue.
Do you know if it is possible to use the 'slqcmd' process to feed into this Lookup Table process?
If so, are you able to provide an example of the code I'd need to use to feed into my 'foreach' loop?
From what you mentioned, it sounds as though the process of piping the dataset variable to the Format-Table process will not work for me, as I then won't be able to the feed that output to the Lookup Table update process.
I was hoping to circumvent the need for a text file, but I guess an alternate would be to send the output from the SQL Query to a Text File and then clean that up before using this Lookup Table update process against that file. At least I know that Text File process works.
Any additional thoughts or suggestions would be very much appreciated.
Cheers,
Wayne
March 26, 2016 at 8:02 pm
Change this line:
foreach($Value in $TargetDataSet.Tables[0])
To this:
foreach($Value in $TargetDataSet.Tables[0].Rows)
And then the "$Value" contains each row, then you can pass each value to the specified property by referring to it as "variable.columnName". So if your $TargetDataSet has 3 columns name "col1", "col2", and "col3" in it you would reference each, within the foreach loop as such:
$Value.col1
$Value.col2
$Value.col3
#If you happen to have column with a space you may have to adjust it with something like:
$Value."col 1"
$Value."col 2"
Not that a script I published is the exact purpose you have but the Analyzing SQL Server Backups script[/url] does illustrate how I reference columns in a data set as you need I think. Just look through the script and you can see where I am populating data from each data set into my object variable "$result".
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 27, 2016 at 1:40 am
Hi Shawn,
You da man!!! Actually, I'm originally from New Zealand (been in the USA for 20 years), so I'm not sure that's the correct terminology, suffice to say that I'm stoked!
What you have provided has nailed it for me. Here I was, struggling to remove headers from a dataset, whereas in reality I was using the wrong technique to reference the data. But, just like that, you provided me with the solution that I needed to solve the issue I have been struggling with for a number of days.
I'll assume that this is a rookie shortcoming on my behalf, but I am so thankful for your feedback and for so easily correcting my problem. The article you composed was also extremely useful, helping me to better understand the concepts and techniques behind working with datasets using PowerShell commands.
Again, I very much appreciate your assistance with solving the problem I've been struggling with for so many hours. You've made my day!.
Cheers,
Wayne
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply