Skip to main content
Solved

Connection issues to MS SQL

sigtill
Contributor
Forum|alt.badge.img+24

I have read this: https://knowledge.safe.com/articles/281/failed-to-connect-using-sql-server-2012-native-cli.html

And tried to fix it with all these steps, with no luck. 

UPDATE: With respect to workspaces these was updated from FME Server 2015.1.3.1 - Build 15573 - win64 to FME Server 2016.1.2.1 - Build 16674 - win64

2017-01-24 20:12:10|   0.1|  0.0|INFORM|FME Configuration: Using Named Connection values from 'XXX' for MSSQL_ADO_1
2017-01-24 20:12:10|   0.1|  0.0|INFORM|Creating reader for format: Microsoft SQL Server Non-Spatial
2017-01-24 20:12:10|   0.1|  0.0|INFORM|Trying to find a DYNAMIC plugin for reader named `MSSQL_ADO'
2017-01-24 20:12:10|   0.1|  0.0|INFORM|FME API version of module 'MSSQL_ADO' matches current internal version (3.8 20160224)
2017-01-24 20:12:10|   0.1|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Read 2 DEF line(s).  Found 9 attribute(s)
2017-01-24 20:12:10|   0.1|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Opening `XXX' for read operation
2017-01-24 20:12:10|   0.1|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Trying to connect using SQL Server 2012 Native Client...
2017-01-24 20:12:25|   0.1|  0.0|ERROR |Microsoft SQL Server Non-Spatial Reader: Failed to connect using SQL Server 2012 Native Client. Provider error '(-2146824582) Provider cannot be found. It may not be properly installed.'. Connection string 'Provider=SQLNCLI11;DataTypeCompatibility=80;Data Source=XXX;Initial Catalog=XXX;User ID=XXX;Password=********'
2017-01-24 20:12:25|   0.1|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Trying to connect using SQL Server 2008 Native Client...
2017-01-24 20:12:25|   0.1|  0.0|ERROR |Microsoft SQL Server Non-Spatial Reader: Failed to connect using SQL Server 2008 Native Client. Provider error '(-2147467259) Named Pipes Provider: Could not open a connection to SQL Server [5]. '. Connection string 'Provider=SQLNCLI10;DataTypeCompatibility=80;Data Source=XXX;Initial Catalog=XXX;User ID=XXX;Password=********'
2017-01-24 20:12:25|   0.1|  0.0|ERROR |Failed to open reader

And these

2017-01-24 20:11:55|  16.9|  0.8|ERROR |Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement.  Query Text `select count(*) as _numodptot from dbo.XXX where XXX= XXX'. Provider error `(-2147467259) Named Pipes Provider: Could not open a connection to SQL Server [5]. '  

It happens randomly, on different workspaces, we have a group of 10 workspaces running every 1-5-10 minutes)

It seems some connection,issue - however this does not exist on the staging server (same FME Server version etc). During the upgrade we replaced the Embedded database parameters with Database Connections to simplify things, not sure if that is messing up stuff. Also the SQLExecutors, Joiners, Readers connecting to the MSSQL was created with FME2015 in the beginning and havent been deleted/recreated. The Joiners have been Upgraded to the latest version. Nor sure if there is some changes with the MS SQL Reader / Writer / Joiner that has changed how to connect to MSSQL and that might be an issue? Seems to work fine on FME Desktop 2016 with no issues.

Any help or suggestions appreciated!

Best answer by sigtill

It seems the issue is due to the process taking to long to process - expiring the timeout for keeping the database connection. even though it was set on 10 minutes on all joiner, reader, sqlexecutor. The trick was to optimize the workspace and replace sqlexecutors with joiners (with Prefetch-queries). We only worked with 8000 rows in this case, however it really slowed down due to very non-optimal workspace. For instance sending 8000 sqls one and one to a server, and doing this multiple times resulted in runtime of more than 10 minutes. With optimizations it seems to work great and spend less than 1minute in the same data.

Perhaps write an error in the log when "Database connection from Joiner_4 / FeatureReader_2 / SQLExecutor_2 have timed out (timeout set to 600) - optimize query or set a larger timeout) - so you can easily see in the logfile - and find the correct Transformer among many

View original
The real SigTill
Did this help you find an answer to your question?

14 replies

sigtill
Contributor
Forum|alt.badge.img+24
  • sigtillContributor
  • Author
  • Contributor
  • January 24, 2017

