Microsoft SQL Server和Azure SQL数据库空间编写器参数

About Database Connections

数据库格式包括数据库连接参数，用于定义和存储身份验证信息。有关共享数据库连接的一般信息，请参阅Using Database Connections。

请注意，根据上下文和/或数据库格式，数据库连接参数略有不同。

Connection

来自Connectionparameter in a database format, you can do one of the following:

  • 选择现有的先前定义的连接。看看重用数据库连接inUsing Database Connections
  • SelectAdd Database Connectionto define a new connection. See database-specific parameters below, as well as the sectionAdding a Database Connection in a WorkspaceinUsing Database Connections新连接只能可见于当前用户，或者可以在多个用户之间共享。

连接参数（添加数据库连接对话框）

Microsoft SQL Server或Azure SQL数据库的主机名。（如果使用默认配置，则不必指定端口。）

If you have configured your Microsoft SQL Server database to use a non-standard port number, you can specify this port here.

正确的语法是：

[\][:]

To initiate a database connection, enter (or browse for) the database name.

  • SQL Server身份验证：Select this option to proceed with specifying login credentials through the用户名和密码parameters.
  • Windows Authentication:Select this option when connecting through a Windows user account, and the database can validate the account name and password using the Windows principal token in the operating system. Since the user account is retrieved by Windows, the用户名和密码忽略参数。

用户名和密码

输入用户名和密码以访问该服务。

Note: ">不te:This parameter is not present in the Azure SQL Database reader and writer. The Azure SQL Database reader and writer will always request Secure Sockets Layer (SSL) encrypted connections.

When selected, this parameter requests Secure Sockets Layer (SSL) encryption for the connection. If the server does not have a certificate trusted by the client machine, the connection will fail. Otherwise, data will be encrypted before traveling over the network. There are multiple ways to trust a server certificate on a client machine.

如果未选择此参数，则加密行为将由为SQL Server本机客户端和SQL Server设置的加密属性来确定。

Tip:When加密连接被选中，请提供完全限定的服务器名称。例如，一个名为的服务器亚搏在线safe-sql-servermight have a fully qualified name of亚搏在线safe-sql-server.dev.safe.。This fully qualified name should be an exact match for the server name on the trusted certificate.

如果尚未返回结果，则在终止数据库时终止查询的时间。

If set to 0, there is no timeout. The default is 30.

Note: ">不te:如果此值不够高，则查询将返回错误提供商错误 - 超时已过期。

Spatial Parameters

Specifies whether to write geometry (planar data) or geography (geodetic data) when writing to tables.

此参数仅适用于此空间栏范围。

指定在写入表时使用的几何或地理列。

此参数仅适用于此Spatial Type范围。

Advanced

设置为“是”时，此参数将插入模式从逐个功能从功能进行更改。这可能导致插入速度六到十倍的任何位置。妥协在错误中减少了粒度，其中一个无效功能可能导致完整事务的失败。

Note: ">不te:The speed of the bulk insert will increase with transaction size (fewer transactions = faster insert). Larger transaction sizes also mean that if a transaction fails, there will be more rows to re-insert.

In Microsoft Azure SQL Database Non-Spatial (MSSQL_AZURE), Bulk Insert is very important, as the insertion speed increase is on the order of 300x. However, for Bulk mode to work with Azure, the Server and Username parameters must be changed:

  • Server:tcp:SERVERNAME.database.windows.net
  • 用户名：用户名@ servername.

其余的参数可以离开unchanged.

Note: ">不te:散装刀片(which is set to Yes by default) invalidates foreign key constraints in order to improve loading speed. You can set this parameter to No in the Navigator, or repair foreign key constraints after writing.

Start Transaction At

此参数指定上次成功事务的事务编号。第一次加载数据时，将此值设置为0。

此参数询问编写器何时开始将功能写入数据库时​​。在传入功能属于其交易编号的事务之后，编写器不会编写任何功能，其交易编号是指定的交易编号的交易。

指定值为0（这是通常设置）导致每个要输出的功能。仅在通过段落失败后恢复数据负载操作时，才会指定非零值。

在事务发生故障后，此参数可用于恢复写入。日志将打印类似的消息：

