Sql Server 2012 Out-DataTable Alternative

PowerShell Help
1

I have seen people use the Out-DataTable cmdlet to create an acceptable data format for writing powershell obtained non-sql query information to a sql table.

i.e. Taking this icm -computername Server1 {Get-CimInstance Win32_Processor} | out-datatable and writing it into a sql table.

My problem is my company is on Windows Server 2012 running SQL Server 2012. We have powershell 3.0 installed and the SQLPS installed aswell.

OUT-DATATABLE is not an option on 2012. I cannot seem to find an alternative to be able to accomplish this task. I would be fine to be able to import a .csv or .txt file into the sql table if needed.

(b)Please assist.(/b)

It would seem logical to me for Microsoft to add this sort of functionality as a native cmdlet process.

2

There are multiple ways to get data into SQL. I needed a way to update a record if it existed, otherwise create a new record. The example below was uploading some DFS server information, but you should get the jest of what it’s doing. Basically, you would generate a PSObject with the same headers as your SQL table and ensure that date\time is a date\time object, etc. If the value is empty, send a DBNull to the database. I did test some other methods like bulk updates, which are much faster because you aren’t looping thru the results checking if a record exists, but they do a simple write operation. If you are not keeping historical information and say just want an up-to-date inventory, I would wipe the table and just do a bulk insert. Hiope this helps.

function Update-DfsAdSqlDb {
     
    [CmdletBinding()]	
    param(
        [parameter(Mandatory=$true,
        ValueFromPipeline=$true)]		
        [PSObject]$Data,
        [string]$ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=TABLE;Data Source=SERVERNAME;Integrated Security=SSPI", 
        [parameter(Mandatory=$true,
        ValueFromPipeline=$true)]        
        [string]$Table
	)

    $adOpenStatic = 3
    $adLockOptimistic = 3

    $objConnection = New-Object -com "ADODB.Connection"
    $objRecordSet = New-Object -com "ADODB.Recordset"

    $objConnection.Open($ConnectionString)

    if($objConnection.state -eq 0){
      #Add-Log -Message ("Connection state is {0}. Unable to make connection to database: {1}" -f $objConnection.State, $connStr) -ErrLevel Error
      exit 1
    } 

    $Data | foreach {
        Write-Verbose ("Processing user \\{0}\{1}\{2} ..." -f $_.ServerName, $_.ShareName, $_.SamAccountName)
        $objRecordset.Open(("Select * From {0} Where SamAccountName  = '{1}' And ShareName = '{2}' And ServerName = '{3}'" -f $Table, $_.SamAccountName, $_.ShareName, $_.ServerName), $objConnection,$adOpenStatic,$adLockOptimistic)    
        # Only add records if no current record exists
        if ($objRecordSet.RecordCount -eq 0){$objRecordSet.AddNew()}

        $_.PSObject.Properties | foreach{
            Write-Verbose "Setting property {0} to {1}" -f $_.Name, $_.Value
            $columnName = $_.Name
            $value = $_.Value

            if (![string]::IsNullOrEmpty($value)) {
                try {
                    $objRecordSet.Fields.Item($columnName).Value = $value
                }
                catch {
                   Write-Host ("Unable to process column {0} with value {1} : {2}" -f $columnName, $value, $_.Exception.Message)
                }
            } 
            else {
                #"NULL: Updating database field {0} with value {1}" -f $columnName, $value
                $objRecordSet.Fields.Item($columnName).Value = [DBNull]::Value
            }               
        }
        $objRecordSet.Update()
        $objRecordSet.Close()
    }
    $objConnection.Close()
}