I saw I only had Native Client 2008 installed on the Server - installed 64bit Native Client 2012 from (https://www.microsoft.com/en-us/download/details.aspx?id=29065)

NB: Download link is found under INSTRUCTIONS NOT when clicking download.

Will reboot and see if there are any changes.

The real SigTill
fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • January 25, 2017
sigtill wrote:

I saw I only had Native Client 2008 installed on the Server - installed 64bit Native Client 2012 from (https://www.microsoft.com/en-us/download/details.aspx?id=29065)

NB: Download link is found under INSTRUCTIONS NOT when clicking download.

Will reboot and see if there are any changes.

Any joy?

 

 

sigtill
Contributor
Forum|alt.badge.img+24
  • sigtillContributor
  • Author
  • Contributor
  • January 25, 2017

No luck :(

However now when some workspace fails, it is in 90% of the cases 2 of the particular workspaces. I suspect that there are just too many joiners / sqlexecutors / readers either in one workspace or that the connection after them does not get cleaned up properly after each run. Unfortunately it is difficult for me to get access to the admin of the database to see the connections. I will try that today and keep this thread update for anyone else with similar issue.

The real SigTill
sigtill
Contributor
Forum|alt.badge.img+24
  • sigtillContributor
  • Author
  • Contributor
  • Best Answer
  • January 25, 2017

It seems the issue is due to the process taking to long to process - expiring the timeout for keeping the database connection. even though it was set on 10 minutes on all joiner, reader, sqlexecutor. The trick was to optimize the workspace and replace sqlexecutors with joiners (with Prefetch-queries). We only worked with 8000 rows in this case, however it really slowed down due to very non-optimal workspace. For instance sending 8000 sqls one and one to a server, and doing this multiple times resulted in runtime of more than 10 minutes. With optimizations it seems to work great and spend less than 1minute in the same data.

Perhaps write an error in the log when "Database connection from Joiner_4 / FeatureReader_2 / SQLExecutor_2 have timed out (timeout set to 600) - optimize query or set a larger timeout) - so you can easily see in the logfile - and find the correct Transformer among many

The real SigTill
sigtill
Contributor
Forum|alt.badge.img+24
  • sigtillContributor
  • Author
  • Contributor
  • January 26, 2017

In the fmeServerConfig.txt there is a part:

#--------------------------------------
# Limits
#--------------------------------------
MAX_TRANSACTION_RESULT_SUCCESSES=100
MAX_TRANSACTION_RESULT_FAILURES=10
MAX_FAILED_TRANSACTION_REQUEST_RETRIES=3
MAX_REGISTRATION_READ_ATTEMPTS=6000
REGISTRATION_READ_RETRY_WAIT=10
MAINTENANCE_IDLE_WAIT=90000
TRANSACTION_DB_RETRY_WAIT=20

1. It this only related to communication between FME Engine and Core - or does it also apply to any other databaseconnections?

2. Is there a parameter that defines the maximum number of databaseconnections from an FME Engine? Some simple math if a assume one FME Workspace that contains 10 reader/joiner/sqlexecutor with the same database connection uses 10 database-connections (1 connection per transformer) and they run 10 workspaces on 10 minutes. Then the number of database-connections to the same database by FME is 10 connections/workspace * 10 workspaces run = 100 connections per 10 minutes. 

3. Does one FME Workspace handle 1 Database Connection as a single connection, or multiple connections when used in several SQLExecutors,joiners,readers,writers? If FME handles one Database Connection as 1 connection through the whole run of the workspace (regardles if there is a Joiner, SQLExecutor, Reader, Writer) that uses the same connection - then the needed connections is only 10. 

The real SigTill
fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • January 26, 2017
sigtill wrote:

In the fmeServerConfig.txt there is a part:

#--------------------------------------
# Limits
#--------------------------------------
MAX_TRANSACTION_RESULT_SUCCESSES=100
MAX_TRANSACTION_RESULT_FAILURES=10
MAX_FAILED_TRANSACTION_REQUEST_RETRIES=3
MAX_REGISTRATION_READ_ATTEMPTS=6000
REGISTRATION_READ_RETRY_WAIT=10
MAINTENANCE_IDLE_WAIT=90000
TRANSACTION_DB_RETRY_WAIT=20

1. It this only related to communication between FME Engine and Core - or does it also apply to any other databaseconnections?

2. Is there a parameter that defines the maximum number of databaseconnections from an FME Engine? Some simple math if a assume one FME Workspace that contains 10 reader/joiner/sqlexecutor with the same database connection uses 10 database-connections (1 connection per transformer) and they run 10 workspaces on 10 minutes. Then the number of database-connections to the same database by FME is 10 connections/workspace * 10 workspaces run = 100 connections per 10 minutes. 

3. Does one FME Workspace handle 1 Database Connection as a single connection, or multiple connections when used in several SQLExecutors,joiners,readers,writers? If FME handles one Database Connection as 1 connection through the whole run of the workspace (regardles if there is a Joiner, SQLExecutor, Reader, Writer) that uses the same connection - then the needed connections is only 10. 

We are looking into this at Safe support. A quick response to your points above:

 

 

1. Those parameters from fmeServerConfig.txt apply to FME Server and not to database connections within a workspace

 

2. There is no parameter for this. The MS SQL Server Spatial reader has a parameter "Make Connection Persistent" so we reuse connections, but I assume you aren't using this reader. For the MS SQL Server (non-spatial) Reader it looks like we will open a connection for each reader. The SQLExecutor should use persistent connections so that multiple SQLExecutor queries will should share a connection. 

 

3. See above. A reader, a Joiner and a SQLExecutor would each be a separate connections but multiple SQLExectuor queries or multiple SQLExecutors should share a persistent connection

 

 

steveatsafe
Safer
Forum|alt.badge.img+12
  • steveatsafeSafer
  • Safer
  • January 27, 2017

Is there any chance the link between FME Server and the Database system drops at all? Even for a split second? I'm really curious to learn more about this. You have experienced several variations of the SQL Server provider error.

 

Can you confirm that the FME Server system only has the SQL Server 2012 client installed and the 2008 client was removed?
SteveatSafe
fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • January 27, 2017

We are fairly sure now that this was a known issue with the Joiner which was reported and fixed in FME 2016.1.2 Build 16670 (PR#71929). The issue is known to have caused workspaces to fail intermittently on FME Server when the Joiner was connecting to Microsoft SQL Server or Oracle and possibly other databases. We will create a Knowledge Center article shortly and include the link here. Upgrading FME Desktop and FME Server to 2016.1.2 or later and upgrading any Joiner transformers within the workspace should hopefully resolve this issue.

sigtill
Contributor
Forum|alt.badge.img+24
  • sigtillContributor
  • Author
  • Contributor
  • January 30, 2017
steveatsafe wrote:

Is there any chance the link between FME Server and the Database system drops at all? Even for a split second? I'm really curious to learn more about this. You have experienced several variations of the SQL Server provider error.

 

Can you confirm that the FME Server system only has the SQL Server 2012 client installed and the 2008 client was removed?
It had only 2008 Native Client, but I installed also the 2012. Currently BOTH are installed. Should I try to remove the 2008?

 

 

The real SigTill
sigtill
Contributor
Forum|alt.badge.img+24
  • sigtillContributor
  • Author
  • Contributor
  • January 30, 2017
fmelizard wrote:

We are fairly sure now that this was a known issue with the Joiner which was reported and fixed in FME 2016.1.2 Build 16670 (PR#71929). The issue is known to have caused workspaces to fail intermittently on FME Server when the Joiner was connecting to Microsoft SQL Server or Oracle and possibly other databases. We will create a Knowledge Center article shortly and include the link here. Upgrading FME Desktop and FME Server to 2016.1.2 or later and upgrading any Joiner transformers within the workspace should hopefully resolve this issue.

It seems the issues is still there on newer versions of FME and after upgrading the Joiner. We are replacing all Joiners with SQLExecutor (or better in this case to use FeatureReaders) to see if issue persist.

 

 

 

The real SigTill
sigtill
Contributor
Forum|alt.badge.img+24
  • sigtillContributor
  • Author
  • Contributor
  • January 30, 2017

I tried to replace all Joiners, and I also forced these group of workspace to use only 1 specific engine. I still have workspaces using both joiner, reader, sqlexecutors - however now they do not seem to fail. I suspect there is an issue when 2 different Engines runs simultaneously connecting to the same MS SQL-database.

Well, case solved for now, and I`ll repost in this thread if the issue starts again. Thanks for all the reply - and the reason I put it here on the forum is for people to easily try the different solutions here if they experience issues with MS SQL on FME Server.

The real SigTill
steveatsafe
Safer
Forum|alt.badge.img+12
  • steveatsafeSafer
  • Safer
  • January 30, 2017
sigtill wrote:
It had only 2008 Native Client, but I installed also the 2012. Currently BOTH are installed. Should I try to remove the 2008?

 

 

Removing the 2008 client might have been a way to reduce confusion of where the issue is occurring. But now seems unlikely to be the issues (as you've posted otherwise)
SteveatSafe
steveatsafe
Safer
Forum|alt.badge.img+12
  • steveatsafeSafer
  • Safer
  • January 30, 2017
sigtill wrote:

I tried to replace all Joiners, and I also forced these group of workspace to use only 1 specific engine. I still have workspaces using both joiner, reader, sqlexecutors - however now they do not seem to fail. I suspect there is an issue when 2 different Engines runs simultaneously connecting to the same MS SQL-database.

Well, case solved for now, and I`ll repost in this thread if the issue starts again. Thanks for all the reply - and the reason I put it here on the forum is for people to easily try the different solutions here if they experience issues with MS SQL on FME Server.

This does give us some information on what we can look for and see what is causing the problem. Thanks for sharing.

 

 

SteveatSafe
sigtill
Contributor
Forum|alt.badge.img+24
  • sigtillContributor
  • Author
  • Contributor
  • January 31, 2017
steveatsafe wrote:
This does give us some information on what we can look for and see what is causing the problem. Thanks for sharing.

 

 

We have now been running for 24 hours without any issues. We have restricted these groups of FME Workspaces to run on only 1 specific engines. Ant it seems to work. Previously more workspaces failed. We have removed the Joiners on only 1 of the 10 workspaces, the rest of the 9 still contains a mix of Joiners / SQLExecutors etc.

 

 

So for this KB:

 

https://knowledge.safe.com/articles/281/failed-to-connect-using-sql-server-2012-native-cli.html

 

 

I would add a case 10:

 

10. Restrict workspaces that connect to the same SQL Server to use only 1 engine. This is to test if this solves the issue or not.

 

 

The real SigTill

Reply


Helpful Members This Week

Community Stats

30,937
Posts
117,248
Replies
38,740
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.