Roll on ASP.4

Scott Guthrie has started creating posts about the upcoming features of VS 2010 and .NET 4.

I've never been a Microsoft fanboy, but you have to give it to them, they really seem to have listened to their users the last few years as they continue to change all the issues people have griped about. The first two posts indicate more good things to come - the web.config files have been cleaned up (at last!) and now it appears there are some great project templates coming. What I am really excited about though is the complete control of client IDs that's coming - the whole UniqueID/ClientID pain in the ass has always bothered me about ASP.NET, and it looks like that has finally been addressed in .NET 4. Flubba dubba.

SQL Server: Currently Executing Queries

I found this great article by Ian Stirk on SQLServerCentral.com, regarding finding queries that are currently executing on SQL Server. The article contains the following VERY useful query:

    -- Do not lock anything, and do not get held up by any locks.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    -- What SQL Statements Are Currently Running?
    SELECT [Spid] = session_Id
 , ecid
 , [Database] = DB_NAME(sp.dbid)
 , [User] = nt_username
 , [Status] = er.status
 , [Wait] = wait_type
 , [Individual Query] = SUBSTRING (qt.text, 
             er.statement_start_offset/2,
 (CASE WHEN er.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
  ELSE er.statement_end_offset END - 
                                er.statement_start_offset)/2)
 ,[Parent Query] = qt.text
 , Program = program_name
 , Hostname
 , nt_domain
 , start_time
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50              -- Ignore system spids.
    AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
    ORDER BY 1, 2

SQL Server: Changing Object Schema

I'd forgotten the syntax for moving an object to a different schema:

alter schema NewSchema transfer OldSchema.ObjectName

SQL Server: Finding and Killing Database Connections

This is a query I use often to check what active connections exist for a specified database:

select  spid, sp.cmd, sp.hostname, sp.loginame, sp.nt_domain, sp.nt_username, sp.program_name
from    master.dbo.sysprocesses sp
where   db_name(dbid) = 'mydatabase'
and DBID <> 0  
and spid <> @@spid 

If I want to take the database offline I'll then kill these processes using the spid.

I also found a great post today that had a nice way of killing ALL active connections, by running the following sql:

alter database dbName set single_user with rollback immediate  

This can then be reverted out with

alter database dbName set multi_user with rollback immediate 

Backing up and Restoring SQL Server Databases with .NET

I need to create a tool that would do some backing up and restoring of databases as part of a long-running job this week. I had heard it was fairly simple C# code, but I was pleasantly surprised when I realised just HOW simple it is.

The namespaces of the SMO libraries required changed between 2005 and 2008, so if you're using the SQL Server 2008 objects, you need to reference the following libraries (usually located in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\). If you're using SQL Server 2005 you only need the first two.

Microsoft.SqlServer.ConnectionInfo Microsoft.SqlServer.Smo Microsoft.SqlServer.SmoExtended Microsoft.SqlServer.Management.Sdk.Sfc

Backing Up Code

SqlConnection conn = new SqlConnection("ConnectionString!");
Server dbServer = new Server(new ServerConnection(conn));
Backup backupMgr = new Backup();
backupMgr.Devices.AddDevice("E:\Backups\YourFile.bak", DeviceType.File);
backupMgr.Database = conn.Database;
backupMgr.Action = BackupActionType.Database;
backupMgr.SqlBackup(dbServer);

Restoring Code

SqlConnection conn = new SqlConnection("ConnectionString!");
Server dbServer = new Server(new ServerConnection(conn));
Restore restoreMgr = new Restore();
restoreMgr.Devices.AddDevice("E:\Backup\MyFile.bak", DeviceType.File);
restoreMgr.Database = conn.Database;
restoreMgr.Action = RestoreActionType.Database;
restoreMgr.SqlRestore(dbServer);

Building Code Documentation with SandCastle

I've always used nDoc for building code documentation, and then (around 2 years ago) I shifted to Sandcastle Help File Builder. We're wanting to start documenting code at work, so today I downloaded the latest version (along with Sandcastle to give the latest versions a whirl.

I was presently surprised. The last version of SHFB was just an nDoc clone, but now it's moved on a lot. It's got a whole pile of nice features now, including caching of all the stuff that used to make a build really slow; HTMLHelp 2; and more.

The best addition though is the support for static content. I actually couldn't figure out how to do it at first - in nDoc there used to be an AdditionalContent option (or something to that affect), and I knew the latest version of SHFB had support for this but I couldn't find an option for it. Google returned pre-2006 results which were incorrect. It was staring me in the face all along though - the newest version works like a VS solution - all you need to do is add the files to your solution and they get automatically incorporated. Very nice.

Overall, I'm very impressed - it's literally hundreds of times faster than the previous verison I used (if you enable the custom build components that come built-in) and it creates a far slicker output file.

Update: Error Using MSBuild

The latest version of SHFB no longer has a console application included - you need to use MSBuild instead. However, when trying to hook up my project to MSBuild as per the documentation, I kept getting the following error:

error MSB4057: The target "Build" does not exist in the project.

I think there's a bug in SHFB where it isn't building the project file correctly. Adding the following line at the bottom of my .shfbproj file sorted the issue out:

  <Import Project="$(SHFBROOT)\SandcastleHelpFileBuilder.targets" />

(You can add this directly above the closing </Project> tag.