Archive for October, 2011

Installing SQL Server Denali CTP3 Weird Issue – Invalid Handle

I was busily preparing my new VMs all night (shiny new VMs!!). Planning to get 2 Windows 2008 R2 Enterprise boxes out as my new playgrounds, one as a Domain Controller, another one as my new Denali CTP3 box.

When I was starting to install Denali, not long after launching the install, I got this weird issue just as I was clicking next on the Product Key window:

SQL Server Setup has encountered the following error:
The handle is invalid. (Exception from HRESULT: 0×0070006 (E_HANDLE))

At first I was thinking maybe my ISO was corrupt, or my VM was wonky! Good thing I stumbled across this old connect post about sql2k8 rtm (dev x64) failed install. Quoting the last answer:

This is a MSDN version so the product key is pidded but it is not enabled as read-only. So, when you’re presented with the product key dialog, you can muck it up.

To reproduce, simply select the “Specify a free edition” radio button without changing the shown PID, then reselect the “Enter the product key” radio button (again without changing the PID) and continue with the installation.

So to recap, the solution is to select the “Enter the Product Key” choice (yup, even if it’s blank). That’s all it took, worked like magic.

VN:F [1.9.22_1171]
Rating: 10.0/10 (3 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

I wanted (needed!) to write a script to automatically generate a number of reports based on sets of parameters. Most of the scripting I’ve done before are for straight up parameters that I can pass using SMO or through the URL string.

This time, I had a cascading query-based parameter, ie one parameter that is filled out based on another parameter. I was doing my usual drill with PowerShell when I got the following infamous error:

“This report requires a default or user-defined value for the report parameter. To run or subscribe to this report, you must provide a parameter value.”

Which is weird because I did provide all the required parameters. A quick search leads me to a few blog posts, most of which mentioned that it’s because of my query based parameter. Most also suggest the resolution is to convert my parameter which has query-based parameter to be non-query based.

The only problem is I can’t. And I don’t want to. That’s not a resolution. This report is meant to be used both by users for ad hoc purposes, and for massive downloading for month-end reports. Removing the query-based values for a parameter *IS NOT* a resolution. Not for me anyway.

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 9.7/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Download SSRS Parameterized Reports in PDF with PowerShell

Here’s a short PowerShell script that :
1. Connects to your report server
2. Loops through a set of parameters stored in an array
3. Saves the PDF version of the report to a local folder, with appropriate names

Here’s a snippet of code to set your SSRS parameters programmatically using PowerShell

  $params = $null;
 
  #set parameters
  #here's a sample usage http://msdn.microsoft.com/en-us/library/microsoft.reporting.winforms.reportparameterinfo(v=vs.80).aspx
  $params = new-object 'Microsoft.Reporting.WinForms.ReportParameter[]' 3
  $params[0] = new-Object Microsoft.Reporting.WinForms.ReportParameter("FISCALYEAR", $fiscalyear, $false);
  $params[1] = new-Object Microsoft.Reporting.WinForms.ReportParameter("MONTHENDDATE", $monthenddate, $false);
  $params[2] = new-Object Microsoft.Reporting.WinForms.ReportParameter("SALESGROUP", $salesgroup, $false);
 
  $rv.ServerReport.SetParameters($params);

Of course this is just a sample, you can definitely extend this by using SMO to automatically pull parameter values, or use values stored in a file (among a million other things you can do with ubercool PowerShell)

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 10.0/10 (8 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)

Daring to Dream .. What It Means To Be an MVP (for me)

Just this week, UBC launched of a very ambitious campaign. The tagline is “Start an Evolution” – UBC is launching the biggest fundraising campaign in history. I am very proud to be part of this organization .. albeit in the shadows (I work on the databases, you see).

For the unfamiliar it probably sounds like just a “money thing”, but for those who are familiar, it is more than just raising money. It is all about building opportunities for students, inspiring them to become what they can become, and empowering them to take on challenges that they otherwise may not be able to take on by themselves. It’s about daring to dream. It sounds so cliche, but that’s what it is.

One example, there was one group of students, inspired by their professor and funded by someone who believed in the dream, went on to do an investigative journalism piece that won an Emmy. There are lots more inspiring stories like this (Read these success stories) … We have to remember that the littlest drop, the littlest change, can cause ripple effects of unfathomable lengths.

Somehow, this is what it feels, and means to be a SQL Server MVP – for me…

Just today I opened my inbox, and I saw an email saying “Congratulations! We are pleased to present you with the 2011 Microsoft® MVP Award! …”.

“huh … gasp!!!” …. I literally had to pause, and re-read my email. Because this is something I did not expect at all. I knew I was nominated, but knowing there are so many other great, expert SQL Server Professionals who are up for the award, I knew I didn’t have a chance.

But … I still dared to dream. Long before I got the email, I did dream and thought it might be nice to get the award. I’d be a hypocrite if I told you getting the award didn’t matter. For me, it’s this nice warm fuzzy feeling knowing that somehow, you’ve been noticed, and that somehow there’s this appreciation for some of the things you’ve done.

However, the MVP award is NOT the be-all end-all reason I’ve poured hours of work answering emails or writing articles or giving talks. I don’t know what it is about SQL Server, but I truly enjoy working with it. I truly enjoy writing the queries and reports, and answering the questions that can’t be easily answered. I like uncovering truths, although it’s not always a party if you’re the bearer of the not-so-great message (Sorry sales are really down). I enjoy being able to troubleshoot these sometimes really cryptic error messages that creep up to servers and seemingly harmless installs and configurations (@sqlbelle – 1, SQL Server – 0! Cha ching!).

I may not be the best at all things SQL Server, but with everything I’ve done, I’ve poured my 110%.

Some people may know I have been teaching at BCIT for a while now, for me I consider this more than just a contract job. Because if it was just that I could have gladly moved to more lucrative contracts. But I consider my teaching at BCIT a passion, and almost a mission. I hope to be able to make a difference, to inspire someone – one out of ten, one out of a hundred, one out of the 10 years I’ve been with the institute. My courses are primarily SQL Server, but I hope in some ways it’s also beyond that. I hope somehow through the courses, students realize that they should pursue what makes them happy. Of course I try to infect my SQL Server enthusiasm to my students – sometimes it works, sometimes it doesn’t. But that doesn’t mean my efforts are in vain (or so I would like to believe).

I probably almost sound like a hopeless romantic (and how unfitting is that in a supposedly technical blog) .. but I really believe you can dare to follow your dreams. You can dare to find what it is that makes you happy. (Joseph Sack just posted a link to this New York Times article – Do Happier People Work Harder? ). And as Confucius says:

Choose a job you love, and you will never have to work a day in your life.

I’m glad I discovered my passion. Yes I know this sounds cheesy, but working with SQL Server makes me happy. Everyday at “work” is not really “work”. It’s another SQL Server adventure.

I dared to dream. Now, I dare to make my ripple effects, however small or large they may be.

PS Thank you Microsoft, for awarding me the MVP. Another source of inspiration .. I still have the warm fuzzy feeling.

PPS Thank you, whoever you are who nominated me. Gesture is very very much appreciated.

VN:F [1.9.22_1171]
Rating: 10.0/10 (12 votes cast)
VN:F [1.9.22_1171]
Rating: +6 (from 6 votes)
`