ebooklub - Monday, January 23, 2017 9:01 AM

Thanks Jeff,
PowerShell replacement of sql based stored procedures is part our project
bellow is solution I used (part of the code)

  #call to unix script
     $UnixOutput = (Run-Cmd -command "listdbpriv -server  $ServerName -user").split("`n")
    
     #$LogicalServerName = Null
     #$Primary1 = Null
     #$AssistUser =Null
     #$Role =Null
       
    foreach( $UnixOutput_row in $UnixOutput)
    {
     #output produce extra empty line
        if($UnixOutput_row.length -gt 0)
        {

            #$UnixOutput_row = ($UnixOutput_row -replace '\s+', ' ')
           
            $LogicalServerName    = $UnixOutput_row.split()[0]
            # first row of output contain column names
            if($LogicalServerName -eq "LogicalServerName")
            {
                $LogicalServerName_start = $UnixOutput_row.IndexOf("LogicalServerName")
                $Primary_start = $UnixOutput_row.IndexOf("Primary")
                $assistUser_start = $UnixOutput_row.IndexOf("assistUser")
                $Role_start = $UnixOutput_row.IndexOf("Role")
                $len = $UnixOutput_row.Length
            }
                    
           

            if(-not($LogicalServerName -eq "LogicalServerName"))
            {  
               #format is logigalservername.dbname
               $Primary_contain_dot =  $UnixOutput_row.Substring($Primary_start,($AssistUser_start - $Primary_start))
              
               if ($Primary_contain_dot -like '*.*')
               {
                    $Primary1 = $Primary_contain_dot.split('.')[1]
               }
               
                $AssistUser = $UnixOutput_row.Substring($AssistUser_start,($Role_start - $AssistUser_start ))
               
                $len = $UnixOutput_row.Length
               
                $Role = $UnixOutput_row.Substring($Role_start,($len - $Role_start))
               
                $SqlQuery ="
                            INSERT INTO [dbo].[ServerDbPrivUsers]
                                       ([ServerName]
                                      -- ,[LogicalServerName]
                                       ,[Primary1]
                                       ,[assistUser]
                                       ,[Role])
                             VALUES
                               ('$Servername',
                               -- '$Logicalservername',                           
                                '$Primary1',
                                '$AssistUser',
                                '$Role'
                               )"
                           
                                      
                ...custom call to insert row in table
             }
        }                             
          
    }
}