Current Software List

Here’s a list of software I use for various tasks. I’m always interested in what tools other people use and figured that having my own list might help someone out. The only problem I've found with other lists (or software reviews in general) is that they get outdated. I'm hoping to periodically update my list to keep things current.

All these tools are for Windows, but some are available on other platforms. Most of them are free (and Open Source).

Desktop Tools

Web Browser
Mozilla Firefox (www.mozilla.com)
Sometimes I split my activities between browsers. For example, I will play music from Pandora.com on IE and have all my other sites up on Firefox. I've found this keeps the my memory intensive applications separate.

Text Editor
Notepad++ (notepad-plus.sourceforge.net)
Great Notepad replacement.

Instant Messaging (IM)
Pidgin (www.pidgin.im)
Great if you use more than one IM service (AOL, Yahoo, MSN, Google, etc). A little more developer centric, so if you consider yourself more of a casual user you might want to consider Trillian.

Quick Launch
Launchy (www.launchy.net)
Indexes your entire Programs folder in the Start Menu. To run a program, just use the key combination: [ALT] + [Space Bar], then type in the name of the program you want to start. It will start suggesting the name of the program after the first couple of letters.

Quick Image Viewer
IfranView (www.irfanview.com)
Great for when I want to quickly look at a picture I downloaded onto my desktop. Not the greatest photo editor or organizer, but that's not what it was designed for. Very fast.

PDF Creation
PDFCreator (www.pdfforge.org/products/pdfcreator)
Adds a (virtual) printer to your list of printers. Print your document to this printer and it will create a PDF for you.

Money Management
Quicken (Quicken)

Here is where I insert my comparison of Quicken vs online money management applications (such as mint.com). I could probably write another post about this topic alone, but here are my thoughts on Quicken:

• Split Transactions are better, easier to understand (this could change at some time)
• It has Manual Transactions (others do not), a big deal breaker for anyone who likes to do even a little planning. They are useful if you write a check, have a significant other who also pays bills, receives money by checks, etc.
• It's Not Free - I'm still lery of losing my personal data to a service that can always say "well, it was free, so you get what you pay for". I have the same issue with Quicken Online too.
• I've tried a lot of others (MS Money, GNUCash, Mint - 3x's), but I always stay with Quicken

Office Product (Word Processor, Spreadsheet, etc):
Open Office (www.openoffice.org)
(Google Docs, MS Office)
I use Open Office at home, but have to admit I use MS Office at work. Open Office is getting better each version, but certain basic things are lacking (i.e. clipart).


File-based Music
Winamp (www.winamp.com), but slowly trying to move to VLC Player (www.videolan.org/vlc)
Winamp is great for playing music files, not so great as an iTunes replacement (music library). I'm kind of annoyed by their consistent updates (reason for researching VLC as a replacement), but it's hard to move away from this classic.

Online Music
Napster (www.napster.com)
Fell in love with online music via Rhapsody, and Napster's service is only $5/month. I sit at my desk a lot and listen to music, so this kind of service may not be for everyone. I find that I don't even bother playing music out of my own collection of music anymore.

As far as Napster Online vs Rhapsody, it depends on your music tastes. I'm always on the lookout for new music and I've found while Napster is much cheaper (about $10/month less), it seems like the Rhapsody library is larger.

Photograph Organizer
Picasa (picasa.google.com)
Wish it had some plugins for importing photos, but you can't beat the price for the functionality it provides.


Development Tools

Network Utility
Angry IP Scanner (www.angryziber.com/w/Home)
Great for finding all the devices on your network, debugging home network issues, etc.

FTP
FileZilla (filezilla-project.org)
Also does Secure FTP (SFTP)

CSS Editor
TopStyle Lite (search for it)
Great IDE-like CSS editor with intellisense for the different CSS commands. This version is a light version of the pay-for version, which in my opinion is too expensive for how much I use it. The lite version works well enough, but I wouldn't mind finding a better tool if one exists.

