FME and SQL Server Troubleshooting Guide

Introduction

The Microsoft SQL Server Spatial and Non-Spatial formats allow FME to store and retrieve data from a SQL Server database. Another useful resource is the SQL Server database Quick facts found in the documentation.

For introductory information on using SQL Server with FME, visit the SQL Server Tutorial series.

Checklist

Are you having problems finding the SQL Server formats in FME?

• Do you have the correct Operating System? SQL Server Spatial and Non-Spatial are available on Windows 32-bit or 64-bit installs. If you are using a Linux or Mac machine, use the Microsoft SQL Server (JDBC) Reader/Writer.
• Do you have the correct license edition? FME Desktop Pricing and Editions
• Do you have the required Native Client?
  • FME tries to connect at first with Microsoft SQL Server 2012 Native Client, if 2012 is not found, FME will try to connect again using the 2008 Native Client.
  • SQL Server Native Client 2012 can also be used with SQL Server 2014 and SQL Server 2016.

Are you having problems connecting to a SQL Server database?

• Is FME giving a Native Client error? Failed to Connect Using SQL Server 2012 Native Client
• Are you using SQL Server Authentication or Windows Authentication? SQL Server Authentication uses the credentials that are created and stored in SQL Server. In Windows Authentication, identification is confirmed through the Windows principal token: credentials are not needed.
• Have you tried the alternative format, if using SQL Server (MSSQL_ADO), try the JDBC format.
• Can you connect outside of FME?
• Is FME returning "SSL Provider: The target principal name is incorrect." Are you using only the hostname of the database server? Try using a fully qualified domain name, for example: hostname.mydomain.com

Are you having problems reading from SQL Server?

• Does your table contain geometries? Spatial tables from your database should be read using the SQL Server Spatial Reader. Non-Spatial tables can be read using the SQL Server Non-Spatial Reader. Reading a spatial table with the non-spatial reader will not successfully read in the geometries.
• Are your SQL Server tables not showing in the Table List?
  • Ensure that you have permissions to access that schema by connecting to SQL Server outside of FME and viewing the tables in the schema.
• Is Microsoft SQL Server 2012 Native Client installed?

Are you having problems writing to SQL Server?

• Failed to Open Writer? Ensure the connection parameters are correct. FME does not check for the correct parameters when adding a Writer unless a user tests the connection. Ensure that you have the required permissions to write to the table. Ensure Microsoft SQL Server 2012 Native Client is installed.
• Failed to write a feature of type, Argument Exception: 24200
• Could not commit row locally? Check the data you are writing to confirm it follows the constraints defined by an existing table in SQL Server.
• Are you using Bulk Insert? Bulk Insert can only be used for INSERTS when writing to SQL Server. For UPDATES or DELETES, Bulk Insert has to be turned off.

Are you having problems with SQL Server and Geometry or Geography?

• Have you set the correct Spatial Type? SQL Server has two Spatial Types: Geography and Geometry. Geography is used for ellipsoidal data (latitude/longitude) and Geometry is used for planar (UTM) data.
• FME is loading features with invalid geometries into my Database
• Writing to Database Tables that contain Multiple Geometry Columns

Are you having issues with SQL Server from an FME Server workspace?

• Error Configuring SQL Server Database

Additional Resources

Advanced SQL Server Use

• FME, SQL Server, and Geomedia
• Executing a Stored Procedure on SQL Server with FME

Advanced Writing Techniques

• Updating Only Certain Columns
• Date and Time Attributes

Performance Tuning

If you are not experiencing a specific issue with SQL Server but want to improve performance, please refer to Write Order and FME Performance for a detailed guide on improving Writer performance. Additionally, the Performance Tuning FME contains useful information for improving performance.

Request Technical Support

If you were not able to resolve the issue and need further technical support, please contact our support team by completing and submitting this Support Request Form.

When submitting a support request, please provide the following information (as applicable) in order to help us to provide a timely response:

• FME log file
• Source and destination datasets, or a small sample of data. It may be necessary to capture the data via a Recorder transformer
• FME Workspace
• FME version and build number (preferably a screenshot of Help > About)
• FMEReport.html (See here)

Once the form has been submitted, we will contact you to quickly resolve your technical issues.