Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Return results from Sybase DB Query with Powershell

Posted on 2014-01-07
7
Medium Priority
?
2,071 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 71

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 71

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
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…

636 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