Get Data From MS-SQL Using a PowerShell Script

by fwhagen Tue, 30 October 2007
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"
$SqlConnection.Close();

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

Running Local PowerShell Scripts

by fwhagen Mon, 29 October 2007

If you do any scripting at all in Windows, you should be using PowerShell to do it.  But the first time you do, assuming you've installed it properly is the following:

File C:\DEV\Report.ps1 cannot be loaded because the execution of scripts is
 disabled on this system. Please see "get-help about_signing" for more details.

You have two options:  sign your scripts (you should do this) or take the easy route and change the Execution Policy (do this at your own risk).  If you want to do it the right way, see Scott Hanselman's excellent post on the subject.  If you just want to run ps1 scripts, and are very careful about the source, namely yourself, execute the following command within the PowerShell shell:

Set-ExecutionPolicy RemoteSigned

Keywords:

Filed Under: PowerShell

Daylight Stoopid Time - Fall 2007 Edition

by fwhagen Fri, 26 October 2007

Here's your reminder that DST is ending again this weekend and you will be losing your free hour everyday.  But Wait!!  Congress moved it this year:  It's actually next week!  Yay!  Another week of free hours.  It a good thing Congress is saving us Time and Money, and more than ever before!

StandardTime.com

Keywords: , ,

Filed Under: Life | Rant | Worse Than Failure

1K+!

by fwhagen Wed, 24 October 2007

Tiny milestone:  1000 page views at this site!

The number of views to Using since moving to WordPress has exceeded 1000 today.  woot...

Keywords:

Filed Under: Blog

Book Review: The Brass Ring - Bill Mauldin (1972)

by fwhagen Mon, 22 October 2007

Bill Mauldin is probably the most famous cartoonist from World War II.  He was an infantry soldier in the Italian campaign who also worked for the Italian theaters version of Stars and Stripes, the soldier-run newspaper.  If you were to see one of his strips, you would immediately recognize his work.

The Brass Ring is Mauldin's autobiography of his early life through the end of WWII.  He tells of his very humble beginnings as the son of a poor farm family, life in the depression, and the start of his career as an illustrator.  He joined the National Guard at an early age at the encouragement of a close friend as it became evident that the Guard would be Federalized at the beginning of the war and before he could be drafted.  He was able to quickly establish himself as a journalist and cartoonist and so avoiding direct insertion into a combat unit.  This is the story of his experiences and the material he created from them.

The Brass Ring has a much more linear telling than Up Front and is easier to read because of it.  Again, I enjoyed the perspective of the infantryman in the trenches although Mauldin never really experienced combat as a reporter.  He seemed to be willing to put himself in the thick of it though, which is refreshing for a rear echelon type.  An enjoyable book, but not the collection of his work that I have been hoping for.  I will keep trying.

Keywords:

Filed Under: Books

"Flag" Fields

by fwhagen Tue, 16 October 2007

I shouldn't be surprised, but I am:  A customer complained to me that the Flag field they wanted to indicate a Yes or No value was failing.  The Yes value was working fine, but No was being returned when they hadn't set anything yet.

I didn't realize that booleans were meant to represent Yes, No, and Maybe.  Silly Programmer, no bits for you.

Keywords:

Filed Under: Worse Than Failure

WordPress and Code Samples

by fwhagen Tue, 09 October 2007

I am struggling with issues posting code samples in WordPress.  I am painfully aware that much of my code is cut off visually on the main page, and highlighting is spotty at best.  Suggestions for making it work in WordPress are appreciated.  I'd love to add an external stylesheet, but am loathe to pay for that add on.  An "extra" to handle it would be great, as long as it doesn't violate any EULAs.

Of course, WP layouts are pretty lacking too, without purchasing additional capabilities.  If I wanted to spend money doing this, I'd host this blog myself somewhere else and have Ultimate Freedom

I am working on it....

Update [10.08]:  I will be looking at Blogger and Blogsome to evaluate their engines for free coding blogs.  It is possible I may move again.  Also, I will renew my feedburner account so moves are more transparent; or completely so for RSS readers.

Keywords:

Filed Under: Blog

Checking for Database NULL Values in C#

by fwhagen Tue, 09 October 2007

This really should have been more obvious to me, so why did I have to look it up?  If you need to check for NULL in a returned field from SQL (or any other datasource), you should the .Equals method on System.DBNull.Value.  I usually prefer the "==" notation for conditionals, but that's just me.  This is more efficient.  The code follows:

if (!dsSpecQuery.Tables[0].Rows[0]["device_type_end"].Equals(System.DBNull.Value)) 
{
    _EndDate = Convert.ToDateTime(dsSpecQuery.Tables[0].Rows[0]["device_type_end"]);
}

Of course, dsSpecQuery is a DataSet, and the field in question is DateTime out of SQLServer (not that it matters for the conditional).

Keywords: , ,

Filed Under: .NET | Programming | SQL

Book Review: Lord's Foul Bane - Stephen R Donaldson (1977)

by fwhagen Thu, 04 October 2007

Lord Foul's Bane is a fantasy classic that I have read before; twice, I believe. But it was so long ago, that I have been meaning to re-read it for awhile.  The books I have are so old, Amazon doesn't even have the images to link in.  Hopefully Wikipedia doesn't mind.

Lord Foul's Bane is the beginning of the story of Thomas Covenant called Unbeliever.  He is a modern day man afflicted by Leprosy.  He finds himself unwillingly thrust into The Land, a mystical realm that is able to resolve some of his nerve damage and remove his disease.  But he is wary, knowing it must be too good to be true.  He must deliver a message of Doom to the leaders of the Land, and resolve his inner turmoil of even being there.

Covenant is a reluctant hero with fears and flaws that match our own.  Donaldson's treatment of him is very deep and extremely satisfying when looking for a character that is more realistically complex.  He spends much of this book battling the psyche of the main character himself.  There are others, I am certain, that have written this series up far better than I.  For more, find such a review online.  This is the first book of the first trilogy of the Chronicles of the Unbeliever.  The 2 trilogies were so popular that Donaldson has started the 3rd and final a couple years ago.  The Chronicles of Thomas Covenant are, without a doubt, required reading for any fantasy fan.

Keywords:

Filed Under: Books

Database Access from VBScript (WSH)

by fwhagen Fri, 21 September 2007

Sometimes its necessary to script out data manipulation for deployments or automation.  The easiest vehicle in Windows is Windows Script Host (WSH) using VBScript.  Below is a very basic framework for accessing a database (MSSql) and looping a RecordSet.

''===// Retrieve Data from Database //=====
Const DBSERVER = "SQLDEV01"
Const DATABASE = "Cellular"
Const DBUSERID = "app_user"
Const DBPASSWD = "app_pass"

'' Construct ConnectionString
szADOConn = "Provider=SQLOLEDB;Network Library=DBMSSOCN;Persist Security Info=True;" &_ 
            "Data Source=" & DBSERVER & ";" &_
            "DATABASE=" & DATABASE & ";" &_
            "User ID=" & DBUSERID & ";Password=" & DBPASSWD & ";" 

Set DBConn = CreateObject("ADODB.Connection")
    DBConn.open szADOConn        
    szSqlQuery = "SELECT PhoneID, Field2, etc  FROM Import_Name WHERE PhoneID <= 200"
Set cmd = CreateObject("ADOdb.Command")
    cmd.CommandType = &H0001  ''adCmdText
    cmd.ActiveConnection = DBConn
    cmd.CommandText = szSqlQuery
Set RecordSet = CreateObject("ADODB.Recordset")
Set RecordSet = cmd.Execute(szSQLlc)

Do While Not RecordSet.EOF
  WScript.Echo RecordSet("PhoneID")  
  RecordSet.movenext
Loop

Set RecordSet = Nothing
Set cmd = Nothing
    DBConn.close
Set DBConn = Nothing

Of Course, it's possible to distill this down further, but I didn't for clarity (Consts) and it's better to not use implicit command objects by opening the database directly with the Recordset object.

Keywords:

Filed Under: SQL

TextBox

RecentPosts