Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

powershell Multiple parameters

Posted on 2015-01-08
8
Medium Priority
?
148 Views
Last Modified: 2015-01-10
I have this data in a file.
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

Open in new window


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 { $_ }

Open in new window

0
Comment
Question by:Leo Torres
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 4

Expert Comment

by:Antyrael
ID: 40539662
Hello,

Have you tried escaping the single quotes in your query? It looks like your variables are not being expanded.
Try like this:
$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"

Open in new window


PS: You have a typo "enviornment" instead of "environment", not that it breaks the script though.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 40539696
NO nothing. Tried it.
0
 
LVL 4

Assisted Solution

by:Antyrael
Antyrael earned 400 total points
ID: 40539755
I'm sorry, I wasn't very sharp earlier and misread the question slightly.

Your script doesn't get any parameters from the csv-file as it is now.

I changed it into this, could you see if it works for you?
$csv = Import-Csv C:\Temp\UCounts.txt -Header @("Environment","SQLInstance","UTMServer","Database","Count")

add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
cls

foreach ($Line in $csv) {

	$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

	foreach ( $item in $Packages) { 
		$item |  Export-Csv -path "C:\temp\Powershell\UTM_Report\UTMReport_$($Line.UTMServer).csv" -Force -NoTypeInformation -Append 
	}

	$Packages | ForEach-Object { $_ }
}

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 71

Accepted Solution

by:
Qlemo earned 1600 total points
ID: 40540709
As shown above I would not call the script for each line but integrate the CSV processing into the script, in particular because the export is also done in the script itself. As general rule: either you read data and pipe them thru cmdlets/scripts, then process the output in a pipeline again, or you do all in one script.

I strongly recommend to NOT call Export-CSV for each package line, as your original code does. Instead, the output should be collected and exported at once. Anyway, as I see it, the inner foreach is superfluous.
add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
cls

foreach ($Line in Import-Csv C:\Temp\UCounts.txt -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 "C:\temp\Powershell\UTM_Report\UTMReport_$($Line.UTMServer).csv" -Force 
  $Packages 
}

Open in new window

0
 
LVL 41

Expert Comment

by:footech
ID: 40540783
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 }

Open in new window

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.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 40541687
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.

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  :

Open in new window


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 
}
                                          

Open in new window

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 40541919
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.
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 40542740
I was not passing parameters correctly. Now I am and its working.

Thank you.
0

Featured Post

Introducing the WatchGuard 420 Access Point

WatchGuard's newest access point includes an 802.11ac Wave 2 chipset, providing the fastest speeds for VoIP, video and music streaming, and large data file transfers. Additionally, enjoy the benefits of strong security as the 3rd radio delivers dedicated WIPS protection!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question