Handling Errors in ASP.Net AJAX Requests

In my previous post I wrote about adding an exception handler in global.asax and adjusting web.config to display user-friendly error messages in ASP.Net applications.

While the strategy works fine, exceptions raised during an AJAX request are not handled by that solution. As I found out, you still get a Server Error page, for example if the SortExpression in a GridView within an UpdatePanel references an invalid column name.

To catch Ajax-generated exceptions, you need to add a AsyncPostBackError to the ToolkitScriptManager of your page or masterpage:

protected void ScriptManager1_AsyncPostBackError(object sender,
    AsyncPostBackErrorEventArgs e)
{
    Exception ex = e.Exception;
    Session["Application_Error"] = ex;
    Session["Application_Request"] = Request;
    Response.Redirect("error.aspx");
}

This sample code uses the same Session variables as in the previous post, and explicitly redirects to the same error page.

Scheduling Backups on SQLServer Express

Management Studio Express does not provide a user interface to schedule backup jobs, but you can easily find a couple of solutions on the web, such as here, here, and here.

The backup solution I came up with was one .sql file each for full and incremental backup, and two .cmd files which execute the respective .sql files via sqlcmd.

The full backup SQL file looks like this:

PRINT 'backup.sql ' + CONVERT(VARCHAR, GETDATE(), 120)

DECLARE @backupSetId AS INT
DECLARE @Filename NVARCHAR(256)
DECLARE @Database NVARCHAR(256)
DECLARE @Backup NVARCHAR(256)

SET @Database = N'<name of database>'
SET @Filename = N'E:\Backups\<name of database>.' +
    REPLACE(CONVERT(NVARCHAR, GETDATE(), 102), '.', '-') +
    N'.bak'
SET @Backup = @Database + N' Full Database Backup'

BACKUP DATABASE @Database
TO DISK = @Filename
WITH NOFORMAT, NOINIT,
NAME = @Backup,
SKIP, NOREWIND, NOUNLOAD, STATS = 10

SELECT    @backupSetId = position
FROM    msdb..backupset
WHERE    database_name = @Database
AND        backup_set_id =
    (SELECT MAX(backup_set_id) FROM msdb..backupset
        WHERE database_name=@Database )

IF @backupSetId IS NULL
    PRINT N'Verify failed. Backup information for database ''' +
        @Database + N''' not found.'
ELSE
    RESTORE VERIFYONLY FROM  DISK = @Filename
    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

This piece of code is repeated for every database you want to backup.

The incremental backup file has the WITH DIFFERENTIAL option in the BACKUP command, and excludes verification.

The batch file which executes the backup.sql file simply calls sqlcmd:

sqlcmd -S <your db server name>
    -i <path to>\backup.sql
    -o <path to>\backup.log

Next, use the Scheduled Tasks control panel application to schedule the full backup once a week, and the incremental backups daily: Use a weekly schedule and select the days of week to define when the backups should run.

Taking a Database Offline in Management Studio Express

SQL Server Management Studio Express does not provide a Take Database Offline command in the Object Explorer menu.

To take a database offline, simply type:

alter database my_database_name set offline

To check, which databases are set offline, use the following query:

select name, databaseproperty(name, 'IsOffline') as IsOffline
from sysdatabases

Checking NavigateUrls in ASP.Net

In previous posts, I wrote how you can analyze your ASP.Net application using graspx.

Now, I want to check whether the links in my aspx files point to existing files, i.e. the values of NavigateUrl and DataNavigateUrlFormatString must refer to existing filenames. This is achieved with a little shell magic:

@for /f "delims=?: tokens=1 usebackq" %f in
  (`graspx -count -col 5 l DataNavigateUrlFormatString *.aspx`) do
    @if not exist %f (echo %f does not exist)

The graspx command lists all XHTML elements with a DataNavigateUrlFormatString attribute. The for commands iterates through this list (rather, through the list of attribute values), and outputs a message if the referenced file does not exist. The “delims=?” option splits the URL arguments from the URL filename.

Use graspx with the f (find) parameter to find out which files are referencing the missing file.

The same operation can be used to identify wrong URLs or missing files for NavigateUrl:

@for /f "delims=?: tokens=1 usebackq" %f in
  (`graspx -count -col 5 l NavigateUrl *.aspx`) do
    @if not exist %f (echo %f does not exist)

Note that the NavigateUrl value may include a bind expression (Eval()), which is not handled separately in this example.

graspx is available for download here.

Process Monitoring in SQL Server

Recently I tried to figure out where a SQL operation was spending its time, so I navigated to Current Acticity in Enterprise Manager but only got a timeout message.

I found that the information I needed was stored in the sysprocesses table: SPID, waittype, and the statement causing the wait.

The nice part is that you can literally watch a trigger or a stored procedure being executing by interpreting the values of sql_handle, stmt_start and stmt_end.

The tricky part is how to interpret them. This article on fn_get_sql() was incredibly helpful.

Here’s the strategy:

• Iterate through all SPIDs with a waittype
• Retrieve sql_handle, stmt_start, stmt_end
• SELECT from fn_get_sql() with these parameters (the function returns a recordset)
• Retrieve SUBSTRING of the text column

With a little copy and pasting you can also display the preceding and following statements, which makes it easier to locate the current statement during debugging (line numbers are not part of the result).