Monday, October 26, 2009

INSERT's inside transactions

Are you a geek? Then read on. :-)

Reading Inside SQL Server 2005: Query Tunning and Optimization, I stumbled upon this beaultiful code sample. The guy (yes, a guy - it seems Ms. Delaney is subcontracting for some chapters...) creates a big sample table with 250,000 rows on it.


And there's not a single shred of comment about the code. He's doing 250,000 INSERT's on a table. In order to optimize it, he opens a transaction right before the loop starts, and on every 1,000 INSERT's he does a COMMIT then opens another transaction. This prevents SQL Server from opening a transaction on each INSERT. (Yes, Luke. When a data modification command to SQL Server you send, a transaction it automatically opens if the command is not already inside one. This allows you, for example, to change your mind and issue a ROLLBACK inside a trigger - even without a preceding BEGIN TRAN).

By using the explicit transactions around the INSERT's, the code runs them on 250 transactions, instead of 250 thousand ones. As I've said before, beaultiful. Simply beaultiful.

And in case you didn't notice: yes, I'm a geek. :-)

Thursday, October 22, 2009

SQL Server Compact is not intended for ASP.NET development

SQL Server Compact is a very restricted SQL Server edition in terms of features. But it runs in-process, wich means it does not run as a service being accessed from your application, like others SQL Sever editions. It runs inside your app. This allows for simplified deploy (non-administrative accounts can install an application using SQL Server CE databases), and reduced RAM, CPU and disk usage. It runs even on Windows Mobile!

Well, if you try to use it on an ASP.NET site, you end up with the error "SQL Server Compact is not intended for ASP.NET development". The reason is, like the message says, SQL Server CE is not made to be used on a multi-user environment like a web aplication. But if you want to use it anyway, for exampe for a quick test, place the following code on Global.asax:


Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", True)
End Sub

This way you are "taking full responsability" for using SQL Server CE on the ASP.NET site, and the error won't show up again.

ASP.NET: Framework 3.5 is not showing up on IIS

When configuring an ASP.NET site on IIS Manager, on the site (or virtual directory) property pages, there is a ASP.NET configuration section. But in the "ASP.NET version" list, version 3.5 (or 3.51, or 3.51 SP1) does not show up:

According to a post by Vijayshinva Karnure from Microsoft, this is the expected behavior. 3.x .NET Framework versions are not "complete" frameworks, like 1.0, 1.1 and 2.0 versions. They build up on version 2.0 with new controls, new classes to FCL, but the "infrastructure" used is from version 2.0. So, if you are deploying an ASP.NET site built on Visual Studio 2008, select version 2.0 on ASP.NET version list at IIS Manager. If .NET Framwork 3.5 is installed on the deployment machine, the site configuration is correct.

Monday, October 19, 2009

AdventureWorks: just the MDF, please!!!

I find the K.I.S.S principle kind of offensive, but sometimes people ask for it. There's a bunch of guys developing a whole project on Codeplex to create a setup program for the AdventureWorks family of SQL Server sample databases. I wonder, is it so complicated to install a few databases? After all, people who will use it are very likely to be dba's or developers, and they certainly have some knowledge on SQL Server administration, or can understand some half-page instructions on how to restore a database backup or how to attach a database file.

Well, I needed the database. Downloaded the MSI for SQL Server 2008, and ran it. On setup things started to go south: setup asks for the instance on wich to install the databases, and presented me a list with the instances on my machine. I had only a default SQL Server Express instance. I selected it, but the "Next" button kept grayed, no matter what.

"Ok". Cancel setup. Go to Google and asks for "SQL Server Express AdventureWorks". There's a bunch of posts with some tortuous ways of doing it. One said, "run script instblablabla.sql installed with the MSI from Management Studio". Loaded the script - 190Kb (!). It creates the empty database structures and loads data from several CSV files that comes with the MSI too. Nope. Few errors, some restriction on having to enable FILESTREAM on SQL Server Express - unfortunatelly, my SQL Server Express installation is a x86 on top of Vista x64, and after some more Googling, I learned that this FILESTREAM stuff does not work on this scenario. But WHY do I need it??? Seems it is used to load data from the CSVs into the tables. [Sigh]. Think "complicated". Then double it.

