Simons SQL Blog

I'm a little overwhelmed we've had 83 sessions from 37 different speakers for SQLBits IV.

Its going to make choosing the sessions very difficult. So we need you to vote on the sessions you want to see.

To vote go to http://www.sqlbits.com/information/PublicSessions.aspx

We are streaming 4 of the sessions live for this conference, so even if you are not going in person you could still see them online. So get voting.

I've just finished tearing my hair out, yes I no longer have curly hair I am now bald, over a bug with SQLBits site.

I just uploaded a load of new changes to the main site. We have a beta site to test things on before breaking the main one.

So I expected that the upload to the main site would be fine. It wasn't.

The URL rewriting we were using for the previous events was breaking the site. With error "Cannot use a leading .. to exit above the top directory. ".

I've had that before and the issue was referencing controls by absolute rather than virtual path i.e. "..\controls\mycontrol.ascx" instead of "~\controls\mycontrol.ascx". I checked all these and found a few but none on the pages that were breaking.

It was breaking within ASP.Net and I couldn't find the control that was causing the problem. So I stripped the page down and rebuilt it gradually, then started using a master page again. Eventually I found the issue with a HyperLink control.

I had the following

<asp:HyperLink ID="TelerikLink" runat="server" NavigateUrl="http:\\www.telerik.com" ImageUrl="~\images\sponsors\telerik_square.gif"> </asp:HyperLink>

Commenting it out and everything worked.

So I figured the only relative URL that is in there is the image but I had other images on the master page. But using a seperate image control and so change it to two controls i.e

<asp:HyperLink ID="TelerikLink" runat="server" NavigateUrl="http:\\www.telerik.com">
<asp:Image ID="TelerikImage" runat="server" ImageUrl="~\images\sponsors\telerik_square.gif" ToolTip="Build with Telerik Asp.Net Components" />
</asp:HyperLink>

And heh presto it worked.

So if you are finding you are getting "Cannot use a leading .. to exit above the top directory. ". then check to see if you are using HyperLinks with the ImageUrl.

We hava had an overwhelming response again for sessions for SQLBits.

So far we have a whopping 70 sessions from a staggering 31 speakers.

There is still chance to submit a session. The key to get votes is to make the title sould interesting.

Voting will open on monday so make sure you get you session submitted this weekend.

If you don't want to do 1 hr you can do a 30 minute session, or even just record a nugget. We will be having a competition for the best nugget, just come along to SQLBits with your nugget prepared and we will record it and but it on the site. The winner will win something, probably an XBox or something similar.

To submit a session go to http://www.sqlbits.com/information/SessionSubmission.aspx

Oh and did I tell you, for the first time, we are streaming 4 of the rooms live.

Firstly for those of you not from the UK a chip shop is takeaway where you can get Fish and Chips. A great feature that I miss when abroad, anyway.

So I walk in to the local chip shop tonight just as the person behind the counter rings up a £85 bill on the till for the couple by the counter. Now £85s worth or fish and ships is a lot and this was OAP fish and chips which is cheaper still.

So I'm thinking thats probably 26 fish and chips, oh bugger. I'm going to be hear a long time.

So why is the chip shop like SQL Server well I though I would be waiting until they had served all those portions of fish and chips but no to my suprise, the couple were being served with a MAXDOP of 2. No this isn't the size of the dollup of mayonaise, of the 3 staff behind the counter, only two were involved in serving the couple.

This is great, that meant the 3rd person was left to serve the person in front of me and me. Very much like the scheduler in SQL Server, whats more the staff are very effecient and take requests even when they don't have enough resource (i.e. chips and fish) they queue the requests up and server the requests as the resources become available.

However the capacity planning of the chip shop wasn't that great, firstly they only had didn't have an RAID arrays configured, they only had one chip hopper, from which the staff could scoop up the chips. This meant there was an slight queue as each person got some chips and wrapped them up. now they did have a RAID 0 serving desk with two serving places which worked ok because the bottleneck with the chip hopper meant this wasn't a bottle neck.

They could have had a RAID 0 chip displenser with two hoppers but that would only have exaserpated the last issue which was running out of throughput. The servers where wanting chips from the chip hopper quicker than the SAN (chip cooker) could provide it. Fortunately all the SAN had to do was get some more peeled potatoes and chuck them in the fryer to satisfy the need, a little more difficult than extending a SAN if you run out of resource.

Finally my fish and chips were served and I as happy in the knowledge that I had a chip shop with RAID 0 serving desks, a 3 processor unit with a scheuler that is capable of parallel processing, with a single chip hopper HBA connected to a deep fat fryer SAN. Whilst their capacity planning was a little lacking at this point, this was an anomoly and you shouldn't really plan your capacity for anomolies of this nature (Unless that 1 anomoly is when you've placed an Ad in the superbowl and you need you website to cope).

Bewarned this is going to be a rant !!!

What is it about windows live that gets its self into such a mess. For me this occurs on two sites. Connect and the MCP site.

The connect site nicely displays me a blank screen when things get screwed, the MCP site tells me I have to login with a valid Live account and displays me the Windows Live Logout button, go figure.

The response, delete your cookies.

My response, No.

If I delete my cookies then I loose all the saved signons I have with the multitude of sites that require signing in, UG sites, forums etc.

Oh and I think the forums has a similar issue in that it just won't let you login.

I'm not sure anyone knows what the issue is, its just very annoying.

