7 Replies Latest reply: Jun 23, 2011 11:42 AM by Alexandre Pfeijffer RSS

    Need some advice for best practice approach

      Hi there,

       

      We are trying to retrieve data from an access database that is located on a different server. We run QV10 on a dedicated win 2008 machine.

       

      I can connect to the database, but only when i make a drive mapping to the server\share with the DB. The MS Access ODBC driver does not support the use of UNC.

       

      Therefor i figured the use of a CMD file in the windows Task Scheduler might be worth a shot.

       

      The contents of the CMD file is this :

      ----------------------------------------------------------------------

      net use P: \\SE94AAPT1\Persmaster$ ******** /USER:********\********   (User and password been asterisked )

      "C:\Program Files\QlikView\qv.exe" /r D:\data\QVW\Extractie\K94_Persmaster\Persmaster.qvw

      net use P: /delete

      ----------------------------------------------------------------------

      In the QVW we refer to the 32-bit ODBC connection which is present on the server. Becuase this driveer doesnt support UNC we are forced to use a drive letter.

       

      A creative sollution but unfortunatly this doesnt run when scheduled and NOT logged in to the server.

       

      Basicly i am looking for some best practice advice as in How to schedule and retrieve data from an access database on another (non qv) server.

       

      Cheers in advance

       

      Alexandre

        • Need some advice for best practice approach
          Daniel Rozental

          I'm not quite sure I understand your problem, I have a few questions for you

          - Why are you not using QlikView server for the reload?

          - Is the drive letter also mapped for the user running the reloads?

          • Need some advice for best practice approach

            Small update :

             

            You can use UNC naming in the OBDC.

            How? Just type it in the filename box when you create a new OBDC connection. This is a bit silly imo because the ODBC ui does not have any buttons or something to support this. This can be classified as a small "You need to know" issue.

             

            Now that i can place UNC inside the ODBC i can reload from the QVS Schedule itself

             

            Well, it should be possible but then i stumbled upon another part of my problem, Authorisation is also an issue.

             

            The access on the filesystem is prohibeted because the DB contains data from the personal. Access is granted under strong supervision. I got acces so now i can "see" the DB at least.

             

            To access the data inside the DB i need to use a different user, which has been configured inside the master DB.

             

            Long story so far...hang on we are getting close.

             

            What i learned so far:

             

            1. The authorisation that is checked is from the person logged into the QVS making the ODBC connection.Regardless of the credentials that you can supply configuring the connection because those are used to access the data insde the DB, not the filesystem. So the user setting up AND Using the connection needs to be granted acces on filesystem level.

             

            2. QVS Schedule runs as a service! This service cant use drive mappings nore create them.

             

            3. You can use UNC inside ODBC connections, just type them inside the name field!

             

            My problem was a mixed one. I am working now to get the authorisation correctly configured by our system admins. The UNC inside ODBC is solved so there is no need for drive mappings etc.

             

            Eventhough it isnt solved atm i am sure it will be very soon.

             

            Thanks for the help, it is much appreciated