Write PowerShell Output to SQL

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 10 months ago.

  • Author
    Posts
  • #6389

    by toybits at 2013-04-03 05:18:13

    Hi all,

    Not sure where to start here as I'm not a SQL person.

    I'm producing out output with Exchange Server Name and Number of mailboxes.

    It just creates a variable called $output that looks like this.

    Name MailboxCount
    —- ————
    Database1 162
    Database2 167
    Database3 178
    Database4 183
    Database5 185
    Database6 185

    We've actually got 144DB's.

    I then need to insert it into a SQL table in a database. This is where I'm a bit clueless. I'm googling my little heart out but any advice would be very appreciated.

    #Get a list of mailbox databases as well as 'Status' information
    $Databases = Get-MailboxDatabase UK-MBX-000* -Status

    #Count each database mailboxes
    $output = $Databases | select Name,@{n='MailboxCount';e={(Get-Mailbox -Database $_.Name).Count}} | Sort-Object MailboxCount

    #Connect to the SQL database
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Server=MYSQLServer;Database=MySQLDB;trusted_connection=true;"

    by cmille19 at 2013-04-03 07:58:15

    One way is to loop through your output and call an insert command:

    #Connect to the SQL database
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Server=MYSQLServer;Database=MySQLDB;trusted_connection=true;"
    $Conn.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Conn

    $output | foreach {
    #write-host "INSERT INTO MySQLTable (Name,MailboxCount) VALUES ('$($_.Name)',$($_.MailboxCount))"
    $Command.CommandText = "INSERT INTO MySQLTable (Name,MailboxCount) VALUES ('$($_.Name)',$($_.MailboxCount))"
    $Command.ExecuteNonQuery() | out-null
    }

    $Conn.Close()

    by toybits at 2013-04-03 08:25:16

    Hi,

    Just came on to make a reply and it looks like I got pretty close to what you've done thanks cmile19. Only thing I'm doing is to export it to a csv first. We want to keep a copy of each import we give to the SQL team. I'm not sure what the | Out-Null does at the end though?

    Thanks for your reply.

    #Get a list of mailbox databases as well as 'Status' information
    $Databases = Get-MailboxDatabase MBX* -Status

    #Count each database mailboxes
    $output = $Databases | select Name,@{n='MailboxCount';e={(Get-Mailbox -Database $_.Name).Count}} | Sort-Object MailboxCount

    #Export the Output to CSV for our keeping
    $csvFile = "c:\ScottTools\Exports\MBCountByDatabase.csv"
    $output | Export-Csv $csvFile -NoTypeInformation

    #Connect to the SQL database
    $sqlsvr = "SQLServerName.uk.dev.local"
    $database = "DatabaseName"
    $table = "exchangeMailStores"
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Data Source=$sqlsvr;Initial Catalog=$database; Integrated Security=SSPI"
    $conn.Open()
    $cmd = $conn.CreateCommand()

    Import-Csv $csvFile | %{
    $cmd.CommandText = "INSERT INTO $table (homemdb, availableCount) VALUES ('$($_.Name)','$($_.MailboxCount)')"

    #Execute Query
    $cmd.ExecuteNonQuery()
    }

    by toybits at 2013-04-03 08:30:13

    Oh one other thing, any pointers on how I clear the table before adding the new data?

    It's giving me an exception error as the homeMDB column is set to primary key. We can take that off I know but was curious.

    Thanks again.

    by cmille19 at 2013-04-03 08:59:36

    The out-null is used because a call to ExecuteNonQuery returns the number of rows affected. Since I don't need our want the output I'll send it to null:

    $Command.ExecuteNonQuery() | out-null

    Other variants of sending things to null you may see in scripts (although out-null is used more in Powershell scripting):

    $null = $Command.ExecuteNonQuery()
    $Command.ExecuteNonQuery() > $null

    You could just delete:

    Connect to the SQL database
    $sqlsvr = "SQLServerName.uk.dev.local"
    $database = "DatabaseName"
    $table = "exchangeMailStores"
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Data Source=$sqlsvr;Initial Catalog=$database; Integrated Security=SSPI"
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "DELETE $table"
    #Execute Query
    $cmd.ExecuteNonQuery()

    by toybits at 2013-04-04 00:45:14

    Ahh OK understood. That's wicked thanks for your help cmille19

You must be logged in to reply to this topic.