VMware Cloud Community
bvi1006
Enthusiast
Enthusiast
Jump to solution

Remove the hyphen of the datacenter name for export purposes

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!

Tags (1)
Reply
0 Kudos
1 Solution

Accepted Solutions
LucD
Leadership
Leadership
Jump to solution

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

View solution in original post

Reply
0 Kudos
10 Replies
LucD
Leadership
Leadership
Jump to solution

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

Reply
0 Kudos
mydearcosmo
Contributor
Contributor
Jump to solution

Does it mean that single quote means    '-','-'    ?

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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

Reply
0 Kudos
bvi1006
Enthusiast
Enthusiast
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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

Reply
0 Kudos
bvi1006
Enthusiast
Enthusiast
Jump to solution

The datacenter name comes up empty when I do this... ?

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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

Reply
0 Kudos
bvi1006
Enthusiast
Enthusiast
Jump to solution

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
 

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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

Reply
0 Kudos
bvi1006
Enthusiast
Enthusiast
Jump to solution

Great, thank you so much Luc! Love your book by the way Smiley Wink

Reply
0 Kudos