• EN
Question

Connecting FME Server to MSSQL server

  • 9 February 2024
  • 5 replies
  • 114 views
tb09114
Rank
Userlevel 1
Badge +8

I have the following scenario.

We use FME Form to gather public data. The data gathered should be written into a MSSQL database. Since we mainly work with ArcGIS Pro, and other ESRI products our MSSQL  server has an SDE extension on it.

As long as I work in Form I can easily use sde connection files to access the databases. However, publishing a workspace utilizing an sde connection will result in errors since we do not have an ArcGIS Pro running on the same machine as our FME Server.

Until now I was using the reader and writer ‘ESRI ArcGIS Portal Feature Service’ but this is

  • slow, and
  • requires published feature services in the ArcGIS Enterprise portal.

I got our IT to open the firewall for direct access of FME Server to the MSSQL server and installed the latest msoledb drivers after getting the following error:

Microsoft SQL Server Spatial Reader: Failed to connect using Microsoft OLE DB Driver for SQL Server. Provider error '(-2147467259) Named Pipes Provider: Could not open a connection to SQL Server [53]. '.

Unfortunately this did not do the trick and I am still getting the same error message.

What is it that I am missing here, or can I not use Microsoft SQL Server Non-Spatial format to connect to SDE-databases? 

5 replies

ctredinnick
Rank
Userlevel 3
Badge +16

You can definitely use the SQL Server Non-Spatial format to connect to databases which have SDE on them. It’s generally the fastest method if saving the extra second or two is crucial, but you have to be careful if you’re writing data not to break IDs (OBJECTID might not be a SQL primary key).

If you setup the database connection on desktop (Form) and it connects, simply having that in the workspace and publishing it to server (Flow) is all the workspace needs. If it doesn’t, maybe compare the workspace log files between desktop and server to see if it’s trying to connect in the same way. I wouldn’t expect to need to install additional drivers.

tb09114
Rank
Userlevel 1
Badge +8

I was comparing the log file of my local Form run against the one on FME Server. What differs there seems to be the driver used for the connection.

 

This block is from my Form run:

2024-02-12 08:11:03|   0.4|  0.0|INFORM|Creating reader for format: Microsoft SQL Server Non-Spatial
2024-02-12 08:11:03|   0.5|  0.1|INFORM|Trying to find a DYNAMIC plugin for reader named `MSSQL_ADO'
2024-02-12 08:11:03|   0.5|  0.0|INFORM|FME Configuration: Source coordinate system for reader R_1[MSSQL_ADO] set to `EPSG:25832'
2024-02-12 08:11:03|   0.5|  0.0|INFORM|Coordinate System `EPSG:25832' parameters: CS_NAME=`ETRS89.UTM-32N' DESC_NM=`Transverse Mercator (ETRS89-TM32)' DT_NAME=`ETRS89/01' EPSG_QD=`1' GROUP=`UTMN' MAP_SCL=`1' MAX_LAT=`84.68333333333334' MAX_LNG=`12.5' MIN_LAT=`38' MIN_LNG=`5.5' PARM1=`9' PROJ=`TM' QUAD=`1' SCL_RED=`0.9996' SOURCE=`EPSG, V6.3, 25832 [Large and medium scale topographic mappi]' UNIT=`Meter' X_OFF=`500000' Y_OFF=`0.0' ZERO_X=`0.0001' ZERO_Y=`0.0001'
2024-02-12 08:11:03|   0.5|  0.0|INFORM|Loaded module 'MSSQL_ADO' from file 'C:\Program Files\FME2023_2\plugins/FME_ADO.dll'
2024-02-12 08:11:03|   0.5|  0.0|INFORM|FME API version of module 'FME_ADO' matches current internal version (4.0 20230426)
2024-02-12 08:11:03|   0.5|  0.0|INFORM|Performing query against MSSQL_ADO dataset `SQL02_NonSpatial'
2024-02-12 08:11:03|   0.5|  0.0|INFORM|MSSQL_ADO reader: Creating bulk features from individual features
2024-02-12 08:11:03|   0.5|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Opening `PublicData' for read operation
2024-02-12 08:11:03|   0.5|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Trying to connect using Microsoft OLE DB Driver 19 for SQL Server...
2024-02-12 08:11:03|   0.5|  0.0|INFORM|MSSQL_ADO reader: Creating bulk features from individual features
2024-02-12 08:11:04|   0.5|  0.0|INFORM|27: Database read complete.  Retrieved <default parameter> feature(s)
 

