World of Whatever

A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Loading...

Wednesday, October 22, 2014

A quick and dirty date dimension for PowerPivot

I've built out this sort of thing a few times but in fine fashion, I've never saved my script. In a proper data warehouse, there would be a date dimension built out and I would just reference it. Whenever I get skunkworks projects for things like PowerPivot demos, since that data's not been cared for, I need something handy.

This script generates approximately 20 years of data. It uses an intelligent surrogate key and begins counting at 2014-01-01. It generates date part names and their numeric values for sorting purposes.

SELECT
    -- 20 years, approximate
    TOP (20 * 365)
    CAST(CONVERT(char(8), D.FullDate, 112) AS int) AS DateKey
,   D.FullDate
,   YEAR(D.FullDate) AS YearValue

,   'Q' + DATENAME(QUARTER, D.FullDate) AS QuarterName
,   DATEPART(QUARTER, D.FullDate) AS QuarterValue

,   DATENAME(mm, D.FullDate) AS MonthName
,   MONTH(D.FullDate) AS MonthValue

,   DAY(D.FullDate) AS DayValue

,   DATENAME(dw, D.FullDate) AS DayOfWeekName
,   DATEPART(dw, D.FullDate) AS DayOfWeekValue

FROM
(
    SELECT
        DATEADD(d, D.number, BOT.StartDate) AS FullDate
    FROM
        (
            SELECT
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS number
            FROM
                sys.all_columns AS AC
        ) D
        CROSS APPLY
        (
            -- Start date
            SELECT CAST('2014-01-01' AS date) AS StartDate
        ) BOT
) D;

Thursday, September 25, 2014

Remove all MS_Diagram extended properties

When you create a view in SSMS using the wizard, it retains information for the layout designer. There's no need for this, especially as it makes my database comparisons messy.

I had already run through the following articles the first time to nuke all those metadata items. Then they restored over my dev environment and I had not saved my scripts.

  • http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/25/generate-drop-statements-for-all-extended-properties.aspx
  • http://www.sqlservercentral.com/articles/Metadata/72609/
  • http://blog.hongens.nl/2010/02/25/drop-all-extended-properties-in-a-mssql-database/
  • http://msdn.microsoft.com/en-us/library/ms178595.aspx

My approach is a wee different. I'm going to use a cursor to enumerate through my results and then use sp_executesql instead of doing the string building the other fine authors were using.

This script will remove all the MS named objects attached to views. I hope that you can easily adapt this to stripping the extended properties from any object by adjusting the join to other system objects and/or using level2 specifications

DECLARE 
    @Query nvarchar(4000) = 'EXECUTE sys.sp_dropextendedproperty @name, @level0type, @level0name, @level1type, @level1name, @level2type, @level2name;'
,   @ParamList nvarchar(4000) = N'@name sysname, @level0type varchar(128), @level0name sysname, @level1type varchar(128), @level1name sysname, @level2type varchar(128), @level2name sysname'
,   @SchemaName sysname
,   @ObjectName sysname
,   @PropertyName sysname
,   @ObjectType varchar(128);

DECLARE CSR CURSOR
READ_ONLY
FOR 
SELECT
    S.name AS SchemaName
,   V.name AS ObjectName
,   EP.name AS PropertyName
,   O.type_desc AS ObjectType
FROM 
    sys.extended_properties AS EP
    INNER JOIN 
        sys.views V
        ON V.object_id = EP.major_id 
    INNER JOIN 
        sys.schemas S
        ON S.schema_id = V.schema_id 
    INNER JOIN
        sys.objects AS O
        ON O.object_id = V.object_id
WHERE 
    EP.minor_id = 0
    -- The underscore is a single character wild card, need to escape it
    AND EP.name LIKE 'MS_Dia%';


OPEN CSR;

FETCH NEXT 
FROM CSR INTO
    @SchemaName 