"Desktop" Search
AstroGrep (astrogrep.sourceforge.net)
Grep for Windows, with a nice simple GUI. Just point it to the folder you want to search and type in your search term. I use it mostly to search through text-based files (i.e. code), but it also has a plug-ins interface for other files (i.e. MS Word). I don't like using Windows search since it always seems to be indexing.

SSH/Telnet client
Putty (www.chiark.greenend.org.uk/~sgtatham/putty)

Color Picker
Pixie (www.nattyware.com/pixie.php)
Great color picker for Windows, supplies you with HEX, HTML, RGB, CMYK, and HSV

Image Editor
The Gimp (www.gimp.org)
Only useful if you have some PhotoShop experience and can't afford PhotoShop. Takes some getting used to, but I've found it very useful.

Zip / UnZip
7-Zip (www.7-zip.org)
Even though Windows includes some zip functionality, this one is easy to configure to use with scripts.

SSRS Multi-Select Parameters for Stored Procedures in SQL 2005

[Intro]

Using SSRS multi-value parameters with stored procedures can be a bit of a pain. Multi-Valued parameters are sent to Stored Procedures as a comma-delimited string. What follows is the method I use to write and debug stored procedures for SSRS reports.
The web is full of tutorials on handling multi-valued parameters in stored procedures. What I’m hoping to show is a method to set them up so that you can easily debug the stored procedure later on down the road.

[Background]

I found some code on SQLServerCentral.com that does a good job of splitting a comma-delimited string into a table of individual values. I wish I could credit the person who originally posted this idea, but there are lots of variations of this code floating around, and I modified the version I originally found, so please accept my apologies if this looks familiar.
This function will break down a comma-delimited string into a table of individual values. The one I’m posting here returns a table of ints, but I have others that return a table of other values (i.e. strings):

---------------------------------------------------- 
-- [dbo].[fnc_split_int] 
-- Parse a comma delimited string and insert the values into a table variable. 
-- Useful for sending a comma-delimited string parameter to a stored proc that will 
-- use it in a where IN clause.     
-- 
-- Ex: 
-- select * from customers     
-- will not work: Where customer_id in (@parameter)     
-- will work        : Where customer_id in (select item from fnc_split_string(@parameter,',')) 
--------------------------------------------------------- 
ALTER    FUNCTION [dbo].[fnc_split_int](
       @list varchar(8000),   
       @Delimiter VARCHAR(10) = ','
)
RETURNS @tablevalues TABLE (
       item int
)
AS
BEGIN

DECLARE @P_item varchar(255)
                                   
WHILE (DATALENGTH(@list) > 0)
BEGIN
   IF CHARINDEX(@Delimiter,@List) > 0
           BEGIN
           SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
               SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + DATALENGTH(@Delimiter)),DATALENGTH(@List))
           END
   ELSE
           BEGIN
               SELECT @p_Item = @List
       SELECT @List = NULL
           END

       INSERT INTO @tablevalues
   SELECT Item = convert(int, LTRIM(RTRIM(@p_Item)))
END

RETURN

END
       

Here’s a simple example of a stored procedure using this function:

create proc dbo.salesinfo (
       @customers varchar(max)
)
as
      
Select * from dbo.sales
where customer_id in ( select item from fnc_split_int(@customers, ',') )

[Using Table Variables]

The method above works well, but can get kind of annoying if you ever have to debug the stored procedure. The stored procedure could be huge, and you might not care too much about which customers are displayed. However it now requires a comma-delimited string of customers anytime you want to run some tests.
To keep me from losing my mind, I add a few table variables in my stored procedure that are designed to handle the multi-value SSRS parameters.


This is probably best explained in an example:

create proc rpt.salesinfo (
   @customers varchar(max) = null    --set to null by default 
)
as
      
--Build local customer table
declare @customerlist table (
   customer_id int
)

if @customers is null
begin
       --Load all customers (null) 
       insert into @customerlist
       select customer_id from customers
end
else
begin
       --Load only those customers chosen in @customers 
       insert into @customerlist
       select item from fnc_split_string(@customers, ',')
end


--report query (using @customerlist) 
select * from sales
where customer_id in (select customer_id from @customerlist)

Notes:

• It might seem a little excessive to build a local table of values when you already have those values in comma-delimited form. But using this method has saved me a lot of headaches when I’m trying to debug something in the stored procedure.


• I could use a join instead of putting the @customerlist table in the where string. However, I’ve found that performance hasn’t really been an issue for our setup. I also think it’s a little easier to read – a new developer doesn’t have to worry about finding a join that is only used to limit the resultset. From what I remember, the server does a lot to optimize queries anyway, so things like this usually do not matter as much.

[Final Thoughts]

This example shows a method I use to test and debug stored procedures for SSRS reports that use multi-valued parameters. There isn’t any ground breaking functionality introduced, just a bunch of smaller things strung together to hopefully make life easier. I should mention there are other ways of doing this, some might be better than this one. At the time of this writing I was only using SQL Server 2005.

Please feel free to ask questions or suggest improvements.

IIS 7 ASP scripts (vbscript) fail to send email (web.com)

I recently had a horrible experience with web.com. They "upgraded" our web server that broke all my client's email functionality for his ASP scripts. These scripts ran flawlessly for years without issue, then all of the sudden none of them worked, and the web host seemed unwilling to help us.

After doing some research, I discovered that they upgraded their servers to MS IIS 7, which no longer directly supports sending emails from an ASP page using "CDO.Message" or "CDONTS". I'm hoping that other web.com users might come across this article in a google search and could save some themselves some time. There might also be other people who have another web host with similar problems.

From what I understand, according to all articles I have found around the web, IIS7 no longer supports sending emails from an ASP page using "CDO.Message" directly.

Here is my old code, which no longer works:

Sub SendMail(Subject,Body,EmailTo,EmailFrom)
Set Newmail = CreateObject("CDO.Message")
newmail.TextBody = Body
newmail.Subject = Subject
newmail.To = EmailTo
newmail.From = EmailFrom
newmail.Send
set newmail = nothing
end Sub


There are a few solutions out there, but the one that worked for me (as a web.com user) is to use CDOSYS. I found this through a series of searches, and my solution I present is just my flavor of the method recommended:

Regarding the Fields used for "CDO.Configuration", I had to play around to find the ones that my webhost seemed to care about. For instance, I have commented out cdoSendUsingPort and replaced it with a 2 (the actual value of cdoSendUsingPort). Most people online seem to be recommending putting the address of your SMTP server for the "smtpserver" setting, but I found that web.com only worked with "localhost".



Function SendEmail(Subject,Body,EmailTo,EmailFrom)
' SendEmail(Subject,Body,EmailTo,EmailFrom)
' 2/10/2009 JMJ: Uses CDOSYS method of sending email
'

Set cdoMessage = CreateObject("CDO.Message")
Set cdoConfig = CreateObject("CDO.Configuration")

Set Flds = cdoConfig.Fields

With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With

With cdoMessage
Set .Configuration = cdoConfig
.To = EmailTo
.From = EmailFrom
.Subject = Subject
.TextBody = Body
.Send
End With

Set cdoMessage = Nothing
Set cdoConfig = Nothing
Set Flds = Nothing

SendEmail = true

End Function 'SendEmail()


Coincidentally, there are a couple more solutions out there. One is to copy a couple dll’s onto the new server to provide support for legacy ASP scripts; another is to just send emails using the database. I’ve provided some links below that were helpful to me.

Tips for Classic ASP developers on IIS7

IIS7 - Classic ASP Forum

How to send e-mail with CDO

ASP Sending e-mail with CDOSYS

Directv Tivo in two rooms

For the longest time I’ve struggled with what to do about a TV in our bedroom. We don’t watch a lot of TV in bed, but occasionally it’s nice to have. We currently only have 2 TVs – one in our living room with a DirecTV Tivo, and the already mentioned bedroom TV with nothing. The Bedroom TV used to have a satellite connection, but we didn’t use it enough to justify the cost.

I finally did some research online and came across the idea to take the Video-Out from the Tivo (actually the Cable-Out) and run a cable to another room. What this does is essentially give us an exact copy of what’s on one TV on the other.

The specifics of the cable hookup are like this:

1. I had all our rooms wired for cable quite some time ago, and all the rooms run into a box in the basement
2. I ran an additional cable from that box to the living room with the Tivo, and fished it through the same hole as the existing cable wall outlet
3. I bought a new cable wall faceplate that has 3 cable connectors instead of the usual one (Directv Tivos use 2 cables from the Satellite dish, I just needed 1 to go back out) and connected this new cable to it. This made it look perfectly normal in the living room, no messy cables coming out of a hole in the wall
4. Connect the new cable coming from the living room (connected to the extra Cable-out from the Tivo) to the cable going to the bedroom
5. Now the TV upstairs sees exactly the same thing as the living room

This solved the problem of getting the TV upstairs to work, but how do I control the Tivo, change channels, etc? Simple, I used a Remote Control Extender – which I got here (link). You basically set up the ufo-looking object somewhere in the room with the Tivo, then put the transmitter in as one of the batteries for your remote. You can then operate the remote from almost anywhere in the house – it’s great. They even supply two batteries, one of which is stored (and charged) in the ufo base.

Number of Decimal places and the SubString() function

I wanted a function that could give me the number of decimal places used in a given number. I really didn’t find any elaborate solutions on the web, so I decided to write my own by converting the number into a varchar and parsing it appropriately. While building the function, I discovered a neat little feature of the SubString() function.

Normally, SQL seems to work with an index of 1. So I if asked it the position of the “J” in “Jason”, it would return 1 (one). However, I was surprised to learn that the SubString() function accepts a 0 (zero) as a parameter for where to start the sub string.

This is an effective way to trim-off the last character of a string using SubString(@var, 0, len(@var)). For example, if I want to trim off the last character of “0040”, I can use the SubString() command to trim off the last “0”.

Here is an explanation with code:

--SubString(string, start, length)

Declare @var varchar(10);

Set @var = '0040';

Select SubString(@var, 1, len(@var)) --will return ‘.0040’

Select SubString(@var, 0, len(@var)) --will return ‘.004’

Using 0 for the start point with the length of the string has the effect of trimming the last character from the string. I wasn’t expecting this behavior, but it certainly is nice!

Decimal Count Function

Here is the entire function in case you are looking for something similar. I built it for use with SQL Server 2005.

Here is my usual disclaimer:

I’m not sure if this was the best way to go about solving the problem, but it worked. I thought my code and comments might be useful for someone else who has a similar problem. This code is free and comes with no guarantees.

One more thing - apparently I lost my formatting (tabs/spaces/etc) when I pasted this code, and I'm too lazy to fix it. Should only take a couple of minutes to make it look the way you're used to seeing it.

/*

DecimalCount

7/3/2008 JMJ

Count of the number of decimal places used after decimal point.

Params:

@numString varchar(MAX) varchar representing a number, assumes 1 (one) decimal point

Returns:

int Number of places used after decimal point, 0 (zero) if no decimal point

is found in the number. Will not consider zeros on the end of the number

(i.e. .0040 will consider only .004, which will return 3).

*/

CREATE function [dbo].[DecimalCount] (

@numString varchar(MAX)

) returns int

as

begin

declare @decimalPlace bigint;

declare @subStr varchar(max);

set @decimalPlace = 0;

--find index of decimal place

set @decimalPlace = Charindex('.',@numString,0);

if (@decimalPlace > 0)

begin

-- @subStr = string without decimal (or anything left of decimal)

set @subStr = @numString;

set @subStr = Right(@numString, Len(@numString) - @decimalPlace);

-- remove any trailing zeros

while(charindex('0',@subStr,len(@subStr))) = len(@subStr)

begin

-- catch conditions for numbers like '100.' or '100.0'

if ( charindex('0',@subStr,len(@subStr)) = 0

and len(@subStr) = 0 )

begin

break;

end

--using 0 for beginning will trim the last char from string

set @subStr = SubString(@subStr, 0, len(@subStr));

end

if (len(@subStr) is null)

begin

return 0;

end

else

begin

return len(@subStr);

end

end

else

begin

--no decimal place

return 0;

end

return 0;

end