Sql Server 2012 Out-DataTable Alternative

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 2 years, 1 month ago.

  • Author
    Posts
  • #20288
    Profile photo of Reid Herron
    Reid Herron
    Participant

    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.

  • #20305
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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()
    }
    

You must be logged in to reply to this topic.