Connection string Timeout - is it possible?

[thenextlevel]

I am having issues with connections to SQL Server 'locking up' and having to manually end the QV.exe and was wondering how to overcome the issue?

This is my connection string;

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=QVUser;Initial Catalog=Cloudjh;Data Source=dbsxd;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=L2318;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is DITePYMGQBcCGaRObE);

There is a Connect Timeout in the Advanced section of Data Link Properties, but it doesn't seem to do anything?

Any ideas would be great.

[thenextlevel]

BUMP!

[thenextlevel]

BUMP!

[Colin-Albert]

There could be a couple of reasons for the lockup...

Are you creating the connect string, or copying an existing connect string?

Can you ping the SQL server the machine that cannot connect?

The string has an embedded password, have you tried creating  a new connect string with a user login and password that has permission to the data.

Are you sure the lockup is the connect failing, or is the load script pausing to generate synthetic keys.

Have you checked the document log?

[thenextlevel]

Hi Colin

Thanks for the response. This is an issue i have noticed over the last 12 months and we have tried everything from recreating the strings, passwords, no passwords etc. etc.

I think the problem is if SQL server is unavailable (for whatever reason) and this is why i want to utilise the timeout facility. It seems like if it can't make the connection immediately it locks up, instead of retrying or feeding back a failure to connect.

[thenextlevel]

BUMP!

[Colin-Albert]

Can you trace the errorlevel or ScriptErrorDetails variables after your failed connection?

Is the SQL server located remotely from the Qlik Server?

Can you run a copy of QlikView local to the SQL server, extract the data to QVD files and then copy the QVDs to the Qlik Server. This may be more robust rather than running a SQL query across the network.

[#8]

Albert, Generally speaking QlikTech recommend don't install any other software's on Qlikview server. If you attached the SQL Server DB to Qlikview server it may impact the performance of Qlikview application because queries also memory based. If I exceute the sql Query in Qlikview, it take Qlikview server resources or DB server resources ?

NextLevel, firstly try to connect to DB from your desktop. Please also ensure also firewall open between DB server to Qlikview server.

[Colin-Albert]

Hi Dathu,

I am not saying install the SQL database on the QV server.

If the SQL server is remote, then a copy of QV desktop on the same network segment as the SQL server will show if the connection failures are due to network issues between the SQL server and the QV server.

It can be installed on a different machine, but on the same network segment.

Any dropped packets between the QV server and the SQL server will cause the connect / select to fail.

One option is to generate QVDs via a desktop copy of QlikView and then transfer the QVDs to the QV server using ftp or copy.

NextLevel,

The first check is that the connection between the SQL server and QV Server is open to the QDS Service account. Does the QDS service account have permissions to the database?

[thenextlevel]

Hi guys

Thanks for the replies.

This is a random issue that i can't replicate unless it happens. The script freezes and therefore does not move beyond the connection string, the only option is to kill the qv.exe. Out of 60 refreshes per week the issue may occur twice, somethies 4 times, there really isn't any logic to it.

I hoped that using a 'timeout' would handle the issue, but they don't seem to work??

Very frustrating!!