Rich "The Hubbins" Claussen - SQL

SQLSaturday #12 Portland 2009

Rich Claussen — Wed, 03 Jun 2009 16:03:59 GMT

What is SQLSaturday?

It's a free one day learning event for SQL Server professionals, loosely patterned on the well known Code Camp model, organized by the Portland SQL Server Users Group (pdxSSug).

Date and Location:

SQL Saturday Portland will be held June 6, 2009 at Mt. Hood Community College, 26000 S.E. Stark St., Gresham, Oregon 97030. The event follows the standard conference model. There are 12 sessions across 2 tracks. Most of the sessions will run 60 minutes. Check in will open at 8:00 am and sessions begin at 9:00 am. Lunch will be provided on site, and the day will close out around 5:00 pm. The after event will be held at McMenamins Edgefield, 2126 S.W. Halsey St., Troutdale, OR 97060 beginning right after the last session. Registration is at http://www.sqlsaturday.com

When will the schedule of sessions be posted?

The call for speakers is closed, and the schedule is finalized.

Can I sponsor this event?

More sponsors are needed to help subsidize the incidental costs of running the event and this is a great event to meet SQL Server professionals or to just give back to the community. If you are interested or want more information, please visit the sponsor page. You can view all of the great sponsors there and it is hoped that most of them will be at the event.

What else can you tell me about the event?

This event is free to anyone that wishes to attend. Check in for the event opens at 8 am.

Be sure to register at http://www.sqlsaturday.com

June SQL SIG Meeting - Visual Studio Reporting

Rich Claussen — Fri, 13 Jun 2008 22:27:56 GMT

The Code Trip is near its end at the MVP summit. Join us Thursday at the Corillian Cafe to cheer the team on through the final leg of their journey.

WHAT:	June PADNUG SQL SIG Meeting
WHEN:	Tuesday 06/17/2008 6:30 p.m. Presentation ~8:30 p.m. After Words at Cornelius Pass Roadhouse?
WHERE:	Corillian Cafe, now part of CheckFree, now a part of FiServ 3400 NW John Olsen Place Hillsboro, OR 97124
WHO:	William R. Vaughn Bill retired from Microsoft (in 2000) after 14 years to focus on mentoring, speaking, and writing. His specialty is data access application design especially when connecting to SQL Server. Bill has worked in the computer industry since 1972. He holds a Bachelor's degree in Computer Science from Mary Hardin-Baylor and a Master's degree in Interdisciplinary Studies from the University of Texas. He's also been awarded an Honorary PhD. from the University of Advancing Computer Technology in Tempe Arizona. Bill spent his early years working in the mainframe data processing industry and transitioned to the personal computer side in the late 70s. Along the way, he worked for the Texas DPS Narcotics Service, EDS (where he was recruited by and worked for Ross Perot), at Mostek/United Technologies, Challenge Systems, Digital Research, and CPT Corporation. After having surfed the PC industry for many years, he began his Microsoft years in 1986 working for the Windows developer liaison team at Microsoft in 1986. For the next 14 years, he worked in various divisions at Microsoft including Microsoft University, the Visual Basic documentation and Visual Studio marketing and Internal Technical Education teams before retiring in 2000 to form his own company, Beta V Corporation. Bill has written seven editions of the popular Hitchhiker's Guide (the 4th, 5th and 6th published by Microsoft Press) and books for APress, including the bestseller ADO and ADO.NET Examples and Best Practices. Peter Blackburn and Bill also coauthored the Hitchhiker's Guide to SQL Server 2000 Reporting Services (Addison- Wesley). His latest work is Hitchhiker's Guide to SQL Server Everywhere-his first e-Book. He writes lead articles for SQL Server Magazine where he is a contributing editor, MSDN, and others. Bill also writes a bi-weekly editorial for Processor.COM magazine. Bill is a top-rated speaker at conferences worldwide, including keynotes and sessions at TechEd, DevWeek, Dev Connections, SQL Connections, VBUG, and many others - where his wit and no-holds-barred technical insights win him rave reviews. Bill is a Microsoft MVP.
TOPIC:	Visual Studio Reporting The inside scoop on how to get the most out of the new ReportViewer control and how it's evolved in Visual Studio 2008. This discusses how to leverage your Reporting Services skills and RDL reports to create application-based reports and how to manage the myriad of issues that come up when trying to emulate Reporting Services functionality like parameters, connections, pick-list population and much more. This session previews the SQL Server Reporting Services technology coming with SQL Server 2008.

While you are at it, consider visiting our Premium Sponsors that make these meetings possible:

Aivea - http://www.aivea.com/
Ascentium - http://www.ascentium.com/
Microsoft - http://www.microsoft.com/
O'Reilly - http://www.oreilly.com/
Robert Half Technology - http://www.roberthalftechnology.com/
SubMain and CodeIt.Right - http://submain.com/codeit.right
JetBrains - http://www.jetbrains.com/resharper/

