Leo Torres
asked on
powershell Multiple parameters
I have this data in a file.
Located at C:\Temp\UCounts.txt (first row is Blank in file)
How would I iterate thru the lines of Ucounts.txt file and pass it thru the code below and append the results to a CSV file.
Right now the my out put file is blank now sure why. The query returns results when ran in SQL Studio.
Located at C:\Temp\UCounts.txt (first row is Blank in file)
Enviornment,SQLInstance,UTMServer,Database,2
Enviornment,SQLInstance,UTMServer,Database,3
Enviornment,SQLInstance,UTMServer,Database,1
How would I iterate thru the lines of Ucounts.txt file and pass it thru the code below and append the results to a CSV file.
Right now the my out put file is blank now sure why. The query returns results when ran in SQL Studio.
Param([string]$Enviornment,[string]$SQLInstance,[string]$UTMServer,[string]$Database, [string]$Count)
add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
cls
$Qry = "USE [UTM_SITE]
select '$Enviornment' as Enviornment,'$SQLInstance' as [Server Name], CNAME, CDBNAME,CTSINSTNAM
from dbo.NTI_master
WHERE CDBNAME IN ('$Database' )
ORDER BY CDBNAME"
Write-Host $Qry
$Packages = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query $Qry
foreach ( $item in $Packages) {
$item | Export-Csv -path "C:\temp\Powershell\UTM_Report\UTMReport.csv" -Force -NoTypeInformation -Append
}
$Packages | ForEach-Object { $_ }
ASKER
NO nothing. Tried it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could keep you code as is and just save it as a .PS1 file, or convert it to function, then you can just pass it the info for the parameters. The following should work.
Import-Csv C:\Temp\UCounts.txt -Header @("Environment","SQLInstance","UTMServer","Database","Count") | ForEach { .\yourscript.ps1 $_.Environment $_.SQLInstance $_.UTMServer $_.Database $_.Count }
If you adjusted the parameters you could even make it accept pipeline input directly by parameter name. But I agree with Qlemo, that with the script itself doing the export it's probably best to just modify the script to include the reading of the input file.
ASKER
Well now i see data going to file.
But a few things its trying to Query Header Data.
Second its only entering 1 entry by second Query.
This is the print out of first 2 Queries
I am only getting one line.
Running Code
But a few things its trying to Query Header Data.
Second its only entering 1 entry by second Query.
This is the print out of first 2 Queries
I am only getting one line.
USE [UTM_SITE]
SELECT 'Enviornment' as Environment,'SiteServer' as [Server Name], CNAME, CDBNAME,CTSINSTNAM
FROM dbo.NTI_master
WHERE CDBNAME IN ('Database')
ORDER BY CDBNAME
USE [UTM_SITE]
SELECT 'ATL E1 UTM' as Environment,'MyserverHere' as [Server Name], CNAME, CDBNAME,CTSINSTNAM
FROM dbo.NTI_master
WHERE CDBNAME IN ('TEMP_UTM_28E88_09282014')
ORDER BY CDBNAME
Environment : ATL E1 UTM
Server Name : MyserverHere
CNAME :
CDBNAME : TEMP_UTM_28E88_09282014
CTSINSTNAM :
Running Code
add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
cls
$File = "C:\temp\Powershell\UTM_Report\UTMCounts.txt"
Clear-Content "C:\temp\Powershell\UTM_Report\UTMReport.csv"
foreach ($Line in Import-Csv $File -Header 'Environment','SQLInstance','UTMServer','Database','Count'){
$Qry = "USE [UTM_SITE]
SELECT '$($Line.Environment)' as Environment,'$($Line.SQLInstance)' as [Server Name], CNAME, CDBNAME,CTSINSTNAM
FROM dbo.NTI_master
WHERE CDBNAME IN ('$($Line.Database)')
ORDER BY CDBNAME"
Write-Host $Qry
$Packages = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query $Qry
$Packages | Export-CSV -NoType -append "C:\temp\Powershell\UTM_Report\UTMReport.csv" -Force #_$($Line.UTMServer).csv" -Force
$Packages
}
its trying to Query Header Data
you told us that the input file does not contain header info, but starts with an empty line. If that is no longer true, we have to change the import command, of course.
Your code modification will now export all data to a single CSV file. That's fine, but UTMServer looses its meaning and is superfluous then.
its only entering 1 entry by second Query.
Are you certain the first query results in rows at all? What you see on the screen is exactly what you should have in your CSV file, and what is retrieved from SQL Server.
you told us that the input file does not contain header info, but starts with an empty line. If that is no longer true, we have to change the import command, of course.
Your code modification will now export all data to a single CSV file. That's fine, but UTMServer looses its meaning and is superfluous then.
its only entering 1 entry by second Query.
Are you certain the first query results in rows at all? What you see on the screen is exactly what you should have in your CSV file, and what is retrieved from SQL Server.
ASKER
I was not passing parameters correctly. Now I am and its working.
Thank you.
Thank you.
Have you tried escaping the single quotes in your query? It looks like your variables are not being expanded.
Try like this:
Open in new window
PS: You have a typo "enviornment" instead of "environment", not that it breaks the script though.