topic Re: Connect Mulitple servers in QlikView

Connect Mulitple servers

rajaqliks — Thu, 12 Jul 2018 06:47:02 GMT

I need one help, i have a requirement

I need to connect dynamically Multiple servers to load the data from SQL server.

I have a code to load data from one server multiple databases but,

I want to load multiple databases from multiple servers please give some suggestions.

Thanks in advance.

Re: Connect Mulitple servers

dapostolopoylos — Thu, 12 Jul 2018 06:57:17 GMT

You will have to define different connections for each server and load data serially...

Re: Connect Mulitple servers

Miguel_Angel_Baeyens — Thu, 12 Jul 2018 06:58:40 GMT

Use the CONNECT command in the script to connect to as many databases as you need. Remember that every CONNECT causes the previous to DISCONNECT, even if you don't write it in the script. So CONNECT to database1, read all the tables, then CONNECT to the next and so on.

Re: Connect Mulitple servers

rajaqliks — Thu, 12 Jul 2018 07:01:15 GMT

i want to load dynamically, the follwoing script i am using to load data from one server.

Variable:

LOAD

vServerAddress,

vDataBase,

vUsername,

vPassword

FROM

Excel file;

Let vVar=NoOfRows('Variable');

for i=0 to NoOfRows('Variable')-1

Let V1=i;

let vServerAddress = peek('vServerAddress',0,'Variable');

let vDataBase = peek('vDataBase',i,'Variable');

let vUsername = peek('vUsername',i,'Variable');

let vPassword = peek('vPassword',i,'Variable');

CONNECT TO [Provider=SQLOLEDB;Server=$(vServerAddress);Database=$(vDataBase);Uid=$(vUsername);Pwd=$(vPassword);];

Script;

next i;

Re: Connect Mulitple servers

Miguel_Angel_Baeyens — Thu, 12 Jul 2018 07:03:32 GMT

So you already got it. After you connect, and before looping to the next database, LOAD whatever tables you need, rename fields, transform or store, and same for the next load.

Re: Connect Mulitple servers

rajaqliks — Thu, 12 Jul 2018 07:04:43 GMT

i am using the following script, one server it will work fine but, if the second server starts automatically it gives a pop to enter username and password.

Variable:

LOAD

vServerAddress,

vDataBase,

vUsername,

vPassword

FROM

Excel file;

Let vVar=NoOfRows('Variable');

for i=0 to NoOfRows('Variable')-1

Let V1=i;

let vServerAddress = peek('vServerAddress',0,'Variable');

let vDataBase = peek('vDataBase',i,'Variable');

let vUsername = peek('vUsername',i,'Variable');

let vPassword = peek('vPassword',i,'Variable');

CONNECT TO [Provider=SQLOLEDB;Server=$(vServerAddress);Database=$(vDataBase);Uid=$(vUsername);Pwd=$(vPassword);];

Script;

next i;

Re: Connect Mulitple servers

Miguel_Angel_Baeyens — Thu, 12 Jul 2018 07:08:33 GMT

Then you need to make sure that username and password are correct in the excel file, and also that the server allows such connections. It could be that the server only accepts Windows users (NTFS) and not external credentials.

Re: Connect Mulitple servers

rajaqliks — Thu, 12 Jul 2018 07:12:04 GMT

i am using the correct credentials but i am facing the issue. if one server multiple databases it will work fine but if it is two servers multiple databases it is giving the pop up.