How to Graph SQL Server Data from a PowerShell Script

Question

How to Graph SQL Server Data from a PowerShell Script

shew

SSChampion

Points: 11389
More actions
October 10, 2012 at 2:56 pm

#282995

Has anyone found a way to graph SQL Server data from a PowerShell script?
This link describes something close to what I hope to do:
http://blogs.technet.com/b/richard_macdonald/archive/2009/04/28/3231887.aspx
However, I would like to replace the functionality of his code:
Now for some data to display:
# add data to chart
$Cities = @{London=7556900; Berlin=3429900; Madrid=3213271; Rome=2726539;
Paris=2188500}
[void]$Chart.Series.Add("Data")
$Chart.Series["Data"].Points.DataBindXY($Cities.Keys, $Cities.Values)
with the output from a SQL Server query similar to this:
[array]$SQLOutput_STR = sqlcmd.exe -S my_server\my_instance -E -Q "select x, y from my_table;"
My query should return data like:
X Y
- -
1 9
3 4
5 7
8 3
0 4
Instead of creating a bar chart, I would prefer to create a line chart.
Any ideas?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

shew SSChampion Points: 11389 More actions · Answer 1

I found this link (http://poshcode.org/1205) which works fine for charting get-process information (despite a couple of typos in the script). But I am not having any luck "trading" sqlcmd.exe output for get-process output. Apparently, get-process returns its data as a PowerShell object, but sqlcmd.exe does not.

Any ideas on how to return the results of a SQL query as a PowerShell object?

Jeff Moden SSC Guru Points: 1004619 More actions · Answer 2

shew (10/11/2012)
Any ideas on how to return the results of a SQL query as a PowerShell object?

I'm no Powershell Ninja by any means so I don't know the Powershell answer to this but I'm sure this is one. In the meantime, couldn't you just BCP the results out to a file using SQLCMD and open the file in Powershell?

By the same token, I'm not sure that PowerShell is the answer here. Why not create a spreadsheet that looks at external data through a pass-through view and plot from that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shew SSChampion Points: 11389 More actions · Answer 3

Jeff,

Thanks for the feedback.

couldn't you just BCP the results out to a file using SQLCMD and open the file in Powershell?

From a PowerShell perspective, I think that character-based (not native-based) BCP output would generate the same type of "text" file as:

sqlcmd.exe -S ... -E -Q "select X, Y from my_table"

I'm thinking BCP and sqlcmd are probably a wash.

By the same token, I'm not sure that PowerShell is the answer here. Why not create a spreadsheet that looks at external data through a pass-through view and plot from that?

I'm trying to remove all Microsoft Office installation aspects from this project. We are a locked down shop which does not allow Office to be installed on its servers, and I would like to be able to graph data while logged onto remote servers that may be accessible only through a VPN. So, the native graphing ability of PowerShell (i.e., Microsoft Chart Controls for the .NET Framework, which is oddly part of our base install) is attractive.

I originally thought that sqlcmd.exe was not returning its results in an object, but I was mistaken.

The http://poshcode.org/1205 link for LibraryChart.ps1 that I cited above is the closest I've found for graphing data from the PowerShell command line. However, it relies on the use of a PowerShell hash table to feed its data to the Microsoft Chart Controls. The PowerShell "get-process" command returns SystemArray output that is compatible with LibraryChart.ps1 and its use of a hash table, but sqlcmd.exe returns a SystemArray that does not appear to be compatible with the script. I'm thinking there is a way to pipe the sqlcmd.exe output to make it more similar to the "get-process" output, but I have not found it yet.

Jeff Moden SSC Guru Points: 1004619 More actions · Answer 4

You do't need to have MS Office installed on the server to do what I suggest. You would use the spreadsheet from your (or a user's) desktop as if it were a 3rd party app (which it is) to access "External Data" and plot it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shew SSChampion Points: 11389 More actions · Answer 5

Jeff Moden (10/12/2012)
You do't need to have MS Office installed on the server to do what I suggest. You would use the spreadsheet from your (or a user's) desktop as if it were a 3rd party app (which it is) to access "External Data" and plot it.

Thanks, Jeff.

I think understood what you meant. In certain circumstances, we already do what you suggest.

However, we are a hosting facility with scores of VLANs and VPNs, and we do not necessarily allow firewall connections to reach the remote data. I need something that works totally on the remote server, without any dependencies on my local desktop.

Jeff Moden SSC Guru Points: 1004619 More actions · Answer 6

shew (10/12/2012)
Jeff Moden (10/12/2012)
You do't need to have MS Office installed on the server to do what I suggest. You would use the spreadsheet from your (or a user's) desktop as if it were a 3rd party app (which it is) to access "External Data" and plot it.
Thanks, Jeff.
I think understood what you meant. In certain circumstances, we already do what you suggest.
However, we are a hosting facility with scores of VLANs and VPNs, and we do not necessarily allow firewall connections to reach the remote data. I need something that works totally on the remote server, without any dependencies on my local desktop.

Sorry for the late reply. Maybe SSRS?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

How to Graph SQL Server Data from a PowerShell Script

Cookies on SQLServerCentral