BMC Remedy AR Driver crashes powershell

Question

My situation is fairly specific so I am hoping that someone has some insight that could help me.

Code

$id = Get-Credential "REMEDY\$($env:USERNAME)"
$userId = $id.UserName.split("\")[1].tolower()
$BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($id.password)
$password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR) 

$cn = New-Object -comobject ADODB.Connection
$rs = New-Object -comobject ADODB.Recordset
$b = "Group5"
$c = "DSN=AR System ODBC Data Source;ARServer=remedyServer;ARServerPort=7130;UID=$userId;PWD=$password"
$cn.Open($c)
$q = "SELECT AuditTrail FROM HPD_HelpDesk WHERE AssignedToGroup='$b'"
$rs.Open($q, $cn)
while (!$rs.EOF) {
    $rs.Fields.item(0).value
    $rs.MoveNext()
}
$rs.close()
$cn.close()

Driver:

Name: AR System ODBC Driver
Version: 7.06.04.30000
File: ARODBC7604_BUILD02.DLL

Issue

Under Windows 7 32bit, powershell v4, everything works 100% of the time without error. Under Windows 10 64bit, it works roughly 5% of the time (forcing powershell x86 or it wont see the ODBC driver at all, but that's more annoying than an issue)

Error

I had a hard time even getting the error because Powershell completely closes instantly when this occurs, however running it from a CMD lets me see the output before it closes. Here is the error I get, and again the exact same code does occasionally work. The line that is actually crashing is the $rs.fields.item(0).value line

Unhandled Exception: System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
   at IDispatchInvoke(IntPtr , Int32 , INVOKEKIND , DISPPARAMS& , Variant& , ExcepInfo& , UInt32& )
   at System.Management.Automation.ComInterop.UnsafeMethods.IDispatchInvoke(IntPtr dispatchPointer, Int32 memberDispId, INVOKEKIND flags, DISPPARAMS& dispParams, Variant& result, ExcepInfo& excepInfo, UInt32& argErr)
   at CallSite.Target(Closure , CallSite , ComObject )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
   at CallSite.Target(Closure , CallSite , Object )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
   at System.Management.Automation.Interpreter.DynamicInstruction`2.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.Interpreter.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.LightLambda.RunVoid1[T0](T0 arg0)
   at System.Management.Automation.DlrScriptCommandProcessor.RunClause(Action`1 clause, Object dollarUnderbar, Object inputToProcess)
   at System.Management.Automation.DlrScriptCommandProcessor.Complete()
   at System.Management.Automation.CommandProcessorBase.DoComplete()
   at System.Management.Automation.Internal.PipelineProcessor.DoCompleteCore(CommandProcessorBase commandRequestingUpstreamCommandsToStop)
   at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input)
   at System.Management.Automation.PipelineOps.InvokePipeline(Object input, Boolean ignoreInput, CommandParameterInternal[][] pipeElements, CommandBaseAst[] pipeElementAsts, CommandRedirection[][] commandRedirections, FunctionContext funcContext)
   at System.Management.Automation.Interpreter.ActionCallInstruction`6.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.Interpreter.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.LightLambda.RunVoid1[T0](T0 arg0)
   at System.Management.Automation.DlrScriptCommandProcessor.RunClause(Action`1 clause, Object dollarUnderbar, Object inputToProcess)
   at System.Management.Automation.DlrScriptCommandProcessor.Complete()
   at System.Management.Automation.CommandProcessorBase.DoComplete()
   at System.Management.Automation.Internal.PipelineProcessor.DoCompleteCore(CommandProcessorBase commandRequestingUpstreamCommandsToStop)
   at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input)
   at System.Management.Automation.Runspaces.LocalPipeline.InvokeHelper()
   at System.Management.Automation.Runspaces.LocalPipeline.InvokeThreadProc()
   at System.Management.Automation.Runspaces.PipelineThread.WorkerProc()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

Isn't this just the driver that is not compatible with windows 10? I can't imagine this being a powershell issue. Does the BMC remedy application itself work properly on the system? — Sonny Puijk, Dec 13 '16 at 14:58
Yes the BMC remedy application works 100%, as does a Microsoft Query using this DSN in Excel. Maybe I should also note its only larger fields like AuditTrail, I can pull CaseID_ till the cows come home — TetraFlash, Dec 13 '16 at 15:04
Query works 100% of the time from 64-bit Excel or 32-bit Excel? — Bill_Stewart, Dec 13 '16 at 15:53
Good point, its 32bit excel. The query works under 32bit unless I am trying to do it through code, vb doesn't work either. It must have something to do with those specific large text fields, perhaps a different way of reading them? — TetraFlash, Dec 13 '16 at 16:03

score 0 · Answer 1 · answered Dec 13 '16 at 15:03

0

Could you please update the Driver and check it. This seems more like a driver issue than the script. Still I would recommend you to try in some other 64bit system and see if it works. In that way, at least you would be able to narrow down the issue.

Hope it gives you a set off.

answered Dec 13 '16 at 15:03

Ranadip Dutta

8,857
3
29
45

Unfortunately I am stuck with this driver, which does work as the Remedy application functions perfectly, as does the same query done in Excel with Microsoft Query, its just Powershell that doesn't want to play nice – TetraFlash Dec 13 '16 at 15:07
I can understand the pain. But really in this case I am helpless. How can the same script results different output each time. I dont have a test BMC remedy infra. I have to test it then – Ranadip Dutta Dec 13 '16 at 15:17

score 0 · Answer 2 · answered Dec 13 '16 at 16:32

I have NO idea why this works, while the previous method failed, but this works

$id = Get-Credential "REMEDY\$($env:USERNAME)"
$userId = $id.UserName.split("\")[1].tolower()
$BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($id.password)
$password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)  
    write-host "Running Query"
    ##### Start the database connection and set up environment
    $DbString="DSN=AR System ODBC Data Source;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

    $SelectStatement="SELECT AuditTrail FROM HPD_HelpDesk WHERE AssignedToGroup='Group5'"
    $DBCommand.CommandText=$SelectStatement
    $DBResult=$DBCommand.ExecuteReader()
    $UserTable=New-Object system.data.datatable
    $UserTable.load($DBResult) 
    $UserTable
    sleep 3
}

I would love an explanation for why this is the case, but at least its working :)

BMC Remedy AR Driver crashes powershell

Code

Driver:

Issue

Error

2 Answers2