Saturday 28 April 2012

How to connect to an SQL Server


So my first post on the all new blog, I'm by no means a Qlikview expert but I have been using it for two years now and can find my way around.

What I'm hoping to do with this blog is compile a list of how to do everything in Qlikview, starting right at the beginning.

The qlikview training is great, however Qliktech seems very focussed on using files as a datasource.  I know the first question I asked after completing the tutorial was, how do I connect to a database?


Although there are plenty of ways to connect, here's how I do it.

1. Create a new Qlikview document, the built in wizard will then appear, I've never used this so just hit cancel


Does anyone use this?





2. Go to file > Edit script 
#Protip: Keyboard shortcut Ctrl+E




3. This will open the script tab, if you don't have any experience coding, DON'T PANIC! It's a lot simpler than you think
4. At the bottom of the script tab, are four tabs Data, Functions, Variables and Settings.  If you have an ODBC connection already, just click connect and select it, then skip to step 7.  If you don't have ODBC set up or are unsure what I'm talking about select "OLE DB" in the drop down and click connect.
#Protip: If using a mixture of 32 bit and 64 bit environments, untick the "force 32 Bit" field

5. Select the provider you want to use, in this case SQL Server, then click Next
6. Enter the server name, choose whether to use windows security or a username and password.  Then select the database on the Server.  Click Test Connection and if all good click ok.
7. Qlikview will then generate the connection string into the script window
Now on the Data Tab, click Select

8. You can then choose whether to use table or views, if you can't find what you're looking for check the Owner 
#Protip: click Preceding Load and Qlikview will list all fields in the script window for future use.

9. and that's it! your script is done


Click OK, save the document and go to File>Reload

#Protip: Keyboard shortcut Ctrl+R

My next post I'll be looking at displaying data - I'll be using UK government data from here



13 comments:

  1. Thanks for directing me here. This helped a lot.

    -Eric

    ReplyDelete
  2. very good anser

    ReplyDelete
  3. Great, thanks! This was a real help.

    ReplyDelete
  4. Does this SQL server provider work for any version of SQL server ? ( I need to connect to SQL server 2008 ). Also when entering the SQL server name , do you need to enter the whole path to the SQL server when it is a remote one ?...like server name : \\domain\server_name for instance ? do you need to specify the port number ? ( I ask you because I was given the SQL server name and a port number as well ).
    Thanks in advance
    Enrique

    ReplyDelete
  5. Does that SQL server provider work for all versions of SQL server ? ( I ask you because I need to connect to a SQL server 2008 ). Also when entering the server name, do you need to enter the whole path to the server when this is a remote one ? ( like for instance server name : \\domain\server_name. Do you need to provide the port number as well ?
    Thanks in advance
    Enrique

    ReplyDelete
  6. Does that SQL provider work with all versions of SQL server? I need to connect to SQL server 2008 and I am not sure if this is possible using Qlikview developer version 8.5. I tried following your steps and I get the following error : ( Microsoft Data Link Error ) Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
    Any suggestions on what the problem could be ?

    ReplyDelete
  7. I am trying to connect to a MS SQL database. When I get the SQL Serve Login I check Trusted Connection and that allows me to connect to the database. Problem is - that does not seem to "stick" with the script. How do I keep that connection?

    ReplyDelete
  8. Appreciate your guides, thank you very much

    ReplyDelete
  9. you're the man! thanks.

    ReplyDelete
  10. Thank youu Chris that was very helpful!

    ReplyDelete
  11. Thanks, its very helpful.

    ReplyDelete