SQL Server collation and ETL does not support sqlserverdatetime2 issues

The collation of SQL Server is roughly divided into Windows collation and SQL Servers collation. When the data is installed, defaults to Sql_latin1_general_cp1_ci_ai are not set by default. When the database is created, if you do not set a collation that uses the default data, you can also set the collation for the columns in the table.

Here are just a few things to keep in mind when you have recently encountered such problems.

First Sql_latin1_general_cp1_ci_ai corresponds to 1252, while chinese_prc_ci_as corresponds to the GBK 936. If you want to save the Chinese correctly, you need to set a collation for the library or column when you are building the library, or when you build the table. The general default should be that the column follows the database, and the database obeys the default values at the time of installation.

Then I encountered the problem and the process of resolution, record, deepen the impression:

The problem I encountered is that using ETL to extract data from Oracle to SQL Server, Oracle is encoded in UTF8. The default collation for SQL Server is Sql_latin1_general_cp1_ci_ai. The library was created without paying attention to setting the collation, using the default, which causes SQL Server to use when it is not recognized. To replace the original Chinese content. Later, the problem was discovered, because modifying the default collation is cumbersome, modifying the collation of the library directly. Then OK. (Note: At the beginning of the problem has been focused on the conversion of ETL, in fact, ETL after extracting data is not garbled, the insertion is generally no problem)

SQL Server A that successfully imported the data was later backed up to another SQL Server B. Checking the default collation of SQL Server B for the target library and the collation of the database are all chinese_prc_ci_as, no matter whether you use SSIS flat files or whether the database source imports data to Sqlserverb. This column cannot be processed, such as "because multiple code pages (936 and 1252) have been specified for the column UUID". "Such a problem. Finally, the problem is anchored to the sort of column. found that the original is in the Sqlserverb column are sql_latin1_general_cp1_ci_ai. This can be caused by the initial data or ETL help generating the table, which causes the column to be sorted as Sql_latin1_general_cp1_ci_ai. Delete tables, import data from SSIS data sources to Sqlserverb, automatically create tables (note that there are times when you need to manually modify a generated table's SQL). At this point the column is sorted correctly, as long as SQL Server to SQL Server to guide the data does not error, I think should be imported is the correct Chinese. After testing, Chinese normal, I think through the ETL will certainly be normal. This is not a test.

Conclusion:

Learn about the functions and meanings of SQL Server collation, as well as the classification, mainly Chinese and non-Chinese issues. If you select a normal collation, the corresponding text will be correct.

There are three levels of collation settings. When you install the database, create the table when the database is created (columns).

Also another question:

ETL sometimes encounters "only dates between January 1, 1753 and December, 9999 is accepted when inserting time data into SQL Server." Problem, but the target column in SQL Server is already set to DateTime2 (7). It is possible to accommodate data outside of this range, and datetime is really only allowed in this range. Therefore, the related features in ETL do not support the new version of SQL Server type.

Workaround:

When SQL Server inserts data into a Time field, if the source field is a varchar type, it is automatically converted, at which point the time of the source is converted to the standard time string (which SQL Server can automatically recognize), such as Oracle can use TO_CHAR ( XX, ' Yyyy-mm-dd HH24:mi:ss '). That can solve the problem. Note, however, that the type of the Target field is to use DateTime2 (7).

The following is an introduction to collations from http://blog.csdn.net/delphigbg/article/details/12744807. The original text also has the modification method to these three kinds of cases.

What is a sort rule? Collations specify rules for sorting and comparing string data based on specific language and locale criteria. SQL Server supports the storage of objects with different collations in a single database. MSDN Explanation: In Microsoft SQL Server, the physical storage of strings is controlled by collations. Collations specify the bit patterns that represent each character and the rules that are used to store and compare characters

When a Transact-SQL statement runs in a different database context that has different collation settings, its results may run differently. If possible, use a standardized collation for your organization. This eliminates the need to explicitly specify collations in each character or Unicode expression. If you must use an object that has different collation and code page settings, encode the query to consider the collation precedence rules.

A collation specifies the bit pattern that represents each character. It also specifies the rules for sorting and comparing characters. Collation features are language-sensitive, case-sensitive, accent-sensitive, kana-sensitive, and full-width-sensitive. As shown below:

Chinese_prc_ci_as first half: Refers to the Unicode character set, the chinese_prc_ pointer to the continental simplified Unicode collation, CI is not case-sensitive, as for accent sensitivity.

SQL Server collation and ETL does not support sqlserverdatetime2 issues