I've been having a blast at SQL PASS Summit 2011. I got to rub elbows with luminaries like Marco Russo, Alberto Ferrari, Davide Mauri, Paul Turley, Ron Talmadge, Denny Cherry, Jessica Moss and many many more.

Here's one bit of proof, a picture of me with Pinal Dave!

SQLSaturday #68 SQLSaturday - Schedule

Some of these sessions have slide-decks uploaded...

SQLSaturday #68 SQLSaturday - Schedule

SQL Saturday #68 and Other SQL Server info I've recently come across

Arie Jones gave two presentations at SQL Saturday that could really accelerate some better database management practices, especially in Change Data Capture:

http://www.programmersedge.com/?p=3135

Toshi Watanabe gave a nice presentation on how to implement row-level security in a relational data warehouse by using AD groups accessed through CLR integration. It puts the security maintenance burden in the hands of Application Admins and AD administrators. Beautiful.

Buck Woody explained that Azure is Platform-as-a-Service...as a developer/dba you only should have to care about code and data. This is not the same as Infrastructure-as-a-Service.

http://blogs.msdn.com/b/buckwoody/

Chuck Lathorpe opened my eyes to some of the capabilities of transactional replication. Now I want to increase the amount of replication we're using at work. And, I want to try out initialization of the snapshot through restore.

Not part of SQL Saturday...although her name did come up as a valued resource....

I want to read and re-read this post by Kimberly Tripp....It's giving me a sinking feeling about some of the GUID-based data that has been pouring into my environments lately....:|

http://www.sqlskills.com/BLOGS/KIMBERLY/category/GUIDs.aspx

SQL NOT IN

Where column not in (subquery)

In this example, we select all books that do not have sales. Note that this is a non-correlated subquery.

SELECT book_key
FROM book
WHERE book_key NOT IN (SELECT book_key FROM sales);

There are serious problems with subqueries that may return NULL values. It is a good idea to discourage the use of the NOT IN clause (which invokes a subquery) and to prefer NOT EXISTS (which invokes a correlated subquery), since the query returns no rows if any rows returned by the subquery contain null values.

NOT EXISTS example
SELECT b.book_key
FROM book b
WHERE b.book_key NOT EXISTS(SELECT 1 FROM sales s WHERE s.book_key = b.book_key);

Note also that adding the WHERE clause in the subquery will eliminate NULL values in the subquery result set, so the NOT IN would work, but NOT EXISTS is preferred for performance reasons.

Sort to the end of a file list

This isn't really code but it is something that many coders may want to do in filenames so...The need is to sort a named file to the bottom of the list. This may only work on windows file systems (i.e., not *nix or others)

I found this answer on Annoyances.org.

If using zz_filename isn't your cup of tea you can use one of the 5 or so greek letters that sort after the "Z" character.

special characters

numeric digits

ALT+1004 (infinity symbol)

alphabetic characters

ALT+1003

ALT+1006

ALT+1001

ALT+1000

ALT+1005

ALT+1002

That last one is the "Ω" - nice.

Two Tools To Try

Usually I post about stuff I've tried. Not this time.

The following two tools sound cool, but I have not used them.

Open Source .msi file creation tool, backed by Microsoft
http://sourceforge.net/projects/wix/files/


.NET code security check.
From the download page at MSDN:

"CAT.NET is a binary code analysis tool that helps identify common variants of certain prevailing vulnerabilities that can give rise to common attack vectors such as Cross-Site Scripting (XSS), SQL Injection and XPath Injection."

http://www.microsoft.com/downloads/details.aspx?FamilyId=0178e2ef-9da8-445e-9348-c93f24cc9f9d&displaylang=en

ProcessingCommands

Scenario: forced to run a production and a test SSAS cube-processing environment on the same server, we discovered that the TEST instance of the SSIS cube processing task was reading from the production relational database.

This is because the DatabaseID attribute of the ProcessingCommands property was incorrectly referencing the production database, despite the fact I had changed the database connection in the connection manager.

So, be aware of the DatabaseID.

You can view the DatabaseID of your Analysis Services database by right-clicking it in SSMS, choosing properties and viewing the value in the ID field.

5 <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema"....snip....>

6 <Object>

7 <DatabaseID>DWEnterpriseCubesTESTDatabaseID>

8 <DimensionID>Dim Enterprise Claim DetailDimensionID

9 Object

10 <Type>ProcessFullType

11 WriteBackTableCreationUseExistingWriteBackTableCreation

12 Process

Transfer Login

If you need to create a set of SQL Server logon credentials on a second server that match the logon credentials of a primary server, simply creating a user with the same name will most likely not work because the SID for the logon will be different between the two servers.

To handle this situation, you need to copy the original logon, including the SID, to the new server.

Microsoft Support explains how, and provides a pair of stored procedures at this URL:

http://support.microsoft.com/kb/918992/

NOTE: I have not tested this process, so this posting may need to be modified.