I guess if it can't be fixed one neat feature would be able to remove cookies for one set of sites

end of rant.

There are a number of ways of managing connections to databases. Configuration files, udls, odbc dsns etc. However when you have set one up to use a remote database how do you test that it is actually making a connection.

Well one way is to use aliases.

I really like this option as it means you don't have to change your configuration between your different environments. All you change is the alias. On each environment you set up the alias to point to a specific server.

You could say that this can be done with DNS well it could sort of but it doesn't handle ports. That means if you have a two instances on the server then one won't be using the standard port. That means to access the second instance you need to either use the instance name (which DNS doesn't understand) or a port number (which ties you to a specific instance). Ok so you could use some port mapping but thats really complex.

What aliases give you is an easy option for specifying a data source in your connection string which is an alias. You then use the SQL Server Configuration Manager to configure what that alias points to, this includes the network protocol to use. The latter is really handy if you have some network problems and have to change between protocols.

Getting back to the point. Having aliases also allows you to effectively route requests to the remote server to a local server. You can then test/profile on the remote server and then change the alias when you want to run against the remote server.

Remember there are 32 bit AND 64 bit aliases. You have to set up the aliases in both depending on the application you are using.

I've liked the idea of LINQ in that you can query in memory collections. It saves a huge amount of code if you want to filter users by other criteria.

I'd seen demos and just couldn't figure out how it worked because I was just getting a red suiggly line

"Error 2 Could not find an implementation of the query pattern for source type 'System.Web.Security.MembershipUserCollection'.  'Where' not found.  Consider explicitly specifying the type of the range variable 'u'."

Now sometimes you just have a mental block but I couldn't figure out what I needed to do to get it working, and I'm sure some onf you reading will be in "Who wants to be a millionaire" mode and will be telling me exactly what the answer is, its obvious.

Well I'm sure I tried what is the correct solution but to no avail.

Anyway I needed to do this again recently and rather than resort to a for loop and a break statement etc I persevered, and soon realised on reading the error message and taking my time was that a simple LINQ query has implied types, i..e in my example the variable "u" (the range variable) is type based on the items in the collection on the right just like var does. However in this case it can't infer the type and so you have to force the type i.e.

var MatchedUsers = (from MembershipUser u in Membership.FindUsersByEmail("SomeEmail")

                    where u.UserName != "Smith"

                    && u.CreationDate > DateTime.Now.AddDays(-1)

                    select u);

Now I'm sure this might not be as quick as doing the looping my self, but its a lot easier. Whats more if we want to do something else with this set of users we can do easily rather than having to loop over the whole collection again.

I've spent the past two days getting the registration process for SQLBits as a part of the website. This is going through testing now ready for registrations being opened up in the next week or so.

This has involved developing an emailing system (which any of you who are registered for SQLBits will have received an email from) as well as the registration process.

As a SQL bod my ASP.Net skills are clearly not great. However I'm quite chuffed with getting this done in such a short time. This has largely been down to a number of tools and technologies,

1. Data Dude (Visual studio for database professionals)

2. LINQ to SQL

3. Telerik ASP.Net controls.

Data dude makes managing the database and deploying schema AND data changes very easy. There is a bug in GDR that means you need a certain server level permission which some hosting companies won't give. But even with that it still make slife easy, especially transferring reference data, using the data compare.

LINQ to SQL means that changes can be made to the schema very easily and then the model changed. Due to the compilation validation you easily find out where things are broken. I'm also using LINQDataSources in some places (generally the backend) which means making changes to data very easy. I do resort to ADO.Net in one place where I want provide an adhoc query function for admins, I couldn't find a way of doing that in LINQ2SQL

Whats more using LINQ against collections in memory means you can simplify code quite a lot some tricks there.

Telerik ASP.Net Controls mean you can make good looking pages (better than you can without). This is especially true for grids. The RadGrid is just great. Whats more I've found that with the base controls I've often thought I need to do X and found they don't support it, whereas the telerik controls have got a easy solution  like a custom property. Things like the load panel, add a nice touch when doing ajax postbacks. We also have used the RadEditor to allow speakers to format their session submissions.

Hopefully you'll see the changes shortly.

Found a neat way of enabling validation groups in user controls.

http://aspalliance.com/1741_Video_Using_ValidationGroup_Control_with_User_Controls

I made a slight change for this to work with panels. Had to iterate through child controls to get the ValidationGroups set correctly.

using System;

using System.Reflection;

using System.Web.UI;

    public class BaseUserControl : UserControl

    {

        public BaseUserControl()

        {

            this.Load += new EventHandler(BaseUserControl_Load);

        }

        void BaseUserControl_Load(object sender, EventArgs e)

        {

            AssignValidation(this);

        }

        public string ValidationGroup

        {

            get { return (string)ViewState["ValidationGroup"]; }

            set { ViewState["ValidationGroup"] = value; }

        }

        protected void AssignValidation(Control ParentControl)

        {

            foreach (Control control in ParentControl.Controls)

            {

                AssignValidation(control);

                PropertyInfo property = control.GetType().GetProperty("ValidationGroup");

                if (property == null) continue;

                property.SetValue(control, ValidationGroup, null);

            }

        }

    }

I would like to inform you of the release of Microsoft E-Learning collection 2778BE: Querying Microsoft SQL Server 2008 with Transact-SQL.

The collection can be found at

https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=235655