A Quick Note About Data Driven Subscriptions

I recently changed jobs and have much more to do with maintaining SSRS native mode servers and configuring Subscriptions -- both Data Driven and regular.

We will be cleaning up the Data Driven subscriptions with the goal of making them more consistent, easier to deploy, and more conducive to being updated. One idea, that will be expanded in later posts, is creating a function that can deliver values for Subject, To, Body, etc.

A question quickly arose. Can the provided parameters, @ReportName and @ExecutionTime be used with Data Driven subscriptions? The short answer is, Yes!

For example, returning a string from the Data Driven query for Subject, for example, with @ReportName in it, will cause SSRS to substitute the report name. Likewise for @ExecutionTIme.

This should mean a little more direct reuse between subscriptions. 

When it comes to Triggers, I have a debugging story that suggests a good general principle (Thanks Steve Jones for hosting T-SQL Tuesdayon Triggers). On at least two separate occasions, someone has asked for help, because they are getting the error:

“SQL ERROR: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression”

The request is accompanied by a query with either a benign subquery (yup, could use a JOIN):

Update Sales

Set tax_rate = 0.07

Where state_id = (Select state_id from States where state_name = ‘Texas’)

Or, more confusingly, no subquery at all

Update Sales

Set tax_rate = 0.07

Where state_id = 42

The answer is to check for a Trigger that is coded to only handle a single record. There will be a query in the code looking up the key (for the single record) from Inserted or Deleted in a subquery (Question of the Day?).

The Trigger was usually written as part of an application and the developer knows, “We only Insert or Update a single record at a time.” That is, until a one-off data load is needed, or an application integration initiative changes the rules.

My general principle is that a Trigger should be written to operate on a set of records, no matter what the primary use case is. A corollary is that, in the case where the Trigger must be restricted to a single record, that it should validate that it only has one record, or raise a meaningful error when there are more. And do not forget there can be no records!

Epilogue

When doing technical interviews, I would occasionally as how to call a trigger. The usual answer was to the effect “I’ve used them in the past, but don’t recall the specifics.” People who knew their stuff answered straight up, but usually looked a bit askance at me…

I hold in my hand the final envelope...

This month of poetry is ending more with a whimper than a bang, but The Road Not Taken by Robert Frost still seems like a fitting poem on which to end. This exercise is clearly not on my beaten path, but I have enjoyed it and gotten some good feedback.

Putting these together made me think about other aspects of oral tradition. Punch lines of jokes and movie quotes are often part of a team's culture at work. Many of the better organizations have shared stories, of adversity, success, or special behaviors. One of my employers had a number of corporate stories that helped focus my efforts.

Think about the poems that influence your life. Ones that make you happy, help you to weather tough times, or remind you of special people.

If you applauded today's title, then I must quote Karnak, "May a weird holyman recite dirty limmericks to your sister."

Enjoy

Fools Like Me

Today's poem is the famous Trees by Joyce Kilmer. This poem possibly understates the grandeur and beauty of trees -- particularly the larger and older ones. This poem has always struck me as something that should be sung like a minstral tune.

As a kid I climbed my fair share of trees. In the last few years, I've heard of Recreational tree climbing and thought I'd like to give it a try. It has not seemed to caught on in Texas, but I might get a chance when visiting Georgia.

Enjoy

Well, once more...

We could not have a National Poetry Month without including The Raven by Edgar Allan Poe. It is one of his most famous works and often studied in schools for its imagery and alliteration. Even young kids can enjoy a little scare from this poem.

During my 90's poetry kick this is one I committed to memory, for a number of reasons. I even goaded my cube-mate (we stared at each other all day) to learn it with me. We often started our day with a recitation and discussed unfamiliar words or the meaning of a stanza. The line "Respite, respite and nepenthe" has come in handy in a crossword puzzle or two.

I had forgotten that we studied this in school, until I remembered my teacher's antics. After reading a very dramatic and ominous "Nevermore" he said, "Actually, it was probably more like 'Nevermore, nevermore'" adopting a Polly Parrot voice.

Enjoy

Out with the bad air...

