Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Microsoft SQL Server Native Client 11.0 TCP Provider: An existing connection was forcibly closed by the remote host.

  1. Advice
0
Votes
Undo
  1. Deepshikha Jain
    Deepshikha Jain
  2. PowerBuilder
  3. Thursday, 20 May 2021 09:23 AM UTC

Hi Folks,

My application is on PB 2017 R2 and its running on windows server 2012 R2 OS. Application is connecting with local DB which is in SQL anywhere and remote database which is in SQL server.

This application is basically fetching the files from a location, storing the data in local dbase (SQL anywhere ) and making the PDFs. While creating the PDFs its doing a entry of report category in few tables of SQL server database.

Exe is scheduled to execute in every 1 and half hour. 

Recently we have upgraded our dbase from SQL server 2008 to SQL server 2016. After this we have started receiving the below error frequently. Every alternate day mostly.

Error connecting to Web Reports database SQLSTATE = 08001
Microsoft SQL Server Native Client 11.0
TCP Provider: An existing connection was forcibly closed by the remote host.

We have upgraded SQL native client as well , it has version 2011.110.7001.00.

There is no SQL log created for this error at database side. We also checked the database server timeout settings , this is also set as 0 (no timeout limit).

We have checked event logs as well but didn't found anything suspicious. Windows firewall is off.

As a work around we are restarting the exe and it starts working  but we are looking for permanent solution of this.

We are struggling to find the cause and solution of this issue since couple of weeks. Any help on this issue would be highly appreciated.

Thanks,
Deepshikha

  1. Database
  2. OLEDB
  3. PowerBuilder
  4. PowerBuilder (Appeon)
  5. SQL Anywhere
  6. SQL Server
  7. Database Provider
How to embedded Image in email body.
How to parse multipart form data?
Responses (4)
mike S
mike S Accepted Answer Pending Moderation
  1. Friday, 21 May 2021 14:22 PM UTC
  2. PowerBuilder
  3. # 1

what do you mean by scheduled?  Do you startup the exe from a scheduler?  Or is the application running the whole time?

 

If it is running the whole time, then you should disconnect from the database when it is done with the the processing.  then reconnect when it starts back up at the next hour.  That way you don't have to worry about your db connection getting timed out due to inactivity.  Plus, there is no reason to stay connected to the database while it is waiting around for the next hour.

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 21 May 2021 16:09 PM UTC
That actually makes a lot of sense!
  1. Helpful
Deepshikha Jain
  1. Deepshikha Jain
  2. Saturday, 22 May 2021 22:29 PM UTC
Hi Mike,

Yes I am starting the exe with scheduler.

So when exe is running its connecting with db, performing tasks and disconnecting. its not because of inactivity.

yes there is no reason to stay connected with db while waiting. And I am not doing this :)

  1. Helpful
mike S
  1. mike S
  2. Sunday, 23 May 2021 13:31 PM UTC
this is a sql server connectivity issue, so you will have better luck with searching sql server forums.

in the meantime, a few things to try/consider:



Your remote sql server - how are you connected to it: internet ? are you using a vpn?



if your network is wireless, then try wired instead.



make sure you have latest windows patches



make sure you database does not autoclose.

  1. Helpful
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Friday, 21 May 2021 13:34 PM UTC
  2. PowerBuilder
  3. # 2

You could check this also

1.  Check whether Power & sleep settings in windows.  Try setting with 'Never' for both Screen & Sleep.

2.  As suggested by Miguel, write a query that runs every one minute (or so) to fetch one single record from any  one table.

3.  Check whether Anti-virus is blocking the application.  May be, every other day some updates to anti-virus might change this settings.  [  It's happening to our applications at client's place ].  

HTH

 

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 21 May 2021 11:31 AM UTC
  2. PowerBuilder
  3. # 3

I'm not familiar with SQL Server (anymore), but in Oracle and Tibero we have a timer event running which does a

SELECT '1' from dual; with a specific time setting. This avoids the connections to time out. If you run it every 8 minutes, you'll probably be fine.

In Sql server you don't have the DUAL table, but you could create a DUMMY table and use that one.

https://stackoverflow.com/questions/28371342/what-is-the-equivalent-of-the-oracle-dual-table-in-ms-sqlserver

hope it helps, though probably not the best solution, it might fix your problem for now.

kind regards,

MiguelL

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 21 May 2021 14:33 PM UTC
Hi Miguel ... The syntax is very close in SS :

SELECT '1' from Dummy;
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 21 May 2021 14:53 PM UTC
Ah ok, thanks, I wasn't sure, but seem to remember (same in SqlAnywhere isn't it?)

regards
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 21 May 2021 15:50 PM UTC
That SQL Syntax is the same for ASE and SA as well. ;-)
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 20 May 2021 17:16 PM UTC
  2. PowerBuilder
  3. # 4

Hi Deepshikha;

  How are you moving the datum back & forth. For example, using a PB based PipeLine object?

Regards ... Chris

Comment
Deepshikha Jain
  1. Deepshikha Jain
  2. Friday, 21 May 2021 10:02 AM UTC
Hi Chris,

No we are not using any pipeline object here. We are using DWs to copy the data from files, Storing in respective tables in local dbase(ASA) then again with the help of DWs we are setting them in format we need as PDF and saving the DW as PDF and copying these PDFs to a location. After PDF creation we are doing one related entry in SQL server database and from where these reports will display under right category in another web application.



So when we are making connection with SQL server, there its giving mentioned error. And its something which is coming randomly anytime.



Thanks,

Deepshikha
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Sunday, 23 May 2021 13:41 PM UTC
Check with your DBA team to see if that SS instance has wait and / or execution timeouts set? If these are occurring, your DB session might be being dropped by SS itself. Food for thought.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.