,   @ObjectName 
,   @PropertyName 
,   @ObjectType;
WHILE (@@fetch_status = 0)
BEGIN

    EXECUTE sys.sp_executesql 
        @Query
    ,   @ParamList
    ,   @name=@PropertyName
    ,   @level0Type = 'SCHEMA'
    ,   @level0Name = @SchemaName
    ,   @level1Type = @ObjectType
    ,   @level1Name = @ObjectName
    ,   @level2type = NULL
    ,   @level2Name = NULL;


    FETCH NEXT 
    FROM CSR INTO
        @SchemaName 
    ,   @ObjectName 
    ,   @PropertyName 
    ,   @ObjectType;
END

CLOSE CSR;
DEALLOCATE CSR;

Tuesday, September 23, 2014

Behind the scenes with Integration Services Catalogs Create Catalog

What happens you create the SSISDB catalog for the first time? I had no idea and this question had me wondering why in the world they were doing a restore. There's only one way to find out.

To profiler, we go! I ran a TSQL Trace against a 2014 instance (that had previously had an SSISDB catalog so this might be missing some conditional checks). Line breaks added for readability

First step, it creates a table variable and populates it with values from the SSISDB catalog, if the catalog exists.

exec sp_executesql N'
        --Preparing to access the Catalog object
        DECLARE @t_catalogs TABLE (
        Name sysname COLLATE SQL_Latin1_General_CP1_CI_AS,
        EncryptionAlgorithm nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS,
        SchemaVersion int,
        SchemaBuild nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS,
        OperationLogRetentionTime int,
        MaxProjectVersions int,
        OperationCleanupEnabled bit,
        VersionCleanupEnabled bit,
        ServerLoggingLevel int,
        OperationLogNumberOfRecords int,
        VersionLogNumberOfRecords int)

        IF DB_ID(''SSISDB'') IS NOT NULL
        BEGIN
        INSERT INTO @t_catalogs VALUES(
        ''SSISDB'',
        (SELECT [property_value] FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''ENCRYPTION_ALGORITHM''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''SCHEMA_VERSION''),
        (SELECT [property_value] FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''SCHEMA_BUILD''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''RETENTION_WINDOW''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''MAX_PROJECT_VERSIONS''),
        (SELECT CAST([property_value] AS BIT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''OPERATION_CLEANUP_ENABLED''),
        (SELECT CAST([property_value] AS BIT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''VERSION_CLEANUP_ENABLED''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''SERVER_LOGGING_LEVEL''),
        (SELECT COUNT(operation_id) FROM [SSISDB].[catalog].[operations]),
        (SELECT COUNT(object_id) FROM [SSISDB].[catalog].[object_versions])
        )
        END



SELECT
''IntegrationServices[@Name=''
+ quotename(CAST(SERVERPROPERTY(N''Servername'') AS sysname),'''''''')
+ '']''
+ ''/Catalog[@Name=''
+ ''''''''
+ REPLACE((SELECT Name from @t_catalogs), '''''''', '''''''''''') + '''''''' + '']'' AS [Urn],
(SELECT Name from @t_catalogs) AS [Name],
(SELECT EncryptionAlgorithm from @t_catalogs) AS [EncryptionAlgorithm],
(SELECT SchemaVersion from @t_catalogs) AS [SchemaVersion],
(SELECT SchemaBuild from @t_catalogs) AS [SchemaBuild],
(SELECT OperationLogRetentionTime from @t_catalogs) AS [OperationLogRetentionTime],
(SELECT MaxProjectVersions from @t_catalogs) AS [MaxProjectVersions],
(SELECT OperationCleanupEnabled from @t_catalogs) AS [OperationCleanupEnabled],
(SELECT VersionCleanupEnabled from @t_catalogs) AS [VersionCleanupEnabled],
(SELECT ServerLoggingLevel from @t_catalogs) AS [ServerLoggingLevel],
(SELECT OperationLogNumberOfRecords from @t_catalogs) AS [OperationLogNumberOfRecords],
(SELECT VersionLogNumberOfRecords from @t_catalogs) AS [VersionLogNumberOfRecords]
WHERE
(CAST(SERVERPROPERTY(N''Servername'') AS sysname)=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'RHUDAUR\DEV2014'
It's an interesting query result
UrnNameEncryptionAlgorithmSchemaVersionSchemaBuildOperationLogRetentionTimeMaxProjectVersionsOperationCleanupEnabledVersionCleanupEnabledServerLoggingLevelOperationLogNumberOfRecordsVersionLogNumberOfRecords
IntegrationServices[@Name='RHUDAUR\DEV2014']/Catalog[@Name='SSISDB']SSISDBAES_256312.0.2000.83651011100

Next up, a quick check to make sure I'm in the admin role

SELECT ISNULL(IS_SRVROLEMEMBER ('sysadmin'), 0)

Does the database already exist? The parameterization of this plus the table variable used in the first looks like the code is designed for more than one "SSISDB" catalog. Why you'd want such a thing is an entirely seperate question.

exec sp_executesql N'SELECT name FROM msdb.sys.sysdatabases WHERE name = @dbname',N'@dbname nvarchar(6)',@dbname=N'SSISDB'

Random check for our version. I assume whatever is issuing the commands uses this.

select SUBSTRING (CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)),1,2);

Hit the registry to see where we have installed the SSIS (DTS) components.

declare @key_value nvarchar(1024);
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
,'SOFTWARE\Microsoft\Microsoft SQL Server\120\SSIS\Setup\DTSPath'
, N''
, @key_value output;
select @key_value;

Check to verify the .bak file exists. I presume this is built off the preceding query. Returns a 1 if it was found.

DECLARE @CatalogFileExists bit
BEGIN
    DECLARE @CatalogFile nvarchar(1024)
    SELECT @CatalogFile = N'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak'
    CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
    INSERT #t EXEC xp_fileexist @CatalogFile
    SELECT TOP 1 @CatalogFileExists=file_exists from #t
    DROP TABLE #t
END
SELECT @CatalogFileExists

Now that we know where a backup is and that we're going to create a database called SSISDB, the code verifies the database doesn't already exist. If it does, it terminates the operation with an error The database, 'SSISDB', already exists. Rename or remove the existing database, and then run SQL Server Setup again.

IF DB_ID('SSISDB') IS NOT NULL
    RAISERROR(27135, 16, 1, 'SSISDB') 

Now we recheck the version except this time we force the failure of the script. I like seeing a reference to "Denali" in the error message.

IF CAST( SUBSTRING (CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)),1,2) AS INT ) < 11
    RAISERROR (27193,16,1, 'Denali or later') WITH NOWAIT

