Author Posts

January 1, 2012 at 12:00 am

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