Rants Tagged with “SQL Server”
1 2 3 > >> (Total Pages: 3/Total Results: 30)
As many of you may have heard, I recently launched http://GiveAQuiz.com as a new web site for creating and taking quizzes. The Data Team at Microsoft were great in helping me build this site. I've written a whitepaper detailing how we used the Microsoft data stack to accomplish it. This whitepaper shows how we used these data technologies to build the site:
If you are using Microsoft's Data stack for your own web-based and/or Silverlight site, give a look...hopefully some lessons learned you can use:
http://msdn.microsoft.com/en-us/library/ff847451.aspx
So my blog has been pretty quiet lately and I thought I'd let you know why. With a small team, I've been building GiveAQuiz.com.
Several months ago I was working with a client who wanted me to put together a short quiz on Silverlight to help determine which of their developers got to join their Silverlight team. So I did what I always do, I searched the Internet for some small startup site that let's me accomplish the task at hand. Unfortunately all I found were logs of 'survey' sites that would let me put together some questions but only receive summary results. The one site I found that would let me build a quiz, only would let me print a PDF of the quiz to give to people by hand. This got me thinking about how a site that let people give quizzes might not only be helpful to people like me, but also to teachers of all kinds. I was determined to create the site...when I had free time (yeah, I know).
As I had chances, I would do some small part of the planning. When I wrote a SketchFlow article, I used the opportunity to prototype some of the screens for the article. But to get the site really built, I needed help. To the rescue came Microsoft and Chris Sells. Chris and I decided we wanted to build the site in the same spirit as NerdDinner.com. Microsoft would help fund the development of the site and I would write a whitepaper and do some videos on how we built the site.
I put together a small team consisting of Chris Rauber (ASP.NET MVC dev extraordinaire), Dennis Estanislao (designer savant) and myself to build this site. The site is now live, but is in a private beta mode (where you can e-mail me to get an account for a limited number of users) until we get the bugs out.
Building the site was a lot of fun and we got to mix a lot of technologies inlcuding:
From the beginning the expectation was to have the source code available under MS-PL so that it could be an exemplar for other developers to see how a site with the latest Microsoft stack. You can find the current version of the source code on CodePlex at http://giveaquiz.codeplex.com. We are accepting new members to help implement new features after launch so if you're interested in helping, feel free to apply on the CodePlex site. We we do a formal release of code once the private beta is complete. We also used AgileZen.com (who graciously granted us a free license since we were working on an open source project) and using it for workflow between disconnected members turned out really well. Love the product!
So now its your turn, I want to ask you my readers to check out GiveAQuiz.com and ask for a private beta account. The first twenty-five people who contact me at "shawn at giveaquiz dot com" will get into the private beta. Help us find the bugs please!
As many of you who've been following know, I use a simple database of XBox game data as my example database. I usually remember to include it on the server project but in some examples its been forgotten. In other cases I've shipped with a 2008 version of the database instead of the 2005 version. To address this, I've uploaded .zip'd versions of both the 2005 and 2008 databases for anyone to use for any reason. This includes my RIA Services sample (which is using a SQL Server 2008 version) or my MVVM MSDN article sample which also uses a 2008 version. They include a MDF and a LDF. If you have trouble attaching them to an existing database, try deleting the LDF file. Go grab there here:
Any questions/problems, post a comment!
Its been an exciting day here at the Wildermuth compound. I noticed that the old setup for my SQL Server Monitor project was broken (mostly dependencies were wrong), so I figured I'd just open it up and tweak a couple of settings and move along with my day. D'oh!
Seems that I'd lost the source to that project. I use Subversion to save my sources locally but that project pre-dated that source control. What to do? Well, I rebuilt it using Reflector to give me the old code. Then I rebuild the setup using Visual Studio 2008's Setup projects (which suspiciously don't seem like they've changed since their inception in Visual Studio 2002!).
Since I had to rebuild the source, I thought it was time to fix the one nagging 'bug' in the code. Not really a bug since it was built without UAC in mind, it required it be to run as Admin on UAC enabled machines (since it mucks with the services and such). I thought I was going to have to dig into some arcane Win32 stuff to get it to work, but interestingly all it needed was a manifest file:
<?xml version="1.0" encoding="utf-8"?>
<asmv1:assembly manifestVersion="1.0"
xmlns="urn:schemas-microsoft-com:asm.v1"
xmlns:asmv1="urn:schemas-microsoft-com:asm.v1"
xmlns:asmv2="urn:schemas-microsoft-com:asm.v2"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<assemblyIdentity version="1.0.0.0"
name="MyApplication.app"/>
<trustInfo xmlns="urn:schemas-microsoft-com:asm.v2">
<security>
<requestedPrivileges xmlns="urn:schemas-microsoft-com:asm.v3">
<requestedExecutionLevel level="requireAdministrator"
uiAccess="false" />
<!--<requestedExecutionLevel level="asInvoker" uiAccess="false" />-->
</requestedPrivileges>
</security>
</trustInfo>
</asmv1:assembly>
The trick was adding the 'requireAdminstrator' as a requestedExecutionLevel. Once the manifest was added as an embedded resource, Vista/2008 recognizes it needs some admin privledges and should ask the user when necessary.
Go get the new setup file if you want to be my beta tester! Please email me at shawn (at) wildermuth.com if you find any bugs.
Hey everybody look...its not a Silverlight post ;)
I just finished listening to this interview with Peter Spiro. Peter Spiro discusses leadership in the SQL Server team, Shackleton and WinFS. Its worth a listen as he's one of the smartest guys in MS (in my opinion) and has enough cred to back it up.
One concern is that it seems that his discussion of WinFS (which he corrects the interviewer and calls a "failure") is cut short. There is a hard break (the only one I noticed) about at 40 minutes into the video, in the middle of the WinFS discussion. I'd love to get a hold of what's missing, but I don't see that happening. Hopefully it was just a rambling discussion of esoteric topics that was cut for clarity, but I can't help but feel like we're missing 18 1/2 minutes of tape.
For those of use who have spent any time in the sample AdventureWorks database, I just found a handy Visio (or HTML) database diagram of the database. Its clean and explains some of their ideas about using schemas in SQL Server. Worth a look if you've ever looked at the AW database.
I got thinking about writing custom paging code with the new SQL Server 2005 ROW_NUMBER, RANK, and DENSE_RANK functions. I started playing around with code and ended up with this simple ASP.NET 2.0 example. It works with SQL Server 2005 and the Adventure Works sample database. The example uses ad-hoc SQL so it would be easy to show how it works, but moving it into stored procs would be simple.
I'd love any feedback!
PagingDemo.zip
I had an interesting discussion with some members of a class I am teaching right now about how paging is done. It looks like there are four Functions that allow for paging and using Common Table Expressions. They all can optionally include a separate ORDER BY clause to specify how they are sorted (so your result set can be sorted differently than it is ranked).
ROW_NUMBER()
This function returns the exact number of the row number in the result. For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, ROW_NUMBER() as RowNum
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE RowNum BETWEEN 1 AND 5
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (ROW_NUMBER() OVER (ORDER BY SalesYTD)) as RowNum
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE RowNum BETWEEN 1 AND 5
ORDER BY LastName
RANK()
This function returns the ranking of the row in the result. Rows with identical results in the ordering will return the same rank (e.g. Two sales people with sales of $1,000 will get the same ranking). For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, RANK() as Ranking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE Ranking BETWEEN 1 AND 5
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (RANK() OVER (ORDER BY SalesYTD)) as Ranking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE Ranking BETWEEN 1 AND 5
ORDER BY LastName
DENSE_RANK()
Same as RANK above except there will not be any gaps (e.g. If rank 3rd and 4th are tied, they will both be RANK'd 3. With DENSE_RANK the next row will be ranked 4 instead of 5 (as RANK would do). For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, DENSE_RANK() as DenseRanking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE DenseRanking BETWEEN 1 AND 5
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (DENSE_RANK() OVER (ORDER BY SalesYTD)) as DenseRanking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE DenseRanking BETWEEN 1 AND 5
ORDER BY LastName
NTILE(x)
Separates the result into x number of batches. When you specify NTILE you must specify the number of 'tiles' to create. For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, NTILE(5) as PageNumber
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE PageNumber = 1
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (NTILE(5) OVER (ORDER BY SalesYTD)) as PageNumber
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE PageNumber = 1
ORDER BY LastName
This is fun!
If you upgrade to RC1 be aware that SQL Server 2005 requires SP2 before it will work with Vista RC1. Yeah, I know there is no SP2...but that's the case. It just doesn't work. I am trying to hack around the problem so I'll let you know if I find a solution...
I have been attempting to try out some new software from Microsoft (including Glidepath and Visual Studio Database Edition). Both of these require SQL Server Express installed. Problem is that I install a Developer Edition of SQL Server 2005 (as well as 2000) because it is more feature friendly than SQL Server Express. Why does Microsoft insist I have a third Database server? Why can't it prompt me to tell it what database to use, or at least attempt to find SQL Server 2005 as the default instance on the current machine? Just stoopid in my opinion. It's keeping me from trying out and possibly exhaulting these new interesting projects.
"There I said it..."
