Happy Halloween

Many of you who follow me on Twitter have wondered about the rather strange photo I’ve had on my Twitter account this month. I promised to fill in everyone, so here it is, my way of saying Happy Halloween!

Several years ago at work we had a dress up day. I went all out, and got into Zombie mode. Which really wasn’t al that difficult, as most days we all sort of stagger around the office moaning “brains, brains, need brains”, at least until after that first pot of coffee.

So without further ado, here are some pics you can use to scare your coworkers. Just imagine their delight when they open up an e-mal with my lovely visage on it! (Note you can click on any photo to see it in full size.)


I can just hear it now. “This is Thriller, thrill of the night… “



Everybody boogie down!



Brains… want brains… with a side order of hash browns, biscuits, and a pumpkin spice chai tea latte






For some reason I’ve got a headache. Wonder why?



I know, I should really cut down on the in-between meal snacks.



They gave me a prize for best costume. I didn’t have the heart to tell them this is how I normally

look until I’ve consumed a few pots of coffee!




Happy Halloween Everyone




The Arcane Coder!



SQL Saturday Orlando Full Text Searching Session

On October 25th, 2008 I presented “Getting Started with SQL Server 2008 Full Text Searching” at SQL Saturday in Orlando. First off, I want to apologize to the attendees for taking so long to get this material posted. But I can finally reveal what has been sucking up my spare time lately!

I, along with other MVP’s have been working on something called the SQL Server 2008 MVP book project. Many SQL MVP’s, myself included, volunteered to write one or more chapters for the book. Once published, all proceeds will go to charity. I’m very excited at the opportunity to contribute the chapter on Full Text Searching, which I submitted today. I’ll keep you posted as things progress.

We’re not the first ones to go down this road, Sara Ford is donating the proceeds from her Visual Studio Tips book to supply scholarships for the young folks in the hurricane ravaged town of Waveland MS. I hope you’ll support her efforts as well.

Meanwhile, what you really came for was the links relevant to my talk.

Detailed descriptive material can be found on my blog, ArcaneCode.com, with links to specific posts below.

First off, the slides and sample code can be located at the Code Gallery site I setup specifically for Full Text Searching with SQL Server:


Look on the downloads page to see various projects around SQL Server Full Text Searching. I’ve created one “release” for each of the projects around FTS. Be sure to look on the right side at the various releases in order to see the various projects.

Next, you can get started with the basics by reading these entries on my blog:

Lesson 0 – Getting the Bits to do Full Text Searching in SQL Server 2005
Lesson 1 – The Catalog
Lesson 2 – The Indexes
Lesson 3 – Using SQL
Lesson 4 – Valid Data Types
Lesson 5 – Advanced Searching

After that you’ll be ready for some advanced topics.

Can you hear me now? Checking to see if FTS is installed.
Exploring SQL Servers FullTextCatalogProperty Function
Using the ObjectPropertyEx Function
Using FORMSOF in SQL Server Full Text Searching
Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search
Creating and Customizing Noise Words in SQL Server 2005 Full Text Search
Creating and Customizing Noise Words / StopWords in SQL Server 2008 Full Text Search
Advanced Queries for Using SQL Server 2008 Full Text Search StopWords / StopLists

SQL Server Reporting Services, Oracle, and Multivalued Parameters

I am working on a series of reports that will use SQL Server Reporting Services 2005 for the reporting tool, with the data stored in Oracle. One of the requirements was that several of the parameters had to be Multivalued, users could pick from one to all of the items in a drop down list. Sounds like it should be straight forward, but there were a lot of gotcha’s that drove me nuts trying to get this to work. Some of the workarounds I had seen on the web, one I figured out for myself, but none had this information consolidated in one spot. So I thought I’d pass along the hard fought hours I spent and perhaps save you some time.

I’m going to keep my example very simple. I’m also going to assume you are familiar with using parameters and reporting services, perhaps just not with Oracle. We’ll pull our data out of a table called “table1”, and have four fields, field1, field2, field3, and field4. Field1 will be the one we’ll use for our multivalued parameter.

So the first thing we’d do with our report is create two datasets. The first will be used to populate the dropdown list for the parameter. We’ll call it dataset1, and it will have some simple SQL with it:

Select field1 from table1 order by field1

Pretty easy so far. Now create a parameter for the report. Name the parameter prmField1, and have it supply the values and labels from dataset1. This should all be familiar so far if you’ve done parameters with SQL Server. Now let’s create dataset2, which will be the main one to supply data to the report. When you create the new dataset, use this for the SQL:

Select field1, field2, field3, field4 
  from table1
 where field1 in ( :prmField1 )

Then click on the parameters tab, and bind :prmField1 to the Parameters!prmField1 parameter.

Now if you’ve used parameters with SQL Server, you’re probably scratching your head. It’s time for that first “gotcha” I mentioned. While SQL Server uses an @ sign for it’s parameters, Oracle uses a : (colon) to prefix it’s parameters. Thus :prmField1 is the parameter in this SQL. Note I could have named it anything, I just used :prmField1 to make it consistent with the parameter we setup to make debugging easier.

There’s one other potential gotcha here, depending on your version of Oracle. Only Oracle 9 and greater support this multivalued parameter syntax. It wasn’t an issue for me since we are on a version later than 9, but if you are on version 8 or earlier, you will have to turn the whole thing into an expression and break out the multivalues manually. Very ugly.

OK, if you run the report it would work, but it would have a few drawbacks. If you use the “Select All” check mark, Reporting Services will create SQL that looks like:

