SQL Query Issues

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Keith Keith 4 weeks ago.

  • Author
    Posts
  • #67611
    Profile photo of Keith
    Keith
    Participant

    Hello,

    Sorry this is a little long –

    I am writing a script to pull records from a database we use to track the work we do. I have it working for the most part. However – I have ran into an issue I cannot figure out.

    One of the fields I am trying to pull data from is a Detailed_Decription (Spelled wrong I know, that's how it is in the system) which offers more information into the ticket we worked. This is where my issue starts to make no sense to me. If I query 1 ticket with the info for one group (when running the query we are trying to run it to search for tickets within 4 groups all together) I am able to pull data, when I query all of the tickets in a timespan with only searching 1 group I can pull all the data I need but If I do more than 1 group in the query it just freezes, I don't get any errors or any feedback from PS.

    I am sure there is a better way to write the script, this is the best I could figure out right now with the extent of my knowledge, I plan on doing some research on arrays and maybe making the script better in the future.

    ## Login Information -
        $id = Get-Credential "Server\$("UsernameHere")"
        $userId = $id.UserName.split("\")[1].tolower()
        $password = $id.GetNetworkCredential().Password 
            write-host "Running Query"
    ##### Start the database connection and set up environment
            $DbString="DSN=DSN;UID=$userID;PWD=$password"
            add-type -path 'BMC.ARSystem.dll'
            $DBConnection=New-Object System.Data.Odbc.OdbcConnection ($SelectStatement)
            $DBCommand=New-Object System.Data.Odbc.OdbcCommand($DBCommand,$DBConnection)
            $DBConnection.ConnectionString=$DbString
            $DBConnection.Open()
            $DBCommand.Connection=$DBConnection
    
    #Prompt user for how many days they want to go back
        $DateRange = Read-Host "How Many Days Do You Want To Query? "
    
    ##Queries 
        $DBCommand.CommandText = "SELECT
         Incident_Number,
         Description,
         Assignee,
         Status,
         Reported_Date,
         Detailed_Decription,
         Assigned_Group
         FROM CRYRPT_Effort_2_INC_n_HPDAssignLog 
         WHERE 
         Status = 'Resolved' AND 
         Assigned_Group='Group1' OR
         Assigned_Group='Group2' OR
         Assigned_Group='Group3' OR
         Assigned_Group='Group4'" 
           
    ##Execute Query
        $DBResult=$DBCommand.ExecuteReader()
        $UserTable=New-Object system.data.datatable
        $UserTable.load($DBResult) 
     
    
    ##Export to CSV
        $UserTable | Where{$_.Reported_Date -gt ((Get-Date).AddDays(-$DateRange))} | Export-CSV "CSVPath\CSV.CSV"
    

    I added some breaks in there and ran the script and it looked like it was possibly getting stuck on $UserTable.load($DBResult) if that helps.

    I should also add that if I don't add Detailed_Decription but I keep all of the Assigned_Groups in there it pulls the data that I need, but I would like to get it to work with the Detailed_Decription in there because the information that it has is important.

    Still new to PS so trying to figure out the best way to go about tackling this issue.

    Thank you

  • #67612
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Well, two bits of good news.

    First, you do not have an issue. Magazines have issues. Bad news, you have a problem.

    Second, it isn't a PowerShell problem. It's either SQL or .NET, because that's what you're using. Bad news, that might limit how useful I can be here.

    Have you tried running equivalent queries just in SSMS? I ask because it seems like a query or server problem and it'll be easier to narrow down by running sample queries in the Management Studio.

    • #67615
      Profile photo of Keith
      Keith
      Participant

      Unfortunately I don't have access to SSMS. I am translating this script from a Perl script we use. I connect via .NET to run the queries.

You must be logged in to reply to this topic.