Translation aborted – rerun by setting the writer parameter “Transaction to Start Writing At” to n

wherenis the transaction number. Transaction numbers are an integer sequence (0, 1, 2, 3, etc.). All features written in the transaction whose number is logged will have been rolled back.

此时，您可能会发现服务器访问问题或输入功能中的问题，例如违反目标表的主键约束的功能。如果可以解决此问题而不更改输入功能的排序（例如，还原对服务器的访问权限，或重新生成功能上的主键属性值），则可以按照日志消息中的指令重新运行转换。

在重新运行期间，记录的交易前的交易中的所有功能都是通过翻译处理的，输入写入器，然后忽略。属于交易的特征，其数量等于或大于记录的事务编号将被写入目标表。

Note: ">不te:If this parameter is set to 0, then all features are written.

每次交易功能

This parameter sets the number of features to be placed in each transaction before a transaction is committed to the database.

默认值500用作事务间隔。

如果此参数设置为0，则使用基于功能的事务。随着作品处理的每个功能，都会检查调用的属性fme_db_transaction.。此属性的值指定撰写器是否应提交或回滚当前事务。

属性的值可以是其中一个commit_before.,commit_awter.,ROLLBACK_AFTER或者IGNORE。If thefme_db_transaction.attribute is not set in any features, then the entire write operation occurs in a single transaction.

SQL to Run Before Write

该参数允许SQL sta的执行tements before writing to a table. For example, it may be necessary to clean up a table before attempting to write to it. The statements will be executed only when the first feature arrives at the writer.

For detailed information about SQL functions, click the corresponding menu item in theSQL运行编辑器帮助关闭。

Available menu options depend on the format.

Multiple SQL commands can be delimited by a character specified using thefme_sql_delimiter.指令，嵌入在SQL块的开头。此指令后的单个字符将用于将SQL块拆分为SQL语句，然后将发送到数据库以进行执行。不te:在角色之前包含一个空格。

例如：

fme_sql_delimiter;从教师删除;从LastName ='Doe'和FirstName ='John'的人那里删除

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

在执行这些SQL语句期间发生的任何错误通常会终止读取器或写入器（取决于SQL语句的执行情况而错误。如果指定的语句先于连字符（“ - ”），则忽略此类错误。

写完后运行的SQL

该参数允许SQL sta的执行tements after a set of tables has been written. For example, it may be necessary to clean up a temporary view after creating it.

For detailed information about SQL functions, click the corresponding menu item in theSQL运行编辑器帮助关闭。

Available menu options depend on the format.

Multiple SQL commands can be delimited by a character specified using thefme_sql_delimiter.指令，嵌入在SQL块的开头。此指令后的单个字符将用于将SQL块拆分为SQL语句，然后将发送到数据库以进行执行。不te:在角色之前包含一个空格。

例如：

fme_sql_delimiter;从教师删除;从LastName ='Doe'和FirstName ='John'的人那里删除

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

在执行这些SQL语句期间发生的任何错误通常会终止读取器或写入器（取决于SQL语句的执行情况而错误。如果指定的语句先于连字符（“ - ”），则忽略此类错误。

When writing geography (geodetic) data, polygons must be oriented according to the left-hand rule: outer boundaries must be counter-clockwise and inner boundaries must be clockwise.

此参数的默认值是是的。If this parameter is set to不，FME不会自动重新定位多边形。您可能希望将此参数设置为不如果已知输入多边形具有正确的方向。

Note: ">不te:FME determines polygon orientation by projecting features onto a plane that does not wrap around the earth’s poles or 180-degree meridian, and does not take into account the curvature of the earth. Therefore, in some cases, FME may incorrectly (re)orient polygons.

If this parameter is set to是的, feature geometry will be written from an aggregate. The default is不。

This aggregate must contain individual geometries (each part is independent from the others and is its own complete geometry). Each part geometry of the aggregate must have a name.

If the aggregate contains geometries with names that match the spatial columns of the table being written, the geometries will be written to the appropriate columns.

When using this feature, the geometry/geography columns cannot be specified.

TagsMSSQL MSSQL_Spatial Azure SQL