Today's poem is another selection from "A Children's Garden of Verses", Where Go the Boats by Robert Louis Stevenson. This poem makes me think of my favorite "Curious George" book where he folds newspapers into boats. I read the book and folded a paper for each of my daughter's classes as "mother reader".

Here is a fun link with Bullwinkle reciting the poem, with just a few problems.

Enjoy

Moon beams...

Today's poem was also shared with me, by a colleague, back in 1998. It is a piece of ancient Chinese poetry called Drinking Alone with the Moon by Li Bai. She learned it as part of applying to school in mainland China. It provided a differentiator besides grades and test scores.

The third and fourth lines --

Till, raising my cup, I asked the bright moon
To bring me my shadow and make us three.

are famous and she taught me how to pronounce them in Mandarin. I searched for good links and was thrilled with the one above. It has a pronunciation guide for each character -- read right to left and the title is included. My time in Taiwan equiped me to recognize the characters for 1, 3, and people (ren). This link gives a number of possible translations.

Enjoy

Music when its good...

Today's selection is really a song, but it is poetic in its own right. I Love by Tom T. Hall is a song that I sang to both my girls when they were young, although I would substitute rootbeer for bourbon. I became a fan of his quite by accident. A cassette tape was left in my player in college. His style of story telling reminds me, a bit, of my grandfather's.

In using a song as poetry, I'm reminded of a "Saturday Night Live" skit. It had a high school english class with kids submitting 70's lyrics as original poetry.

Enjoy

Water, Gin, and Beer

Today's poem was a favorite of my father. Gunga Din by Rudyard Kipling is the story told by a British soldier in India about the waterboy. While the narrator views Gunga Din as an inferior, he ultimately respects him. This is a poem likely to raise eyebrows in our politically correct world.

The poem is interesting as each stanza has a second part, almost like a chorus, starting "Din, Din, Din...". The most famous part of the story are the final lines.

Tho' I've belted you an' flayed you,
By the livin' Gawd that made you,
You're a better man than I am, Gunga Din!

Enjoy

Marmalade?

Today's poem was often presented as a short play on "Captain Kangaroo" with liberal use of wigs and hats. The King's Breakfast by A. A. Milne tells the story of a king asking for butter and a reluctant cow finally providing it. The king insists, "Nobody, can call me a fussy man".

It feels like an analogy could be made between this poem and customer service or even requirements gathering for projects. Mostly, I think of Bob Keeshan in a Heidi wig.

Enjoy

Go ask Alice...

What do all the strange words mean in today's poem, Jabberwocky by Lewis Carroll? Quite a few are just made up and mean nothing, while the meaning of some could be as clear as common words like "smog" and "stacation". Better that I leave the full explanations to the experts.

My introduction to this poem came in school and it is one of the earliest poems I committed to memory. We didn't have as clear an explanation as to the meaning, however.

Enjoy

Building at home...

Today's poem is a childhood favorite of mine from second grade. While I learned it from my english book, Block City by Robert Louis Stevenson was published as part of his collection of poems called "A Child's Garden of Verses". A number of cherished children's poetry came from that book.

Years ago I searched for it on the web and the first hit was a page discussing Object Oriented Programming which used a couple of lines as an opening quote. There are a number of parallels between software and this poem. The building blocks are objects or components. The poem suggests design patterns with the make up of the city, "A kirk and a mill and a palace besides". System development is like building a make-believe world from an internal vision. Its not uncommon to put things together only to throw them away later.

Enjoy

A promise made...

Poetry is meant to be shared, so it is particularly fun to learn of a poem from someone else. Today's poem The Cremation of Sam McGee by Robert W. Service is such an example. It is one that a friend and work colleague memorized in school.

We often talk about this poem when projects and deadlines are based on our performance. The line "A promise made is a debt unpaid" can often keep one going, although it is not uncommon to exclaim, "Oh God, how I loathed the thing!"

I lived, for years, in places where the winters were cold. Now, having lived in the south for so long, I can relate much better to old Sam McGee.

Enjoy

Poems and Picture Ladies

The elementary school, which I attended, used the "The Roberts English Series". Every other chapter or so would begin with a poem some of which may already have been discussed. Today's poem, The Charge of the Light Brigade by Alfred Tennyson is one example. I don't recall studying this poem and rather remember reading it from my older sister's book.