Finally I lost my patience and downloaded the SQL Server *2005* version of AdventureWorks - from the Codeplex site itself. Extracted the MSI content and there they are, sitting beautifully on some subdir, the MDF and LDF files to the database. Attached them to my SQL Server Express that, inspite of being a 2008 installation, reads the 2005 MDF files without problems. Quick. Simple.

The MSI with the database files can be downloaded from http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004. Select the "AdventureWorksDB.msi" option.

To extract the MSI content, run the following command from a command prompt:
msiexec -a FileName.MSI -qb TARGETDIR=extract_location_full_path
To attach a MDF file to SQL Server Express, open Management Studio and, on Object Browser, right click on "Databases" and select "Attach..."

Is it really necessary a setup project for that???

Sunday, October 11, 2009

Global error handling in ASP.NET and Windows Forms

You should not let exceptions unhandled in *any* part of your application. But that doesn't mean you have to spray your entire code with a swarm of try/catch blocks. Both Windows Forms and ASP.NET applications offer the possibility to create a global error handler, that will be activated whenever an unhandled exception (i.e., an exception that has not happened inside a try/catch block) occurs.

Windows Forms Global Exception Handler

As always, things are easier in Windows Forms than in ASP.NET. Windows Forms provides the developer with the Application object, that among other things has the ThreadException event. Basically, all you have to do is to add an even handler to the ThreadException event, and your global event handler is set.

Create a new VB.NET Windows Forms project, add a button to the Form1 form, and replace its code with the following:


Imports System.Diagnostics

Public Class Form1

Const MYAPP_SOURCE_NAME As String = "My Application"

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Creates the event source on Windows Application Log
' This has to be done only once, usually during app setup
If Not EventLog.SourceExists(MYAPP_SOURCE_NAME) Then
EventLog.CreateEventSource(MYAPP_SOURCE_NAME, "Application")
End If
' Designates the global error handler
AddHandler Application.ThreadException, AddressOf Me.TheGlobalErrorHandler
End Sub

Sub TheGlobalErrorHandler(ByVal sender As Object, ByVal e As Threading.ThreadExceptionEventArgs)
' This is how we get the exception data
Dim unhandledException As Exception = e.Exception
' Writes technical error information to the Application Log
EventLog.WriteEntry(MYAPP_SOURCE_NAME, _
"Unhandled error: " + vbNewLine + unhandledException.ToString(), _
EventLogEntryType.Error)
' Some nice message to the user
MsgBox("An unexpected error has occured. Technical error information " & _
"has been writen to the Windows Application Log.", _
MsgBoxStyle.Critical)
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' Simulates an unhandled exception
Throw New Exception("Oh oh error accessing the database")
End Sub
End Class



Click on the button to simulate an unhadled exception. A message is displayed and if you look at the Windows Application Log on EventViewer (Start > Run > eventvwr.msc), there it is the exception.

ASP.NET Global Exception Handler

ASP.NET provides you with the Application_Error global application event, that is fired whenever an unhandled exception occurs. This event handler belongs to the global application class, as Visual Studio calls it. The global application class code is located on the Global.asax file. ASP.NET applications, by default, don't have a Global.asax file. You can add one by going to the Project menu and selecting Add New Item > Global Application Class.

On the Application_Error event handler code, you have to use two special methods:
  • Server.GetLastError() gives access to the unhandled exception.
  • Server.ClearError() prevents ASP.NET from showing the default error page.
Create a new ASP.NET Web Application, add a Global.asax file to it, and replace the code in Global.asax.vb with the following:

Imports System.Web.SessionState
Imports System.Diagnostics

Public Class Global_asax
Inherits System.Web.HttpApplication

