Corey Roth [MVP]

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.

WITH MyCTE1 AS
(SELECT Column1, Id FROM Table1);

WITH MyCTE2 AS
(SELECT Column2, Id FROM Table2)

SELECT Colun1, Column2 FROM MyCTE1 
INNER JOIN MyCTE2
    ON MyCTE1.Id = MyCTE2.Id

That flat out will not work. Instead group multiple CTEs together using a comma and omitting subsequent WITH statements.

WITH MyCTE1 AS
(SELECT Column1, Id FROM Table1),
MyCTE2 AS
(SELECT Column2, Id FROM Table2)

SELECT Colun1, Column2 FROM MyCTE1 
INNER JOIN MyCTE2
    ON MyCTE1.Id = MyCTE2.Id

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

Or maybe you need to update some table...

UPDATE MyOtherTable 
SET MyOtherTable.ColumnAverage = MyCTE.ColumnAverage
FROM MyOtherTable 
INNER JOIN MyCTE
    ON MyOtherTable.ColumnAverage = MyCTE.ColumnAverage

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.

ajax.asp.Net

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.

If you are using Windows Server 2008, use this command instead.

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.

Virtual PC 2007 Beta

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.

ReportParameter myReportParameter = new ReportParameter("Id",
Request.QueryString["Id"]);

Next, parameters on the ReportViewer are set using the SetParameters method. It takes an array of ReportParameter, so you would have syntax like the following.

MyReportViewer.ServerReport.SetParameters(
new ReportParmeter[] { myReportParameter });

That's all there is to it. Typically you would want to set that in the same method as you set credentials (i.e.: OnLoad).

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.

using System;
using System.Data;
using System.Configuration;
using System.Net;
using System.Security.Principal;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using Microsoft.Reporting.WebForms;

/// 
/// Summary description for CustomReportCredentials
/// 
public class CustomReportCredentials : 
Microsoft.Reporting.WebForms.IReportServerCredentials
{

    // local variable for network credential.
    private string _UserName;
    private string _PassWord;
    private string _DomainName;
    public CustomReportCredentials(string UserName, string PassWord, 
string DomainName)
    {
        _UserName = UserName;
        _PassWord = PassWord;
        _DomainName = DomainName;
    }
    public WindowsIdentity ImpersonationUser
    {
        get
        {
            return null;  // not use ImpersonationUser
        }
    }
    public ICredentials NetworkCredentials
    {
        get
        {

            // use NetworkCredentials
            return new NetworkCredential(_UserName, 
_PassWord, _DomainName);
        }
    }
    public bool GetFormsCredentials(out Cookie authCookie, 
out string user, out string password, out string authority)
    {

        // not use FormsCredentials unless you have 
implements a custom autentication.
        authCookie = null;
        user = password = authority = null;
        return false;
    }
}

Now that you have your custom credentials class, you need to pass the credentials to your ReportViewer control. The code below passes a username, password, and domain the reporting server.

EventReportViewer.ServerReport.ReportServerCredentials
= new CustomReportCredentials("MyUserName", "MyPassword", "MyDomain");

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.

For example, to format a date, do something like this:

<%# Bind("MyDateColumn", "{0:d}") %>

Special thanks to Bobby for the contribution.

If you have an MSDN subscription, you can download RC2 of Windows Vista. Currently, it looks like only the x86 version is available, but hopefully x64 will show up soon.

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.

Microsoft Report Viewer Redistributable

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

string[] fieldArray = {"filename"};

// get u1a directory location from webconfig

string directoryLocation = ConfigurationManager.AppSettings["U1AStorageDirectoryLocation"];

// create a Sb object and append the strings needed
StringBuilder formattedString = new StringBuilder();
formattedString.Append(directoryLocation);
formattedString.Append("/{0}");

// create a HyperLink Column and add it to the GridView dynamically
HyperLinkField showFileColumn = new HyperLinkField();
showFileColumn.DataNavigateUrlFields = fieldArray;
showFileColumn.DataNavigateUrlFormatString = formattedString.ToString();
showFileColumn.Text = "Show File";
GridView1.Columns.Add(showFileColumn);

Kyle Kelin

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.

CS0030: Cannot convert type 'ASP.login_aspx' to 'System.Web.UI.WebControls.Login'

Source Error:

Line 118: public login_aspx() {
Line 119:  string[] dependencies;
Line 120:   ((Login)(this)).AppRelativeVirtualPath 
= "~/login.aspx";
Line 121: if ((global::ASP.login_aspx.@__initialized 
== false)) {
Line 122: dependencies = new string[2];
 

An error similar to the above is what you will always get. Rename the class inside your page or whatever to get around this annoying issue.