?
Solved

Return results from Sybase DB Query with Powershell

Posted on 2014-01-07
7
Medium Priority
?
1,959 Views
Last Modified: 2014-10-06
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
Comment
Question by:AjarnJonesy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
7 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 39764878
$x = MyPSScript.ps1
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39767423
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
 

Author Comment

by:AjarnJonesy
ID: 39768322
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
 
LVL 70

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 39904195
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question