This poem was more sobering when I read it as an adult than as a child. It says as much about blind loyalty -- "...yours but to do and die" -- as it does about heroism -- "...into the jaws of death, into the mouth of hell".

Another elementary school tradition was the "picture lady", a parent volunteer position. The school district had a number of pieces of art, mostly pictures, which were rotated among the schools and in classrooms. The "picture lady" would bring a new piece each month and give some background. There was one piece that I associate with "Light Brigade" although there is not real connection. It was called "The Victor and the Vanquished" and was remarkable because the paint was built up on the canvas perhaps as much as 1/2 inch. It was a fairly gruesome picture. Of all the silly things I have found on the web, an image of this has alluded my searches.

I'm tempted to include the story about plates of tomatoes and the song "Stuck in the Middle with You", but this is getting a bit long.

Enjoy

What doesn't kill you...

Today's poem is probably less well known. It came up, for me, in an english class. Terence, this is stupid stuff by A. E. Housman is an interesting poem, because it answers the poet's critics about the depressing subjects of his works. He justifies his outlook as better preparing for possible troubles.

This poem appeals to me on several levels. Its rhyme scheme and alliteration make it fun to read. It makes some interesting comments on trying to drink away problems. The story about King Mithridates is intriguing.

The poem makes me recall some musings on leadership. We often look at leaders to step forward in times of crisis. I think we don't insist, enough, on leaders who layout plans and visions which prevent or lessen crises in the first place.

Enjoy

Rage, rage...

I remember hearing today's poem in a movie and I believe it was the Dudley Moore movie "Six Weeks". Do Not Go Gentle Into That Good Night is a well known poem by Dylan Thomas, written for his father. I cannot find a reference to it being in the film, but I know that I bought a book of Thomas' poetry in the same timeframe.

This page gives some good analysis of the form, rhyme, and meaning of the poem. It quotes Shakespeare's "Henry VI", but could have quoted "Galaxy Quest's" Commander Taggert -- "Never give up. Never surrender".

Enjoy

Miles to go...

This is the end of what has been a long week for me. And as Houston temperatures continue to rise to summer levels, it is refreshing to remember Stopping by Woods on a Snowy Evening by Robert Frost. As weary travelers we can stop and take a look around, but remember we still have far to go and much more to do.

This poem is the basis for the school song at my wife's alma mater Saint Mary-of-the-Woods College.

Enjoy

Look on my Works, ye Mighty, and despair!

This post's title does not refer to my blog, but rather reminds us of today's poem Ozymandias By Percy Bysshe Shelley. It is one that is often studied in school and is probably familiar. The web has some interesting discussion about what inspired it and the fact that it was part of a competition between two authors.

My family spent lived for a time in Egypt and we were able to visit the pyramids and monuments at Aswan, Luxor, and Alexandria. The poem captures the juxtaposition of impressiveness and decay, not to mention the sand.

Enjoy

"It's awful fun to be born at all."

We go back, today, to A.A. Milne and enjoy his poem Spring Morning. This poem is known, in part, because of being read by Arnold Schwarzenegger in his movie "Kindergarten Cop".

This is a fun and carefree poem. I like the stanza from the cloud's perspective, "Doesn't the sky look green today?" There is also a lot of truth to the question "What does it matter where people go?"

Enjoy

An odd couple...

Today, we have a pair of poems that are not usually grouped together. They are both quite famous and seem, to me, to have analysts finding great meaning in much the same way as modern art is often interpretted. Perhaps I'm being too cynical. The larger question is whether I'll regret the grouping toward the end of the month.

It was probably experiments in diversity in education which brought We Real Cool by Gwendolyn Brooks to the suburbs. The link has an audio clip of the author reciting and explaining the origin of the work. I recall mentioning this poem to a colleague a few weeks before hearing of the poet's death.

Another school book favorite is The Red Wheelbarrow by William Carlos Williams, considered the author's masterwork. The web has much to say about this poem. I always get the "chickens confused with "ducks" and replace the word "glazed" with "soaked" which probably hinders my appreciation.

Enjoy