Thursday, December 6, 2012

SSIS to QlikView task execution


QlikView is a BI visualization tool that has been in my tool belt for some time, and I use it as part of comprehensive BI solutions. SQLServer Integration Services is another important piece of the solution, and, in addition to the obvious ETL and data store functions, I tend to use it to orchestrate the actions of the system. Therefore, it is necessary to call QlikView functionality from within SSIS packages.
In QlikView version 11, the way to do this is through EDX (Event Driven Execution). The nice thing about this mechanism is that the API is exposed via SOAP over HTTP. It becomes easy to build a little .Net app to call the services associated to internal QlikView tasks.
If you are not a .Net developer, QlikTech’s Arthur Lee created a very nice article and .Net console application that you can use. Check out the article.

If you didn't recognize it right away, one of the benefits of having the API as a service is that you don't have to deal with the hassle of trying to execute a command-line call or batch file on a remote server. It's not as simple as running the thing because you'd be running in the calling box, not on the remote server.

It sounds very silly but I wanted to remind you about something that can save someone a lot of aggravation. When setting up your EDX triggers from within QlikView make sure there are no spaces in between words in the task names as well as no spaces at the end of the task names. Yeah… ask me how I know that.

Happy ee-tee-elling.

Tuesday, December 4, 2012

SSIS, I love you but I hate you so much

It got me again, during development I used a mapped drive throughout my project and everything works perfectly. To see those green boxes fill your screen relaxes you like nothing else. I mean, not really but it feels good like nothing else. Ok, not true, but back to the point...
Deployed the package, run it through a SQL Agent job and kaboooom. Error. Spit out the gum, straighten the back, pull the chair into the desk a little more...
After hours of agony I remembered that you should never use mapped drives with SSIS/SQL jobs, and pretty much anything else.
After using UNC paths to network shares everything is back to green.

Take away: D O    N O T    U S E    M A P P E D    D R I V E S    I N    S S I S. They don't mix well, just like oil and water...



Friday, November 23, 2012

Sleep Stats


As a father of an infant born with severe acid reflux I am up many times throughout the night and I wanted to know how many actual hours of sleep I was yielding. Since I am a proud owner of a Windows Phone, I decided to build an app to do that.
It is called Sleep Stats and it can be found here www.sleepstats.com.

It's really easy to use - just press the Sleep button when you go to sleep and the Wake Up button when you wake up. Do that throughout the night and you'll get a total number of sleeping hours, number of interruptions, average quality of sleep, etc. You can than save all this data as a .csv file to your SkyDrive and open it in Excel for even more number crunching.

The second update of the app has a couple of bug fixes and support for Portuguese.

Check it out! Night-night...



Sunday, November 11, 2012

Wasted a couple of hours (hopefully you won't)

I hope that you reach this post if you 1) are using Isolated Storage Explorer in your project, and 2) your app runs fine on the emulator but crashes on the device. Ok, by now you're thinking "Aaaaaah! Of course!" and are no longer reading.
In case you are still here, yes, you're right, you forgot the Isolated Storage Explorer registration code in the Application_Launching and Application_Activated events.

You can leave the code there but make sure you run it only when in the emulator:


if (Environment.DeviceType == DeviceType.Emulator)
      IsolatedStorageExplorer.Explorer.Start("localhost");   

Monday, November 5, 2012

Mobile Client switch for Windows Phone apps

Many of the Windows Phone apps I use have some sort of integration with SkyDrive, sometimes as a store for backup data. You can find sample code on the web on how to do this, it's pretty simple. However, I had a bit of a problem trying to get connected and the app wouldn't even show the SkyDrive login page. 
After some digging around I found that, because my app was a Windows Phone app, it requires a newer set of OAuth mechanism, and in order to use it the app needs to be marked as Mobile Client App.

To find this option log onto dev.live.com, go to "My Apps" and select API Settings:

Tuesday, October 30, 2012

Windows Phone's LinqToSql flakyness


I started doing Windows Phone development with Mango, which comes with SqlCe + LinqToSql. I had a lot of experience building WPF and Silverlight applications LinqToSql and Entity Framework against SQL Server, so I thought it all should be the same, or pretty close.
I started coding away and pretty soon realized some of the database operations, such as updates and deletes, were not working. I would have one update/SubmitChanges and, right after, re-query the entity and realized no changes were saved.
INotifyPropertyChanged was properly implemented, everything appeared to be ok.

I finally found out the culprit: lack of INotifyPropertyChangING on all properties of the table.

Yes, make sure to call your NotifyPropertyChanging before the assignment in the setter, and NotifyPropertyChanged afterwards. Something like this:

private DateTime startDateTime;
[Column]
public DateTime StartDateTime
{
    get { return startDateTime; }
    set
    {
        NotifyPropertyChanging("StartDateTime"); 
        startDateTime = value;
        NotifyPropertyChanged("StartDateTime");
    }
}

Here is my implementation of the two handlers for your convenience. This is not production-worthy because of lack of type-checking on the property name, but it's ok for illustrating my point:

public event PropertyChangedEventHandler PropertyChanged;
public void NotifyPropertyChanged(String propertyName)
{
    var handler = PropertyChanged;
    if (null != handler)
    {
        handler(this, new PropertyChangedEventArgs(propertyName));
    }
}

public event PropertyChangingEventHandler PropertyChanging;
public void NotifyPropertyChanging(string propertyName)
{
    var handler = PropertyChanging;
    if (null != handler)
    {
        handler(this, new PropertyChangingEventArgs(propertyName));
    }
}

Thursday, September 6, 2012

No pasting in Remote Desktop sessions?


You're trying to copy/paste from a Remote Desktop session and it is not working... Do you then open your web-based email provider to email yourself that long script from the server? If so you're doing it wrong!

The quick fix for when Windows' Remote Desktop's "copy/paste" capabilities stop working is to kill the "RDPCLIP.EXE" process and run it again.

But if you're using another remote-desktop-like system that doesn't have this feature or stops working for other reason, my favorite tool is PiratePad (if the copying/pasting object is simple text, that is). Simply put it gives you a collaboration surface that you can leave open in multiple machines. There's no account to create, no login to worry about, etc.

Monday, August 27, 2012

SQL Server - retrieving record count instantaneously


So here it is, the first one of these not-so-original knowledge, but I didn't know until I needed it today.

In SQL Server it may take a long time to simply return a total record count using select count (*) from YourTable as the engine may decide to perform a scan instead of a seek.

Among other places this page helped me with a solution that retrieves the record count of a table instantaneously:

SQL 2005 or later
select sum (spart.rows)
from sys.partitions spart
where spart.object_id = object_id('YourTable')
and spart.index_id < 2

SQL 2000
select max(ROWS)
from sysindexes
where id = object_id('YourTable')

Your blog sucks!


Within the past 3 months two people made comments criticizing the fact my blog is dead. Not that they used to read it and then I suddenly I stopped producing content, which would be silly because the content is very very minimal.

These individuals don't even have a blog to begin with but they are right. If I had the initiative to start one why the hell didn't I keep up with it. It's been 16 months since my last post!

I believe it has to do with wanting to write something unique, or a nugget of information that is hard to find. But following the advice from one of my mentors (despite the fact he doesn't know I exist) Scott Hanselman, I will try to write anything that is useful to me, regardless if it's easily found throughout the internets.