Friday, December 19, 2008

Why can't I see my Visual Studio 2008 Solution File in the Solution Explorer

In Visual Studio 2008 when you open your project or even open a solution file the Solution Explorer doesn't show the solution file by default. There are times that you want to see the solution file, like when you want to add multiple projects to a solution. To show the solution file in the Solution Explorer just follow the instructions below once you are have your project open in Visual Studio 2008.
  1. Go to the Tools Menu and choose the Options... menu item.
  2. In the Options window, expand the Projects and Solutions node.
  3. Click the General node (child of the Projects and Solutions node)
  4. Click the Always show solution checkbox.
  5. Click OK.
Now you should be able to see the Solution file in the Solution Explorer. If you want to add another project to the solution all you have to do is right click on the solution and choose Add | New Project...

Wednesday, December 17, 2008

Excel spreadsheet is Read-Only after opening from SharePoint

Here is the situation, I am using MOSS 2007, and a particular user or group of users was able to checkout, open an excel file from a SharePoint document library, make changes, save, and check in before some magical event and then they no longer have this ability. Now when user does this now, the user can check out the file, but when Excel opens the file, it is read only and cannot save file to SharePoint space either. The user can clearly see that when the file opens up the Excel title bar says that it is in Read Only mode, so it is very easy to see if this fixed it or not. Be sure to check it out first, otherwise it will be in read only mode. Especially if someone else has it checked out. There are a lot of suggestions out there on how to deal with this issue. There are even more complaining how different solutions didn't work. This solution works for me. Almost all of the solutions stem from issues with Microsoft Office SharePoint Services Support (which I believe is part of the Microsoft Office Access Web Datasheet Component) being installed incorrectly. The most important file appears to be OWSSUPP.DLL. By default it lives at C:\Program Files\Microsoft Office\Office12\OWSSUPP.DLL This is the DLL that should be used with MOSS and WSS. The problem that I have seen is that when the user experiences the issue described above, it is not using the correct version of this DLL. As it turns out there are other version of this DLL. In a perfect world, you would only have one version on your computer. The problem comes when you have different versions of Office products. An example would be that you have MS Excel 2007, but you are using an older version of MS Project, or MS Word 97, etc. In this type of installation there will likely be more than one copy of the OWSSUPP.DLL. I would search your Program Files directory for all instances of it. Generally, depending on your installation, you may find that they are located at: C:\Program Files\Microsoft Office\Office11\OWSSUPP.DLL C:\Program Files\Microsoft Office\Office10\OWSSUPP.DLL One solution people have recommended is reinstalling the Microsoft Office SharePoint Services Support using Add/Remove Programs. You have to click the Change button, and this should launch the MS Office installer. There you should be able to reinstall the component. With this method, I would imagine you will need to reinstall products in the order they were released so that the Office 2007 installation is last and will hopefully be found first. Another solution I personally tried was to just rename the non-Office12 instances of the file and try again. I recommend something like OWSSUPP_DISABLED.DLL so that it is clear that it has been renamed. I restarted IE, but I don't know if it was really necessary. I as happy I did NOT have to restart Windows though. This issue came up on Microsoft Office SharePoint Server 2007 (MOSS), but is most likely an issue on Windows SharePoint Services 3.0 (WSS). A side note, based on what I read on forums, the problem is on particular computers (which makes sense based on what this solution). Anyone who logs into this computer will likely have the same issue. If the user that is having the issue logs into another computer they will not have the issue while they are on that other computer (assuming it does not suffer from the same issue).

Wednesday, November 19, 2008

Which w3wp.exe is the one I want?

If you have every tried to debug a web application running on Windows Server 2003 you will be faced with which w3wp.exe process do you attach to remotely. I have figured out a few ways. In order of ease:
  1. User Name - If you change the App Pool Identity to something other than Network Service (like your personal user account... temporarily of course) you should be able to open Windows Task Manager and see name next to one of the w3wp.exe process. I highly recommend stopping and restarting the Application Pool (not just recycle it). The best part is you can also see the User Name in Visual Studio when are in the attach to process window and choosing the process.
  2. CPU Time - If you open Windows Task Manager, now recycle the App pool. The CPU Time should go to zero. When you hit a page on the site, it should show some small value greater than zero depending on how much cpu the page actually takes. This will allow you to get the PID (you may have to add the column to Windows Task Manager) which you can see when you are in Visual Studio and attaching to the process.
  3. You can also use Sysinternals (now Microsoft owns it so....) Process Explorer. Using this tool, you can right click on the w3wp.exe in its list of processes and look at the Command Line that was used to launch the process. The last parameter is the name of the App Pool. Assuming you are using different app pools for each web site, this will allow you to get the PID, and use that to attach to the process just like the other scenarios.
I hope this helps. I find it generally frustrating that it is so difficult to determine which process is the one I need. Oh well, this seems to work reasonably well.

Friday, November 14, 2008

Can't access Account Settings in Outlook 2007

If you are using Outlook 2007 and have some controlling system administrators they may have locked you out of Account Settings in Outlook 2007. You will know they have locked you out because you will get a message like the following when you go to Outlook 2007 | Tools | Account Settings. This feature has been disabled by your system administrator. For more information, contact your system administrator. This is unfortunate because this is an easy place to add RSS Feeds, etc. To temporarily (until the next Group Policy refresh most likely) enable your access again there is a very easy registry change you can do. In fact, you can put the registry change in a file and just double click it to make the change. Once the change is applied you should be able to open up Outlook 2007 and go to Tools | Account Settings. The registry key you need to change is the following:
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Outlook\Setup\ModifyAccounts

You need to set it to 0 to enable access, and 1 to block access.

If you want to be able to just double-click a file to make the change do the following.

  1. Open notepad
  2. Copy and paste the following into notepad Windows Registry Editor Version 5.00 [HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Outlook\Setup] "ModifyAccounts"=dword:00000000
  3. Save file with a .reg file extension. For example: unlockOutlook.reg
Keep in mind this is a group policy so you will likely need to make the change (or double-click the file you created) each time you want to access the Account Settings. For more great Outlook hacks check out: http://wiki.yobi.be/wiki/Outlook#All_in_one_registry_file

Monday, November 10, 2008

Release the memory after using a SPWeb object

It is important to clean up after you are done with a SPWeb. Below is a snippet of code that connects to the top level site of SharePoint and loops through all the webs (recursively). The important thing to note here is that after we do whatever we want to do with the web, we call the Dispose() method on the SPWeb. If you don't do this and your site if big, you will run out of memory. Since this code needs to run on your SharePoint server, it is definitely not a good thing to use up all your memory. :)

using (SPSite site = new SPSite("http://sharepointserver"))
{

   SPWebCollection webs = site.AllWebs;
   foreach (SPWeb web in webs)
   {
       DoSomethingWithWeb(web);
       web.Dispose();
   }
}

Wednesday, October 1, 2008

ERROR: Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS in the equal to operation

If you get the following SQL Server error message:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS in the equal to operation.


It is because of Collation settings on two columns you are joining on (or maybe something similar. There are permanent fixes, but are not simple and mostly assume you can change table definitions, etc.

I prefer the simple fix.  

The first step is to figure out what are two collations that are conflicting. Assuming that the collation has not been set at the column level and is instead at the database level, here s what you need to do.

Execute the following two statements (after you put your two database names in the statements).

select DATABASEPROPERTYEX ( 'DB1', N'Collation' )
select DATABASEPROPERTYEX ( 'DB2', N'Collation' )

If you are on SharePoint you will likely get Latin1_General_CI_AS_KS_WS. If you are on most any other database and use the default settigs you may get: SQL_Latin1_General_CP1_CI_AS.

Now that you know what you are dealing with you just need to do something similar to a CAST, but for Collation. It is called Collate. Here is an example of a query that joins on two columns that have different collations.

select * from DB1.dbo.User u
join DB2.dbo.UserMap m
on (u.email = m.email COLLATE SQL_Latin1_General_CP1_CI_AS)


For your query you will should choose the Collation of one of the columns.

If you want to understand more about this, I suggest checking this link:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/12/883.aspx

The database schema is too old to perform this operation in this SharePoint cluster.

Have you ever received the following error while trying to reconnect or restore a SQL Server database in SharePoint? The database schema is too old to perform this operation in this SharePoint cluster. This is a little mis-leading in my opinion. It is talking about a version mismatch between SharePoint Configuration database and the database you are trying to restore or reconnect to SharePoint. This should apply to both Windows SharePoint Services v2 (WSS) and SharePoint Portal Server 2003 (SPS), though the names of the databases may be different. Let's assume your content database is called xxx_SITE where xxx is the name of your SharePoint site if you are using SPS. If you are using WSS you are looking for your Content database. If you open up SQL Server Management Studio 2005 or Query Analyzer you can run the following two queries. You will see that there is a mismatch on the version numbers. select Version from xxx_SITE.dbo.SystemVersion select SchemaVersion from SPS01_Config_db.dbo.Globals What this means is that you have a different version of SharePoint installed than the version that was installed when SharePoint was backed up. I have found on the web documentation that says it has to be the same service pack level, but my experiences has told me that the patches have the be the same as well. Here if you look under the comments for one by Andrew Woodward you'll see he also had the same experience. This site seems to have a good list of what the different versions are in relation to patches. Once you have the same version of SharePoint installed again everything should restore fine. If you are not sure what the previous version was you can get it from the content database (xxx_SITE in this example) using the above query.

Friday, September 19, 2008

Alternate syntax for order by in SQL Server

The order by clause is very flexible in SQL Server. You can pass it nearly anything and it will work.

In this section I show how to sort a results based on a runtime value. This works well for a stored procedure that is used for sorting and the user interface has the ability to sort by different columns. Instead of using Dynamic SQL, creating multiple stored procedures, or have a long list of if-else and then copy and pasting the same basic code (just changing the order by column), this solution is simple and easily maintainable.

Declare @Orderby as varchar(20)
Set @Orderby = 'CREATED DESC'
SELECT [NAME] as FullName, SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY -- add columns to sort by here
 CASE @Orderby WHEN 'NAME DESC' THEN [NAME] END DESC,
 CASE @Orderby WHEN 'NAME ASC' THEN [NAME] END ASC,
 CASE @Orderby WHEN 'SEARCH_CODE DESC' THEN SEARCH_CODE END DESC,
 CASE @Orderby WHEN 'SEARCH_CODE ASC' THEN SEARCH_CODE END ASC,
 CASE @Orderby WHEN 'CREATED DESC' THEN CREATED END DESC,
 CASE @Orderby WHEN 'CREATED ASC' THEN CREATED END ASC

You can refer to columns by number instead of column name. This can make columns that have complex subqueries easier to reference in the order by.

SELECT [NAME] as FullName, SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY 3 DESC, 1 ASC

Instead of column name or column number, you can use the column alias

SELECT [NAME] as FullName, SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY FullName ASC

Here are some variations on the previous example

SELECT [NAME] as 'FullName', SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY FullName ASC

SELECT [NAME] as 'FullName', SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY 'FullName' ASC

SELECT [NAME] 'FullName', SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY 'FullName' ASC

Wednesday, September 10, 2008

Override any CSS style (even inline styles) no matter where it is defined

Cascading Style Sheets (CSS) are very nice for formatting HTML. In general, I think it is a very bad idea for an ASP.NET control to emit inline styles because it prevents the developer from overriding the formatting. This is of course unless there is a corresponding property or programmatic way provided of changing it.

The ASP.NET GridView is a great example of this. It emits the following inline style for the table tag it emits.

style="border-collapse:collapse"

If I want to change that *they* provide no way to do so.

However, CSS does provide a way to that I recently found here http://home.tampabay.rr.com/bmerkey/cheatsheet.htm.

So, all I have to do is create a CSS class called something like GridViewStyle by including the following between the head tags on the .aspx page.

<style>
.GridViewStyle { border-collapse:separate ! important; }
</style>

This is the key to this entire solution. Notice the ! important; This means that border-collapse will be set to separate even if the inline style says differently. This is *very* powerful.

To apply this CSS class to the table tag that is emitted by the GridView all we have to do is set the CssClass property of the GridView to GridViewStyle.

Add TBody and THead to GridView

If you want to play nice with standard (for many reasons), you will notice that GridView does a fair job. However, it does not use tbody, thead, or tfoot tags for the table that is generated when rendered in the browser.

This technique for this entry was learned from this article. However, I found that it was really correct or functional in all cases. For example, if you click on a column i the GridView to sort or do anything else on the page to cause the GridView to do its databinding again the html is regenerated. Which means that after DataBind() is called (implicitly using ObjectDataSource or SqlDataSource) you need to make the changes again. You could technically do this in the PageLoad event, but you would also have to do it in the other places that cause the data binding to fire again. The easiest way I no to have the code in one place is to do it in the PreRender event of the GridView.

L
uckily, there is a simple fix

protected void GridView1_PreRender(object sender, EventArgs e)
{

   // You only need the following 2 lines of code if you are not 
   // using an ObjectDataSource of SqlDataSource

   GridView1.DataSource = Sample.GetData();
   GridView1.DataBind();

   
if (GridView1.Rows.Count > 0)
   
{
      //This replaces <td> with <th> and adds the scope attribute
      GridView1.UseAccessibleHeader = true;

      
//This will add the <thead> and <tbody> elements
      GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;

      
//This adds the <tfoot> element. 
      //Remove if you don't have a footer row

      GridView1.FooterRow.TableSection = TableRowSection.TableFooter;
   }

}

The easiest way to wire up this event is to in the Designer, get properites on the GridView, and then click the Events icon (lightning icon) to get the list of events. Now, double-click the PreRender space. This will create a method calle something like the one above. Copy the above code and you are ready to go.

Or

If you prefer the no GUI way. Just add it to your GridView tag. That would look something like this:


<asp:GridView ID="GridView1" runat="server" 
    OnPreRender="GridView1_PreRender">
</asp:GridView>

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.

Option 1:

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

Option 2:

An even nicer way to do this because it can be included as a sub-query is to use the following:

select STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(EmailAddress)) AS 'data()' FROM vEmails
FOR XML PATH('')),' #!',', '), 1, 2, '') as EmailAddresses

This was suggested by this page.

















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

Thursday, July 31, 2008

MyEclipse tabs (views)

If you are new to MyElipse and Elipse you may have accidentally closed one of the tabs in one of your perspectives. I did, and it took me a while to figure out were to go to add it back since I didn't know what the tabs were called anyway. The answer is that they are called Views. Once I figured that out, it was easy to find the solution under Window menu | Show View and then click the View you want to show. You can also use Other... to a full list of Views if you don't see the one you want in the list. You can also drag the View from one dockable area (I don't know what they are really called) to another.

Tuesday, July 29, 2008

Automatically label releases to production using Visual Source Safe

When I move a project from my computer to the production server I like to create a label in Visual Source Safe (VSS) that notes the date and time and that something was deployed. This allows me and others to always know what is in production and how long it has been in production. It also makes it easy to do a bug fix. It is nice to have this at the click of a batch file. Otherwise, I am just to lazy most of the time to wait for VSS (it is on a slow connection so it takes a long time to load or do anything), remember to check in my code, find the project I am working on, then create a label, and type in the date and time and try to remember the format of my label so that is the same. Here is the code I put in a batch file (just create a text file and change the extension to .bat) using my favorite text editor. For more information on how the NOW variable is populated and limitation on it, see my other entry at: http://justgeeks.blogspot.com/2008/07/getting-formatted-date-into-variable-in.html. If you are not using US English data format you will definitely need to change the line that sets NOW to work with your locale. Also, you will need to adjust the paths in this script to match your environment.

@echo off set NOW=%date:~10,4%-%date:~4,2%-%date:~7,2%--%time:~0,2%.%time:~3,2% echo Please make sure all changes are checked in pause set SSDIR=\\myVssServer\someDirWhereIniFileIs"C:\Program Files\Microsoft Visual SourceSafe\ss" Label $/MyVssProjectPath -LDEPLOYED-%NOW% -C- echo A label has been created in VSS called: DEPLOYED-%NOW%

Save output of DOS command to variable

What I want to do is something like: set myVar=pwd BASH and other *nix shells allow you to do things like that (yes the syntax is different) and it is built into the shell. I find it unbelievable that it is so difficult in a batch file to save the output of a DOS command to a variable that can be used later in the script. I know you can use redirection to a file, call the file, delete the file, etc, but that just seems really lame. Then I found that the for loop will allow me to do what I want. It is truly a hack, but it does work, and is fairly straight forward. FOR /F "tokens=1 delims=" %%A in ('pwd') do SET myVar=%%A echo %myVar% if you are not in a batch file you can test it using FOR /F "tokens=1 delims=" %A in ('pwd') do SET myVar=%A echo %myVar%

Getting formatted date into variable in batch file

There are many times I create a batch file to do backups of a deployment before I do a new deployment. I want to keep the history of deployment backups, so I want to include the current date and time in the filename. I could not believe how incredibly different and complex the solutions were for this when I Googled it. I am doing this on my own machine or at least environments that I know so this batch file doesn't have to worry about this working on all machines with different date formats, etc. The script can be adjusted to work on any date format, but it will not work as is on all date formats. As it turns outs there is already a variable with the date and another with time in it called %date% and %time%. You can see this by typing echo %date% %time% at a command prompt. On my machine this returns a string like the following: Tue 07/29/2008 11:30:10.54 A little know bit of functionality built into the command prompt (at least in XP and maybe earlier) is substring functionality. It uses zero based indexes. Using the previous example we can reformat the output to be just about anything we want. As an example lets do 2008-07-29--11.30. To do this we would do the following echo %date:~10,4%-%date:~4,2%-%date:~7,2%--%time:~0,2%.%time:~3,2% Now if you want to use this in a script that zips a directory and gives the filename with the current date and time here is what you could do: set NOW=%date:~10,4%-%date:~4,2%-%date:~7,2%--%time:~0,2%.%time:~3,2% zip -r c:\myApp%NOW%BU.zip \\myserver\myapp

Sunday, July 27, 2008

External Hard Drive setttings

I just found out that buying an external hard drive case that is USB 2.0 and putting an ATA EIDE drive in the case needs to have the correct settings (Single / Master / Slave). I found out that slave sort of works. It worked for about 1 minute before hanging. It also reported some issues when accessing it. I then changed the setting to Single and it works great with no issues. This is the case for Mac OS X and Windows.

Friday, July 25, 2008

A brief overview of Testing

Why do we need to test anyway?
  • Living documentation of the expected behavior of the system
  • To build confidence that software works and still works after making a change
  • Reduce cost by finding bugs early
  • A method to document requirements and verify solution meets the requirements
  • Quality assurance including behavior, performance, etc
Questions to think about?
  • When you make a change, how do you currently determine if the change does what it is supposed to?
  • ... that it didn't break something else?
  • ... where do you document that this is a new requirement?
  • ... how much time will the next programmer have to spend to be able to verify that your requirement still works after his or her new change?
Types of Functional Testing
  • Unit Testing - tests of methods or classes, but not across them.
  • Regression Testing - typically uses unit tests to verify functionality still works as expected
  • System Testing - testing across classes and methods. This is general higher level and tests more of how the system behaves vs the requrements rather than implementation.
  • Integration Testing - tests across classes and methods; it stri
  • Acceptance Testing - done by the end user to give their stamp of approval
Types of Non-Functional Testing Black Box Testing Testing that assumes you don't know the implementation of what is in the box. In this type of testing you have inputs and outputs, but you don't know how inputs are mapped to outputs. In this scenario you think of the box as something you bought or got from someone else, and don't really know how it works, just what it is supposed to do. Examples are: Use case testing, White Box Testing Testing that assumes you DO know the implementation of what is in the box. In fact, the purpose of the test is to make sure that all paths of execution in the box are tested (or at least the ones that can break if you subscribe to Extreme Programming techniques). Typically boundaries, ranges of values, control flow, data flow, and other code execution testing is the focus. Examples are: Unit Testing Test Driven Development Is an approach to design better software. Test Driven Development (TDD) starts the development cycle with gathering requirements, but then quickly moving to writing test cases that document the requirement and force the designer to think about exactly what the system is supposed to do. It takes automated unit testing and regression testing and makes them a basis for all development activities. This means that when adding new functionality a test is written first, it will fail this test at first, then as it is implemented it will eventually pass the test. The best part is now refactoring can take place because we have regression testing already in place to verify by refactoring that we did not break anything. This promotes clean, clear, modularized code because if you can test the code it is likely that it is modular and easier to maintain.

Thursday, July 24, 2008

Adding Loading animation to all AJAX calls

So, you like AJAX, and your users like it except they can't tell when something is being done in AJAX. They complain that there is nothing that indicates to them that something is happening such as a query for a list, save, etc. One solution to this is to show an animated gif next to the element that caused the AJAX call in the first place. While the first line below and the BeginRequest() method are ASP.NET AJAX specific, the addLoadingAnimation() method is just JavaScript and can be used with Java, PHP, etc. The addLoadingAnimation() is IE and FireFox compatible on Windows, and has not been tested in any other browsers or platforms. The animation is removed in the endRequest event in ASP.NET when using an Update Panel. There is nothing needed because of how the Update Panel works. In other situations, the animation may need to be removed manually. Additional code would be needed, but should be able to be done in a similar way. The code assumes that the image for the text input will fit nicely inside the text input. A 16 x 16 image will probably work for text inputs of default height. The loading image for all other elements can be whatever size you like.
// Register our event listener that is called when an AJAX request is made.
Sys.WebForms.PageRequestManager.getInstance().add_beginRequest(BeginRequest);

function BeginRequest(sender, args) {
var srcElement = args.get_postBackElement();
addLoadingAnimation(srcElement);
}

// Adds a loading animated gif to the right of
// the element that started an element
// (Except if the element is a text input,
//  then the image is inside the text input
//  as a background image)
function addLoadingAnimation(srcElement)
{

// if element is a textfield, then show the loading image in the textfield itself.
if (srcElement.tagName == "INPUT" && srcElement.type == "text")
{      
   srcElement.style.backgroundImage = 'url(images/loading.gif)';
   srcElement.style.backgroundRepeat = "no-repeat";
   srcElement.style.backgroundPosition = "right";
}

// else the element looks better with the loading image to the right of the element
else
{
   // only add the animation if it isn't there already
   // i.e. user click link twice in a row
   if (srcElement.nextSibling == null
       ||
       (
           !srcElement.nextSibling.innerHTML
          || (
             srcElement.nextSibling.innerHTML
             && srcElement.nextSibling.innerHTML.indexOf("otherLoadingImage") == -1)
             )
      )
   {
      var anim = document.createElement("span");
      anim.innerHTML = '<IMG ID="otherLoadingImage" BORDER="0" SRC="images/loading.gif">';
      srcElement.parentNode.insertBefore(anim, srcElement.nextSibling);
   }
}
}