• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2147
  • Last Modified:

Return results from Sybase DB Query with Powershell

I hae the following powershell script I am working on... Basically I have utilized the Sybase ASE Client Assembly, Stored my connection info and dates vars . I can open the connection to the server and as I understand it run the query. However the issue I am having now is how to input the results of the query into a variable. What I am looking to do is run the query and store the value returned in a var called $results.
Now to the issue... Should I be using  Invoke-Command to fire off the query or something else. I have seen a couple of different ways (for MS SQL) of doing it but not sure the best route.
#Add Sybase ASE Client Assembly
  Add-Type -AssemblyName "Sybase.AdoNet2.AseClient, Version=2.157.701.0, Culture=neutral, PublicKeyToken=9x9x9x9x9x9x9x9x9x9e"
  
  #database and  Connection vars
  $user = "user"
  $pwd = "99999999"
  $db = "Reporting"
  $beginDate = (Get-Date).AddDays(-31)
  $endDate = (Get-Date).AddDays(-2)
  $WebUsageQuery="select count( * )  from Reporting..WebUsage w where w.LogDate between '$beginDate' and '$endDate'"

  write-host $WebUsageQuery  
  #Create Connection String 
  $SybConn = New-Object Sybase.Data.AseClient.AseConnection
  $SybConn.ConnectionString = "Data Source='sybserverA';Port=5800;Database='$db';UID='$user';PWD='$pwd';"
  $SybConn.Open()

  $sybCmd = New-Object Sybase.Data.AseClient.AseDataAdapter
  $sybCmd.SelectCommand =  "$WebUsageQuery"
  $sybCmd.SelectCommand.Connection = $SybConn

  Write-Host $sybCmd and $SybConn

  $sybAdapter = New-Object Sybase.Data.AseClient.AseDataAdapter
  $sybAdapter.SelectCommand = "$sybCmd"
  
  $DataSet = New-Object Sybase.Data.AseClient.AseDataAdapter
  #$sybAdapter.Fill($DataSet)

  $SybConn.Close()

Open in new window

0
AjarnJonesy
Asked:
AjarnJonesy
  • 2
1 Solution
 
x-menIT super heroCommented:
$x = MyPSScript.ps1
0
 
QlemoC++ DeveloperCommented:
Is that a simplificated example only, or your real query? The approach depends on how much data you expect to get back. For single value we can use something much more simple than for a more generic query. The DataAdapter/DataSet is needed only for "complex" stuff, but of course you can use it here too. Just fill the DataSet var, and then dump the first table of it either to the output or into another var:
  $DataSet = New-Object Sybase.Data.AseClient.AseDataAdapter
  $sybAdapter.Fill($DataSet)
  $result = $DataSet.Tables[0]

Open in new window

0
 
AjarnJonesyAuthor Commented:
Qlemo
Thanks for the response.
that is the complete query, all I am looking to do is get a row count from a specific date range. Once I get that working I am going to basically kick it out with a simple
If less than 200,000 rows for the specified range then fire off an email with specific verbage. This is just used to check a row count for the date range then send an automated email if the row count is below what the expected inserts are supposed to be.. I will tinker around with what you have posted and see if I can get it working..
Thanks
0
 
QlemoC++ DeveloperCommented:
I reckon this should give you a start:
#Add Sybase ASE Client Assembly
Add-Type -AssemblyName "Sybase.AdoNet2.AseClient"

#database and  Connection vars
$user = "user"
$pwd = "99999999"
$db = "Reporting"
$beginDate = (Get-Date).AddDays(-31)
$endDate = (Get-Date).AddDays(-2)
$WebUsageQuery="select count( * )  from Reporting..WebUsage w where w.LogDate between '$beginDate' and '$endDate'"

$SybConn = New-Object Sybase.Data.AseClient.AseConnection
$SybConn.ConnectionString = "Data Source='sybserverA';Port=5800;Database='$db';UID='$user';PWD='$pwd';"
$SybConn.Open()

[Sybase.Data.AseClient.AseCommand] $sybCmd = $SybConn.CreateCommand();
$sybCmd.CommandText =  "$WebUsageQuery"
$count = $sybCmd.ExecuteScalar()

if ($count -lt 200000)
{
  Send-MailMessage -SmtpServer mx.Domain.Com -From Me@Domain.Com -To Me@Domain.Com -Subject "Reporting warning with $count rows"
}

$SybConn.Close()

Open in new window

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now