iBoth

I’m a PC, but I’m also a MAC.

Thank you Steve.

Steve Jobs 1955 - 2011

Steve Jobs 1955 - 2011

An aggregate may not appear in the set list of an UPDATE statement

Ever seen the error “An aggregate may not appear in the set list of an UPDATE statement” when working with SQL Server?  I ran into this one recently after trying to put a COUNT in an UPDATE statement.  I was rewriting some legacy code to use a stored procedure, and it turned out to be the perfect case for a Temporary Table.

Instead of boring you with a work scenario, let’s take a simpler one that uses the AdventureWorks database.  This example will create a list of sales people, total order count for each person, and store this list a single table variable to be used as the final data table.

Should be three simple steps right?

1. Create @Table variable

[sql]
DECLARE @SalesPeople TABLE
(
EmployeeID int NOT NULL,
SalesPersonID int NOT NULL,
FullName varchar(200) NOT NULL,
Title varchar(200) NOT NULL,
sales_count int NULL default 0
)
[/sql]

2. INSERT sales people into @Table

[sql]
— HACKISH : Match SalesPersonID to EmployeeID, and fill @SalesPeople
INSERT INTO @SalesPeople
( EmployeeID, SalesPersonID, FullName, Title )
SELECT e.EmployeeID, sp.SalesPersonID,
c.FirstName + ‘ ‘ + c.LastName as FullName,
e.Title
FROM Sales.SalesPerson sp,
HumanResources.Employee e,
Person.Contact c
WHERE sp.SalesPersonID = e.EmployeeID
AND e.ContactID = c.ContactID
[/sql]

3. UPDATE @Table with COUNT

[sql]
UPDATE @SalesPeople
SET
sales_count = COUNT( soh.SalesOrderID )
FROM @SalesPeople sp, Sales.SalesOrderHeader soh
WHERE EXISTS (
SELECT DISTINCT SalesPersonID FROM @SalesPeople WHERE SalesPersonID = soh.SalesPersonID
)
AND sp.SalesPersonID = soh.SalesPersonID
[/sql]

Not COUNT allowed in an UPDATE SET statement

The third step is where the original error comes in, so let’s update this to four steps and see how a Table Variable gets through this.

1 & 2 – Repeat from above

3. Create Table Variable of order counts

[sql]
SELECT soh.SalesPersonID, COUNT( soh.SalesOrderID ) AS sales_count
INTO #SalesOrderCounts
FROM Sales.SalesOrderHeader soh
WHERE EXISTS (
SELECT DISTINCT SalesPersonID FROM @SalesPeople WHERE SalesPersonID = soh.SalesPersonID
)
GROUP BY soh.SalesPersonID
[/sql]

4. Update @Table with order counts

[sql]
UPDATE @SalesPeople
SET sales_count = tmp.sales_count
FROM @SalesPeople sp, #SalesOrderCounts tmp
WHERE sp.SalesPersonID = tmp.SalesPersonID
[/sql]

And here’s the full script from start to finish with the table variable in use.

[sql]
— Master table of sales people
DECLARE @SalesPeople TABLE
(
EmployeeID int NOT NULL,
SalesPersonID int NOT NULL,
FullName varchar(200) NOT NULL,
Title varchar(200) NOT NULL,
sales_count int NULL default 0
)

— Match SalesPersonID to EmployeeID, and fill @SalesPeople
INSERT INTO @SalesPeople
( EmployeeID, SalesPersonID, FullName, Title )
SELECT e.EmployeeID, sp.SalesPersonID,
c.FirstName + ‘ ‘ + c.LastName as FullName,
e.Title
FROM Sales.SalesPerson sp, HumanResources.Employee e, Person.Contact c
WHERE sp.SalesPersonID = e.EmployeeID
AND e.ContactID = c.ContactID

— put sales counts into the other kind of #tableVariable
SELECT soh.SalesPersonID, COUNT( soh.SalesOrderID ) AS sales_count
INTO #SalesOrderCounts
FROM Sales.SalesOrderHeader soh
WHERE EXISTS (
SELECT DISTINCT SalesPersonID FROM @SalesPeople WHERE SalesPersonID = soh.SalesPersonID
)
GROUP BY soh.SalesPersonID

— Update our master @table with data from #tableVariable
UPDATE @SalesPeople
SET sales_count = tmp.sales_count
FROM @SalesPeople sp, #SalesOrderCounts tmp
WHERE sp.SalesPersonID = tmp.SalesPersonID

— dump the results
SELECT FullName, Title, sales_count
FROM @SalesPeople

— cleanup
drop table #SalesOrderCounts
[/sql]

Injecting javascript into asp.net via code

Microsoft has a great MSDN article on using javascript along asp.net, but they didn’t mention a technique I like to use, put it in a Literal control.  While there are many ways to add javascript to a page, I find putting the javascript in a literal much less stressful. Using a Literal control placeholder is also a good way to add messaging to a page after postback, but we’re just going to look at adding javascript.

Let’s take a simple example.  Say you’ve got a comment form that you want to auto close, or reload after the form was posted.  Below is a simple single file style asp.net page with a simple javascript function that reloads this page.

