Unable to Pass variable value to For each loop

%3CLINGO-SUB%20id%3D%22lingo-sub-1083137%22%20slang%3D%22en-US%22%3EUnable%20to%20Pass%20variable%20value%20to%20For%20each%20loop%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1083137%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20new%20to%20Poweshell%2C%20Someone%20tell%20what's%26nbsp%3Bwrong%20in%20below%20code%3F%20i%20want%20to%20pass%20%24server%20value%20to%20for%20each%20loop%20but%20below%20code%20is%20failing%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23getServerList%3CBR%20%2F%3E%24Servers%20%3D%20Invoke-Sqlcmd%20-ServerInstance%20%22localhost%22%20-Query%20%22SELECT%20%5Bserver_name%5D%20FROM%20%5BDBName%5D.%5Bdbo%5D.%5BServersList%5D%22%20-Database%20%22DBName%22%3C%2FP%3E%3CP%3E%23Write-output%20%24server%3CBR%20%2F%3Eforeach(%24Server%20in%20%24Servers)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%23SQl%20Query%3CBR%20%2F%3E%24sql%20%3D%20%22SELECT%20%40%40SERVERNAME%20AS%20'ServerName'%2C%20DB_NAME(dbid)%20AS%20'Database'%2Cname%2C%20CONVERT(BIGINT%2C%20size)%20*%208%20AS%20'size_in_kb'%2C%20filename%3CBR%20%2F%3EFROM%20master..sysaltfiles%22%3CBR%20%2F%3EInvoke-Sqlcmd%20-ServerInstance%20%24server%20-query%20%24sql%20-Database%20master%20-OutputAs%20DataTables%20%7C%3CBR%20%2F%3E%23WriteData%3CBR%20%2F%3EWrite-SqlTableData%20-ServerInstance%20localhost%20-Database%20master%20-SchemaName%20dbo%20-TableName%20DatabasesSizes%20-Force%3CBR%20%2F%3E%7D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1083137%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPower%20shell%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1093929%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20Pass%20variable%20value%20to%20For%20each%20loop%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1093929%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F501752%22%20target%3D%22_blank%22%3E%40sivaponaka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESadly%20im%20no%20SQL%20expert.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20if%20you%20run%20%22%3CSPAN%3EWrite-output%20%24server%22%20do%20you%20get%20any%20output%20and%20does%20it%20look%20correct%3F%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAlso%2C%20what%20error%20do%20you%20get%20when%20trying%20to%20run%20the%20code%3F%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EKind%20RegardsOliwer%20Sj%C3%B6berg%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1093981%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20Pass%20variable%20value%20to%20For%20each%20loop%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1093981%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20your%20time%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345947%22%20target%3D%22_blank%22%3E%40oliwer_sjoberg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EMy%20issue%20got%20resolved%20after%20adding%3C%2FEM%3E%26nbsp%3B%3CEM%3Eparameterized%20property%26nbsp%3Bas%20below%20to%26nbsp%3Bcode.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3Eforeach(%24Server%20in%20%24Servers.server_name)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1094105%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20Pass%20variable%20value%20to%20For%20each%20loop%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1094105%22%20slang%3D%22en-US%22%3E%3CP%3ENo%20problem!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F501752%22%20target%3D%22_blank%22%3E%40sivaponaka%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EAh%20thats%20nice!%20Good%20job%20%3A)%3C%2Fimg%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

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

Highlighted

Thanks for your time @oliwer_sjoberg 

 

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

 

foreach($Server in $Servers.server_name)

 

 

Highlighted

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