64-Bit Database Mail, SQL Server Agent, and Microsoft Support

When I was preparing to do my first install of SQL Server 2005 64-bit I came across this article in Microsoft Help and Support: http://support.microsoft.com/default.aspx/kb/908360. The article states that, "SQL Server Agent is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions..." with a note that, "In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems." Well, after reading the article, I was not sure if SQL Server Agent supported Database Mail without going through the steps in the article or not. Did SP1 fix an issue in the RTM edition or did SP2 cause an issue?

I installed SQL Server and added SP2. I then setup Database Mail and found the bug I note in my earlier posts about The Ghost of Database Mail (1 and 2). I also tested using Database Mail with SQL Server Agent Job Notifications and Alerts and I was happy to find that it does indeed work and Microsoft's Help and Support article should state in it's note that, "SP1 and later adds support for Database Mail with SQL Server Agent". IMHO this would make it clear that Database Mail integration is now supported on the 64-bit platform.

Update on the Ghost of Database Mail

After having posted the problem on SQL Server Central and getting feedback (forum discussion found here). It has been determined that the Reply To phantom is caused by applying .NET Framework 2.0 SP1. I have submitted the problem to Microsoft through Microsoft Connect. Go here if you have experienced it and want to add your voice as well.

As an aside, if you are an IT professional and do not participate in your fields on-line forums you should. You learn a lot and can be a help to others in your profession.

Linq To SQL Requirement

As I was reading some Mike Taulty's blog I came across this link to an MSDN article, http://msdn2.microsoft.com/en-us/library/bb386929.aspx, where I learned that Linq To SQL requires that the Named Pipes protocol to be enabled. Now, I normally have this and TCP enabled on my SQL Servers, but doesn't this contradict Microsoft's practice of reducing the surface area exposed.

Orlando Code Camp, Linq, and the DateTime Datatype

I spent last Saturday at the Orlando Code Camp and had a good time. I went to most of the Linq sessions. Since my co-worker, Rodd Harris, and I are in the process of developing a new application in C# and SQL Server, I felt like I needed to become more familiar with Linq. I really enjoyed Jim Wooley's sessions on Linq to XML and Building Data-Driven Web Sites Using Linq. Check out his web site at ThinqLinq.com, which is built using Linq.

I am mainly a SQL Server DBA/Developer, so I have some concerns about Linq To SQL regarding security, performance, and maintainability. Especially when I hear things like, "With Linq, you'll never have to write a stored procedure again." Well, I would beg to differ with that comment. Stored Procedures help isolate the database from people who think they know SQL and those who know how to use Access or ODBC to get to the data. Call me paranoid, but I think that some paranoia is necessary for a good DBA. I certainly believe Linq is a useful tool to have in your toolbox, but it should be used with thought as every tool should be. Toward the end of the day I had a good discussion about it with Andy Warren from End to End Training. He and I have similar feelings about Linq To SQL, but I think Andy is a little more anti-Linq To SQL than I am.

The coolest thing I learned about Linq was that it gives developers the ability to do heterogeneous joins. So I can have a Linq to Objects query and join on a Linq to SQL query. Now that is cool! For example this code creates an xml document using the new VB support for xml literals and then joins a Linq To XML query to a Linq To Objects query (taken from Jim Wooley's Linq To XML presentation):

       Dim fileTypes = <FileTypes>
                           <FileType extension=".xml" description="Extensible Markup Language"/>
                           <FileType extension=".dtd" description="Data Type Definition"/>
                           <FileType extension=".htm" description="Hypertext Markup Language"/>
                           <FileType extension=".rtf" description="Rich Text Format"/>
                           <FileType extension=".txt" description="Text File"/>
                           <FileType extension=".wpd" description="WordPerfect Document"/>
                       </FileTypes>

       Dim results = _
           From fi In New System.IO.DirectoryInfo("C:\projects\linq\shakespeareXml").GetFiles _
           Join ext In fileTypes. On fi.Extension.ToLower Equals ext.@extension _
           Where fi.LastAccessTime >= Now.AddMonths(-6) _
           Select FileName = fi.Name, Exten = ext.@description _
           Order By FileName Descending

       For Each item In results
           Console.WriteLine(item)
       Next

If you have the opportunity to go to these events and local user groups you should make the effort. It is worth it. You will always pick up something useful and it's a great opportunity to network.

I also found a cool blog post over at SQLSkills.com on why SQL Server's datetime data type starts at 1/1/1753, 1753, datetime and you.

The Ghost of SQL Server 2005 Database Mail

If you didn't know, there is a ghost in SQL Server 2005 Database Mail. The ghost's name is Reply To. I saw the ghost this week as I was setting up database mail on my new SQL Server. I set up a mail profile and then, as I moved on to create an account for the profile. I set up the from, the from name, the smtp server, and there is was, Reply To! I thought to myself, this is a great feature as I don't want any replies going to the server account. I can have them sent to me! So I confidently approached the Reply To ghost and entered my email address. I successfully sent a Test Mail from the server to myself. Now, to test the repy to settings, I hit reply in Outlook! The email was sent and, NOTHING happened! I did not receive the reply, off it went to the From account. I double-checked, then triple-checked my database mail account setup, Yes, everything was set up correctly. I fearlessly checked msdb.dbo.sysmail_account and, yes, replyto_address, was there! Then I checked sp_send_dbmail and there was not a reply to parameter. Now I had found the reason for the ghost. You have nothing to fear from this ghost. Just don't expect it to be helpful.

Hopefully this ghost will be banished from SQL Server 2008

Entering the blogosphere

As I begin blogging, I hope that I can add some useful insights about SQL Server, programming, sports, and real life.

I'll start with sports. I'm a native New Englander and have been a Red Sox, Patriots, Celtics, and Bruins fan all my life and Revolution since their creation. If you eliminate Spygate/CameraGate the last year has been very good. Sox win the World Series, Pats to the Superbowl (so they lost, it happens), Celtics get KG and Allen, Revolution to the MLS Cup, and the Bruins are actually relevant again. Lets hope it continues for a few years. It's nice to root for winners.

SQL Server and programming are what I do for work. I love to work with SQL Server and enjoy contributing to the SQL Server community on SQLServerCentral.com and through attending user groups. Hopefully this blog will allow me to contribute more to the community.

Talk to you later