10 Reasons to Join a PASS or .Net User Group

I am a member and leader at multiple chapters of technology-based international professional associations, like PASS (Professional Association for SQL Server) and INETA. Through that affiliation, I have experienced many, many benefits that such a small percentage of our industry takes advantage of.

1. Great people – Attendees, speakers, organizers, volunteers, sponsors, and venue hosts. Attend regularly and I promise you will make life long friends.
2. A place to talk shop with peers, learn about other organizations and practices, scope out job opportunities, help others with their challenges, and mentor people new to the field.
3. Great content – free training, new technology previews, and deep dives into specific topics.
4. Access to experts – speakers almost always provide their contact info and give you permission to ask questions outside of the presentation. Get help on your challenges from other attendees during the social networking time. Sometimes you even get to meet Industry celebrities and national conference presenters, such as Brent Ozar, Brad McGehee, and Scott Guthrie.
5. Food and beverage
6. Free swag – books, software (Development software, Operating Systems, 3rd party tools, Xbox games, and more), hardware (keyboards, mice, web cams, etc.), training vouchers, discount coupons, quirky and unusual items (such as Bucky Balls),  travel mugs, tote bags, back packs, and the list goes on.
7. News and announcements about other events and happenings in the industry – training conferences like the PASS Summit and SQL Cruise, all day training opportunities, such as Day of .Net, Codemash, SQL Saturday, virtual training events, contests, free eBooks promotions, Job opportunities.
8. Save money travelling to regional training or events by carpooling with your fellow user group friends.
9. Opportunities to present – show off your own proficiency in your craft, get experience to prepare you to speak on regional or national level, establish credibility as a teacher/communicator.
10. Opportunities to be a board member or volunteer to hone your leadership and logistical skills enhances your career marketability. Also, volunteering or leading a local chapter or regional event is great way to prepare you to become a board member on a national scale, if that is of interest to you.

Really there are few reasons not to attend, join, volunteer, or lead a professional organization. If you’re interested in professional organizations in general, read my popular post about 10 reasons to join a professional association.

What have I missed?

SQL Server Compatibility Level Testing

I stumbled on interesting version compatibility issues while working with a database in SQL Server 2008R2. The database was originally developed in SQL Server 2000. It was upgraded to MSSQL 2008R2 as a part of a virtualization initiative, however, the Compatibility Level was still set to 80 (MSSQL 2000).

After seeing columns of type VARCHAR(MAX), it got me wondering about compatibility issues since there was no VARCHAR(MAX) in 2000.

Anomaly #1

Doing some research I found this post with a script for Testing all procs before upgrading to Compatibility Level 90. I ran it on the database (while still set to Compatibility Level 80) and it reported a stored procedure that had a syntax error that was not caught by the syntax checker in MSSQL 2000, 2005, or 2008R2. The SQL is below:

SELECT     ChangeLogID

    , CourseID

    , RecordID

    , RecordTable

    , ChangeType

    , ModifiedDateTime

    , ModifiedByEmpNo

    , CASE RecordTable 

        WHEN 'Course' THEN

        ( SELECT CourseName 

          FROM Course 

          WHERE CourseID = cl.CourseID)

        WHEN 'Lesson' THEN

        ( SELECT LessonName 

          FROM Lesson 

          WHERE LessonID = cl.LessonID)

         WHEN 'Content' THEN

        ( SELECT ContentName 

          FROM Content 

          WHERE ContentID = cl.ContentID)

        ELSE "None"

    END AS RecordName

FROM         ChangeLog cl

WHERE     (CourseID = @CourseID)

The error message was: Invalid column name 'None'. Note the quotation marks around “None” after the ELSE. Strangely, the stored procedure executes without error. It only detects the syntax error on CREATE, not when I ALTER the stored procedure.

Anomaly #2

Using the same database described above, the compatibility error detection script I used (while still having a Compatibility Level of 80) did not detect the error:

Msg 8127, Level 16, State 1, Procedure GetNextLessonForLessonNo, Line 49
Column "Lesson.LessonNo" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

The SQL generating this error is:

SELECT     TOP 1 MIN(L.LessonNo) AS LessonNo

    , L.LessonID

    , L.CourseID

    , L.LessonName

    , L.LessonDesc

    , COUNT(Content.ContentID) AS NContentPages

FROM     Course C 

         INNER JOIN Lesson L 

            ON C.CourseID = L.CourseID

         LEFT OUTER JOIN Content 

            ON L.LessonID = Content.LessonID

WHERE     (L.LessonNo > @LessonNo 

       AND L.CourseID = @CourseID)

GROUP BY L.LessonID

    , L.CourseID

    , L.LessonName

    , L.LessonDesc

ORDER BY L.LessonNo

This error is not detected in SSMS (in a query window, or by running the detection script) until the Database Compatibility Level is changed from 80 (MSSQL 2000) to 100 (MSSQL 2008). The issue can be resolved in SQL Server 2008 by adding L.LessonNo to the GROUP BY clause.

photo credit: somegeekintn / CC BY 2.0