<%@ Page Language="C#" %>

<script runat="server">
/// <summary>
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {

    }
}

/////////////////////////////////////////////////////////////////////////////// 
/// Do stuff with the form data, then refresh page using javascript
protected void submitComments(object sender, EventArgs e)
{

    try
    {
	//
	// do stuff here
	//

	// set javascript timer to reload page afer 3 seconds
	js_target.Text = "setTimeout('reload()', 3000);";

    }
    catch (Exception exc)
    {
        Response.Write( "ERROR : " + exc.Message );
    }
}
</script>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
	<title>Comments</title>
	<script type="text/javascript">
	// page reload helper
	function reload() {
		document.location.replace( document.location );
	}
	</script>
</head>
<body>

<form id="form1" runat="server" method="post">

<script type="text/javascript"><asp:Literal runat="server" id="js_target" /></script>

	Comments
	<asp:TextBox runat="server" ID="comment_box" Width="200" />
	<br><br>

	Your name
	<asp:TextBox runat="server" ID="fullname" Width="200" />
	<br><br>
	<asp:Button runat="server" ID="submit_btn" onclick="submitComment" Text="submit" />

</form>
</body>
</html>

If you look just under the form tag you’ll see the key to this technique, an asp literal wrapped by an open and close script tag.

<script type="text/javascript"><asp:Literal runat="server" id="js_target" /></script>

When you load your page and view the source you’ll just see an empty script tag, so it shouldn’t interfere with the execution or rendering of your page.

The last part of this technique is simple, in your server code just set your Literal control’s .Text value to your javascript code. In this case when I post my comment form, after handling the input data I display a thank you message, then set some javascript to reload the page.

ltl_js.Text = "setTimeout('reload()', 3000);";

That’s all there is to it. Drop a literal in an empty script block and BAM!, you have an easy way to add javascript to your asp.net page.

Use sys.dm_exec_sessions to disconnect SQL user connections

