Powershell Problem

Heyas,

I can't get an particular SQL query to export to txt file.  The powershell script I have works for all other sql queries and what the script does is export the query results to a txt file.I have administrative rights on my server and below is the sql query.
SELECT
		sysjobs.Name
FROM      msdb..sysjobhistory
		INNER JOIN  msdb..sysjobs ON msdb..sysjobs.job_id= msdb..sysjobhistory.job_id

I also have error catch statement on my powershell but it never returns any errors.

But I never any get any results back from the server. Is this a security issue any ideas?



Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ZackGeneral IT Goto Guy

Author

Commented:
Any ideas anyone?
Does this work?

USE msdb
SELECT sj.Name
FROM sysjobs As sj
INNER JOIN sysjobhistory As sjh
ON sj.job_id = sjh.job_id

Open in new window


Does the code work if you run it against the DB in SQL Management Studio?

Commented:
Is it possible one (or both) table(s) are empty?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

ZackGeneral IT Goto Guy

Author

Commented:
The code works fine SQL Management Studio, just not when I try get the run sql query from the powershell script I created. I have tested the powershell script with other queries I have developed and they work fine.

Thank you.
DB Expert/Architect
Top Expert 2011
Commented:
Hi,
Following script is working fine for me.
 
$conn = New-Object System.Data.SqlClient.SqlConnection
# Change Server=. to your server name, in case it's not local; e.g. Server=Srv01
$conn.ConnectionString = "Server=.; Database=msdb; 
  Integrated Security=true"
$conn.open()


$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "SELECT DISTINCT sj.Name AS Name,CAST(CAST(sjh.run_date AS 
VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('0' + CAST(sjh.run_time AS VARCHAR(6)), 6), 3, 
0, ':'), 6, 0, ':') AS DATETIME) AS RunAt
FROM sysjobs As sj INNER JOIN 

sysjobhistory 
As sjh ON sj.job_id = sjh.job_id"
$cmd.Connection = $conn


$dr = $cmd.ExecuteReader()

Write-Host

If ($dr.HasRows)
{
  Write-Host Number of fields: $dr.FieldCount
  Write-Host
  While ($dr.Read())
  {
    Write-Host $dr["Name"] $dr["RunAt"]
  }
}
Else
{
  Write-Host The DataReader contains 0 rows.
}

Write-Host

# Close the data reader and the connection
$dr.Close()
$conn.Close()

Open in new window

ZackGeneral IT Goto Guy

Author

Commented:
My apologies for the delay in answering net connection was down.
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
No problem, it's goo to know your issue is resolved ;)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial