Unable to Pass variable value to For each loop

New Contributor

Hello Everyone,

 

I'm new to Poweshell, Someone tell what's wrong in below code? i want to pass $server value to for each loop but below code is failing

 

 

#getServerList
$Servers = Invoke-Sqlcmd -ServerInstance "localhost" -Query "SELECT [server_name] FROM [DBName].[dbo].[ServersList]" -Database "DBName"

#Write-output $server
foreach($Server in $Servers)
{
#SQl Query
$sql = "SELECT @@SERVERNAME AS 'ServerName', DB_NAME(dbid) AS 'Database',name, CONVERT(BIGINT, size) * 8 AS 'size_in_kb', filename
FROM master..sysaltfiles"
Invoke-Sqlcmd -ServerInstance $server -query $sql -Database master -OutputAs DataTables |
#WriteData
Write-SqlTableData -ServerInstance localhost -Database master -SchemaName dbo -TableName DatabasesSizes -Force
}

3 Replies

Hello @sivaponaka 

Sadly im no SQL expert. 

 

But, if you run "Write-output $server" do you get any output and does it look correct? 

 

Also, what error do you get when trying to run the code? 

 

Kind Regards
Oliwer Sjöberg

Thanks for your time @oliwer_sundgren 

 

My issue got resolved after adding parameterized property as below to code.

 

foreach($Server in $Servers.server_name)

 

 

No problem! @sivaponaka 
Ah thats nice! Good job :)