Help SQL Query Issues

Tagged: , , ,

This topic contains 1 reply, has 2 voices, and was last updated by  Chris Bakker 5 days, 17 hours ago.

  • Author
    Posts
  • #77226

    Keith
    Participant

    Hello,

    I am in a bit of a bind, and am looking for some help because I am just about at the end of what I know how to do with the script I am making. I am making a PS script to replace a Perl script we use to pull metric data from out database and I am able to get the connection through the ODBC and pull data however, when I include all of the data tables that are required to be pulled my script just freezes running the query. But If I pull the table away that is causing the freeze I can pull the data just fine.

    I am hoping someone can look at it and make some suggestions on what I can do to fix the problem.

    $id = Get-Credential "Domain\$("Username")"
    $userId = $id.UserName.split("\")[1].tolower()
    $password = $id.GetNetworkCredential().Password 
        write-host "Running Query"
        ##### Start the database connection and set up environment
        $DbString="DSN=AR SYSTEM ODBC Driver;UID=$userID;PWD=$password"
        $DBConnection=New-Object System.Data.Odbc.OdbcConnection
        $DBCommand=New-Object System.Data.Odbc.OdbcCommand
        
        $DBConnection.ConnectionString=$DbString
        $DBConnection.Open()
        $DBCommand.Connection=$DBConnection
    
    #Not Fully Functional Yet
    #$DateRange = Read-Host "Enter Days"
    
    ##Query Fields
    $DBCommand.CommandText=
    "SELECT
        Detailed_Decription,
        Description,
        Incident_Number,
        Status, 
        Last_Resolved_Date, 
        Reported_Date, 
        Effort_Hour,
        Effort_Minute,
        Effort_Second, 
        AssigneeEffortDurationSeconds,
        Assignee, 
        Assigned_Group, 
        Priority 
    FROM 
        Help_Desk
    WHERE Status = 'In Progress' AND 
        Assigned_Group ='Group1'
        OR Assigned_Group ='Group1'
        OR Assigned_Group ='Group3'
        OR Assigned_Group ='Group4'"
    #Execute Search
        $DBResult=$DBCommand.ExecuteReader()
        $UserTable=New-Object system.data.datatable
        $UserTable.load($DBResult)
            
         $UserTable | Out-GridView
    

    The issue is with the Detailed_Decription field. If I query all of the fields with it in there if freezes, but if I query 1 record it pulls it up.

    I am wondering if it is possible to maybe create an array for the data to be exported into a CSV if that makes sense. Any help or suggestions will be appreciated.

    Thank you

  • #77263

    Chris Bakker
    Participant

    Cant test this without the data n the sql table. But is Detailed_Decription is empty, probably the datatable wont like that.
    Put is on the end of the file select and see what happens.

    For what the query concerns:

    WHERE Status = 'In Progress' AND
    Assigned_Group ='Group1'
    OR Assigned_Group ='Group1'
    OR Assigned_Group ='Group3'
    OR Assigned_Group ='Group4'"

    Is this want you want?

    WHERE Status = 'In Progress' AND
    (Assigned_Group in ('Group1','Group2','Group3','Group4'))"

You must be logged in to reply to this topic.