As part of the installation of the SSISDB, we must have CLR enabled. I already had mine enabled so I expect there's a step after this that I didn't capture.

SELECT [value_in_use] FROM sys.configurations WHERE [name] = 'clr enabled'

This step builds out the path where the data files should be located

DECLARE @path nvarchar(1024) = Convert(nvarchar(1024),ServerProperty('MasterFile'));
SELECT @path = SUBSTRING(@path, 1, CHARINDEX(N'master.mdf', LOWER(@path)) - 1);
SELECT @path;

We check to see if the SSISDB .mdf file exists

DECLARE @CatalogFileExists bit
BEGIN
    DECLARE @CatalogFile nvarchar(1024)
    SELECT @CatalogFile = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.mdf'
    CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
    INSERT #t EXEC xp_fileexist @CatalogFile
    SELECT TOP 1 @CatalogFileExists=file_exists from #t
    DROP TABLE #t
END
SELECT @CatalogFileExists

And we check to see if the SSISDB .ldf file exists

DECLARE @CatalogFileExists bit
BEGIN
    DECLARE @CatalogFile nvarchar(1024)
    SELECT @CatalogFile = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.ldf'
    CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
    INSERT #t EXEC xp_fileexist @CatalogFile
    SELECT TOP 1 @CatalogFileExists=file_exists from #t
    DROP TABLE #t
END
SELECT @CatalogFileExists

This step generates the files contained within our bak. I assume this is used to generate the next command.

exec sp_executesql N'RESTORE FILELISTONLY FROM DISK = @backupfile'
,N'@backupfile nvarchar(67)'
,@backupfile=N'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak'

Now we're cooking with gas. Here we actually perform the restore of the SSISDB backup.