Const MYAPP_SOURCE_NAME As String = "My Application"

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
' Creates the event source on Windows Application Log
' This has to be done only once, usually during app setup
If Not EventLog.SourceExists(MYAPP_SOURCE_NAME) Then
EventLog.CreateEventSource(MYAPP_SOURCE_NAME, "Application")
End If
End Sub

Sub Application_Error(ByVal sender As Object, ByVal e As EventArgs)
' Stores the unhandled exception
Dim unhandledException As Exception = Server.GetLastError()
' Says to the Framework "Don't show default error page - we will handle it"
Server.ClearError()
' Writes techinical error information to the Application Log
EventLog.WriteEntry(MYAPP_SOURCE_NAME, _
"Unhandled error: " + vbNewLine + unhandledException.ToString(), _
EventLogEntryType.Error)
' Redirects user to some page with nice error message
Response.Redirect("/error.aspx")
End Sub

End Class


Create a page named "error.aspx"; that's the page users will be redirected to after an unhandled exception.

Finnaly, add a Button to Default.aspx, and replace its code with the following:

Partial Public Class _Default
Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
' Simulates an unhandled exception
Throw New Exception("Oh oh that database access error again")
End Sub
End Class


Click on the button and you will be redirected to error.aspx, and an event will be logged on the Application Log with the exception data.



UPDATE: the Application_Start code will fail on Windows Vista & Windows 7, because of tightened security. The code tries to create an event source in order to log events to it. The methods EventLog.SourceExists() and EventLog.Create() both try to scan the Windows Logs to check if the specified source exists, but access to the Security Log by ASP.NET apps is not allowed by default. There are several ways to "do it right", but a quick and dirty solution is to create the following registry key: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\eventlog\Application\YOUR_APP_NAME, changing YOUR_APP_NAME to the event log source you want to create. A possible way to "do it right" is to create a command line app containing the Application_Start code, and set it to run from your site's setup package.

Thursday, October 8, 2009

SQL Server Compact is not intended for ASP.NET development

The exception "SQL Server Compact is not intended for ASP.NET development" is thrown when you try to use a SQL Server Compact Edition Database on an ASP.NET web site. That's because, as the message says, SQL Server CE was not made to be used on ASP.NET, because of its several restrictions relating "normal" SQL Server editions. If you cannot install a Standart or Developer Edition SQL Server on your development machine, try using Express Edition. It has everything a developer needs and it's free.

If you really want to use Compact Edition, add the following to your Global.asax file and the exception will go away:

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", True)
End Sub

Sunday, October 4, 2009

Virtual Server: VM's not starting

Virtual Server has a Administration Website, from where you start VM's. It seems that the site does not work on IE8 or FireFox 3 for some operations, starting VM's being one of them (another: changing virtual hard disk type from IDE to SCSI). The IE status bar showed "Error on page", just like when you load a page with JavaScript errors on it. Well, I'm not sure what was going on, but if you select the Compatibility View mode on IE, the page runs ok.


Friday, October 2, 2009

400 Bad Request accessing Azure Blob Storage

This one was kind of dumb. I'm posting it in hope someone doesn't loose as much time as I've lost. When I was trying to retrive a Blob Container object from Azure Blog Storage, I kept getting a "400 Bad Request" exception. After 3 hours I gave up and asked a friend to look at my code. In 3 minutes he spot the bug: I was using the Table Storage credentials on the acces to the Blob Storage.

Wrong code:
BlobStorage blobs = BlobStorage.Create(
StorageAccountInfo.GetDefaultTableStorageAccountFromConfiguration());
Right code:
BlobStorage blobs = BlobStorage.Create(
StorageAccountInfo.GetDefaultBlobStorageAccountFromConfiguration());
The StorageAccountInfo class has 3 methods to retrieve default credentials: GetDefaultTableStorageAccountFromConfiguration(), GetDefaultBlobStorageAccountFromConfiguration(), and GetDefaultQueueStorageAccountFromConfiguration(). One for each storage Azure has. Big names with little differences between them...