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...

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

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...

Thursday, March 27, 2014

Biml - RebuildIndex Task

Biml - RebuildIndex Task

The maintenance task, Rebuild Index Task, described with Biml. By now you know the drill, it requires an ADO.NET Connection Manager to work.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package 
            ConstraintMode="Linear" 
            Name="Task_RebuildIndex">
            <Tasks>
                <RebuildIndex 
                    ConnectionName="CM_ADO_DB" 
                    DatabaseSelectionMode="All" 
                    ObjectSelectionMode="Tables" 
                    Name="RI All Tables">
                </RebuildIndex>
                
                <!-- 
                    Rebuild the index on a specific view
                -->
                <RebuildIndex
                    ConnectionName="CM_ADO_DB"
                    DatabaseSelectionMode="Specific"
                    ObjectSelectionMode="Views"
                    Name="RI Specific View"
                    ReindexPercentage="50"
                    ReindexWithOriginalAmount="false"
                    SortInTempDB="false"
                    SkipUnsupported="true"
                    KeepOnline="true">
                    <Databases>
                        <Database>AdventureWorks2012</Database>
                    </Databases>
                    <Objects>
                        <Object>Production.vProductAndDescription</Object>
                    </Objects>
                </RebuildIndex>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

The above Biml describes a package that creates two Rebuild Index Tasks. The first rebuilds on all the tables while the second targets a specific indexed view.

RI All Tables

Here we reindex all the tables in all the databases.

RI Specific View

This snippet of Biml describes reindexing a specific view, Production.vProductAndDescription in the AdventureWorks2012 database.

Specify Database

Specify the database(s) we should use. Normally, I'd have trimmed out the empty space at the top of the window there but I left it as is to point out the wasted real estate. It's like there's a missing title block.

Specify Object

Here we're selecting the one object we're interested in re-indexing.

Tuesday, March 11, 2014

Biml - Notify Operator Task

Biml - Notify Operator Task

The maintenance task Notify Operator contacts the pre-defined operators. It requires an ADO.NET connection manager to work. Specify the operator, subject and message contents and away you go.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2012;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />
    </Connections>

    <Packages>
        <Package ConstraintMode="Linear" Name="Task_NotifyOperator">
            <Variables>
                <Variable Name="OperatorName" DataType="String">Ops</Variable>
                <Variable Name="Subject" DataType="String">Look here</Variable>
                <Variable Name="Message" DataType="String">Things have gone south</Variable>
                <Variable Name="Query" DataType="String" EvaluateAsExpression="true">"
DECLARE @OpName sysname = 'Ops';

IF NOT EXISTS
(
    SELECT * 
    FROM msdb.dbo.sysoperators AS SO 
    WHERE SO.name = @OpName
)
EXECUTE msdb.dbo.sp_add_operator 
    @name = @OpName
,   @enabled = 1
,   @pager_days = 0
,   @email_address = N'spam@billfellows.net';
                "</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL 
                    ConnectionName="CM_ADO_DB" 
                    Name="SQL Create Operator">
                    <VariableInput VariableName="User.Query" />
                </ExecuteSQL>
                
                <NotifyOperator 
                    ConnectionName="CM_ADO_DB" 
                    Name="NO Ack">
                    <OperatorsToNotify>
                        <Operator>Ops</Operator>
                    </OperatorsToNotify>
                    <Subject>Ack</Subject>
                    <Message>Body of the notification</Message>
                    <Expressions>
                        <Expression PropertyName="Subject">@[User::Subject]</Expression>
                        <!--<Expression PropertyName="OperatorName">@[User::Subject]</Expression>-->
                        <Expression PropertyName="Message">@[User::Message]</Expression>
                    </Expressions>
                </NotifyOperator>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

A package is created with an Execute SQL Task to ensure our Operator exists and then an actual Notify Operator Task is dropped into our Control Flow.

NO Ack

This is our Notify Operator task and it acknowledges the errors on the server.

Expression

I could figure out how to configure the message and the subject but couldn't figure out how to set the Operator name via expression.

Monday, March 10, 2014

Biml - Message Queue Task

Biml - Message Queue Task

Why have one tool when you can multiples? In this case, queuing technologies. As a SQL Server professional, I was aware of Service Broker but there's a whole native queuing technology built into Windows: Microsoft Message Queuing. Until doing the research on the SSIS Message Queue Task, I had assumed it pulled from Service Broker. Yeah, well assumptions...

MSMQ Setup

I originally created a private queue on a different box than I was running my package on but the native Message Queue Task can't converse with a remote server. MSDN has a write up on how you can use a script task to talk to a remote private queue

Biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <MsmqConnection Name="CM_MSMQ" Path=".\private$\POC" />
    </Connections>

    <Packages>
        <Package ConstraintMode="Linear" Name="Task_MessageQueue">
            <Variables>
                <Variable Name="MessageSource" DataType="String">What God hath wrought</Variable>
                <Variable Name="MessageReceipt" DataType="String"></Variable>
            </Variables>
            <Tasks>
                <MessageQueue 
                    MsmqConnectionName="CM_MSMQ" 
                    Name="MQ Send Message"
                    >
                    <VariableInput VariableName="User.MessageSource"></VariableInput>
                    <Expressions>
                        <Expression PropertyName="MessageString">@[User::MessageSource]</Expression>
                    </Expressions>
                </MessageQueue>

                <MessageQueue 
                    MsmqConnectionName="CM_MSMQ" 
                    Name="MQ Receive Message"
                    RemoveFromQueue="true"
                    ReceiveMessageTimeOut="30"
                    >
                    <StringVariableOutput VariableName="User.MessageReceipt" />
                </MessageQueue>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

A package is created with a pair of Message Queue Tasks. One to send and one to receive. There are two Variables: MessageSource and MessageReceipt, both of type String. MessageSource is preloaded with a value of What God hath wrought. After execution, if all went well, that value will be loaded into MessageReceipt. All of this will use our MSMQ connection manager, CM_MSMQ.

CM_MSMQ

Here's a screenshot of what the connection manager looks like. The value of the path is .\private$\POC to indicate the message queue server is local, it's a private queue named POC.

MQ Send Message

A send message task will put our message onto our private queue, POC.

General tab

We have set the task to send a message

Send tab

Sent tab is set to send an unencrypted message. While the StringMessage property appears to be hard coded, that's just an artifact of how the UI works.

Expressions

The MessageString is actually configured based on our @[User::MessageSource] Variable.

MQ Receive Message

A receive message task will pull messages off the queue and assign their value to our Variable.

General tab

The task is configured to receive messages. As you'll see, there's no need for expressions on this task.

Receive tab

In the Receive tab, we remove them from the source queue and assign the message into our Variable User::MessageReceipt.

Execution Results

Green is good. Even better is that the value of @[User::MessageReceipt] matches @[User::MessageSource]

Thursday, March 6, 2014

Biml - Maintenance Cleanup Task

Biml - Maintenance Cleanup Task

The Maintenance Cleanup Task removes artifacts from maintenance plans, backups, reports, etc. As with other Maintenace Tasks, this requires an ADO.NET Connection Manager.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2012;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />
    </Connections>

    <Packages>
        <Package ConstraintMode="Linear" Name="Task_MaintenanceCleanup">
            <!-- 
                Backup\InstanceName\DBName\DIFF 
                Backup\InstanceName\DBName\FULL 
                Backup\InstanceName\DBName\LOG 
            -->
            <Variables>
                <Variable Name="FileExtension" DataType="String">bacon</Variable>
                <Variable Name="FolderBase" DataType="String">J:\Backup</Variable>
                <Variable Name="InstanceName" DataType="String">WESTMARCH$DEV2012</Variable>
                <Variable Name="DatabaseName" DataType="String">Adventureworks2012</Variable>
                <Variable Name="FolderPath" DataType="String" EvaluateAsExpression="true">@[User::FolderBase] + "\\" + @[User::InstanceName] + "\\" + @[User::DatabaseName] </Variable>
            </Variables>
            <Tasks>
                <!--
                    http://stackoverflow.com/questions/11571002/can-someone-help-explain-some-of-the-new-ssis-2012-properties 
                -->
                <MaintenanceCleanup 
                    ConnectionName="CM_ADO_DB" 
                    Name="MC Clean backup files" 
                    DeleteFromAllBricks="true"
                    AgeBased="true"
                    FileTypeSelected="BackupFiles"
                    OlderThanTimeUnits="4"
                    OlderThanTimeUnitType="Weeks"
                    >
                    <!-- Delete all the bacon -->
                    <FolderAndExtension 
                        Folder="C:\DrivenByVariable" 
                        FileExtension="VariableToo"
                        IncludeFirstLevelSubfolders="true"
                    >
                    </FolderAndExtension>
                    <Expressions>
                        <Expression PropertyName="FolderPath">@[User::FolderPath]</Expression>
                        <Expression PropertyName="FileExtension">@[User::FileExtension]</Expression>
                    </Expressions>
                </MaintenanceCleanup>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

A package is created with Variables to control the FileExtension and FolderPath that are removed. I'm basing the three Variables that build my FolderPath variable based on the default output of Ola's Maintenance plans.

MC Clean Backup Files

Great googly-moogly, someone's attempting to get rid of all the bacon! Well, at least any Backup files that are in the first-level subfolder, with an extension of bacon, that are older than 4 weeks.

Expressions

As you can see, I've applied an Expression to control both FileExtension and FolderPath based on our user Variables.