exec sp_executesql N'RESTORE DATABASE @databaseName
FROM DISK = @backupFile  WITH REPLACE
,MOVE @dataName TO @dataFilePath
,MOVE @logName TO @logFilePath'
,N'@databaseName nvarchar(6),@dataName nvarchar(4),@dataFilePath nvarchar(75),@logName nvarchar(3),@logFilePath nvarchar(75),@backupFile nvarchar(67)'
,@databaseName=N'SSISDB'
,@dataName=N'data'
,@dataFilePath=N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.mdf'
,@logName=N'log'
,@logFilePath=N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.ldf'
,@backupFile=N'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak'

If for some reason the restore left the SSISDB in read-only mode, force it into read-write.

USE master;
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name]='SSISDB' AND [is_read_only] = 1)
ALTER DATABASE [SSISDB]
SET READ_WRITE WITH
ROLLBACK IMMEDIATE

At this point, we have an SSISDB but it's not secure. We have the ability to store sensitive data in there so we need to protect our jewels.

USE [SSISDB];

IF EXISTS (SELECT [name] FROM sys.symmetric_keys WHERE [name] = '##MS_DatabaseMasterKey##')
    DROP MASTER KEY

Secure our database with a master key

exec sp_executesql N'USE [SSISDB];
DECLARE @pwd nvarchar(4000) = REPLACE(@password, N'''''''', N'''''''''''');
EXEC(''CREATE MASTER KEY ENCRYPTION BY PASSWORD = '''''' + @pwd + '''''''');'
,N'@password nvarchar(20)',@password=N'pass@word1'

Create an asymmetric key from our assembly

IF NOT EXISTS(SELECT * FROM sys.asymmetric_keys WHERE name = 'MS_SQLEnableSystemAssemblyLoadingKey')
CREATE ASYMMETRIC KEY
    MS_SQLEnableSystemAssemblyLoadingKey 
FROM
    EXECUTABLE FILE = 'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll' 

I have no idea what this virtual account is for and why we'll drop it if it exists but so be it. I assume we're doing this to ensure it has the correct permissions in the next step.

IF EXISTS(SELECT [name] FROM sys.server_principals where name = '##MS_SQLEnableSystemAssemblyLoadingUser##')
DROP LOGIN ##MS_SQLEnableSystemAssemblyLoadingUser##

Create a login that can create unsafe assemblies. An unsafe assembly? Cats and dogs are signing leases at this very moment.

CREATE LOGIN ##MS_SQLEnableSystemAssemblyLoadingUser## FROM ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey
GRANT UNSAFE ASSEMBLY TO ##MS_SQLEnableSystemAssemblyLoadingUser##

I allowed the SSIS CLR to run on startup so the installing is obliging me. Here it drops it in case it existed.

IF EXISTS(SELECT name FROM sys.procedures WHERE name=N'sp_ssis_startup')
BEGIN
   EXEC sp_procoption N'sp_ssis_startup','startup','off'
   DROP PROCEDURE [sp_ssis_startup]
END

Creation of the stored procedure dbo.sp_ssis_startup in master.

CREATE PROCEDURE [dbo].[sp_ssis_startup]
AS
SET NOCOUNT ON
 /* Currently, the IS Store name is 'SSISDB' */
 IF DB_ID('SSISDB') IS NULL
  RETURN

 IF NOT EXISTS(SELECT name FROM [SSISDB].sys.procedures WHERE name=N'startup')
  RETURN

 /*Invoke the procedure in SSISDB  */
 EXEC [SSISDB].[catalog].[startup]

I have no idea why they have IF here but they do.

IF (1=1)
BEGIN
 /* Run sp_ssis_startup when Sql Server restarts */
 EXEC sp_procoption N'sp_ssis_startup','startup','on'
END

At this point, we are going to get busy with setting up maintenance for the SSISDB. Drop any jobs named "SSIS Server Maintenance Job" and hope that you didn't have anything vitally important with the same name.

IF EXISTS (SELECT name FROM sysjobs WHERE name = N'SSIS Server Maintenance Job')
EXEC sp_delete_job
    @job_name = N'SSIS Server Maintenance Job' ;

