Get Data From MS-SQL Using a PowerShell Script

by fwhagen Wed, 23 July 2008
Here's an extremely useful stub to get data from SQL-Server within a PowerShell script:
$TaskName = "20071029-AllRejectedDuring"
$SqlServer = "SQLDEV01";
$SqlCatalog = "MyData";

# Get the T-SQL Query from .SQL file
$SqlQuery = Get-Content (".\" + $TaskName + ".sql")

#Write-Host ($SqlQuery) -foregroundcolor "gray"

# Setup SQL Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 
          "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

# Setup SQL Command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

# Setup .NET SQLAdapter to execute and fill .NET Dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

#Execute and Get Row Count
$nRecs = $SqlAdapter.Fill($DataSet)

Write-Host ($nRecs.ToString() + " Records retrieved.") -foregroundcolor "Cyan"

if ($nRecs -gt 0)
  # Do Stuff
  $DataSet.Tables[0].Rows[0][0]  #Print first data element
The connection is using Integrated Security for simplicity, it wouldn't be difficult to switch to UID/PWD instead.  Also, I put the SQL in a .sql file (flat text) to make life easier; you could also put the statement in the string declaration, if it is a simple query.  PowerShell's Get-Content mechanism makes reading a file very easy.  Also, clean up after yourself, I won't include that here. UPDATE:  I have posted a full script to export to Excel or XML in a followup post.

Keywords: ,

Filed Under: PowerShell | SQL

Getting Data from SQL to Excel Using PowerShell

by fwhagen Wed, 23 July 2008

Earlier, I wrote about getting data from MS-SQL with PowerShell, which is a very useful and powerful thing.  But just displaying data is very limited.  I have a need at work to create extract "reports" for business owners frequently into Excel.  So I have leverage this scripting technique to do the legwork for me.  After many iterations, I have completed the first version of a script that will take a SQL query in a text file and create an Excel file with the results.  All fully automated with time-stamping, archiving of the successful query, and the ability to export XML or CSV as well.

The script is unfortunately only listed as a page on this site because WordPress does not allow posting of non-media files.  You will have to copy the full content and paste it into a text file named QuerySQL.ps1.  One day, I will host this weblog on a real provider in order to have better control (I will accept donations!).  If many folks prefer, I will post it on my personal webspace with my ISP and provide links on request.  And maybe to CodeProject too.

Feel free to use it in anyway you wish.  Please learn from it if you can, or conversely, send me a note on how to improve it.  I would love to get the field-name bolding working among other things.

Keywords: ,

Filed Under: PowerShell | SQL

Book Review: Triplanetary - E.E. Doc. Smith (1948)

by fwhagen Fri, 18 July 2008

Heralded by many to be one of the founding series of modern science fiction, this is the first book of the Lensmen series.  It has been made into games, TV shows, etc, none of which I am familiar with.  So, I thought I'd better get with it and do so.  I picked up an old dime-store copy and got reading.

Triplanetary seems to be a collection of early stories and a novella that sets the stage for the series to come.  The first few stories tell of the fall of Atlantis, the burning of Rome, WWI and WWII, etc.; important events in human history.  They expose an galactic plot between two superpowers at war with one another, although one doesn't know of the others existence, in which Earth and its inhabitants are unknowing pawns.  The final novella is about 3 Terrans captured by space pirates, escape, then by a vastly advance fish alien civilization, which, of course, are able to escape from again.  Fortunately, Terran military scientists are able to quickly reverse engineer the fish alien's vastly superior technology in only a few weeks (days?), and built a super-battleship capable of defeating the aliens very civilization.

Yes, this is early sci-fi.  Pulp fiction, et al.  And I probably would offend the leet geeks, but I thought this book was terrible.  Maybe this was a fan-service book to set the series, but I felt the characters were flat and trite; I didn't care what happened to them at all.  The swooning girl, the incredibly capable hero with a tender spot for our frail damsel, the military leaders with single-minded faith in our hero to the point of having no contingencies, all make for great plot indeed.  Maybe I have been led to expect too much, but Burroughs, Howard, Asimov, Clarke and the others have done so, and in surrounding time frames.  So, I won't find out if the rest of the series is any good.  I just don't care.  Indeed, I can't even be bothered to find cover art for this post.


Filed Under: Books

Book Review: Company Commander - Charles MacDonald (1947)

by fwhagen Mon, 16 June 2008

Dale BrownRecommended by another of my ex-Navy friends, I have just completed my 3rd read of this book.  I first read it in 1996, and have considered it one of the most important books I have ever read.  I love combat books written from the point of view of the man in the trenches; this is the one that started it all.  I prize my copy so highly that I will not lend it to anyone.

Company Commander is the experiences of Captain Charles MacDonald, US Army, from the fighting at the Siegfried Line to the end of fighting of World War II.  He is put in command of I Company as a replacement for the previous CO killed during their last engagement.  His baptism of fire during the Wehrmacht's last offensive at the battlements is a stunning introduction to the front.  Mac works us through his fears and nervousness as a new commander and gives us witness to the elation of survival in the trenches.

This book is one of the most memorable I have read about combat infantrymen in WWII.  I dare say it may be one of the most important I have ever read.  Although I have no way of knowing its accuracy, I suspect that the frank portrayal of the front-line infantryman is the reason for the gripping read.  Ironically, he has also captured the long stretches of boredom followed by the frantic moments of utter panic.  Yet the slow areas are more a relief than they are dull passages.  The only complaint I might have is the near complete absence of any technologic detail of the weaponry used, although that is more than made up for by the detailed analysis of the order of battle used during the conflict.  If you have any interest in infantry combat in WWII, this is a must-read book.


Filed Under: Books

Quad Core: Useful or Waste?

by fwhagen Wed, 21 May 2008

There has been a lot of talk in the channels lately about the Quad Core CPUs lately.  Most of it has been along the lines regarding most applications only use 1 or 2 cores so the others sit idle.  Others say that in the future, that will change, but why spend the extra money on the purchase and in the cost of the power until then.  Essentially, expert opinion is that it is a waste of money.

I am not an expert.  I am, however, a professional developer and a experienced computer user (~30 years) that owns a Q6600 Core2Quad CPU.  My experience is that, indeed, while playing games, I probably see no performance increase over 2 cores.  Unless at the same time I am encoding video or audio, downloading something, or a myriad of other things that I do all the time.  With my last CPU, granted it was "only" a Prescott, I had to stop all other tasks, and frequently services, to play a heavy game, or to encode audio, or other high-priority tasks.  With the Q6600, I don't ever have to do that anymore.  My system will hum along, no matter how many things I have going on at once.  And to me, that is the advantage of the 4 cores.  Be realistic, how many things do you have running at the same time?

Also, I have seen many applications utilize more than one core at a time.  Almost all games I play use at least two.  Or more specifically, the ATI video driver uses more than one for itself, effectively offloading much of its performance cost to idle cores.  LAME only uses one, but I can now run up to 4 instances.  Or more to the point, I can rip and encode simultaneously, which gets high-quality CD ripping down to under 3 minutes for me.  And the H.264 video encoder I use pegs all four cores.

If the Q6600 was twice as much as the E6600 was when I bought it, I would have agreed that it was not worth it, but it was only $40 to $50 (~25%) more expensive.  And the G0 series chip only uses a bit more power (90W?), so yes, it is more expensive to run, but Vista S-states minimize that somewhat.  In my opinion, quad core CPUs are a must for the power user today.   While the benefit isn't 2X over a dual core, I believe that one day it will be.  I don't make these statements as a defense of my purchase, I made the purchase because I really believe that it is true.  The main reason I waited to upgrade my system was to delay until Intel dropped the price from the $500+ level to $279 so I could afford to buy one.

I cannot tell you how much Nehalem excites me.  I wish I could afford to get one, but there is no way that I am going to be buying a new mobo and memory any time soon.  I may buy a newer Core2Quad on the 45nm die for power and speed reasons, but the Q6600 is an incredible chip.


Filed Under: System

Easy Image Tools (and FREE too!)

by fwhagen Thu, 08 May 2008

It amazes me in my travels how many people feel the NEED Photoshop for web development.  And, of course, they only use about 1% of its total capability.  It is true that years ago, it was the only game in town for Image creation and manipulation.  But not for a while now.  There are many free alternatives that suit the web developer much better.

Paint.NET My favorite for most tasks is Paint.NET.  It greatest feature is its price:  absolutely free!  It started life as a college project and was so successful, that it lives on as a great piece of Freeware development.  It is not Open Source, however.  Some of the other features include layering, filters, great image processing tools, alpha transparency, PNG, and many, many more.  All put together in a small, efficient package that is both easy to use and very powerful.  And hundreds of dollars less than Photoshop and lacking the disturbing Adobe trend toward spyware.  And as the name suggests, it is built on C#.NET, so I feel like I am supporting the community I am a part of.

GIMP If you need more, there is the GIMP project.  Much more complicated, far more powerful, still free, but not nearly as user friendly.  It resembles Photoshop in many ways, including the powerful plug-in model that PS uses.  I use GIMP 2.2 for things I cannot do in Paint.NET, which really isn't very often. 

You know, between the two, I don't at all miss the copy of PS I had at my last job.  Not at all.  Of course, I am not a graphic designer or a heavy Photographer either that needs the power of PS, but I haven't worked with many people who really were either.

One last note.  For those that have been heard to say, "[MS] Paint is good enough.":  Do yourself a favor and get Paint.NET.  MSPaint hasn't been good enough since the '90s.

Keywords: , ,

Filed Under: .NET | Programming | Work

Book Review: Blind Man's Bluff - Sontag & Drew (1998)

by fwhagen Fri, 18 April 2008

Dale BrownRecommended by Sub Dave, one of my seemingly many ex-submariner friends, I picked this book up in my never-ending search for good military books.  It does not disappoint. 

Blind Man's Bluff:  The Untold Story of American Submarine Espionage is a collection of some of the USNavy's most daring reconnaissance missions using specially modified submarines during the Cold War.  From the first Captain to follow a new Soviet missile sub for 47 days (!) to the tapping of undersea telephone cables under 400 feet of water, the tales are riveting and surprising.  There is a great deal of high-tech shenanigans here!

A large, but very enjoyable read, I consumed this one quickly.  Each episode is laid out well and neatly presented for a single sitting.  The stories selected read like character driven novels and string together to form a intriguing look at the hidden side of submarine warfare during the Cold War.  This has become a permanent part of my collection.

Keywords: , ,

Filed Under: Books | History | Military

Windows Media Player - Black Bar on Bottom

by fwhagen Sat, 12 April 2008

I have looked this up 3 times now, so I am posting it here. 

Every once in awhile, WMP will add a black bar at the bottom of the display area.  This is not the letter-boxing of video, as it is normal in full-screen mode and also appears while playing audio files.  The bar is the Subtitle Area.  To disable, Right-Click the display area, select "Lyrics, Captions and Subtitles", and choose "Off".  It's gone.

Some key press activates it for me accidentally.  I doubt it's the default Ctrl-Shirt-C combo; that would be awfully hard to oops. Now we know.


Filed Under: System

Flash - Web Necessity or Overbearing Evil

by fwhagen Wed, 09 April 2008

Just why do I hate Flash requirements on sites so much?  I mean, every time I see a nice webpage marred by some Flash applet, I cringe and wonder why they decided to make that choice.

It used to be that requiring a user to have Flash installed just to visit your site was an inexcusable arrogance on your part as a web developer.  And then there was the bandwidth requirements for the content; after all, you are developing locally, so who cares that the end-user has to wait?  Well, you should, for starters.  And many times I see Flash, even still, being used as an easy way out or to simply animate a graphic. So historically, there were many, many reasons to never use Flash except for very specific applications.

Today, many of those arguments are not valid.  I believe all browsers have Flash preloaded, and bandwidth concerns are almost negligible anymore.  So why do I still find Flash to be a over-bloated gorilla on an otherwise clean design?  After all, I find the exclusion of PNG support on many browsers today to be inexcusable, but the inclusion of Flash a mere convenience.

Unfortunately, I don't have any clear answers.  To me, Flash doesn't belong on most websites I see today.  Yes, Flash does have it place in many online applications, but not as a graphic element.  And I believe that is the crux of the argument:  it is not a good replacement for some well designed image elements.  I am an old school developer that believes that good, solid, static design will always be superior to flashy, dynamic design in the long run.  Do not mistake that statement as a call to return to HTML as the primary development tool.  I refer to design; not content, which should always be dynamic, save of course for documentary content.  In my opinion, as a former CBT developer and current Intranet developer and BI Programmer, all design should be restricted to good use of CSS, PNG/GIF/JPG, and XHTML.  And there is also the fact that it is a closed standard.  Or actually, not a standard at all.  Flash is owned by Adobe, and is subject to their whims.  I just don't think that belongs in general web design from an end-user perspective, much the same way that I don't think it's right to use PDF as a publishing medium for web documents.

And don't even get me started on the crapware/bloatware/spyware that is a mark of Adobe products lately.  I flatly refuse to use Adobe's PDF reader anymore, but use Foxit Reader instead for any PDF files I might need to read.

Keywords: ,

Filed Under: Programming | Rant

Book Review: Staked - J. F. Lewis (2008)

by fwhagen Thu, 03 April 2008

J F LewisI am always looking for some light reading for my wife, who doesn't read much escapism. I try to get her to read more nonessential stuff to help her relax at night in order to sleep better. John Scalzi featured an interview with the author and the article intrigued me, so I bought it for her. So over the weekend, while my wife and daughter were in Orlando, I took a break from the great book I am reading and consumed this one. I was impressed. And annoyed to find out its the 1st of a trilogy that hasn't been written yet.

Staked is the story of Eric, a newly undead, but powerful, vampire with a bit of a memory problem.  He lives the dark lifestyle, running  a strip club for menu selection, hangs with a bunch of over vamps, and has girlfriend issues made worse by letting her talk him into turning her too.  He finds himself in the middle of a war with the werewolves for reasons he can't understand.

This is not your typical vampire novel, so Anne Rice lovers will need to look elsewhere.  This is a more intelligent take on the blood-sucker genre but still is a light read that I consumed in 4 days.  Not a record, even for me, but much faster than most books lately.  This is the first Vamp book that I really enjoyed for its logical storyline and decent belief-suspension qualities.  No over-the-top sexual situations or mind-groaning magical assertions, beyond the necessary to set the situation, of course.  Read the Scalzi feature and get the book.  It doesn't disappoint.


Filed Under: Books