Introduction
The Microsoft SQL ServerSpatialandNon-Spatialformats allow FME to store and retrieve data from a SQL Server database. Another useful resource is the SQL Server databaseQuick factsfound in the documentation.
For introductory information on using SQL Server with FME, visit theSQL Server Tutorialseries.
Checklist
Are you having problems finding the SQL Server formats in FME?
- Do you have the correctOperating System? SQL Server Spatial and Non-Spatial are available on Windows 32-bit or 64-bit installs. If you are using aLinux or Macmachine, 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.
- Can youconnect outsideof FME?
Are you having problems reading from SQL Server?
- 你的表包含几何图形吗?空间表from your database should be read using theSQL服务器空间Reader. Non-Spatial tables can be read using theSQL Server Non-SpatialReader. 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 correctSpatial 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?
Additional Resources
Advanced SQL Server Use
Advanced Writing Techniques
Performance Tuning
If you are not experiencing a specific issue with SQL Server but want to improve performance, please refer to写的顺序和FME性能for a detailed guide on improving Writer performance. Additionally, thePerformance Tuning FMEcontains 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 thisSupport 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 aRecordertransformer
- 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.
Do you have any update SQL Server 2017? I have been testing FME Server 2018.1 with SQL 2017 and I run into to:
Microsoft SQL Server Non-Spatial Writer: Failed to connect using SQL Server 2012 Native Client. Provider error ''. Connection string 'Provider=SQLNCLI11;Data Source=;Initial Catalog=null;User ID=;Password=********' Microsoft SQL Server Non-Spatial Writer: Trying to connect using SQL Server 2008 Native Client... Microsoft SQL Server Non-Spatial Writer: Failed to connect using SQL Server 2008 Native Client. Provider error 'Description: Class not registered'. Connection string 'Provider=SQLNCLI10;Data Source=;Initial Catalog=null;User ID=;Password=********'Microsoft SQL Server Non-Spatial Writer: A suitable Microsoft SQL Server Native Client is not found.Install Microsoft SQL Server 2012 Native Client from http://www.microsoft.com/en-ca/download/details.aspx?id=29065 . Please expand 'Install Instructions' and download 'sqlncli.msi' Failed to open writer Failed to open writer Destination Feature Type Routing Correlator(RoutingFactory): Failed to open writer Failed to open writer
So I figured I needed to get a newer version of Native Client. But there is none for 2017. So...
"Instead, use the newMicrosoft OLE DB Driver for SQL Server(MSOLEDBSQL) which will be updated with the most recent server features." (fromMicrosoft)
Tried that, and I still get the same error message. I can run my workbenches just fine in Desktop but FME Server blows up.
For anyone else referring to this question, it looks like it got resolved in this Q&A here:https://knowledge.safe.com/questions/105181/microsoft-ole-db-driver-for-sql-server-for-sql-201.html
Contributors