Looking forward to seeing you all there!

April PADNUG Meeting - Mix-a-Lot

Rich Claussen — Wed, 09 Apr 2008 11:58:45 GMT

The Code Trip is near its end at the MVP summit. Join us Thursday at the Corillian Cafe to cheer the team on through the final leg of their journey.

WHAT:	April PADNUG Meeting
WHEN:	Thursday 04/10/2008 6:00 p.m. Pizza (by Ascentium) 6:30 p.m. Presentation ~9:00 p.m. After Words at Cornelius Pass Roadhouse
WHERE:	Corillian Cafe, now part of CheckFree 3400 NW John Olsen Place Hillsboro, OR 97124
WHO:	The Code Trip Team
TOPIC:	Mix-a-Lot Join us at the Portland Area .NET User Group (PADNUG) where we run through the newest technologies to emerge from MIX08, including Silverlight 2 Beta 1, Internet Explorer 8, SQL Server Data Services, Windows Live, and more.

Also, don't forget to register for the PDX Webfoot event on Saturday! It should be a great event and it looks like it will be a fabulous day to bring the family out to an event. We're using Yahoo!'s Upcoming for registration at: http://upcoming.yahoo.com/event/468242/. There'll be food and lot's of give-aways.

While you are at it, consider visiting our Premium Sponsors that make these meetings possible:

Aivea - http://www.aivea.com/
Ascentium - http://www.ascentium.com/
Microsoft - http://www.microsoft.com/
Online Business Systems - http://www.obsglobal.com/
O'Reilly - http://www.oreilly.com/
Robert Half Technology - http://www.roberthalftechnology.com/
SubMain and CodeIt.Right - http://submain.com/codeit.right
JetBrains - http://www.jetbrains.com/resharper/

Looking forward to seeing you all there!

February Bonus PADNUG Meeting - SQL Server 2008 Intro Part 1

Rich Claussen — Tue, 19 Feb 2008 13:22:08 GMT

Arnie is one of the top SQL Server people in the area. He is going to give us a look ahead at the coming release of SQL Server 2008.

WHAT:	February Bonus PADNUG Meeting
WHEN:	Tuesday 02/19/2008 6:00 p.m. Sandwiches 6:30 p.m. Presentation
WHERE:	Microsoft Portland Office located in Lincoln Tower 10260 SW Greenburg Road Suite 600 Portland, OR 97223
WHO:	Arnie Rowland
TOPIC:	SQL Server 2008 Intro Part 1 Come see what's new in SQL Server 2008. This is part one of two meetings we'll hold.

While you are at it, consider visiting our Premium Sponsors that make these meetings possible:

Aivea - http://www.aivea.com/
Ascentium - http://www.ascentium.com/
Microsoft - http://www.microsoft.com/
Online Business Systems - http://www.obsglobal.com/
O'Reilly - http://www.oreilly.com/
Robert Half Technology - http://www.roberthalftechnology.com/

Looking forward to seeing you all there!

Assigning the Scalar from a Stored Procedure

Rich Claussen — Fri, 25 Aug 2006 00:50:34 GMT

This is just one of those little reminders to self. Self, you never seem to remember how to assign the return value from a stored procedure to a variable in SQL. I try various forms and always seem to miss the right one. So, in an effort to mark it for my future use, I place it here in the blog.

One does NOT accomplish it through any of these methods:

DECLARE @ReturnedValue INT

    SET @ReturnedValue = StoredProcedure
@Param1, @Param2

IF( @ReturnedValue = @Something )

BEGIN

    --Do This Stuff

END

DECLARE @ReturnedValue INT

    SET @ReturnedValue = StoredProcedure
( @Param1, @Param2 )

IF( @ReturnedValue = @Something )

BEGIN

    --Do This Stuff

END

DECLARE @ReturnedValue INT

    SET @ReturnedValue = EXEC StoredProcedure
@Param1, @Param2

IF( @ReturnedValue = @Something )

BEGIN

    --Do This Stuff

END

DECLARE @ReturnedValue INT

    SET @ReturnedValue = EXEC StoredProcedure
( @Param1, @Param2 )

IF( @ReturnedValue = @Something )

BEGIN

    --Do This Stuff

END

One does by this method:

DECLARE @ReturnedValue INT

EXEC    @ReturnedValue = StoredProcedure
@Param1, @Param2

IF( @ReturnedValue = @Something )

BEGIN

    --Do This Stuff

END

I just have to remember the order. I suppose it makes sense when I see it, but I forget when it's time to type it. It's just too easy to get mixed up with Function format.

