Tuesday, August 26, 2008

Hide the bookmarks menu in FireFox 2 and 3

There are many reasons you may want to hide the Bookmarks menu in Firefox. Usually because you have some add-ons that give you similar functionality that you want to use. What ever the reason here is how you hide it.

Open the file userChrome.css which is located at: C:\Documents and Settings\<your username>\Application Data\Mozilla\Firefox\Profiles\<some code>.default\chrome\userChrome.css

In Firefox 2.x add the following to this file
 
#bookmarks-menu {
    display: none;
}


In Firefox 3.x add the following to this file

#bookmarksMenu {
    display: none;
}

A Mock Object (method) for sp_send_dbmail

It can be messy to test MS SQL Server 2005 Database Mail in your stored procedure because often you don't want to spam real people. So, often I found myself just putting some limited print statements and commenting in and out this code depending if I was testing.

I generally like adding if-else statements that key off of a test flag to permanently have the code available for testing. This helps when I sometimes need to make sure it is still working in production.

In eithe case, I still had a lot of duplicated code that I didn't like to look at and I was writing that debug code all the time.

My solution (adapted from unit testing principle that says we should use Mock Objects to simulate external systems when we do unit tests) is to create a method that takes the parameters that I typically use for sp_send_dbmail and does nothing more than print out their values. This allows me to use the exact same code when debugging as when I actually send the mail.

Here is my mock stored procedure:

create proc sp_send_dbmail_mock_object
(
 @profile_name nvarchar(128),
 @recipients varchar(MAX),
 @blind_copy_recipients varchar(MAX) = null,
 @subject nvarchar(255),
 @body nvarchar(MAX),
 @mailitem_id int = null output
)
as
 print char(13) + char(10) + 'sp_send_dbmail_mock_object called: '
 print 'Recipients: ' + ISNULL(@recipients, 'NULL')
 print 'Blind Copy Recipients: ' + ISNULL(@blind_copy_recipients, 'NULL')
 print 'Subject: ' + ISNULL(@subject, 'NULL')
 print 'Body: ' + ISNULL(@body, 'NULL')
 Set @mailitem_id = -1
 
go

I can now use it something like this

if @ActuallySendEmail = 1
BEGIN

 -- Docs: http://msdn.microsoft.com/en-us/library/ms190307.aspx
 EXEC msdb.dbo.sp_send_dbmail
   @profile_name = @Profile,
   @recipients = @RowRecipients,
   @blind_copy_recipients = @RowBlind_copy_recipients,
   @subject = @TemplateSubject,
   @body = @RowBody,
   @mailitem_id = @RowMailItemID output
END
else
BEGIN
 -- print debug info
 EXEC sp_send_dbmail_mock_object
   @profile_name = @Profile,
   @recipients = @RowRecipients,
   @blind_copy_recipients = @RowBlind_copy_recipients,
   @subject = @TemplateSubject,
   @body = @RowBody,
   @mailitem_id = @RowMailItemID output
END

What timezone is MS SQL Server?

The simple answer is you have to calculate it. This can be done quite easily by using the following sql. This will actually give you offset from UTC (GMT), and not the user friendly name of the timezone. select DATEDIFF(hh, GetUtcDate(), GetDate())

Timezones for date columns in msdb.dbo.sysmail_mailitems

There are a few date columns in msdb.dbo.sysmail_mailitems. They are send_request_date sent_date last_mod_date When working with these dates it is good to know what timezone they are stored in. MS SQL Server doesn't store timezone information in the datetime datatype. However, I have determined that these three date fields are stored in the timezone of the MS SQL Server. In other words, if you send an email you will see that these dates are close to the time that you get when you do: SELECT GetDate()

Thursday, August 21, 2008

Free tool to mount ISO images

The name of great little free Microsoft ISO image mounter is called MS Virtual CD Control Panel and can be downloaded directly from Microsoft or from many other sites. I have used this tool for years, and recently needed it again. This is an excellent tool for Windows XP Professional or Home or Media Center addition. It does not work on the server well. If I remember correctly, technically it will work for one use before you need to restart, but the issue comes when you need to unload the driver. It doesn't really play well in a multi-user environment is my take on it. I highly recommend this tool if you are looking for a free tool that is reasonably easy to use.

Looping through an array of regular expressions in JavaScript

Regular Expressions in JavaScript are easy (assuming you know regular expressions ;). This is NOT a tutorial on regular expressions. I recommend the this site as an intro to Regular Expressions using JavaScript. What this entry does cover is how to create a regular expression in JavaScript. The recommended way is var regex = /int/ This will create a RegExp object that matches on the pattern int. This is not very interesting but it does illustrate that lack of double-quotes. This a very nice syntax that does NOT require the regular expression to be a literal string which in turn require the escaping of backslashes. Here is an example of a match on a digit var regex = /\d/ There are times like when getting input from the user or a database or something that you need to use the string syntax. In this case you do have to escape backslashes. This site explains this concept in more depth than I do here. var regex = new RegExp("\\d"); By default Regular Expressions in JavaScript match based on case and only match on the first occurrence. You can change that by using one of the following options. var regex = /int/gi or var regex = new RegExp("int", "gi"); Now that we know how to create a regex expression you may be starting to see how we can use them in arrays. Arrays in JavaScript can be done a few ways. var myArray = new Array(/int/gi, /\w/gi); or var myArray = new Array( new RegExp("int", "gi"), new RegExp("\\w", "gi" ); or var myArray = new Array(5); myArray[0] = new RegExp("int", "gi"); myArray[1] = /int/gi

Tuesday, August 19, 2008

Joining rows in T-SQL into a single delimited string

I think the easiest way to think of what I want is to think of how you can join elements in an array and create a delimited string. I want to do the same thing except instead of an array, I have rows in a database. I will describe what I am trying to do from a real world example in a simplified fashion.

Here is the scenario. I have a query that returns one column of data. That column of data is an email address. The end product that I want is a single string (varchar) with the emails from each row to be delimited by commas. Once I have that, then I can pass the string to msdb.dbo.sp_send_dbmail.


Here is a simple way to show you what I have without all the tables, complex query, etc. In real life I would have a query that is on a table, etc. :)

create view vEmails as
select
'joe.tester1@mycompany.com' as EmailAddress union
select
'mary.someone@companyxyz.com' union
select
'happy.luck@aces.com'

Now we can do

select EmailAddress from vEmails


Here is the output that I currently have.

joe.tester1@mycompany.com
mary.someone@companyxyz.com
happy.luck@aces.com

What I want is

joe.tester1@mycompany.com, mary.someone@companyxyz.com, happy.luck@aces.com


The criteria is I want an elegant solution that doesn't use cursors.  Here is my solution.

Declare @EmailList as varchar(MAX)
select
    @EmailList = ISNULL(@EmailList + ', ', '')
    + EmailAddress
from vEmails
ORDER BY EmailAddress
select @EmailList

NOTE: The same can be done using COALESCE instead of ISNULL. The parameters are identical in this example.

The result is

happy.luck@aces.com, joe.tester1@mycompany.com, mary.someone@companyxyz.com

I love this solution because it is clever and does not use cursors. It also works with rows that have a NULL email address. If you decide you want to generalize the solution into an aggregate function you can NOT do this in T-SQL. You will need to do this in VS2005 or greater using the Database Project and write it in C# or VB.NET. This is an entirely different solution. The solution here is unfortunately not really generic.

One last important thing I figured out is that if you add an ORDER BY 1 (ORDER BY EmailAddress works fine) the @EmailList variable will only contain the value from the last row, not the accumulation of rows separated by commas. I am speculating, but I think the issue is that the select statement does not actually select any data, it actually assigned the value to a variable. So, using an alias (or whatever it is technically called) instead of the actual column name in an order by, this causes some weird behavior. The moral here is use the full name in the order by and you should be fine.

I got most of my solution from:

http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string


 

Monday, August 18, 2008

Sending Multiple Emails in SQL Server 2005

Some applications have batch jobs that send emails to users based on some criteria. There is no user interface to this, so a stored procedures that run as schedule jobs in SQL Server 2005 works well. An example of this is a reminder feature of your application. Maybe you want to remind all users to enter their time sheets on Friday afternoons. Below is an overview of how to write the sql for the lines in the stored procedure that can be used in the schedule job.

For more information on using a cursor see my blog entry at: http://justgeeks.blogspot.com/2008/08/using-t-sql-cursor-in-sql-server.html For more informaiton on configuring SQL Server Database Mail check out http://www.mssqltips.com/tip.asp?tip=1100 or do a search on Google for something like: setting up database mail. There are lots of articles on the subject. Please note that this code will only work on SQL 2005 (and maybe newer version, but definitely not SQL 2000). There is a lot of options for sp_send_dbmail. I recommend checking out the parameters, etc if you need more information. See http://msdn.microsoft.com/en-us/library/ms190307.aspx Here is the code to send email to a selection of people.

Declare @FName as nvarchar(50) Declare @LName as nvarchar(100) Declare @Email as nvarchar(255) Declare @MySubject as nvarchar(128) Declare @Message as nvarchar(MAX) Declare PersonCursor CURSOR FAST_FORWARD FOR Select fname, lname, email from Person where city = 'Phoenix' order by fname OPEN PersonCursor FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email WHILE @@FETCH_STATUS = 0 BEGIN    -- send the email    Set @Message = 'This is a test email.'    Set @MySubject = 'Hi ' + @FName + ' ' + @FName    EXEC msdb.dbo.sp_send_dbmail       @profile_name = 'MyProfile',       @recipients = @Email,       @subject = @MySubject,       @body = @Message    FETCH NEXT FROM PersonCursor    INTO @FName, @LName, @Email END CLOSE PersonCursor DEALLOCATE PersonCursor

Using a T-SQL Cursor in SQL Server

I can never seem to remember the syntax for a cursor in T-SQL so I decided to add it to my blog. If you are not familiar with a cursor, it is essentially a way to loop through selected rows in a SQL database.
Please use caution with cursors. They are very poor performers and often other options make more sense for performance reasons. However, there are times when it makes sense to use cursors. It can be difficult for programmers to think like DBAs sometimes because programming is typically a step-by-step logical progression of thought, but SQL databases should be thought of in terms of sets. Cursors are in a gray area that kind of abuses SQL a little bit because that is not what databases are optimized for.
There is my two cents. Assuming you need to use cursors here is how they work. Basically a cursor takes a query, and copies each column in that select statement into T-SQL variables. You can then do whatever you want with these variables. In our case we will send email. Once you are done with that row you get the next row.
In general you want to use the where clause to limit the rows that the cursor has to go through. For example, do NOT do something like (select * from Person) and then in the cursor use an if statement to ignore all records that don't meet your criteria. This is best done in a where clause.
Let's assume you have a query like the following:

Select fname, lname, email from Person where city = 'Phoenix'
order by fname


Now you want to send an email to each of these people. Here is what you do:

  1. Declare variables for each of the columns you are select Declare @FName as nvarchar(50)
    Declare @LName as nvarchar(100)
    Declare @Email as nvarchar(255)
  2. Declare the Cursor Declare PersonCursor CURSOR FAST_FORWARD FOR
  3. Now your we use the select statement (the one you want to loop through) Select fname, lname, email from Person where city = 'Phoenix' order by fname
  4. Open the cursor and copy the columns into the T-SQL variables. Be sure to order the select statement columns and the columns below in the same order since the order determines how the values are mapped.
    OPEN PersonCursor FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email
  5. Now that we have the data for the first row, let's start the WHILE loop. Notice, that we get the first row BEFORE the WHILE loop is started so that the loop condition will be satisfied to start with. To control when the WHILE loop will exit we use the @@FETCH_STATUS built-in T-SQL variable. Notice that at the end of the WHILE loop we use exact same FETCH lines as we did to get the first row of data.
    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- do row specific stuff here
       print 'FName: ' + @FName print 'LName: ' + @LName print 'Email: ' + @Email
       FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email
    END
  6. The most important lines are the next two lines. These are the lines that free the resources. If you don't do this you have what they call a memory leak. It can eat up valuable server memory and possibly eventually take down your server if you don't clean up after using resources.
    CLOSE PersonCursor
    DEALLOCATE PersonCursor
Here is the completed example in one big block.
Declare @FName as nvarchar(50)
Declare @LName as nvarchar(100)
Declare @Email as nvarchar(255)

Declare PersonCursor CURSOR FAST_FORWARD FOR 
Select fname, lname, email from Person where city = 'Phoenix' order by fname
OPEN PersonCursor
FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email
 
WHILE @@FETCH_STATUS = 0
BEGIN
   -- do row specific stuff here
   print 'FName: ' + @FName print 'LName: ' + @LName print 'Email: ' + @Email
   FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email

END 
CLOSE PersonCursor
DEALLOCATE PersonCursor

Tuesday, August 12, 2008

How to Completely Remove PocketMac for Windows Mobile

Mark / Space (makers of Missing Sync) have the best instructions on how to completely remove PocketMac for Windows Mobile. http://www.markspace.com/support/remove_pocketmac.html

Manually Uninstalling Missing Sync for Windows Mobile

Okay, I went ahead and downloaded the installer - follow these steps exactly: 1) Go to Apple menu -> System Preferences .... a) Go to Accounts, click on your account and select "Login items" .... b) Select "Missing Sync WM Monitor" then click the "-" button .... c) Close System Preferences 2) Delete: .... /Applications/Missing Sync for Windows Mobile (or whatever you have) .... /Applications/MarkSpace:Notebook.app .... /Applications/MarkSpace .... /Library/Application Support/Missing Sync for Windows Mobile .... /System/Library/Extensions/MissingSyncWm.kext .... /Users/<your account>/Library/Application Support/Missing Sync for .. .... /Users/<your account>/Library/Application Support/SyncServices/Local/clientdata/com.markspace.missingsync.* .... /Users/<your account>/Library/Caches/com.markspace.missingsync.windowsmobile .... /Users/<your account>/Library/Caches/Missing Sync for Windows Mobile .... /Users/<your account>/Library/Caches/Missing Sync WM Monitor .... /Users/<your account>/Library/Caches/MissingSync_cardinfo .... /Users/<your account>/Library/Caches/MissingSync_mounted .... /Users/<your account>/Library/Logs/MissingSync for Windows Mobile.log .... /Users/<your account>/Library/Preferences/com.markspace.missingsync.* 3) Log out/Log back in, or Reboot your machine. I got the details from: http://forums.macrumors.com/showthread.php?t=440590

Thursday, August 7, 2008

Basics of Subclipse when starting a new project

Subclipse is a plugin for Eclipse that adds a GUI in Eclipse to perform the basic functions of Subversion (like CVS or Visual SourceSafe (VSS)). Basically if you want to use Subversion while in Eclipse, Subeclipse is the tool for you.

This entry assumes that you have a Subversion repository already setup. It does NOT assume your project is in Subversion though. It does assume you already have a project in Eclipse though. In my examples, the name of my Eclipse project is called MyApp. Below are some basic things you should know where to find, but does not really get into Subversion features and how to use them. This entry assumes you know what Merge, Update, Commit, etc are used for, and how to use the other Subversion version control concepts. By no means is this a comprehensive look at all the features. It is really a view of things I use often or can't remember when I do them when I get a new project or start a new project. :)

Adding your project to Subversion.

Make sure your project is actually open (not closed), otherwise the Share Project... menu item will be disabled.

While in Eclipse, Go to the Package Explorer and right-click on MyApp (or whatever your project is called). Go to Team | Share Project...

When prompted select SVN as the repository type.

When prompted select your existing repository location, or create a new one if you don't have one already.

When prompted, use a custom path and append /MyApp/trunk to the path instead of just the default of /MyApp

Enter a comment like Initial import if it is not already specified.

When prompted select the files you want to add to source control. This should exclude any files that are specific to other version control systems such as VSS or CVS.

I would also highly recommend creating three remote folders (branches, tags, and trunk) under the project you just created in SVN. To do this do the following:

Go to the SVN Repository View (you can add it by going to the Window | Show View | SVN Repository menu item if it is not already visible.

Right-click on MyApp project in the SVN Repository View and choose New | new remote folder.

Enter branches

Then do the same for the other one tags

When you are done you will have a hierarchy like the following or something similar if you did not put yoru project at the root of the repository.

- svn://mySubversionServer/MyRepository/MyApp ---- branch ---- tags ---- trunk

Using Subversion operations on a file

Subclipse does a very nice job of integrating Subversion into the Eclipse IDE. To perform operations of a file simply do the following.

In the Package Explorer right click on the file you want to work with.

Go to Team and then choose the operation you want to use. This includes things like Merge, Commit, Show History, get the latest using Update, and a few other features as well.

Useful Perspectives

Subclipse includes some useful perspectives that may not be visible to start with. You can find them by going to Window menu Open Perspective Other...

This will show a window with a list of perspectives.

Click the SVN Repository Exploring and click OK. It is good to see all the projects in the repository or have direct access to the Repository.

Do the same for Team Synchronizing. Team Synchronization has the Synchronize view in it.

If you don't see them, click the Show all button in the Open Perspective window. There are also two other menu items that are of use when you right-click a file. They are the Compare With and Replace With menu items.

Adding a project from Subversion

This handy if you have a new machine, re-installed Eclipse, or just adding a project to Eclipse workspace that someone else put in Subversion and you need to work on now. To do this, do the folowing.

File menu New Other SVN folder Checkout Projects from SVN.

Walk through the wizard until it is finished.

If you want more information on Subversion operations, and installing subclipse see the link below.

http://www-128.ibm.com/developerworks/opensource/library/os-ecl-subversion/

Adding Regular Expressions (Regex) to SQL Server 2005

It is very easy to add Regular Expressions or other custom functionality to SQL Server 2005 because it support CLR. This means all you have to do it create a custom Assembly, mark it up with some attributes (similar to the way you do web services), and click the deploy button, and that is it. Microsoft really does make it easy using Visual Studio 2005 (and 2008 I assume). All you have to do it create a SQL Server Project. You can use VB.NET or C#, just pick the appropriate SQL Server Project. It pretty much walks you through creating the project. After it is done, you will need to right-click the project | Add | User-Defined Function... Give it whatever name you want. It gives you a simple stub. Just build and deploy. It deploys the assembly to the database it helps you with initially, and makes User-Defined functions (that call the assembly). You can then call your function like any other User-Defined function. The name and parameters show up in the User-Defined functions section under Database | Programmability | Functions | Scalar-valued Functions. It was also recommended by someone (see references) to in execute the following SQL (I only did it the first time I deployed) to enable CLR and install required support. sp_configure 'clr enabled',1 reconfigure There is one VERY important thing you need add to any method you want to be able to access from SQL. You must add the attribute [SqlFunction]. The method must also be public and static I believe. The parameters and return value have to be SQL types like: SqlChars, SqlString, SqlInt32, etc. You can use standard C# and VB.NET types everywhere within your method, but the parameters and return value MUST be SQL types. Below is my implementation (or at least part what I wrote and part an adaptation of parts from what other people wrote... see references) of three key Regular Expression methods I think are very useful.
  • RegexMatch - returns 1 if pattern can be found in input, else 0
  • RegexReplace - replaces all matches in input with a specified string
  • RegexSelectOne - returns the first, second, third, etc match that can be found in the input
  • RegexSelectAll - returns all matches delimited by separator that can be found in the input
Examples of how to use them in SQL:
  • select dbo.RegexMatch( N'123-45-6749', N'^\d{3}-\d{2}-\d{4} Returns 1 in this case since the phone number pattern is matched
  • select dbo.RegExReplace('Remove1All3Letters7','[a-zA-Z]','') Returns 137 since all alpha characters where replaced with no character
  • select dbo.RegexSelectOne('123-45-6749xxx222-33-4444', '\d{3}-\d{2}-\d{4}', 0) Returns 123-45-6789 since first match was specifed. If last parameter was 1 then the second match (222-33-4444) would be returned.
  • select dbo.RegexSelectAll('123-45-6749xxx222-33-4444', '\d{3}-\d{2}-\d{4}', '|') Returns 123-45-6749|222-33-4444
The actual implementation of this is nothing special other than the conversion of SQL Types. The complete source code (no project since it is so specific to your environment) to the implementation is available for download here.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Text;

public partial class UserDefinedFunctions
{

    public static readonly RegexOptions Options = RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline;

    [SqlFunction]
    public static SqlBoolean RegexMatch(SqlChars input, SqlString pattern)
    {
        Regex regex = new Regex(pattern.Value, Options);
        return regex.IsMatch(new string(input.Value));
    }

    [SqlFunction]
    public static SqlString RegexReplace(SqlString expression, SqlString pattern, SqlString replace)
    {
        if (expression.IsNull || pattern.IsNull || replace.IsNull)
            return SqlString.Null;

        Regex r = new Regex(pattern.ToString());
        return new SqlString(r.Replace(expression.ToString(), replace.ToString()));
    }

    // returns the matching string. Results are separated by 3rd parameter
    [SqlFunction]
    public static SqlString RegexSelectAll(SqlChars input, SqlString pattern, SqlString matchDelimiter)
    {
        Regex regex = new Regex(pattern.Value, Options);
        Match results = regex.Match(new string(input.Value));

        StringBuilder sb = new StringBuilder();
        while (results.Success)
        {
            sb.Append(results.Value);

            results = results.NextMatch();

            // separate the results with newline|newline
            if (results.Success)
            {
                sb.Append(matchDelimiter.Value);
            }
        }

        return new SqlString(sb.ToString());

    }

    // returns the matching string
    // matchIndex is the zero-based index of the results. 0 for the 1st match, 1, for 2nd match, etc
    [SqlFunction]
    public static SqlString RegexSelectOne(SqlChars input, SqlString pattern, SqlInt32 matchIndex)
    {
        Regex regex = new Regex(pattern.Value, Options);
        Match results = regex.Match(new string(input.Value));

        string resultStr = "";
        int index = 0;

        while (results.Success)
        {
            if (index == matchIndex)
            {
                resultStr = results.Value.ToString();
            }

            results = results.NextMatch();
            index++;

        }

        return new SqlString(resultStr);

    }

};

What I used to get started. http://weblogs.sqlteam.com/jeffs/archive/2007/04/27/SQL-2005-Regular-Expression-Replace.aspx How to optimize regex calls: http://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx More Information: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx I found this after writing this, but it explains other details I did not. http://www.codeproject.com/KB/string/SqlRegEx.aspx?display=PrintAll

Wednesday, August 6, 2008

Getting Started with Subversion Command Line

I just started using Subversion and thought I would list some sites I found useful. Below is a list of useful places to start when you want to get started with Subversion. Start here for information on installing and basic syntax. https://www.projects.dev2dev.bea.com/Subversion%20Clients/CommandLineSVN.html#install For more information and examples on syntax and concepts in Subversion in a book like format check out: http://svnbook.red-bean.com/en/1.0/index.html

Automatically backup and tag a release using Subversion

In a previous entry I discussed automating the backup and tagging of a project using Visual Source Safe (VSS). Today, I want to do the same thing, but using Subversion. The example below backs up a MyApp.war file on my Tomcat server and copies it to my local backup directory. It then pauses to remind me to check in all my changes (into Subversion). Finally, it creates a tag (like a label for your VSS folks) that includes the date and time the tag was created and notes that it is a deployment tag. @echo off set NOW=%date:~10,4%-%date:~4,2%-%date:~7,2%--%time:~0,2%.%time:~3,2% set NOW_FRIENDLY=%date:~10,4%-%date:~4,2%-%date:~7,2% %time:~0,2%:%time:~3,2% zip -r "C:\backups\MyApp-prior%NOW%.war.zip" "\\myServer\c$\Program Files\Apache Software Foundation\Tomcat 6.0\webapps\MyApp.war" echo Backup of web site has been saved at: "C:\backups\MyApp-prior%NOW%.war.zip" echo Please make sure all changes are checked in pause svn copy svn://subversionServer/MyRoot/MyApp/trunk svn://subversionServer/MyRoot/MyApp/tags/DEPLOYED-%NOW% -m"DEPLOYED %NOW_FRIENDLY%" echo A label has been created in Subversion called: DEPLOYED-%NOW% pause