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