Write PowerShell Output to SQL

Welcome Forums General PowerShell Q&A Write PowerShell Output to SQL

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

  • Author
    Posts
  • #6389

    Member
    Points: 0
    Rank: Member

    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

The topic ‘Write PowerShell Output to SQL’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort