# Wednesday, July 16, 2003

So many ways, so little time


Currently working on my VSLive all day .NET Data Access: Soup to Nuts workshop.  Andrew Brust and I will be presenting this on July 31st at the VSLive Conference in New York.


It got me thinking. With .NET, Microsoft gives you so many ways to work with transactions.  You can:

  • Program against DTC yourself (and you have to be crazy to do this)
  • Use Enterprise Services
  • Use ADO .NET Transactions
  • Use TSQL Transactions


So many transactions, so little time. Well you would never want to use DTC. Period, there is just way too much pluming to deal with. Working with Enterprise Services and creating Serviced Components is quite compelling. There is a fair amount of work involved, so it is only really good when you need a two-phased commit across multiple data sources.


Then there is ADO .NET. Not sure why you would ever want to do this either. The SQLClient transaction object is worthwhile, but you would be better of (even if you want to deal with isolation levels) doing your transaction in TSQL. Maybe I am biased against lots of logical code in ADO .NET and rather place the transaction processing inside the TSQL for performance and maintenance reasons. I think that lots of transaction code in ADO .NET leads to very fat code that is also very verbose for no reason. Your middle tier should be thin and fast. Your database is better equipped to handle the transaction. Since ADO .NET limits to one connection, the two are almost the same thing. Now there is always an exception. There are times when ADO .NET is appropriate, I am just searching for it. :)

posted on Wednesday, July 16, 2003 3:56:07 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [6] Trackback
# Tuesday, July 15, 2003

Keep the Cars Out...


I used to think that cars and athletes could share Central Park. Over the last few weeks, topped off by an incident this morning, I have changed my mind.


First some background. I live on W. 57th Street a block off Central Park South- the 6th Avenue entrance. I have lived there for like 7 or 8 years and use the park all the time for biking, jogging and skating. There has been an ongoing campaign to keep the cars out of the park. I have never signed up for this campaign since I always figured that it would add more traffic to the already overcrowded streets of Manhattan.


This triathlon season, I moved started biking and running in the park much more than on the course on the West Side Bike Path I used last year. So I basically am exercising in the park 6 days a week. I don’t see enough cars! The amount of cars on Park Drive is pretty light, and I exercise at different times each day, sometimes a run or bike ride in the early morning, sometimes an afternoon rollerblade, sometimes a bike ride in the evenings. At all times of day, and at times considered to be “rush hour” I don’t see enough vehicular traffic in the park to warrant allowing traffic in the park. On the other hand there are just massive amounts of people exercising, even on cold and rainy days.


So my solution? Start charging the cars to use Park Drive. Make it a toll road. To avoid backups, just require an EZ-Pass for entry. Mayor Mike, are you reading this?

posted on Tuesday, July 15, 2003 11:32:13 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [4] Trackback
# Monday, July 14, 2003

Efficient Data Retrieval?

Is that a good title for an MSDN WebCast? Who knows? But then again, when have I ever did anything normal?

For those of you who did not see this popular session in Dallas at TechEd or can't make it on out to Kuala Lumpur, Malaysia for TechEd in late August, you can catch this session Thursday at 2pm EST via an MSDN WebCast.

When Microsoft asked me to put together this session back in February for TechEd Dallas, I had no idea how to approach it. Target the DBA, TSQL Programmer, or VB/C# programmer? I am not a DBA, not even close. I also don’t think that I am a full time “TSQL Programmer”, even though I spend hours each day writing TSQL. I use to be a full time VB/C# developer, but now as “CTO” of my company, I spend more time architecting and managing than coding, but I still do write a fair amount of C# code each day. (Epically when my developer calls in sick and we have deadlines!)

So I took a radical approach (or what I call the Bill Clinton approach) and tried to be all things to all people. Most small firms in this economic downturn don’t have a full time DBA, SQL Programmer and VB/C# developers on staff. Some times the DBA is the TSQL Programmer and sometimes the VB/C# developer is the TSQL Developer. When I polled the over 700 people who attended this session at TechED, only a handful of the crowd was only one of these jobs, a vast number were all three. This is bad, I am a firm believer in splitting the role of DBA and TSQL programmer as full time positions. But I understand the economic times so this session was born.

So the WebCast? We start with some TSQL code to make data retrieval from SQL Server easier and smarter. (We will talk about the obscure but totally powerful The Rozenshtein Method that my pal Richard Campbell showed me and like being unplugged from the Matrix for the first time I had an epiphany about my relationship with TSQL). After we talk about TSQL we will hit some classic DBA issues, l mostly about indexes and file groups. Then we will move on into taking advantage of this stuff from ADO .NET, all the tips and tricks that the VB/C# developer will enjoy. So there is something for everyone, hope to see you there. You can sign up for it here.

Here is the abstract:

Take a look at how to optimize using Stored Procedures for efficient and secure data retrieval in the middle tier of your Web, Windows, mobile and Web services applications. Learn how to do crosstab queries for reporting that take seconds to execute instead of hours, exploiting sub-queries and taking advantage of self-joining. Explore performance tuning from the perspective of stored procedures used for data retrieval. Look at how to get a higher cache-hit ratio, efficient index creation and utilization and how to guarantee that ADO.NET takes advantage of these optimizations. Focus on squeezing the last bit of performance out of ADO.NET. Look at how to best architect your application to take advantage how ADO.NET was designed to work with data in a disconnected. Look at comparisons between the DataSet and the DataReader, and an explanation of the best use for each of these objects. Look at techniques for using multiple result sets in one DataReader or DataSet for efficient client databinding, using stored procedures for dynamic sorting, proper connection pooling, optimizing connections with the DataAdapter, and ExecuteScalar vs. ExecuteNonQuery comparisons.  

posted on Monday, July 14, 2003 11:53:45 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [15] Trackback
# Sunday, July 13, 2003

The USS Ronald Reagan (CNV 76)

On Saturday I watched the with some friends the commissioning of the USS Ronald Reagan. The USS RONALD REAGAN was built by Northrop Grumman Newport News in Newport News, Virginia, the only company in the United States with the unique ability to build nuclear powered aircraft carriers. RONALD REAGAN is the ninth Nimitz class aircraft carrier. Being nuclear powered, it can operate for more than 20 years without refueling.

I did feel old when my friend's daughter asked us: "Who was Ronald Reagan?"

posted on Sunday, July 13, 2003 8:30:43 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Friday, July 11, 2003

Who Needs No Stinkin’ Transaction Log (Well Sometimes)


I have a problem. I have to transform 33 million rows from one table to another in my staging SQL Server. The problem is that my Insert Into statement is getting logged and it takes over 10 hours to run due to File I/O. I want it to run faster since the data is not 100% ready and I will have to make some changes to the raw data then retransform in. I called my teammate and pal Andy Catlin (who claims that 1.1 million people read this blog daily) for some advice on how to have the insert statement without getting logged to the transaction log. So here is my insert statement that takes 10+ hours:


Insert into dbo.RPT_ADNPMP12

Select CCODE as County_ID, SICGP as SIC_Code, Rpt_Year, Rpt_Quarter, SpendOrig, SpendDest, 10 as MediaType_ID, 'Other Publications' as MediaType, 11 as Sort_Order,

0 as PercentOfTotal,(ONLNAT+ONLDIR) + ONLLOC  as TotalSpending_AMT,

ONLLOC as Local_AMT,  ONLNAT+ONLDIR  as National_AMT

From dbo.PreptoUpload


Andy said to use with caution the following, setting the recovery mode (using alter database) before and after the insert statement. Not logging made the 33 million rows run in less than 2 hours from the 10 it took with logging. Here is the same code not logged:


Alter Database TestStageToProduction SET Recovery Simple


Insert into dbo.RPT_ADNPMP12

Select CCODE as County_ID, SICGP as SIC_Code, Rpt_Year, Rpt_Quarter, SpendOrig, SpendDest, 10 as MediaType_ID, 'Other Publications' as MediaType, 11 as Sort_Order,

0 as PercentOfTotal,(ONLNAT+ONLDIR) + ONLLOC  as TotalSpending_AMT,

ONLLOC as Local_AMT,  ONLNAT+ONLDIR  as National_AMT

From dbo.PreptoUpload


Alter Database TestStageToProduction SET Recovery Full


So my advice to you is to use this with CAUTION, the transaction log is a GOOD THING, but in a rare case like this when we are just transforming data from one table to another life is good without the log.

posted on Friday, July 11, 2003 6:30:04 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [18] Trackback
# Thursday, July 10, 2003

Outlook Auto Complete is Evil

I am sure that most of you have a problem with having way too many email addresses and more importantly a lot of old email addresses. Or have a hotmail account for MSN IMs and a real account that you actually read.

Well, if you ever reply to an email via Outlook, Outlook will save that email address in its cache and when you type in a name in the TO line in the future, Outlook will auto complete for you to that email address. A feature? Well what if you also sometimes, rarely, send email from an account that you don't want people to send to you. Meaning I run a list server and it comes from my hotmail account. Two friends started to send mail all the time to my hotmail account and it would go to my junk folder (but herbal Viagra and porn came right in to my inbox). I begged and pleaded with them and they did not know what to do.

Well the Dutch to the rescue. My pal Remi Caron steered me to a KB article (http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b287623) that will show you how to clear this cache. The steps below are how to completely clear this cache. I can not figure out how to edit this cache at this point since I can't understand the file format when I open it in UltraEdit32.

