SEARCH
Subscribe!
How to Verify Your Stored Procedures
Posted in: Programming by Steve on May 5, 2010 | No Comments
One of the things I’ve been trying to do is to eliminate the unnecessary stored procedures from our project. Currently, we have about 150 left, but a lot of them are still used. Some of them are still left over and no longer are called from our application. As I was building a new SQL install script, I was trying to run a script of all stored procedures in one database and create them all in a new one. I found that some of them no longer would work because of changes we’ve made to the database that have broken these stored procedures without even knowing.
I asked on twitter how this could be done and Argenis Fernandez (@afernandez) suggested that I look into a Powershell script to verify stored procedures still work.
After some investigation on how Powershell and SQL work together, I scraped together this script:
$server = "localhost"; # The SQL Server instance name
$database = "MyDatabase"; # The database name
# Load the SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
# Create the SMO objects
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server;
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database");
# Get the database
$db = $srv.Databases[$database];
# For each stored procedure in the database
foreach($proc in $db.StoredProcedures)
{
# NOTE: my stored procedures are all prefixed with "web_"
if ($proc.Name.StartsWith("web_"))
{
$proc.TextBody = $proc.TextBody;
$proc.Alter();
}
}
If any error occurs, it will display which procedure name did not get altered properly.
In my application, all my stored procedures that I use in the websites are prefixed with “web_”. This made it easy to check if the procedures used still work.
SQL Query Madness
Posted in: Programming by Steve on April 15, 2010 | 3 Comments
I would consider myself to be pretty good with database design and queries, but this one has me stumped.
Our application allows administrators to add “User Properties” in order for them to be able to tailor the system to match their own HR systems. For example, if your company has departments, you can define “Departments” in the Properties table and then add values that correspond to “Departments” such as “Jewelry”, “Electronics” etc… You are then able to assign a department to users.
Here is the schema:
In this schema, a user can have only one UserPropertyValue per Property, but doesn’t have to have a value for the property.
I am trying to build a query that can use the PropertyValues as the filter for users. My query looks like this (SQL Server 2005):
SELECT UserLogin, FirstName, LastName FROM Users U LEFT OUTER JOIN UserPropertyValues UPV ON U.ID = UPV.UserID WHERE UPV.PropertyValueID IN (1, 5)
When I run this, if the user has ANY of the property values, they are returned. What I would like to have is where this query will return users that have values BY PROPERTY.
So if PropertyValueID = 1 is of Department (Jewelry), and PropertyValueID = 5 is of EmploymentType (Full Time), I want to return all users that are in Department Jewelry that are EmployeeType of Full Time, can this be done?
I’m not sure how this can be accomplished without building dynamic SQL, so I thought I would throw this out to see what I could find.
UPDATE:
I posted this question on StackOverflow to see if there are any more ideas
My First Rails Application
Posted in: Programming by Steve on April 2, 2010 | 2 Comments
After CodeMash, I decided it was time to see what the hubub was about with rails, so I picked up a Mac, a few books and a lot of beer. The result is my first website built using rails and Heroku. There’s not much to it right now other than my resume, but I’m looking at building a contact form as well. So it’s not much of an application other than an HTML page rendered using rails, but at least it’s the start of something new.
Here’s to learning more in the coming months!
New Laptop – I’m a Mac
Posted in: Programming by Steve on February 1, 2010 | No Comments
After going back and forth between getting a Dell, HP Envy or Asus, I decided to go outside of my comfort zone and bought a Macbook.
The reason I got it was because I wanted to learn a new programming language and to see how the other side lives. Codemash taught me that if I want to get better as a programmer, I have to widen my experiences to more than just .NET.
So this is the first step to jump start my learning. I hope to upgrade it so a better hard drive (SSD), more memory and to get the new Speck soft satin case (and a travel case too). I will be blogging more about the experience of OS-X vs Windows along with attempting to learn Ruby on Rails.
CodeMash 2.0.1.0.
Posted in: Programming by Steve on January 12, 2010 | No Comments
CodeMash is just around the corner and thanks to the good folks at telerik, I will be one of about 700 people attending the conference. I will be tweeting from the conference and posting recaps of sessions and happenings with an occasional picture or two. I might blog from the lazy river, who knows!
As of now, here is the list of sessions I would like to attend, along with backup sessions:
Thursday Sessions
9:45 AM: What Makes Ruby Different (Joe O’Brien, Mark Peabody & Leon Gersing) or Agile Iteration 0 (Ken Sipe)
11:00 AM: Maintainable ASP.NET MVC (Chris Patterson) or User Stories: Closing the Agile Look (Barry Hawkins)
1:45 PM: Ruby and Rails for the .NET Developer (Matt Yoho) or Seeing Constraints: Kanban Explained (Jon Stahl)
3:35 PM: Domain Driven Design: An Introduction (Barry Hawkins) or Refactoring the Programmer (Joe O’Brien)
4:45 PM: Testing ASP.NET Applications Using Ruby (Ben Hall)
Friday Sessions
9:30 AM: Software Design and Testability (Jeremy D. Miller) or Testing the Enterprise (Leon Gersing & Charlie Baker)
10:45 AM: 0-60 with Fluent NHibernate (Hudson Akridge) or Leadership 101 (Jim Holmes)
1:45 PM: IronPython with ASP.NET (Chris Sutton) OR Credit Crunch Code: Time to Pay Back the Technical Debt (Gary Short)
3:35 PM: Analyze and Optimize your .NET Web Application (James Avery) or SOLID Ruby (Jim Weirich)
This will be my first large conference. I’ve attended a few other conferences like the Iowa Code Camp and Chicago Code Camp. This will be a new experience for me and I hope that I will get a lot out of it. I am looking forward to meeting the people I follow on twitter too. This should be a great time.
See you in Sandusky!
Building out a new Laptop
Posted in: Programming by Steve on January 11, 2010 | No Comments
In March of 2009, I decided it was time to get a netbook. I wanted to have something that was portable and fast. I decided to go with the ASUS Eee PC 1000HE Blue Netbook
After using it for 9 months and trying to show videos and do more programming on it, the netbook route isn’t working for me. I need to have a laptop that is able to handle the load of video since my son, Conner, loves to watch the Muppets. I am looking to upgrade to a new laptop.
The first step is to clean house. I am what you would call a “pack rat”. I have all my old computer equipment and other miscellaneous electronics stored in my closet, stacked to the ceiling (my wife can attest to this). With all the stuff I have, I couldn’t possibly use it all, so I am selling it on eBay.
The second step is to determine which laptop to purchase. Here are my requirements for the laptop:
Core i7 CPU - 4GB RAM
- Discrete graphics card
- Between 12” and 15.5” High Resolution LED screen
- Bluetooth
- capable of handling an Intel X25-M SSD upgraded hard drive
- 3 year warranty
- no more than $1,700
- 0% financing for at least 6 months
ASIDE: Originally, I was looking to purchase a MacBook Pro, but since the refresh hasn’t happened and I’m well invested in the Windows environment (.NET, Windows Home Server, gaming, knowledge), I am not looking into one currently. Maybe in the future I’ll investigate a Apple, but for now, I’m sticking to Windows laptops.
I’m still investigating the best choice from these:
Right now, the Dell seems to be winning on pricing point at $1,500 with tax and shipping and 9 months of 0% financing with the following load out:
- Intel® Core™ i7-720QM Quad Core Processor @ 1.6GHz (2.8GHz Turbo Mode, 6MB Cache)
- 3 Year Basic Service Plan
- 15.6” Full High Definition (1080p) High Brightness LED Display with TrueLife™ and Camera
- 4GB Shared Dual Channel DDR3 at 1333MHz
- 320GB SATA Hard Drive (7200RPM)
- 512MB ATI Mobility Radeon HD 4570
- 8X Slot Load CD/DVD Burner (Dual Layer DVD+/-R Drive)
- 85 Whr Lithium Ion Battery (9 cell)
- Intel 5300 Ultimate-N Half Mini Card (3×3) with MyWiFi
- Dell Wireless 365 Bluetooth Internal (2.1)
- Back-lit Keyboard
With this load out, I can do all my development and show videos to Conner (like this one, his favorite) without any problems.
The HP Envy comes out closer to $1,700 with a 3 year warranty and only 6 months of 0%. It’s too bad the HP Envy 13 doesn’t come with a Core i7 CPU because that would be the perfect replacement for my netbook. I’m wondering if I should just get the Envy 13 since the processor is still such a better upgrade to what I have now. I did see one of these at Fry’s the other day and it definitely is one sweet laptop.
The Asus is out of stock and I haven’t seen one in person to make an informed decision on this one.
I will update on any further information or if a purchase is made. If you have any other thoughts or ideas for me, please feel free to comment.
Attending CodeMash 2010
Posted in: Programming by Steve on December 5, 2009 | No Comments
Thanks to telerik, I will be attending CodeMash 2010! I was selected as the winner of their Facebook contest.
I would like to thank telerik for the opportunity to attend this wonderful conference.
Typemock Isolator 5.3.1 and Automated Builds
Posted in: Programming by Steve on June 8, 2009 | 2 Comments
I recently upgraded to Typemock Isolator 5.3.1 and ran into a couple issues in my build. From my post on getting Typemock to report results to TeamCity, we needed a few files included in the source control to get this working. In version 5.3.1, we need a couple more files included.
For TeamCity to report on nUnit’s test results, here are the files that you will need:
- Configuration.dll
- exclude.dat (new for 5.3.1)
- MockWeaver.dll
- namespaces.dat
- ProfileLinker.dll
- Typemock.ArrangeActAssert.dll
- Typemock.Configuration.exe
- TypeMock.dll
- Typemock.Interceptors.dll (new for 5.3.1)
- Typemock.Isolator.VisualBasic.dll
- TypeMock.NAntBuild.dll
- typemockconfig.xml
After adding these 2 new files to my external libs directory, I was able to run my build script successfully.
Typemock ASP.NET Bundle Launch
Posted in: Programming by Steve on May 18, 2009 | No Comments
Unit Testing ASP.NET? ASP.NET unit testing has never been this easy.
Typemock is launching a new product for ASP.NET developers – the ASP.NET Bundle – and for the launch will be giving out FREE licenses to bloggers and their readers.
The ASP.NET Bundle is the ultimate ASP.NET unit testing solution, and offers both Typemock Isolator, a unit test tool and Ivonna, the Isolator add-on for ASP.NET unit testing, for a bargain price.
Typemock Isolator is a leading .NET unit testing tool (C# and VB.NET) for many ‘hard to test’ technologies such as SharePoint, ASP.NET, MVC, WCF, WPF, Silverlight and more. Note that for unit testing Silverlight there is an open source Isolator add-on called SilverUnit.
The first 60 bloggers who will blog this text in their blog and tell us about it, will get a Free Isolator ASP.NET Bundle license (Typemock Isolator + Ivonna). If you post this in an ASP.NET dedicated blog, you’ll get a license automatically (even if more than 60 submit) during the first week of this announcement.
Also 8 bloggers will get an additional 2 licenses (each) to give away to their readers / friends.
Go ahead, click the following link for more information on how to get your free license.
Combining Date and Time in SQL Server
Posted in: Programming by Steve on March 5, 2009 | 3 Comments
I have been working with Log Parser to go through IIS data from the last week. In working with it, I found that the queries I wanted to run were a little obscure to me, and not having time to mess around with learning a new query language, I just imported the data into SQL.
The W3C logs store the date and time in separate columns, and for one of my queries, I wanted to combine the date part with the time part to create a single datetime field.
So here’s my function that I created:
CREATE FUNCTION [dbo].[CombineDateTime](@dtDate DATETIME, @dtTime DATETIME) RETURNS DATETIME AS BEGIN RETURN @dtDate + CONVERT(CHAR(8), @dtTime, 108) END
Hope this helps!