November 20, 2014 at 6:54 am
Hi,
The function scripted below executes a stored proc (SP) in SQL. This SP return the database file paths.
example:
D:\MSSQL11.Test\MSSQL\Data\Test08.mdf
D:\MSSQL11.Test\MSSQL\Data\Test08_log.ldf
The SP drops the test db and lists the database file locations. For each row returned I want to delete the file. I know I can do this with Remove-Item $Path but I am not sure how to feed this parameter and run through each file.
[String] $inventoryinstance="myinstance"
[String] $inventorydatabase="DBA_DB"
#add-pssnapin sqlserverprovidersnapin100
#add-pssnapin sqlservercmdletsnapin100
$smoAssembly = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
if (!($smoVersion))
{ Set-Variable -name SmoVersion -value $smoAssembly.GetName().Version.Major -Scope Global -Option Constant -Description "SQLPSX variable" }
[reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') > $null
function Get-ServerList
{
$instance = New-Object ('Microsoft.SQLServer.Management.SMO.Server') $inventoryinstance
$database = $instance.Databases[$inventorydatabase]
$result=$database.ExecuteWithResults("dbo.up_DatabaseFiles_rs")
$result.Tables | foreach { $_.Rows}
}
November 20, 2014 at 7:59 am
I am actively trying to work on this function.
I have slightly altered my code but now I am getting an error:
[String] $inventoryinstance="MyInstance"
[String] $inventorydatabase="DBA_DB"
#add-pssnapin sqlserverprovidersnapin100
#add-pssnapin sqlservercmdletsnapin100
$smoAssembly = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
if (!($smoVersion))
{ Set-Variable -name SmoVersion -value $smoAssembly.GetName().Version.Major -Scope Global -Option Constant -Description "SQLPSX variable" }
[reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') > $null
function Get-ServerList
{
$instance = New-Object ('Microsoft.SQLServer.Management.SMO.Server') $inventoryinstance
$database = $instance.Databases[$inventorydatabase]
$result=$database.ExecuteWithResults("dbo.up_DatabaseFile_rs")
$result | foreach $result {remove-item $result.fullname -force}
#| ForEach-Object {Remove-Item $_.fullname}
}
Get-ServerList
Error Message:
foreach : Input name "System.Data.DataSet" cannot be resolved to a method.
At line:20 char:12
+ $result | foreach $result {remove-item $result.fullname -force}
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (System.Data.DataSet:PSObject) [ForEach-Object], PSArgumentException
+ FullyQualifiedErrorId : MethodNotFound,Microsoft.PowerShell.Commands.ForEachObjectCommand
November 20, 2014 at 9:57 am
Should
$result | foreach $result {remove-item $result.fullname -force}
be
$result | foreach {remove-item $result.fullname -force}
or
$result | foreach {remove-item $_.fullname -force}
November 20, 2014 at 9:58 am
You need to pipe through the result to the for each command.
I am sorry but I have little time this afternoon but this might help:
$database.ExecuteWithResults().Tables[0].Rows | ForEach-Object { Remove-Item $_.Fullname -Force }
I would use Write-Output to check what you are getting e.g.
$database.ExecuteWithResults().Tables[0].Rows | ForEach-Object { Write-Output $_.Fullname }
or even build up your understanding like the following:
$database.ExecuteWithResults().Tables[0].Rows | ForEach-Object { Write-Output $_.GetType().ToString() }
$database.ExecuteWithResults().Tables | ForEach-Object { Write-Output $_.GetType().ToString() }
$database.ExecuteWithResults() | ForEach-Object { Write-Output $_.GetType().ToString() }
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 20, 2014 at 10:00 am
Gazareth (11/20/2014)
Should$result | foreach $result {remove-item $result.fullname -force}
be
$result | foreach {remove-item $result.fullname -force}
or
$result | foreach {remove-item $_.fullname -force}
The latter. The former doesn't use the current object in the loop i.e. $_.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 21, 2014 at 2:51 am
Gary Varga (11/20/2014)
Gazareth (11/20/2014)
Should$result | foreach $result {remove-item $result.fullname -force}
be
$result | foreach {remove-item $result.fullname -force}
or
$result | foreach {remove-item $_.fullname -force}
The latter. The former doesn't use the current object in the loop i.e. $_.
Thought so, cheers
November 21, 2014 at 4:02 am
I am using the code as you suggested but I cant seem to get the Remove-Item code to work.
When I run the second line of code the output is what I expect but when I run the first line I get the following error. What am I getting wrong?
Remove-Item : Cannot find path 'C:\Users\System.Data.DataRow' because it does not exist.
At line:19 char:93
+ ... rEach-Object { Remove-Item $_ -Force }
+ ~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (C:\Users\...em.Data.DataRow:String) [Remove-Item], ItemNotFoundException
+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.RemoveItemCommand
$database.ExecuteWithResults("dbo.up_DatabaseFile_rs").Tables[0] | ForEach-Object { Remove-Item $_-Force }
$database.ExecuteWithResults("dbo.up_DatabaseFile_rs").Tables[0] | ForEach-Object { Write-Output $_}
November 21, 2014 at 4:38 am
I think that you need to know what you are dealing with i.e. what is $_?
Instead of immediately using the following:
Remove-Item $_-Force
Perhaps you should be doing the following:
Write-Output $_.GetType().ToString()
Then you know what you can do with it. Maybe the following is the answer:
Remove-Item $_.Filename -Force
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 21, 2014 at 4:58 am
When I run the $._GetType() the following is returned: System.Data.DataRow
November 21, 2014 at 5:07 am
So what do you get from:
Write-Output $_[0].ToString()
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 21, 2014 at 5:13 am
I tested the $_[0] within the Remove_item and it worked. Can you explain what the [0] does?
Thank you for your time. Much appreciated.
[String] $inventoryinstance="MyInstance"
[String] $inventorydatabase="DBA_DB"
#add-pssnapin sqlserverprovidersnapin100
#add-pssnapin sqlservercmdletsnapin100
$smoAssembly = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
if (!($smoVersion))
{ Set-Variable -name SmoVersion -value $smoAssembly.GetName().Version.Major -Scope Global -Option Constant -Description "SQLPSX variable" }
[reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') > $null
function Get-ServerList
{
$instance = New-Object ('Microsoft.SQLServer.Management.SMO.Server') $inventoryinstance
$database = $instance.Databases[$inventorydatabase]
#$database.ExecuteWithResults("dbo.up_DatabaseFiles_rs").Tables[0] | ForEach-Object {Write-Output $_[0].ToString()}
$database.ExecuteWithResults("dbo.up_DatabaseFiles_rs").Tables[0] | ForEach-Object {Remove-Item $_[0] -Force }
}
November 21, 2014 at 5:36 am
$_ is a DataRow as you previously highlighted. A DataRow is made up of data items. You can access an individual item using the index operator []. There are two versions of the index operator; one takes an integer and one takes a string. The integer version takes the zero based column index (i.e. 0 is the first column) whereas the string version takes the column name (e.g. "A" if the query was "SELECT A from TableT").
Details of DataRow can be found here.
So what you have got is a collection of DataRows and for each of those rows ($_) you are accessing the value of the first column for that row ($_[0]).
(You're most welcome.)
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply