Help SQL Query Issues

PowerShell Help
1

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

2

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'))"
3

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.

4

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

5

Thanks,

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

6

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
7

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.