We are having a problem with one of the SQL scripts written inside a QlikView application. The problem is, is that one of the scripts is locking tables within our database. We have over 200 QlikView dashboards and so going through each one is not really an option.
What I would like to know is if it is possible to read script tabs and copy all information from them and paste into another file like a .txt or .csv
I can create the loop that will read each QlikView application, just need to know if the above can be done and what the code for this piece would be
It was actually Rob Wunderlich who came up with a solution for me, with an solution based around his Document Analyzer that exported the script from all .qvw files in a folder. I don't know if he has made the solution generally available.
Just a couple of thoughts on the original point - using the NO LOCK hint on the SQL query may help things, also reducing the concurrency on reloads in Publisher (or Server) may help. Time spent optimising indexes in SQL can also pay dividends.
The log file contains quite a lot of the script in it - but it is not the same as exporting the load script (which can be done from within the script editor). Notably if you have a loop in your load script the code within that loop is written to the log file many times.
Thanks for all your responses. I will review and reply to each one
We figured out a solution to do this. This was our approach. I will post the code as soon as I am back in the office
1) Create a loop for each QlikView document using the filelist function.
2) The loop would contain an EXECUTE command that creates a prj folder for each document using MKDIR
3) Each document unfortunately needs to be opened and saved for the prj folder to actually create the log files and document properties. This was also done in script, but it does take a bit of time for those slightly large qvw
3) In the prj folder is a .txt file with the name load script
4) Create another loop to retrieve each load statement script as a field with another field representing the document it came from
5) Once the data is loaded, create a table box object and search for the code that creates the lock
6) The SQL DBA already knows what code is creating the locking, so all we had to do was tell him which report contains it
Just happy we got this right and learnt a lot through the process. However, the above is not a practical solution. I also want to review the QlikView governance app which from what I heard would also contain scripting information in the front end for review. Have to have a look and see
Once again, thanks for all your responses and will reply through the course of the day
That's a neat solution. The parsing of the script files could have been done without using QlikView as there are lots of tools for searching text files (I use a lightweight one called SR32, or Notepad++ can do this).