<!–[CDATA[

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- init vars
DECLARE @sessID int,
@dbName varchar(50),
@userName varchar(50)
SET @dbName = 'DA413' -- your database name
SET @userName = 'DA413' -- sql user account to look for
-- use a cursor to store all session_ids
DECLARE session_cursor CURSOR
FOR
SELECT session_id
FROM sys.dm_exec_sessions
WHERE original_login_name = @userName
-- open cursor and grab first row
OPEN session_cursor
FETCH NEXT FROM session_cursor INTO @sessID
-- loop through session_ids
WHILE @@FETCH_STATUS = 0
BEGIN
-- kill it
-- using EXEC because the sproc kill does not like @variables
EXEC('kill ' + @sessID)
-- get the next session_id
FETCH NEXT FROM session_cursor INTO @sessID
END
-- cursor cleanup
CLOSE session_cursor
DEALLOCATE session_cursor

Permalink

| Leave a comment

2 of 10,388 days remaining #TRON

<!–[CDATA[

— Using tsql to figure out how long until TRON:LEGACY

DECLARE @tron datetime,
@tron_legacy datetime
SET @tron = ‘7/9/1982 12:00:00’
SET @tron_legacy = ’12/17/2010 12:00:00′
SELECT CAST( DATEDIFF( DD, GETDATE(), @tron_legacy ) as varchar(2) ) + ‘ of’ + CAST ( DATEDIFF( DD, @tron, @tron_legacy ) AS VARCHAR(1000) ) + ‘ days remaining’ as ‘How long until TRON:LEGACY?’
— returns
— 2 of10388 days remaining

Permalink

| Leave a comment

Endless Mural wins FWA site of the day

FWA Site of the day > Nov 22 2010

Endless Mural wins FWA for HTML5

I’m ecstatic to announce the Endless Mural HTML5 project has won the prestigious FWA Site of the day award.  While this isn’t the first project I’ve worked on that has won the FWA, it is the first NON-Flash, HTML5 and ASP.NET project that has.  No Flash, and we still got the FWA site of the day, SWEET!.

I need a SQL Ninja

On Friday July 23rd I got the chance to skateboard with my good buddy and personal hero Joshua Davis. I feel lucky being able to say we’ve actually been skating together for a few years now, but this was certainly my favorite session we’ve had so far. We started out at Broomfield’s new park because I had to show Josh the new mini bowl.

skate or fry

Two hot dogs ( http://yfrog.com/n3hs5j )

After Broomfield we made a quick stop for lunch, and then on to the Denver Skatepark in downtown.  It was great showing Josh the lines at my hometown skateparks, as well as a few radical maneuvers.

Now that Josh had seen my non-frontside airs, it was time to wrap things up.  As we were saying our goodbyes I decided to ask about work.  Normally I don’t talk about work at the skatepark, but I was thinking about going indie again, and figured what the heck.

Turns out Josh was about to start a project for Microsoft ( WHAT?!?! ) and he need to “find a SQL Ninja” ( DOUBLE WHAT?!?! ).  I’ve been actively working with MS SQL Server since version 6.5, so I let him know he was looking at his sql ninja.  Josh was interested, but gave me the “just cause we’re bros, doesn’t put you on the team”.

HTML5 drawing tool in one night

Driving home from the skatepark I called my wife super giddy.  “Honey, I may be going indie sooner than we planned”.  I gave her the rundown of the potential project Josh and I just spoke about, and let her know I had some homework to do.  That night I went home and built out a distant cousin of the endless mural project.

You can draw with HTML5

HTML5 drawing tool powered by ASPX and MySQL

sloppy html5 drawing

HTML5 drawing tool powered by ASPX and MySQL

EFDRAW is a really simple HTML5 drawing tool powered by ASP.NET and MySQL. It has most of the features of the mural ( draw, save, replay, share ), but this was only a proof of concept.  This was my first dive into HTML5 development, and it’s pretty sweet.

http://www.screentoaster.com/swf/STPlayer.swf

If you’re interested in HTML5 drawing tools, feel free to play around with EFDRAW, view source, help yourself.

There’s one thing…. Azure

A few days after building EFDRAW my band The Compilers played at Ignite Denver 7. Right before starting our first set my phone rings and it’s Josh! OMG I think, this is either the “you got it” or the “sorry bud, we’ll skate again” call. I decide to take the call even though we were locked and loaded, standing on stage with our gear waiting for the house music to go down. I answer and it’s Josh, but not the usual hyperactive Josh I’m accustomed to. I ask about the gig and he says “Well, there’s one thing. We have to use Azure”.

I let him know I’ve worked with other cloud platforms already, just not Microsoft’s. So we talk a little more and Josh passes the phone to Branden so we can talk 0s and 1s. After talking to Branden “mega brain” Hall for a few minutes he asks if I can do this. I tell him yes, he says yes, I get excited, he gets excited. Branden passed me back to Josh and I’m in shock at this point.

Now the boring stuff

So that’s the story of how I landed the Endless Mural gig, now the boring technical details.

The drawing portion of the mural was built by Branden Hall of Automata Studios.  I did the backend which is made up of ASP.NET ( C# ), SQL Azure, and Windows Azure.  We’re using Azure blob storage to save and serve up the PNGs created at the mural.  To access SQL server, I wrote a super lightweight data access library using all native .NET.

For the most part the backend was very much like every other .NET SQL Server project I build, but Azure did introduce a few gotchas.

  1. The publishing and management of your cloud site mainly goes through http://windows.azure.com/.
  2. You can deploy your site from Visual Studio which proved to be immensely helpful after my Azure deploy package grew beyond 100 MB.
  3. You can access SQL Azure directly from SQL 2008+ management tools.
  4. You can not FTP single files up to the cloud, only the full ball of wax.
  5. You can still use web.config for configuration storage, but Azure also has it’s own version of web.config.
  6. If you need to edit your settings after deploying, store those settings in your Azure service config, not web.config
  7. SQL Azure requires all tables to use clustered indexes
  8. SQL Azure has it’s own TSQL restrictions ( not many, but be aware )
  9. On average, doing a full republish of an Azure site took a full hour.

I could probably ramble on and on about Azure, but I’ll cut it short.  If you happen to have any questions about Azure feel free to hit me up or leave a comment.  I would also like to say that I know Microsoft is and has been actively improving Azure by the day.  The state of Azure today is most likely even better than when we built the mural, so my experiences may not be your own.

The toolbox

  • Windows Azure SDK
  • Windows Azure Platform Kit June 2010
  • Windows Azure Tools for Visual Studio ( v1.2 )
  • Microsoft Seadragon Ajax library
  • Microsoft SQL Server 2008 R2
  • Microsoft SQL Azure
  • ASP.NET 4 ( C# )
  • Windows Azure
  • Azure Storage Explorer

Here is the toolbox that Branden used on the client side.

Hotlinks from the server guy

It’s a wrap

This project was the most concentrated five weeks I’ve had in quite some time. I still wonder if we were only given five weeks because this was an HTML5 project. Either way, the mural team made some magic and now you can too. If you’re like me and just want to doodle, go make some art at the mural. If you’re a developer interested in HTML5 and Javascript programming, go check out the javascript library okapi.js which Branden Hall recently open sourced.

Also be sure to visit the magicians, I mean artists, who made the amazing patterns you see when using the mural. I’m a life long doodler, but can’t art myself out of a paper bag.

Guilherme Marconi

Guilherme Marconi - brain.marconi.nu

Guilherme Marconi - brain.marconi.nu

Matt Lyon

And lastly I put up a photo album on Facebook of all my camera phone pictures from the trip.  Check out the endlessmural photo album.

Want to see some kick ass HTML5?

I’m extremely happy to announce the www.endlessmural.com project was launched today and it was a huge success.  I intend on posting something with more details when I return home, but in the meantime please please check this site out.  It’s my current favorite example of HTML5 in action, and it works in all modern browsers ( yes, even iPad ).

The coolest HTML5 sample you will see on the internet

Go make art at endlessmural.com

Endlessmural.com is a generative drawing tool written in HTML5, Javascript, CSS3, on top of a Microsoft Azure backend.  Go, make art, share the url.

Here is a piece I made today.

endlessmural.com artwork