Reza Alirezaei’s Blog

We have developed this sequential Workflow that’s automatically kicked off when a registration request is dumped in a custom list in a SharePoint site. The registration can be initiated in a public facing zone and by users who have no corporate account or internal email address ;therefore Workflow must be able to send out emails to the external email addresses such as John.Doe@foo.ca.

In our code , we used SPUtility.IsEmailServerSet(SPWeb) to ensure the mail server is setup and used SPUtility.SendEmail() method (instead of using the Email Activity) to send out email notifications.In our dev machines , we all use stand-alone Mail servers (i.e. smarter mail), so we potentially couldn’t test the email functionality to the external users until we got our code to the QA environment where Exchange was properly configured to handle all mail stuff. In QA environment emails could not be sent out to the external users.

Armed with this command line SMTP mailer called blat , we realized that this was an SMTP relay issue. Here’s what the tool threw at us:

Sending test.txt to John.Doe@foo.ca
Login name is mossadmin@mycompany.com
The SMTP server does not like the name John.Doe@foo.ca
Have you set the ‘To:’ field correctly, or do you need authorization (-u/-pw) ?
The SMTP server response was -> 550 5.7.1 Unable to relay for John.Doe@foo.ca

We associated the IP of the WFE to allow SMTP relay. Bammm – Email are NOW SENDING EXTERNALLY!

I just thought that I should drop a line and let you know that DevTeach, now, offers new price discounts! I personally enjoy DevTeach , becuase it offers TechEd like content to Canadians derived by top-notch speakers from across Canada and US. If you haven’t looked at their events,I’d highly recommand that you take a look at their Web site to see if you or your organization can benefit from their events.

If you have already enrolled in any of the TechDays events , just be aware that all TechDays attendees can receive $350 off the conference price and all Canadian user group attendees will receive $100 off the conference price too. Last but not least, all attendees will receive $1000 in software (Visual Studio 2008 Professional, Expression Web 2 and the Tech-Ed Conference DVD Set). This is  just a bargain! eh?

It’s now confirmed that I will be speaking as part of the Techdays 2008 tour in Winnipeg,Calgary and Vancouver at the following dates , delivering the same session I presented in Toronto last Week. I had some schedule conflicts which is now resolved and I am good to go!

Winnipeg: December 04, 2008

Calgary : December 11, 2008

Vancouver:January 22, 2009

Can’t wait to see the great SharePoint/SSRS community  in the above cities. I am really looking forward to this.

Here is my presentation wrapped up at Techdays 2008 Toronto,  2 hours later , I’m sitting at the speaker room doing what every *normal* geek does! Writing a follow up blog post on my session. Truth to be told, once I am done with his blog post, I feel comfortable saying goodbye to Toronto and say hello to Winnipeg, Calgary and Vancouver’s upcoming sessions! can’t wait to see folks in west coast….I am in particular excited about Vancouver because 3 years ago when I immigrated to Canada I first landed there and I have very good Canadian friends back there who taught me how to converse, convey and write in English so I feel kind of attached to the city!

Alright , let’s get to the formal THANK YOU paragraph :

First and foremost, I’d like to thank every each of you for attending my session. Data track room was packed and I truly had fun , up on the stage, presenting to all 250 people sitting in the room.  I am hoping that you managed to walk away from this presentation with some take away. I apologize about wsdl command line not working as  I had left my contacts at home and I had difficulty reading the small fonts on the virtual machines (Yeah one of VMs and the mic was really driving me around the band) , therefore I didn’t notice that I was misspelling it .Below is a picture that shows how a call into the wsdl command line is made to get the proxy class generated for you from the ReportService2006.asmx endpoint.

With that being said, here are couple follow up points to my session:

1) Due to the volume of the things I wanted to share with you , I didn’t have the chance to spend enough time on the security context of your executing reports as much as I had planned to.  I would highly recommend that you read the following post before touching anything, because you want to avoid headaches. right?

 SSRS 2008 integrated mode: Security

2)  On the beginning of my presentation I demonstrated that how you can flip the configuration from native to integrated mode. you remember that? cool , here is the blog post that walks you through the steps with lots of screen shots . Enjoy!

 Shifting From SSRS 2008 Native to Integrated For Good

3)  Mini IIS term I referred to during my session has been clearly document here:

Say Goodbye to IIS, Say Hello to HTTP.SYS!

4) Powershell and Feature code and a walk through : Put the mouse pointer here and here and kindly press the right button!

5) Data Driven Subscriptions Part1 and Part2 – Remember, If you googled it , that’s me LOL

Got a question? Drop me a line!

Now I can go home, relax and have a nice cigar in my bathtub! 🙂

In Part 1, I gave you a quick tour on what needs to be set up before you can jump into data driven subscriptions in SharePoint. Now the real fun begins. From the ECB menu of a report choose Manage Subscription.

In the Manage Subscriptions page , click on the Add Data Driven Subscriptions button. This will take you to step 1 of 4 of setting up a data driven subscription.

Step 1) This step is literally the heart of the subscription and where our stored proc comes to the picture. Basically, you tell the subscription where to connect to retrieve the parameters at run time and how! I am using a shared data source I already built it to point to my Membership provider (not covered in this post). In the Query textbox I specified a call into my stored proc. Remember, you can also use a T-SQL query instead of the stored proc thing , but you gotta make sure that you hit that darn Validate button to not only validate your query, but also to retrieve the schema of the returned result set to be used in the upcoming steps.

A big No Answer to couple of potential smart questions:

• Can I make the data driven query dynamic and extract it at runtime too? No.
• Can I merge delivery extensions in one single subscription, say deliver the report via email and dump into a doc lib as well? No.
• Is there anyway I can create a 1:m relationship between my data driven subscription and my reports? No – only 1:1.
• I know that the list of subscribers to a data driven subscription can be extracted dynamically and it should be stored somewhere , but is there any way to make where subscribers data source points to , dynamic as well? No. Subscribers data source (In this case membership provider)must be static and defined when setting up the data driven subscription. No dice in making that dynamic too.

Step 2) The sample report I am using in this demonstration has a parameter called sales order number. In this step, you get a chance to set this parameter. I am opting for the parameter’s default value (first option) , but as shown in the picture below you can set another value (specific for this subscription) in the textbox or you can get the parameter from a field returned from your subscription query (discussed in step 1) from the drop down control.

Step 3) A subscription (either static or data driven) is meaningless without a delivery mechanism. In this step , you indicate what the delivery extension you want your subscription to use. The plan is to deliver the report via email so go ahead and choose Email from the drop down menu. As you can tell , there are many other properties in this page that can be parametrized and initialized at run time. Our stored proc only returns the actual email addresses so just set TO field to Email column of the returned result set. You also have to set the Subject and Format (not shown in the picture below) before proceeding to the next step.

Step 4) Now , you need to tell the subscription when to fire off. You have a choice of using an already created Shared Schedule or you can configure a dedicated schedule for this particular subscription. I am using a shared schedule (and Yes, I am going to present this whole thing at my presentations in Microsoft techdays 2008 tour across Canada)

When you click Finish on Step 4, you will be redirected to where you start and you are done. Click on the subscription you just built and you should be able to see something like the following picture:

Once the subscription gets executed , emails are nicely delivered as expected :

Two things need to be highlighted here :

• If you had chosen to include a link to the actual report in step 3 (I didn’t) , you would see that the link to the report is from the zone that user belongs too. It was kind of interesting!
• I am using smarter mail as my mail server and client. Read my post here for more information.

Click on the attachment and watch the result :

Yeap! that’s pretty much all about it. Hope you have enjoyed our journey on SSRS data driven subscription topic.