What's the Opposite of a SELECT TOP in SQL?

Rich Claussen — Tue, 23 May 2006 07:04:28 GMT

This is a question asked of Google that led to my blog today. Of course, I haven't blogged on such a thing, but I figure what the heck? Let's do it!

For the next person that searches for such an item, the opposite of SELECT TOP is also SELECT TOP. Huh? You simply change the ORDER BY clause to be descending. For example, if you have a query such as:

SELECT TOP 10 FirstName, LastName, BirthDate

  FROM Persons

 ORDER BY LastName, FirstName

then the opposite would be:

SELECT TOP 10 FirstName, LastName, BirthDate

  FROM Persons

 ORDER BY LastName DESC,
FirstName DESC

Simple, huh?

You may be asking, "What if I don't care about the order?" Then I have to say that you don't really care about TOP versus BOTTOM, either as you are going to get a random (as far as we know) set of rows in either case.

Fabulous LINQ Presentation at PADNUG

Rich Claussen — Wed, 01 Mar 2006 07:32:25 GMT

All I can say is: Wow! Nick, you really did a great job!

For those that missed it, Nick effectively showed how the .NET Framework already supports all that is neccesary to make LINQ possible by implementing the SELECTion bit in his own namespace. Then, dazzled by switching to the packaged namespace without changing the code.

If you would like to grab the presentation and/or code examples, <<click here>>. You too will be able to write code like this one day:

    1 partial class Program
{

    2   static void DemoCode()
{

    3     DataContext
dc = new DataContext(

    4      
"server=(local);database=Northwind;Integrated Security=true");

    5 

    6     Table<Customer>
customers = dc.GetTable<Customer>();

    7 

    8     var
results = from customer in customers

    9      
      where customer.CustomerID.StartsWith("A")

   10        
    select customer;

   11 

   12     Show(results,
"Results from DLinq Query");

   13   }

   14 }

   15 

   16 [Table(Name="Customers")]

   17 public class Customer
{

   18   [Column(Id=true)]

   19   public string CustomerID;

   20 

   21   [Column]

   22   public string CompanyName;

   23 }

Making Pivot Tables with SQL Queries

Rich Claussen — Tue, 31 May 2005 02:15:13 GMT

Some time ago, I was asked to devlop some reports for a little tracking application I'd written for my employer. In my past life, I did a lot of this work (heck, it was my job!) and feel pretty comfortable using Excel to twist my numbers around in all sorts of Pivot-y ways.

What I hadn't done is to create pivots that were conditional and customized. I suppose, with further exploration, I'd find reasonable ways to accomodate my needs from within Excel, but I had a moment's epiphany - why not just build the proper pivot query in SQL to begin with?

I know I had seen it done, but never took the time to find out details. So, with this new thought, I did a bit of exploring - Duh! That's easy! Simply count conditionally for each column.

So, for posterity's sake - and my own future needs - here follows the basic structure for doing a pivot table with a SQL query (Counts number of items for a given number of weeks prior to current):

SELECT TOP 100 PERCENT

    'Opened On Week' AS LineType,

    'All Modules' AS AppModule,

    COUNT( CASE WeeksBackOpened WHEN 4 THEN IssueId ELSE
NULL END )

       AS "4 Weeks Ago",

    COUNT( CASE WeeksBackOpened WHEN 3 THEN IssueId ELSE
NULL END )

       AS "3 Weeks Ago",

    COUNT( CASE WeeksBackOpened WHEN 2 THEN IssueId ELSE
NULL END )

       AS "2 Weeks Ago",

    COUNT( CASE WeeksBackOpened WHEN 1 THEN IssueId ELSE
NULL END )

       AS "Last Week",

    COUNT( CASE WeeksBackOpened WHEN 0 THEN IssueId ELSE
NULL END )

       AS "This Week"

  FROM PivotTableView

UNION

SELECT TOP 100 PERCENT

    'Opened On Week' AS LineType,

    Application + ': ' + ModuleName AS AppModule,

    COUNT( CASE WeeksBackOpened WHEN 4 THEN IssueId ELSE
NULL END )

       AS "4 Weeks Ago",

    COUNT( CASE WeeksBackOpened WHEN 3 THEN IssueId ELSE
NULL END )

       AS "3 Weeks Ago",

    COUNT( CASE WeeksBackOpened WHEN 2 THEN IssueId ELSE
NULL END )

       AS "2 Weeks Ago",

    COUNT( CASE WeeksBackOpened WHEN 1 THEN IssueId ELSE
NULL END )

       AS "Last Week",

    COUNT( CASE WeeksBackOpened WHEN 0 THEN IssueId ELSE
NULL END )

       AS "This Week"

  FROM PivotTableView

GROUP BY Application, ModuleName

ORDER BY AppModule, LineType