So I’ve finally ditched doing things with ODBC and gone towards an OLEDB
One thing to note about the OLEDB connection you cannot line wrap it, it must be all one line. I like this approach because you can also specify the Application Name, so when you are looking on SQL server you can identify which Qlikview is currently hammering the server 🙂
LET vMyDatabaseName = ‘mydatabase’;
LET vMyServerName = ‘myserver’;
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=$(vMyDatabaseName);Data Source=$(vMyServerName);Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Application Name=MyAwesomeQlikview;];
I stole this technique from http://qlikshare.com/545 his is actually more elaborate with storing the connection outside in a text file, but this is sufficient for now.
In my case since we don’t run as admins on our desktop I had to create a shortcut to QV.exe and then shift click run as different user (windows 7) then when I reload happens it runs under the correct security context.
One other interesting thing I do, is when we create a .QVD I pop it in a folder on the Qlikview server and then share that folder. Then when creating the .Qvw I just drag the qvd out from the share into my qlikview window and bam it triggers the wizard. This is nice because when I publish the qlikview on the server the path to the QVD is still correct. It basically loops back through the share to do the reload. I suppose this could cause performance problems?? but not at the size of our Qlikview. This is also how we allow our users to self create QVW’s, point them at the share drag it into Qlikview and go to town. Then when they publish it paths are all correct as well.
I would love to hear similar techniques, and other peoples best practices!