Dot Net Mafia

Group site for developer blogs dealing with (usually) Ionic, .NET, SharePoint, Office 365, Mobile Development, and other Microsoft products, as well as some discussion of general programming related concepts.

October 2006 - Posts

The process of using multiple CTEs in a stored procedure is kind of wierd, so I thought I would document the syntax. Say you have a situation where you want to create two CTEs and then join the results, you might think you would try something like the following.

Common Table Expressions (CTE) are a useful new feature that allows you to define a virtual view. They are useful with aggregates as well as the ROW_NUMBER() and RANK() functions. So basically, you can create a temporary view inside a stored procedure, perform some operation with it, and then it goes away automatically. I have used this many times when I need to insert the average of something into a new table. The most common way to use a CTE is with a SELECT statement.

WITH MyCTE
AS (SELECT Id, Column1, AVG(Column2) as ColumnAverage
FROM MyTable WHERE SomeColumn = @SomeValue)

You can use just about any SELECT statement you want although there is some wierdness when you do stuff with groups. Once the CTE is built you can query from it like the following.

SELECT ColumnAverage FROM MyCTE
WHERE SomeOtherColumn = @SomeOtherColumn ORDER BY Id DESC

Prior to SQL Server 2005, the statement like the above would have been a pain in the ass requiring temp tables and what not. Now, it is pretty simple.

A couple of other things to remember with CTEs. First, any preceding statement to the declaration of a CTE must end with a semi-colon (;). Secondly, if not in a stored procedure, the statement querying or using the defined CTE must occur immediately after the definition (i.e.: if you are testing in SQL Server Management Studio).

Beta 1 of ASP.NET AJAX is now available. This is the official name for the product formerly known as Atlas. It is supposed to be a lot more compatible (including working in Safari). They have split up the download into three pieces. First is the core download, secondly is a Value-Add CTP download which contains more of the experiemental *** that isn't fully supported yet. Last is the control toolkit that contains all of the cool extenders.

Have an application pool going crazy with memory or CPU and want to know which application to blame? It's actually pretty easy to determine using a VBS script included in the Windows\System32 folder called iisapp.vbs. To execute the script, just run the following.

cscript iisapp.vbs

This will give you the PID of all of the application pools currently running. You can then, take that PID and look it up in Task Manager.

Virtual PC 2007 Beta is now available from Microsoft Connect. There are a couple of key imporvements. First it supports Windows Vista as both a Host and a Guest. Secondly, it supports ISO images larger than 2.2GB now.

When you build reports they often need paramters passed to them. One common case of using the ReportViewer control is the need to pass a parameter via QueryString. This isn't terribly difficult, but I figure giving a code example, might be useful.

First it is necessary to ccreate a ReportParameter object (be sure you have a reference to Microsoft.Reporting.WebForms). The contructor takes two string parameters one for the Name and one for the Value. A new parameter would look like the code below.

The reason I write this today is that the documentation is completely 100% worng on how to do that in MSDN. It has you set properties that are get only, etc. So here is how to do it since it took me a while to figure out.

So you've built that fancy new report and you want to integrate it into an existing web application, now what? Well you have a couple of options, call the reporting web service or use the ReportViewer control. If you are using the ReportViewer control, it can operate in two modes, local or remote. In local mode, you can access a report rdl file right off the web server and display it. Ideally, though you will use a remote reporting server. By default, this uses the credentials of the current logged in user or the NETWORK SERVICE account if you are not using windows authentication.

If you are not using Windows Authentication, that means you have to pass credentials to the report server programmatically. First, determine the account you want to use and assign that account the Browser role inside Reporting Services. Assign any other folder level permission that are necessary to access the report.

After you have an account set up, you have to add some statements to the code behind of your page in the OnLoad method or wherever appropriate. Before you start be sure and add a using statement for Microsoft.Reporting.WebForms. You may need to add this as a reference to your project first. To set credentials, you access the ServerReport.ReportServerCredentials of the ReportViewer control. The problem is this property only accepts the interface IReportServerCredentials. There is no class out of the box to assign to this. That means you have to create one of your own.

So now you have to create a custom class that implements Microsoft.Reporting.WebForms.IReportServerCredentials. Basically it just manipulates a typical NetworkCredentials class and adds a method. When it comes time to implement it, just copy and paste the class below. You can put it in App_Code, or if you have a class library you can put it wherever in there as long as it references Microsoft.Reporting.

Ideally, you would have those credentials stored some place securely. That sounds like a lot but its really not too bad. Luckily, I did all the hard research to figure it out for you so you don't have to.

If you are using two-way data binding (whether it is a formview, gridview, etc), you may find a place where you need to format a column such as a date. Bind has a second parameter which accepts typical bind expressions.

If you deploy an application that uses the ReportViewer control, you may find that you get an error like the following.

Could not load file or assembly 'Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies.

A quick look at the gac folder (C:\Windows\Assembly) will quickly review that the necessary reporting controls are not present. This is because the controls are installed by VS2005, and not the framework.

To resolve this problem, simply go download the Reporting Services redistributable and install it on your server. This registers the necessary DLLs in the GAC to allow your ReportViewer controls to work.

If you have been running Vista, you may run into a time where you need to use ipconfig to perform some sort of operation such as flushing DNS or getting a new IP address. By default when you run it, you will get an error like the following.

The Requested Operation Requires Elevation.

This is a feature of the new UAC functionality in Vista which I am sure most of you will end up turning off. To perform this functionality, right click on your command prompt icon, and choose Run as Administrator. Note, this is required even if your user account is an administrator account. You'll find many things in Vista will require running in this manner to work correctly (i.e.: debugging web sites with VS2005). Once you run the command prompt with this manner, you can then run ipconfig to do whatever you needed.

The gridview is pretty cool and can save you alot of time, but there are times when you need to add a gridview column dynamically. Say you have a hyperlink column that contains a filepath from your webconfig and you want the url of the hyperlink to use that filepath. One way to do this is in your Page_Load method get your filepath using Configuration Manager and the create the new column while adding to the DataNavigateUrlFormatString property.

// the DataNavigateUrlFields property requires and array even though I just have one item

If you haven't discovered this by now, you will. Never, never, never, name a page or control, Login. It just won't work. Maybe it will work in the IDE, but not a chance in hell when you precompile the site.