Drop an existing virtual login that will be associated to our job.

IF EXISTS(SELECT * FROM sys.server_principals where name = '##MS_SSISServerCleanupJobLogin##')
    DROP LOGIN ##MS_SSISServerCleanupJobLogin##

Create our login

DECLARE @loginPassword nvarchar(256)
SELECT @loginPassword = REPLACE (CONVERT( nvarchar(256), CRYPT_GEN_RANDOM( 64 )), N'''', N'''''')
EXEC ('CREATE LOGIN ##MS_SSISServerCleanupJobLogin## WITH PASSWORD =''' +@loginPassword + ''', CHECK_POLICY = OFF')

Disable the login we just created...

ALTER LOGIN ##MS_SSISServerCleanupJobLogin## DISABLE

Create our job, owned by the disabled login above

EXEC dbo.sp_add_job
    @job_name = N'SSIS Server Maintenance Job',
    @enabled = 1,
    @owner_login_name = '##MS_SSISServerCleanupJobLogin##',
    @description = N'Runs every day. The job removes operation records from the database that are outside the retention window and maintains a maximum number of versions per project.'

Cleanup, aisle 1. The job step runs a stored procedure to do the cascading deletes


DECLARE @IS_server_name NVARCHAR(30)
SELECT @IS_server_name = CONVERT(NVARCHAR, SERVERPROPERTY('ServerName'))

EXEC sp_add_jobserver  @job_name = N'SSIS Server Maintenance Job',
                       @server_name = @IS_server_name

EXEC sp_add_jobstep
    @job_name = N'SSIS Server Maintenance Job',
    @step_name = N'SSIS Server Operation Records Maintenance',
    @subsystem = N'TSQL',
    @command = N'EXEC [internal].[cleanup_server_retention_window]',
    @database_name = N'SSISDB',
    @on_success_action = 3,
    @retry_attempts = 3,
    @retry_interval = 3;

Clean up the old versions of the .ispac files

EXEC sp_add_jobstep
    @job_name = N'SSIS Server Maintenance Job',
    @step_name = N'SSIS Server Max Version Per Project Maintenance',
    @subsystem = N'TSQL',
    @command = N'EXEC [internal].[cleanup_server_project_version]',
    @database_name = N'SSISDB',
    @retry_attempts = 3,
    @retry_interval = 3;

Create a schedule to run daily at midnight. Again, this might not be optimal for your ETL processing window (see link above).

EXEC sp_add_jobschedule
    @job_name = N'SSIS Server Maintenance Job',
    @name = 'SSISDB Scheduler',
    @enabled = 1,
    @freq_type = 4, /*daily*/
    @freq_interval = 1,/*every day*/
    @freq_subday_type = 0x1,
    @active_start_date = 20001231,
    @active_end_date = 99991231,
    @active_start_time = 0,
    @active_end_time = 120000

Finally, we need to ensure our user can run the two stored procedure in the job (makes sense). Drop that user, like it's hot...

USE SSISDB
IF EXISTS (SELECT name FROM sys.database_principals WHERE name = '##MS_SSISServerCleanupJobUser##')
    DROP USER ##MS_SSISServerCleanupJobUser##

Add the user back in, based on our disabled login.

CREATE USER ##MS_SSISServerCleanupJobUser## FOR LOGIN ##MS_SSISServerCleanupJobLogin##

Give them rights to run internal.cleanup_server_retention_window

GRANT EXECUTE ON [internal].[cleanup_server_retention_window] TO ##MS_SSISServerCleanupJobUser##

Give them rights to run internal.cleanup_server_project_version

GRANT EXECUTE ON [internal].[cleanup_server_project_version] TO ##MS_SSISServerCleanupJobUser##

References

Sunday, September 21, 2014

Biml The connection string format for Connection is not valid

A brief post on resolving errors with Connections in Biml. A friend had sent me some sample Biml and the latest batch wasn't validating. It looked valid, ish, but I was getting
Description: The connection string format for Connection X is not valid Recommendation: Please supply one or more components of the form X=Y, separated by semicolons The supplied code was
<Connection 
    Name="CM_OLE" 
    ConnectionString="Data Source=.\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;" 
/>
So, what's the issue? It's the trailing space on the ConnectionString. It seems my friends text editor/email program decided to "help" his code by adding that space.

Resolution

Fix the connection string by removing the trailing space inside the ConnectionString property.

Monday, August 4, 2014

Dead SQL Objects Code Query

Bring out your dead!

Just a quick post to share the script. At my current client, they have a large codebase that's not been cared for. I give them credit for generally avoiding dynamic SQL but the proliferation of autogenerated code has resulted in thousands, not an exaggeration, of objects that were generated and never used. I had run down fixing a dependency chain only to learn the rootmost object was broken as the day is long.

There has to be a better way of figuring this out. SQL Server Data Tools you say? Yeah, it took 2 days to reverse engineer the database and validate the dependencies only to run out of memory. I settled on sys.sp_refreshsqlmodule to at least give me a fighting chance of finding out what's broken in the database. The following query generates a list of all non-schemabound objects and then calls sys.sp_refreshmodules against the objects. if it raises an error, I drop it into a table so I can inspect it later as well as print out an error message. Quick and dirty, there's probably some logic issues in there, things that aren't dead yet, but this at least helps me triage the database.

-- This script attempts to generate a report on whether all the SQL modules are valid
-- in a database. This can generate false positives as there is no intelligence
-- built into the order to ensure object dependencies are followed.
SET NOCOUNT ON;
DECLARE 
    @SchemaName sysname
,   @ObjectName sysname
,   @QualifiedObjectName sysname
,   @QueryTemplate nvarchar(1000) = N'EXECUTE sys.sp_refreshsqlmodule @name;'
,   @ParamList nvarchar(20) = N'@Name sysname';


DECLARE ObjectCursor CURSOR READ_ONLY
FOR 
SELECT ALL
    SCHEMA_NAME(S.schema_id) AS SchemaName
,   S.name AS ObjectName
FROM 
    sys.objects AS S
    INNER JOIN
    (
        -- schema bound objects can't get recompiled
        SELECT
            SM.object_id
        FROM
            sys.sql_modules AS SM
        WHERE
            SM.is_schema_bound = 0
    ) AS SM
        ON SM.object_id = S.object_id
WHERE 
    S.is_ms_shipped = 0
    -- adjust this list as needed
    AND S.type in ('FN', 'IF', 'IT', 'P', 'TF', 'TR', 'V')
ORDER BY S.schema_id
;

DECLARE @ManualCleanup TABLE
(
    SchemaName sysname
,   ObjectName sysname
,   SQLStatement nvarchar(1000) NOT NULL
);

OPEN ObjectCursor

FETCH NEXT 
FROM ObjectCursor 
INTO @SchemaName
,   @ObjectName;

WHILE (@@fetch_status > -1)
BEGIN
    SET @QualifiedObjectName = QUOTENAME(@schemaName) + '.' + QUOTENAME(@ObjectName);
    BEGIN TRY
        EXECUTE dbo.sp_executesql @queryTemplate, @ParamList, @Name = @QualifiedObjectName;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        INSERT INTO
            @ManualCleanup
        (
            SchemaName
        ,   ObjectName
        ,   SQLStatement
        )
        SELECT
            @SchemaName
        ,   @ObjectName
        ,   'EXECUTE sys.sp_refreshsqlmodule @name = N''' + @QualifiedObjectName + ''';';
    END CATCH

    FETCH NEXT 
    FROM ObjectCursor 
    INTO @SchemaName
    ,   @ObjectName;
END

CLOSE ObjectCursor
DEALLOCATE ObjectCursor


SELECT 
    MC.* 
FROM 
    @ManualCleanup AS MC;

Tuesday, July 1, 2014

I am not an MVP

I am not a SQL Server MVP, but I'm happy I had the opportunity to go through the process. I am grateful to the awesome but unnamed MVP that tossed my hat into the ring.

It seems that every quarter there's always some heartache and disappointment from those that didn't make the cut. I know I kept hoping I'd just magically show up on Microsoft's radar and *poof*, MVP. Now that I know more about how this works, I thought it might be helpful to share it with others.

What is the process?

It starts rather simply: someone, even you, fills out a nomination form on the MVP site. For me, this happened in early February. I then received an email inviting me to fill out a form covering the past 15 or 18 months of activity. I guess this is the first culling of nominees. You only have one shot while filling out this form so make sure it's complete before hitting submit. There's no opportunity to revise it once you click submit but it does allow you to save your progress as you fill it out.

Phase 2

At the end of April, the MVP Community Program Manager reached out to me and had me fill out more forms, this time only covering the 12 months prior to my candidacy period so May 1, 2013 to April 30, 2014. The crux of that was "all I am looking for is an organized, concise and efficient breakdown of your activities that is simple to digest."

Phase 3

At some point between the submission, the folks evaluate all the candidates and make their selection. You aren't notified if you don't make the cut. The email states "Currently you are still under consideration for a Microsoft MVP Award. If you are awarded as an MVP, you will receive a separate communication around beginning of July 2014 from the MVP Award Program administrator. If not awarded, no notification will be sent." A little birdie told me that others have gotten their positive acknowledgement already (June 26) and since my email's at inbox zero, I can draw my own conclusions.

Why me

Beyond my general arrogance and hubris, I thought I'd done quite a bit for the community but since I had nothing to measure against, it was hard to tell where I fell on the spectrum. It was a bit frustrating that the accumulative effect doesn't count. I've organized all 5 of Kansas City's SQL Saturdays, but only one of them counted since that was within the time boundary.

What I thought qualified me from a quantitative perspective was

  • blogging
  • presenting
  • organizing
  • stackoverflowing
This past year was my most productive from a blog perspective. The Biml stuff dang near writes itself. I averaged a presentation, either user group or SQL Saturday, a month with one of those being the 2013 Summit. SQL Saturday 191 was our biggest event yet with 300+ attendees. On StackOverflow I earned my gold badge for SSIS. Gold badge definition: Earned at least 1000 total score for at least 200 non-community wiki answers in the ssis tag. That's going to be a pretty tall order to top.

Infographic

I collected a lot of numbers to try and tell the story in an interesting way and while I have Tufte books, I don't live and breath it. But my amazing, awesome and super talented coworker, Meagan Longoria (b|t) does. She can quote you Tufte and Few, chapter and verse, and she was kind enough to pull this infographic together.

Takeaways

Keep track of what you do. The Phase 2 document breaks activity down into
  • Speaking Engagements
  • User Group Participation (include presentations under Speaking)
  • Event Organizer or Chair (other than user group leader/organizer)
  • Forum Activity
  • Blogging
  • Publishing
  • Twitter/Social Media
  • Other

For Speaking engagements, they will want to know how many people were there. For UG activity, they want to know total group size, frequency of meetings, and average attendance. Event Organizer - how many people attended. Forums, they are interested in quantity of answers plus any answers that you wanted to provide as a highlight. Blogging, they care about your numbers reached per month. Publishing, what and how many things? Twitter/social media, it only asks how many followers you had an links to your account.

That was a pain the backside to pull some of that data together. I had a general idea of how many people were at my UG/SQL Saturday presentations, but was that room big? It seemed full but was full 25 people or 45? While data.stackexchange.com was a cute way to get data out of the site, it still left me wanting as I had to change my user id per site.

Beyond that, since my recipe didn't work, I can't say what else is needed if you want to be an MVP. I am thankful for everyone who helped me along the way. And to those newly awarded or renewed, a hearty congratulations. For those that were not renewed, thank you for your efforts. #mvpbuzz

Update 2014-10-01

I was nominated again for Q3 of this year. The process was the same and my efforts weren't any different. After talking to other MVPs about it, I made two differences in my "sales" pitch. The first was to help the committee understand the reach of my activities. The second was to find some advocates that were aware of my efforts.

Phase 4

On September 8th, I was notified that my nomination was accepted and would become effective today, October 1st. As part of that, they need more information about your name and such that you have to get back to them so you can get set up in their MVP system. Legally, I am not Bill. I'm William. The name I supplied when responding to that email was Bill because that's what all my SEO should be using but that also drove the creation of an electronic NDA which I couldn't alter to use my legal name.

Phase 5

At 9:48 local time, I received my official email from mvpaward.com telling me to go fill out the forms on that site. Maybe it's just my giddiness but I couldn't tell whether I was doing the right things in there. For example, there's a private Connect system for the MVPs so you need to set up a Connect account. I had a Connect account already and didn't see anything to click to specify "let me in" although they have all this text about the special Mac Connect site. The other clicks led me back to MVPAward.com which said go do the Connect site and so I assumed I had whatever filled out and went on with the steps.

Wrap up

To those that have stepped out of the program, thank you for your efforts. To my fellow SQL Server inductees, no rest for the wicked. See you at the double Summit in November.

Tuesday, June 17, 2014

Solving the wrong problem

One of the new architects sent out an email and in fine engineer fashion, I decided to focus on the question that was asked and obtusely miss the point. Plus, it was an opportunity for me to dust off some long forgotten python programming.

Problem definition

If: A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
Is equal to;
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

Then

H+A+R+D+W+O+R+K ;
8+1+18+4+23+15+18+11=98%

K+N+O+W+L+E+D+G+E ;
11+14+15+23+12+5+4+7+5=96%

L+O+V+E;
12+15+22+5 = 54%

L+U+C+K ;
12+21+3+11 =47%

None of them makes 100%.

Then what makes 100% ???

Is it Money?
NO !!! M+O+N+E+Y= 13+15+14+5+25=72%

Leadership?
NO !!! L+E+A+D+E+R+S+H+I+P= 12+5+1+4+5+18+19+8+9+16=97%

Every problem has a solution, only if we perhaps change our "ATTITUDE".

A+T+T+I+T+U+D+E ;
1+20+20+9+20+21+4+5 = 100%

 
It is therefore OUR ATTITUDE towards Life and Work that makes OUR Life 100% Successful.. 

What makes 100%?

Ignoring that it's not a percent, but really what words add up to 100 given the supplied 1 based translation system... well, that just sounded like a fun nerdy challenge.

Python to the rescue

If you've never heard me talk about it, I loved the 3ish years I spent writing python code to parse all these random source files we'd receive and push it into our marketing databases. Once you get over the whitespace thing with python, you'll likely agree that it's one of the more beautiful and elegant languages out there.

Capital A is ASCII value 65; Z is 90; a is 97 and z is 122. Knowing this, if I make everything uppercase, convert each letter to its ordinal value and subtract 64, and then add up all the values, I should be able to identify the value of a particular word. Piece of cake.

# create a variable to hold our translation between ASCII and percent value
offset = 64

def WordValue(word):
    # compute the value for each character
    # convert to upper case and subtract 64 to get it to 1-26
    return sum([(ord(c)-offset) for c in word.upper()])

# Create a word list. I cribbed from http://www.manythings.org/vocabulary/lists/l/
f  = open('/Users/bfellows/qualities.txt')

# Create an empty list to hold all matching words. Since my source has duplicates,
# I will want to filter them out
l = []

# rip through my file line by line
for line in f.read().splitlines():
    # Test whether our summed value matches the target
    if WordValue(line) == 100:
        # keep track of everything that matches
        l.append(line)

# Enumerate through all of our matches
for item in set(l):
    # Display matches to the screen
    print item

Source data

As I indicated in the code, I simply took some of the word lists from manythings.org and appended them to a text file---one row per word. Sample data follows
a
an
able
about
above
abuse
accept
accident
accuse
across

What makes 100%?

Based on the 2360 words in my source file, besides Attitude, the following words will also give 100%
  • prevent
  • telescope
  • Congress
  • hospital
  • ornament
  • telephone
  • boycott
  • culture
  • inflation
  • excellent
  • writing
  • interfere
  • repress
  • lightning
Now I'm torn between re-writing this in R versus trying to use the native system dictionaries...