This block is from the FME Sever run:

2024-02-12 08:24:15|   0.5|  0.0|INFORM|Creating reader for format: Microsoft SQL Server Non-Spatial
2024-02-12 08:24:15|   0.5|  0.0|INFORM|Trying to find a DYNAMIC plugin for reader named `MSSQL_ADO'
2024-02-12 08:24:15|   0.5|  0.0|INFORM|FME Configuration: Source coordinate system for reader R_1[MSSQL_ADO] set to `EPSG:25832'
2024-02-12 08:24:15|   0.5|  0.0|INFORM|Coordinate System `EPSG:25832' parameters: CS_NAME=`ETRS89.UTM-32N' DESC_NM=`Transverse Mercator (ETRS89-TM32)' DT_NAME=`ETRS89/01' EPSG_QD=`1' GROUP=`UTMN' MAP_SCL=`1' MAX_LAT=`66.45' MAX_LNG=`12.866666666666667' MIN_LAT=`57.11666666666667' MIN_LNG=`4.183333333333334' PARM1=`9' PROJ=`TM' QUAD=`1' SCL_RED=`0.9996' SOURCE=`EPSG, V6.3, 25832 [Large and medium scale topographic mappi]' UNIT=`Meter' X_OFF=`500000' Y_OFF=`0.0' ZERO_X=`0.0001' ZERO_Y=`0.0001'
2024-02-12 08:24:16|   0.5|  0.0|INFORM|Loaded module 'MSSQL_ADO' from file 'C:\Program Files\FMEServer\Server\FMEEngineUpgrade_2022.2.2\plugins/FME_ADO.dll'
2024-02-12 08:24:16|   0.5|  0.0|INFORM|FME API version of module 'FME_ADO' matches current internal version (4.0 20220615)
2024-02-12 08:24:16|   0.5|  0.0|INFORM|Performing query against MSSQL_ADO dataset `SQL02_NonSpatial'
2024-02-12 08:24:16|   0.5|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Opening `PublicData' for read operation
2024-02-12 08:24:16|   0.5|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Trying to connect using Microsoft OLE DB Driver for SQL Server...
2024-02-12 08:24:32|   0.5|  0.0|WARN  |Microsoft SQL Server Non-Spatial Reader: Failed to connect using Microsoft OLE DB Driver for SQL Server. Provider error '(-2147467259) Named Pipes Provider: Could not open a connection to SQL Server [53]. '. Connection string 'Provider=MSOLEDBSQL;DataTypeCompatibility=80;Data Source=SQL02;Initial Catalog=PublicData;User ID=********;Password=********'
2024-02-12 08:24:32|   0.5|  0.0|INFORM|Failed to open reader

virtualcitymatt
Rank
Userlevel 5
Badge +28

Hmmm - looks like maybe differnt versions of FME and FME Server? From the log it looks like your FME Server is running on FME 2022.2.2 and FME Desktop is on 2023.2. 

I’m not sure that’s the issue but it could explain the issue. 

oliver.morris
Rank
Badge +10

On the server you have running FME Server do you have SSMS if so try to connect to SQL Server through it - ideally using the same driver. I suspect you have a connection issue between the two servers tbh.

tb09114
Rank
Userlevel 1
Badge +8

Thank you all for having a look at things and your comments.

Now I can tell you that @ctredinnick was correct and there should be no problem using the connection. 
Yes, the FME Server is on a different version than the Flow I am publishing from, but also that did not matter.

The issue was that the machine where the FME Server is on, has no domain, and is simply in WORKGROUP… Since I was using the name of the SQL Server in the connection string and not the IP the connection was not able to resolve the address. When we went down this road it dawned on us what was going on. Now the address issue is resolved, the connection is established and the speed of the workspace is sublime! 😊

Thank you all so much.

Reply


Community Stats

28,818
Posts
109,858
Replies
36,638
Members
Terms & ConditionsCookie 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.