# Query Database From PowerShell

**URL:** <https://forums.powershell.org/t/query-database-from-powershell/7991>\
**Category:** PowerShell Help\
**Created:** [February 3, 2017, 1:51pm UTC](https://forums.powershell.org/t/query-database-from-powershell/7991 "2017-02-03T13:51:36Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![keithn](https://avatars.discourse-cdn.com/v4/letter/k/8491ac/32.png) [@keithn](https://forums.powershell.org/u/keithn)\
**Post date:** [February 3, 2017, 1:51pm UTC](https://forums.powershell.org/t/query-database-from-powershell/7991/1 "2017-02-03T13:51:36Z")

</div>

Hello,

I am _attempting_ to convert some Perl script into PowerShell. I am still pretty new to PowerShell and think I may have the right idea, but am a little lost so I am hoping for some help.

The company I work for uses a program called BMC Remedy to track tickets for work and whatnot. I am trying to get the script to query for data in the database. With the Perl Script it connects through the ODBC connection and queries a bunch of data; I am only attempting to query at least 1 ticket right now. I think I have the connection right for the database but am a little unsure how to query for data in PS.

Here is the PS Code:

```
$id = Get-Credential "ONE\$($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 Driver;UID=$userID;PWD=$password"
    add-type -path 'C:\Users\\Documents\ARAPI764.NET\BMC.ARSystem.dll'
    $DBConnection=New-Object System.Data.Odbc.OdbcConnection
    $DBCommand=New-Object System.Data.Odbc.OdbcCommand
    $DBConnection.ConnectionString=$DbString
    $DBConnection.Open()
    $DBCommand.Connection=$DBConnection

    $SelectStatement="SELECT * FROM HPD_HelpDesk_QueryList_Service WHERE (Assignee ='Username Here')"
    $DBCommand.CommandText=$SelectStatement
    $DBResult=$DBCommand.ExecuteReader()
    $UserTable=New-Object system.data.datatable
    $UserTable.load($DBResult) 
    $UserTable
    sleep 3
```

I have it query for my credentials which should hopefully log me in pull data. When I run it I get a quite a few errors however.  
The first is:  
"Exception calling “Open” with “0” argument(s): “ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an  
architecture mismatch between the Driver and Application”  
At C:\Users\Desktop\DatabaseODBC.ps1:13 char:5

- 

```
$DBConnection.Open()""

```

And the second is:  
"Exception calling “ExecuteReader” with “0” argument(s): “ExecuteReader requires an open and available Connection. The  
connection’s current state is closed.”  
At C:\Users\Desktop\DatabaseODBC.ps1:18 char:5

- 

```
$DBResult=$DBCommand.ExecuteReader()"

```

I really have no idea how to fix these. I think the 2nd error is because I have my query wrong but the first one I am not sure how to fix. I don’t have much experience attempting to query data from a database with powershell so it is all new to me.

any help would be great.

Thank you

---

<div class="post-metadata">

**Author:** ![daniel-krebs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/daniel-krebs/32/484_2.png) [@daniel-krebs](https://forums.powershell.org/u/daniel-krebs)\
**Post date:** [February 3, 2017, 2:16pm UTC](https://forums.powershell.org/t/query-database-from-powershell/7991/2 "2017-02-03T14:16:45Z")

</div>

I believe the AR Remedy ODBC driver is only available as 32-bit version. Try to run your script in the 32-bit PowerShell console or PowerShell ISE.

c:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe  
c:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell\_ise.exe

You should also able to replace below lines

```
$BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($id.password)
$password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)
```

with

```
$password = $id.GetNetworkCredential().Password
```

---

<div class="post-metadata">

**Author:** ![keithn](https://avatars.discourse-cdn.com/v4/letter/k/8491ac/32.png) [@keithn](https://forums.powershell.org/u/keithn)\
**Post date:** [February 3, 2017, 4:01pm UTC](https://forums.powershell.org/t/query-database-from-powershell/7991/3 "2017-02-03T16:01:29Z")

</div>

Thank you,

after doing that I am now getting the errors -

Exception calling “ExecuteReader” with “0” argument(s): “ExecuteReader: CommandText property has not been initialized”  
At C:\Users\Desktop\DatabaseODBC.ps1:16 char:5

- 

```
$DBResult=$DBCommand.ExecuteReader()

```

- 

```
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

```

  - CategoryInfo : NotSpecified: (🙂 , MethodInvocationException
  - FullyQualifiedErrorId : InvalidOperationException

Exception calling “Load” with “1” argument(s): “Value cannot be null.  
Parameter name: dataReader”  
At C:\Users\Desktop\DatabaseODBC.ps1:18 char:5

- 

```
$UserTable.load($DBResult)

```

- 

```
~~~~~~~~~~~~~~~~~~~~~~~~~~

```

  - CategoryInfo : NotSpecified: (🙂 , MethodInvocationException
  - FullyQualifiedErrorId : ArgumentNullException

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:39pm UTC](https://forums.powershell.org/t/query-database-from-powershell/7991/4 "2024-05-16T20:39:17Z")

</div>