Help SQL Query Issues

This topic contains 6 replies, has 4 voices, and was last updated by  Keith 1 month 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'))"

  • #79712

    Keith
    Participant

    Sorry for the late response I ended up going out of town and this fell by the wayside a bit. However, is it possible to have the query return 'Null' for any blank incidents that have a blank detailed_decription?

    I didn't even think that a blank field would be causing the error.

    • #79781

      iain Barnetson
      Participant

      re replacing Null values: SELECT ISNULL(detailed_decription,'makeupwhatyouwant') FROM Help_Desk

    • #79928

      Keith
      Participant

      Thanks,

      I tried it and doesn't seem to work. Maybe because I am using an ODBC connector and not a SQL one?

  • #79931

    Rick
    Participant

    I'm not a SQL master but would this work? I couldn't test it fully so you might need to tweak it.

    $id = Get-Credential "Domain\$("Username")"
    $Global:userId = $id.UserName.split("\")[1].tolower()
    $Global:password = $id.GetNetworkCredential().Password 
    write-host "Running Query"
    
    
    [string] $UserSqlQuery= $("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'")
    
    
    function ExecuteSqlQuery ($SQLQuery) {
        $Datatable = New-Object System.Data.DataTable
        
        $Connection = New-Object System.Data.SQLClient.SQLConnection
        $Connection.ConnectionString = "DSN=AR SYSTEM ODBC Driver;UID=$userID;PWD=$password"
        $Connection.Open()
        $Command = New-Object System.Data.SQLClient.SQLCommand
        $Command.Connection = $Connection
        $Command.CommandText = $SQLQuery
        $Reader = $Command.ExecuteReader()
        $Datatable.Load($Reader)
        $Connection.Close()
        
        return $Datatable
    }
    
    $resultsDataTable = ExecuteSqlQuery $UserSqlQuery 
    $resultsDataTable | Out-GridView
    
    • #79985

      Keith
      Participant

      I can't seem to get it to work. Probably because it's using SQL and I have to use an ODBC driver. This whole process is new to me so I am trying to learn it all on the fly.

You must be logged in to reply to this topic.