Use the steps in the following section to reset the Outlook 2002 nickname cache for both Microsoft Windows 2000 and Microsoft Windows Millennium Edition (Me).

  1. Quit Outlook.
  2. Start Windows Explorer.
  3. On the Tools menu, click Folder Options, and then click the View tab.
  4. Under Advanced Settings, click to select the Show hidden files and folders check box.
  5. Click OK.
  6. Click Start, point to Search, and then click For Files or Folders.
  7. In the Search for Files or Folders box, type *.NK2 in the File Name box.
  8. In the Look In box, click to select your local hard disk.
  9. Click Search Now.
  10. Right-click the .NK2 file with the name of the profile that you want to reset, and then click Rename.
  11. Rename the file to profilename.bak, and then press ENTER.
  12. Quit Windows Explorer.
  13. Restart Outlook.

Outlook will generate a new nickname cache.



posted on Thursday, July 10, 2003 10:58:25 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [5] Trackback
# Wednesday, July 9, 2003

WS-Federation Security Specification

IBM and Microsoft (companies that are not known to be friends) released yesterday the WS-Federation Security Specification. It has not been released to the W3C for specification (but most likely will), mostly because the W3C is moving too slow and IBM/BEA/MS want the business world to start using this new standard. WS-Federation also follows IBM & Microsoft’s by-now familiar way of delivering WS- specifications with partners, signing-up ISVs with expertise in specific markets. Verisign and BEA put their names to WS-Security and Business Process Execution Language for Web Services (BPEL4WS) for example.

I really like the approach of Microsoft and IBM, standards bodies are not moving fast enough and we need this technology now. WS-Federation will really allow users of future Services to interoperate much easier with better security-by federating the user’s identity across several Web Services. This is a good spec. The problem is that since the W3C is sitting on its hands, there is competition in the standards space. That competition comes from the obstructionist company called SUN Microsystems.

SUN Microsystems cried foul today to the release WS-Federation security specification. Will this near bankrupt obstructionist company just stop acting all bitter and spiteful and kill off the competing Liberty Alliance specification for once and for all and join with IBM, BEA and Microsoft with the WS enhancements. Jeeze, if IBM and Microsoft can agree, why can't SUN bury the hatchet for the good of the industry. Liberty has the smell of CORBA to me (remember that specification that nobody understood or used).

I think that Liberty is dead in the water.

Some background:

"There's got to be some overlap there," said Britta Glade, vice chairwoman of the Liberty Alliance Project's business and marketing expert group. "It focuses on federated identity. That's what we've been focusing on for two years."

Sun--whose two biggest rivals are Microsoft and IBM--launched Liberty at the behest of Visa International, but the effort now is controlled by many companies. There is no unwillingness to incorporate others' technology in Liberty, Glade said, adding that the group incorporated some of the work by an earlier Microsoft-IBM Web services effort, WS-Security.

posted on Wednesday, July 9, 2003 11:04:29 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [11] Trackback
# Tuesday, July 8, 2003

Another RD returns to the Mother Ship


Steve Lasker, the Microsoft Regional Director for Dallas, TX has joined the collective and is going to work for Microsoft as the Program Manager for the VB .NET team. He has started a few days ago and so far he is happy (but Seattle has not gotten any rain yet!). Good luck Steve!


After Chris Sells and other RDs moved to the evil empire this year, more and more good people are making the move to Microsoft.


This raises the question, will I ever work for Microsoft? Not anytime soon.

posted on Tuesday, July 8, 2003 4:04:16 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [21] Trackback
# Monday, July 7, 2003

The Future of Microsoft Access


The most difficult session I had to do at TechED in Barcelona was DAT231: Migrating Your Mission Critical Access Applications to SQL Server. The reason why it was hard was I had to convince a roomful of Access developers about the merits of upsizing to SQL Server and that since the two products architectures are so different, upsizing your application is actually going to be a rewrite in .NET (Windows or ASP .NET) or COM/ASP. The crowd did not boo me off the stage and I actually got pretty good scores and comments-it was a very highly ranked session on the Data Management Track. With some distance in Paris and time to reflect on long plane journeys, I started to think about the future of Microsoft Access.


It use to be that an Access developer can make the jump from Access VBA development to VB/COM development with not that much effort. That migration path does not exist anymore. Access and VB/COM had lots of things in common, including the language VBA (yes VB 6 developers, VBA is the language behind the Product you know as Visual Basic 6.0.) But .NET is just way too different.


Not sure what has to happen, maybe an Access .NET?? Who knows.

posted on Monday, July 7, 2003 6:04:47 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [14] Trackback