Although this is technically not a VITK question I can see that this could be useful for several VITK users.
Since I couldn't find any straightforward examples I came up with this.
$SQLSERVER = <SQL-servername>
$Database = <SQL-dbname>
$Table = "VMmachines"
$Create = "if OBJECT_ID('$Table') IS NULL`n" +
"BEGIN`n" +
"CREATE TABLE $Table ( `n" +
"Id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() NOT NULL PRIMARY KEY, `n" +
"VMname NVARCHAR(35) NULL, `n" +
"Memory INT NULL, `n" +
"Host NVARCHAR(35) NULL) `n" +
"END`n"
$Select = "select * from $Table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True"
$SqlConnection.Open()
# Create command object
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
# Create the table
$SqlCmd.CommandText = $Create
$SqlCmd.ExecuteNonQuery()
# Read current rows
$SqlCmd.CommandText = $Select
# Create adapter object
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
# Create dataset object
$DataSet = New-Object System.Data.DataSet
# Read current rows from DB into dataset
$SqlAdapter.Fill($DataSet, $Table)
# Generate Insert, Delete and Update commands
$SqlCmdBuilder = New-Object System.Data.SqlClient.SqlCommandBuilder
$SqlCmdBuilder.DataAdapter = $SqlAdapter
# Add news rows
Get-VM | %{
$dataset.Tables[$Table].Rows.Add($null, $_.Name,$_.MemoryMB,$_.Host)
}
# Write updated dataset to DB
$SqlAdapter.Update($DataSet, $Table)
$SqlConnection.Close()
In short what this does:
- it connects to the SQL server and on there to the database you specify in $Database
- it checks if there is a table called $Table
- if there isn't such a table, the table is created
- then it loops through all VMs and writes for each VM a number of properties to the table
- I let the SQL server generate a unique ID for each row that was added. This can probably be done in a different and better way.
For more info on the SQL objects used see the SMO info on for example the MS site.
Some advise:
- do not use the VC database for these kind of tables !
- talk to your DBA before creating databases on the SQL server(s) !
- there are multiple methods to accomplish this. Check what is the standard in your environment !
