topic Re: Connection string Timeout - is it possible? in QlikView

Connection string Timeout - is it possible?

thenextlevel — Fri, 07 Nov 2014 08:09:29 GMT

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.

Re: Connection string Timeout - is it possible?

thenextlevel — Tue, 25 Nov 2014 15:41:33 GMT

BUMP!

Re: Connection string Timeout - is it possible?

thenextlevel — Fri, 16 Jan 2015 17:02:38 GMT

BUMP!

Re: Connection string Timeout - is it possible?

Colin-Albert — Fri, 16 Jan 2015 17:28:53 GMT

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?

Re: Connection string Timeout - is it possible?

thenextlevel — Mon, 19 Jan 2015 07:58:51 GMT

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.

Re: Connection string Timeout - is it possible?

thenextlevel — Tue, 23 Jun 2015 09:37:18 GMT

BUMP!

Re: Connection string Timeout - is it possible?

Colin-Albert — Tue, 23 Jun 2015 10:16:21 GMT

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.

Re: Connection string Timeout - is it possible?

Tue, 23 Jun 2015 11:42:55 GMT

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.

Re: Connection string Timeout - is it possible?

Colin-Albert — Tue, 23 Jun 2015 11:51:20 GMT

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?

Re: Connection string Timeout - is it possible?

thenextlevel — Tue, 23 Jun 2015 12:00:02 GMT

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!!

Re: Connection string Timeout - is it possible?

Colin-Albert — Tue, 23 Jun 2015 12:15:42 GMT

Can you add some trace commands after the connect string to see exactly where the error is?

Can you run a ping to the SQL box to see if the network connection drops.

Re: Connection string Timeout - is it possible?

thenextlevel — Tue, 23 Jun 2015 12:52:51 GMT

I'll have a look.

I've found this which might help.

Data server is not available. How to exit script without error?

Re: Connection string Timeout - is it possible?

Thu, 10 Nov 2016 13:04:04 GMT

Hello,

We are also facing similar issue while connecting SQL server from Qlikview dashboard.

We are getting error like below when Qlikview trying to connect sql server from script window,

Error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Login timeout expired

This is a random issue that we can't replicate unless it happens. some times it auto resolved and again happens with out making any change in code or configuration on sql server.

Can you help to find exact root cause of this behavior ?

Thanks,

Re: Connection string Timeout - is it possible?

Anonymous — Wed, 16 Nov 2016 18:16:37 GMT

We have a similar need for a timeout here as well.

I've got connection attempts that are hanging open when the database accepts the old credentials, but then prompts for a password change.

Does setting a document timeout in the reload task force this to move on from waiting around?