locked
Run Same Syntax Against Multiple Databases RRS feed

  • Question

  • I am thinking (and this may not be possible), but could I have a function which is a sql statement, and I pass in a variable for the tablename (similar to inline sql)?  So something like this (rough thought - not actual code)

    $tables = @('One', 'Two', 'Three')
    
    foreach ($table in $tables)
    
    Function SqlSelect {
        param ($table)
    	
    	$QueryToUse = "Select abc, def, geh, haha, lalal from servername.database.$table"
    }
    
    Function RunSQLQuery {
    	param($QueryToUse)
    	
    	#Connect To SQL Server
    	#Execute QueryToUse
    	#Save To CSV	
    }



    • Edited by Dwarf Goat Tuesday, August 11, 2015 6:37 PM
    Tuesday, August 11, 2015 2:36 PM

Answers

  • This is the script I came up with (I have tested and it is working) -- may be a bit overkill or lengthier code than needed, but it's my 1st PS script so I am happy it works!

    Function Run-Query
    {
    	param([string[]]$queries)
    		
    		$server='server'
    		$dbname='database'
    		$connStr='Server={0};Database={1};Integrated Security=SSPI;' -f $server,$dbname
    		For ($i = 0; $i -lt $queries.count; $i++)
    		{
    			$Command = New-Object System.Data.SqlClient.SqlCommand
    			$conn=New-Object System.Data.SqlClient.SQLConnection($connStr)
    			$conn.Open()			
    			$Command.Connection = $conn
    			$Command.CommandText=$queries[$i]
    			$Command.ExecuteNonQuery() 
    			$conn.Close()
    		}
    }
    
    Function Execute-SQLquery {
        param ($QueryName, $QueryString, $extractFile, $SaveDirectory)
    
    	$server = "server"
    	$database = "database"
    	$1234Path = "L:\Ready\"
    
    	$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
    	$connectionString = [string]::Format($connectionTemplate, $server, $database)
    	$connection = New-Object System.Data.SqlClient.SqlConnection
    	$connection.ConnectionString = $connectionString
    
    	$command = New-Object System.Data.SqlClient.SqlCommand
    	$command.CommandText = $QueryString
    	$command.Connection = $connection
    
    	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    	$SqlAdapter.SelectCommand = $command
    	$DataSet = New-Object System.Data.DataSet
    	$rowCount = $SqlAdapter.Fill($DataSet)
    	
        if ($rowCount -gt 0)
        {
    		if(!(Test-Path -path "$1234Path\$1234Date\"))
    		{
    			New-Item "$1234Path\$1234Date\" -type directory
    		}	
    		[System.IO.Directory]::CreateDirectory($SaveDirectory)
    		$filename = [IO.Path]::GetFileNameWithoutExtension($extractFile) + "_$date" + [IO.Path]::GetExtension($extractFile)
    		$extractFile = Join-Path $SaveDirectory $filename
    		$DataSet.Tables[0] | Export-Csv $extractFile -NoTypeInformation
    		$Global:MyArray += "$tables - $rowCountTotal `n"
        } 
    	$connection.Close()
    }
    
    $tables = @('1','2')
    
    foreach ($table in $tables)
    {
    	$fulllocation = "servername.databasename.dbo"+$table
    	Write-Host $fulllocation
    
    	$updatequery += "UPDATE $fulllocation SET lettersent = 'Yes'
    	                 Where terminated = 'Yes' AND terminatedDate = GetDate()"
    
    	Run-Query -queries $updatequery
    	
    	$1234TemplatesDirectory = "\\Z:\\Test\\CSVFiles\\"
    
    	$1WorkbookName = "Mitchell"
    	$2WorkbookName = "Jeromoe"
    
    	$1FileName = $1234TemplatesDirectory $1WorkbookName + ".csv"
    	$2FileName = $1234TemplatesDirectory $2WorkbookName + ".csv"
    
    	$date = Get-Date -f 'MM.dd.yy'
    	$1234Date = Get-Date -f 'MM.dd.yy'
    
    	$1Dir = "$1234Path\$1234Date\1\"
    	$2Dir = "$1234Path\$1234Date\2\"
    	$1234PID = "552233"
    	$1234IP = "192.168.0.1"
    	$1AID = "N/A"
    	$1PID = "N/A"
    	$2AID = "54369"
    	$1234AID
    	$1234WorkbookName 
    	$1234FileName
    	$1234Dir
    	
    	
    	switch ($table) 
        { 
            $1 
    		{
    			$1234AID = $1AID
    			$1234PID = $1PID
    			$1234WorkbookName = $1WorkbookName
    			$1234FileName = $1FileName
    			$1234Dir = $1Dir
    		}	
    		$2
    		{
    			$1234AID = $2AID
    			$1234WorkbookName = $2WorkbookName
    			$1234FileName = $2FileName
    			$1234Dir = $2Dir
    		}		
        }
    	
    	$selectquery = "Select abc, def, geh, haha, lalal FROM $fulllocation""
    
    
    	Execute-SQLquery $1234WorkbookName, $selectQuery, $1234FileName, $1234Dir
    
    }
    
    
    $EmailBody = $Global:MyArray 
    $Outlook = New-Object -ComObject Outlook.Application
    $Mail = $Outlook.CreateItem(0)
    $Mail.To = "ramalad34@gmail.com"
    $Mail.Subject = "Sent Through Powershell"
    $Mail.Body = $EmailBody
    $Mail.Send()
    
    stop-process -Id $PID



    • Edited by Dwarf Goat Wednesday, August 12, 2015 2:17 PM
    • Marked as answer by Dwarf Goat Wednesday, August 12, 2015 5:21 PM
    Wednesday, August 12, 2015 2:13 PM

All replies

  • You ask about multiple databases but you example shows multiple tables.

    I think you need to think this through and repost the question.

    First check here for information on how PowerShell works: https://technet.microsoft.com/en-us/scriptcenter/dd793612.aspx?f=255&MSPPError=-2147217396


    \_(ツ)_/

    Tuesday, August 11, 2015 6:34 PM
  • You ask about multiple databases but you example shows multiple tables.

    I think you need to think this through and repost the question.

    First check here for information on how PowerShell works: https://technet.microsoft.com/en-us/scriptcenter/dd793612.aspx?f=255&MSPPError=-2147217396


    \_(ツ)_/

    It is in fact different tables on the same database, I have updated my question.  Thank you for that link, I will dig through the thickets.

    Tuesday, August 11, 2015 6:38 PM
  • You ask about multiple databases but you example shows multiple tables.

    I think you need to think this through and repost the question.

    First check here for information on how PowerShell works: https://technet.microsoft.com/en-us/scriptcenter/dd793612.aspx?f=255&MSPPError=-2147217396


    \_(ツ)_/

    I know how to connect to SQL Server and execute 1 query and export the results to a csv, I am not clear on (and didn't see it in the link provided) how to iterate an array of table names.

    Tuesday, August 11, 2015 8:08 PM
  • In any scripting or programming language there are syntax constructs used for enumerating collections. PowerShell has many of these.

    I posted the link so you could take time to learn the basics of PowerShell.  YOu have copied and posted  some things with no understanding of what you have posted.  This has led to an ambiguous question.

    Start by going through the tutorials until you understand what PowerShell is and how it works.  Look for totorials on foreach, foreach-object, for( a in b) and other scripting constructs that will let you write a script.

    In a few hours you will understand how to use this.


    \_(ツ)_/

    Tuesday, August 11, 2015 8:20 PM
  • This is the script I came up with (I have tested and it is working) -- may be a bit overkill or lengthier code than needed, but it's my 1st PS script so I am happy it works!

    Function Run-Query
    {
    	param([string[]]$queries)
    		
    		$server='server'
    		$dbname='database'
    		$connStr='Server={0};Database={1};Integrated Security=SSPI;' -f $server,$dbname
    		For ($i = 0; $i -lt $queries.count; $i++)
    		{
    			$Command = New-Object System.Data.SqlClient.SqlCommand
    			$conn=New-Object System.Data.SqlClient.SQLConnection($connStr)
    			$conn.Open()			
    			$Command.Connection = $conn
    			$Command.CommandText=$queries[$i]
    			$Command.ExecuteNonQuery() 
    			$conn.Close()
    		}
    }
    
    Function Execute-SQLquery {
        param ($QueryName, $QueryString, $extractFile, $SaveDirectory)
    
    	$server = "server"
    	$database = "database"
    	$1234Path = "L:\Ready\"
    
    	$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
    	$connectionString = [string]::Format($connectionTemplate, $server, $database)
    	$connection = New-Object System.Data.SqlClient.SqlConnection
    	$connection.ConnectionString = $connectionString
    
    	$command = New-Object System.Data.SqlClient.SqlCommand
    	$command.CommandText = $QueryString
    	$command.Connection = $connection
    
    	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    	$SqlAdapter.SelectCommand = $command
    	$DataSet = New-Object System.Data.DataSet
    	$rowCount = $SqlAdapter.Fill($DataSet)
    	
        if ($rowCount -gt 0)
        {
    		if(!(Test-Path -path "$1234Path\$1234Date\"))
    		{
    			New-Item "$1234Path\$1234Date\" -type directory
    		}	
    		[System.IO.Directory]::CreateDirectory($SaveDirectory)
    		$filename = [IO.Path]::GetFileNameWithoutExtension($extractFile) + "_$date" + [IO.Path]::GetExtension($extractFile)
    		$extractFile = Join-Path $SaveDirectory $filename
    		$DataSet.Tables[0] | Export-Csv $extractFile -NoTypeInformation
    		$Global:MyArray += "$tables - $rowCountTotal `n"
        } 
    	$connection.Close()
    }
    
    $tables = @('1','2')
    
    foreach ($table in $tables)
    {
    	$fulllocation = "servername.databasename.dbo"+$table
    	Write-Host $fulllocation
    
    	$updatequery += "UPDATE $fulllocation SET lettersent = 'Yes'
    	                 Where terminated = 'Yes' AND terminatedDate = GetDate()"
    
    	Run-Query -queries $updatequery
    	
    	$1234TemplatesDirectory = "\\Z:\\Test\\CSVFiles\\"
    
    	$1WorkbookName = "Mitchell"
    	$2WorkbookName = "Jeromoe"
    
    	$1FileName = $1234TemplatesDirectory $1WorkbookName + ".csv"
    	$2FileName = $1234TemplatesDirectory $2WorkbookName + ".csv"
    
    	$date = Get-Date -f 'MM.dd.yy'
    	$1234Date = Get-Date -f 'MM.dd.yy'
    
    	$1Dir = "$1234Path\$1234Date\1\"
    	$2Dir = "$1234Path\$1234Date\2\"
    	$1234PID = "552233"
    	$1234IP = "192.168.0.1"
    	$1AID = "N/A"
    	$1PID = "N/A"
    	$2AID = "54369"
    	$1234AID
    	$1234WorkbookName 
    	$1234FileName
    	$1234Dir
    	
    	
    	switch ($table) 
        { 
            $1 
    		{
    			$1234AID = $1AID
    			$1234PID = $1PID
    			$1234WorkbookName = $1WorkbookName
    			$1234FileName = $1FileName
    			$1234Dir = $1Dir
    		}	
    		$2
    		{
    			$1234AID = $2AID
    			$1234WorkbookName = $2WorkbookName
    			$1234FileName = $2FileName
    			$1234Dir = $2Dir
    		}		
        }
    	
    	$selectquery = "Select abc, def, geh, haha, lalal FROM $fulllocation""
    
    
    	Execute-SQLquery $1234WorkbookName, $selectQuery, $1234FileName, $1234Dir
    
    }
    
    
    $EmailBody = $Global:MyArray 
    $Outlook = New-Object -ComObject Outlook.Application
    $Mail = $Outlook.CreateItem(0)
    $Mail.To = "ramalad34@gmail.com"
    $Mail.Subject = "Sent Through Powershell"
    $Mail.Body = $EmailBody
    $Mail.Send()
    
    stop-process -Id $PID



    • Edited by Dwarf Goat Wednesday, August 12, 2015 2:17 PM
    • Marked as answer by Dwarf Goat Wednesday, August 12, 2015 5:21 PM
    Wednesday, August 12, 2015 2:13 PM
  • Actually as fancy as that looks it will not work as requested.

    When you have  errors please start a new topic with the code that causes the error and the full error message.  YOU have may errors that may be typos and you might find them if you actually run this.  There also appear to be some logic issues.

    To send mail you can use "Send-MailMessage" as it is a simple one-line command.


    \_(ツ)_/

    Wednesday, August 12, 2015 6:13 PM