Select field1, field2, field3, field4 from table1 where field1 in ( ‘a’, ‘b’, ‘c’, ‘d’, …

Which works OK if you only have a limited number of items in the list, but if you have a lot of items selected your performance is going to be poor, and if you have more than 1000 Oracle won’t run the query at all. So how do we get around this gotcha?

The common wisdom here is to add your own ‘ALL’ selection to your drop down multiparameter list. Let’s go back to dataset1, and change the SQL statement to read:

selectALLas field1 
  from dual
union all
select field1 
  from table1
 order by field1

This will add ‘ALL’ as the first item in your list. If you are not familiar with Oracle, I’ll also mention “from dual” is just a way to tell Oracle that no table really exists, just return this one row with the fields I specify. Now we need to modify the query in dataset2 to take advantage of this shortcut. My first pass looked like:

select field1, field2, field3, field4 
  from table1
 where ( field1 in ( :prmField1 ) OR :prmField1 = ‘ALL’ )

Only problem is, this didn’t work right. If I picked ‘ALL’, or one item in my list the report worked, but if I picked more than one item from the list the report errored out. I believe that by using the :prmField1 = ‘ALL’ syntax, my multivalued parameter was getting converted to a single valued parameter. After several hours of head bashing though, I finally came up with this rewrite:

select field1, field2, field3, field4 
  from table1
 where ( field1 in ( :prmField1 ) orALLin ( :prmField1 ) )

Success! By changing my syntax to check for IN on both items I don’t coerce prmField1 into a single valued parameter, instead leaving it as multivalued. Another benefit / gotcha. You can execute this query from the dataset editor window. When you do a small dialog pops up and asks you to supply values for each parameter in the query. Through this dialog you can supply one value, thus I could use ‘ALL’, or some other valid value for prmField1, but only 1. This dialog does not allow you to enter more than one value, if you want to test your multivalue functionality, you’ll have to actually execute the full report.

You may also be wondering why I wrapped my where clause in parenthesis. Future maintenance. It will make it easier should I come back later and add more items to the where clause, like so.

select field1, field2, field3, field4 
  from table1
 where ( field1 in ( :prmField1 ) orALLin ( :prmField1 ) )
   and field2 = ‘Arcane Code’

Because there’s an OR in there, wrapping in parenthesis will keep my and/or logic correct for the rest of the query.

There is one final “gotcha” you need to be aware of when using this approach. While it does indeed solve the problem, it will take some education to train the users to use your ‘ALL’ feature instead of the built in ‘select all’ that comes with SQL Server Reporting Services. Once our users got used to it, it worked fine, but did take some education.

So let me summarize the key points for quick reference:

  1. Be sure your Oracle version is 9 or greater, none of this works on versions 8 or older.
  2. When using parameters with Oracle, use a : instead of an @ sign – :param instead of @param.
  3. Add an ‘ALL’ option to your datasets that supply values for multivalued drop down parameters.
  4. Check for the ALL in your where clause by using “where ( field1 in ( :prmField1 ) or ‘ALL’ in ( :prmField1 ) )” syntax.
  5. You can execute your query from the dataset window, but can only supply 1 value. However that value can be ‘ALL’.
  6. Educate your users on ‘ALL’ versus ‘(select all)’ .

Hopefully this will help you as you create your own SQL Server Reporting Services reports against Oracle databases using multivalued parameters.

Blog Action Day – Fighting Poverty with Tech

Today is Blog Action Day, a day for bloggers to rally to a cause. This year the cause is poverty. I firmly believe one of the best ways to fight poverty is via education. Since what I know is development technology, I’d like to offer some suggestions and advice for someone aspiring to get into a career as a developer. Assuming someone has access to even a moderately low end computer, there are some great ways to educate yourself about development technologies at no cost, even ways to get development software at no charge that you can put to use to begin making money.

First are the express editions of Microsoft Visual Studio and SQL Server. You can find these at http://www.microsoft.com/Express/.

If you are still in school, Microsoft gives you access to more full featured versions of their development tools through their DreamSpark program. The site has complete instructions on getting your school added if it’s not already.

There are many ways to learn how to use these products. One of the main ways is through podcasts and video training. There are many I listen to, but the ones I’d suggest starting with are DNRTV, DotNetRocks, Channel 9 and Security Now. Other shows are linked to on my links page.

If you want something to help you manage and find other podcasts, consider the Zune software. Even if you can’t afford to purchase a Zune, you can download the Zune software for free and use it to manage and listen to your podcasts. You can go to the marketplace to find and subscribe, for free, to many podcasts. Once subscribed the software will download them automatically for you.

Finally, check out the free educational opportunities available to you at local user groups. Again on my links page you’ll find a list of groups local to my area. At these user groups, most of which are free, you can learn, and more importantly make contact with people in the tech industry. These contacts will be valuable to you as you seek to begin your career. They can tip you off to better paying jobs, prep you on how to look / talk, and more.

Of course, most of the links I’ve provided here revolve around Microsoft technologies. There are also other technologies available, surrounding languages such as Java, Perl, PHP, Ruby and more that are also free to the aspiring developer. Since they are a bit out of my areas of expertise I’d suggest searching the web for what interests you. If you are not sure, check the want ads (both newspaper and on-line) for the skill sets that are in demand in your area.

Using your time to advance your education can give you knowledge with makes your more valuable in the workplace, a proven path to escaping poverty.

October Open Spaces at BSDA

Come join us in October for a very special Birmingham Software Developers Association meeting. We will be having a Open Spaces session, to expose members and visitors to the concept. In Open Spaces, participants place topics for discussion on the board, then the group votes on the topics they’d like to discuss. A moderator moves the discussion along, and when the topic is worked out moves onto the next topic.

Some possible topics might include open source software, imperative versus declarative languages, and more. Any topic is open for debate and discussion, so please come prepared to discuss!

The meeting will begin at 6:30 p.m. on Thursday October 9th at the New Horizons Training Center in Homewood, AL. Everyone is welcome.