Hi,
I am trying to take datastore information, including the datacenter name, and export it to SQLServer. I am using both functions out-datatable and write-datatable which Marc van Orsouw has posted and someone else has modified (http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-...).
I am getting all datastores and putting them in to an array with the datacenter name. However, the datacenter name has a hyphen (datacenter-xx). This will not write to sql because it is not in quotes I am told by my db admin. I have not been able to figure out how to get around this, would you help? Thanks!
No, the parameters to the Replace method are:
dquote-hyphen-dquote,dquote-squote-hyphen-dquote
Or just copy from the code I gave above.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Did you try the TSql escape character (a single quote) .
Something like this
$dcName = "datacenter-01"
$escDcName = $dcName.Replace("-","'-")
You replace the hyphen by a single quote-hyphen sequence.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Does it mean that single quote means '-','-' ?
No, the parameters to the Replace method are:
dquote-hyphen-dquote,dquote-squote-hyphen-dquote
Or just copy from the code I gave above.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Thanks Luc,
I'm actually doing the following:
$dt= @($Datastores | Select Datacenter, Name,@{N="Capacity";E={[math]::Round($_.CapacityGB,2)}}, @{N="Free_Space";E={[math]::Round($_.FreeSpaceGB,2)}},@{N="Allocated";E={[math]::Round((($_.ExtensionData.Summary.Capacity - $_.ExtensionData.Summary.FreeSpace + $_.ExtensionData.Summary.Uncommitted)/1GB),0)}}) | Out-DataTable
It is the datacenter name here that I can't seem to do anything with. Can you think of a way to convert the datastore name in this case?
Thanks
Try it like this
$dt= @($Datastores | Select @{N="Datacenter";E={$_.Datacenter.Replace("-","'-")}}, Name, @{N="Capacity";E={[math]::Round($_.CapacityGB,2)}}, @{N="Free_Space";E={[math]::Round($_.FreeSpaceGB,2)}}, @{N="Allocated";E={[math]::Round((($_.ExtensionData.Summary.Capacity - $_.ExtensionData.Summary.FreeSpace + $_.ExtensionData.Summary.Uncommitted)/1GB),0)}}) | Out-DataTable
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
The datacenter name comes up empty when I do this... ?
I assume you collected the values in the $Datastores variable with a Get-Datastore cmdlet ?
In that case, try it like this
$dt= @($Datastores | Select @{N="Datacenter";E={$_.Datacenter.Name.Replace("-","'-")}}, Name, @{N="Capacity";E={[math]::Round($_.CapacityGB,2)}}, @{N="Free_Space";E={[math]::Round($_.FreeSpaceGB,2)}}, @{N="Allocated";E={[math]::Round((($_.ExtensionData.Summary.Capacity - $_.ExtensionData.Summary.FreeSpace + $_.ExtensionData.Summary.Uncommitted)/1GB),0)}}) | Out-DataTable
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Yes, I did have the datastores values - everything showed up except for the datastores.
With the code below I got the datacenter name like :
datacenter'-xx as the name. In otherwords it has a single quote followed by a dash and it did go in to SQL just fine. Was that your expected output?
$dt= @($Datastores | Select @{N="Datacenter";E={$_.Datacenter.Name.Replace("-","'-")}}, Name, @{N="Capacity";E={[math]::Round($_.CapacityGB,2)}}, @{N="Free_Space";E={[math]::Round($_.FreeSpaceGB,2)}}, @{N="Allocated";E={[math]::Round((($_.ExtensionData.Summary.Capacity - $_.ExtensionData.Summary.FreeSpace + $_.ExtensionData.Summary.Uncommitted)/1GB),0)}}) | Out-DataTable
Yes, the single quote is the tSql escape character, with single quote - hyphen you escape the special meaning of the hyphen in TSql.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Great, thank you so much Luc! Love your book by the way