Considerations for data conversion and conversion (SQL) _ MySQL

Before using DTS to convert or convert data between heterogeneous data and the target server, consider the changes in data types and SQL statements supported by different programs, providers, and drivers. When using microsofregulatory; SQLServer #8482; as the data source, consider the following: converting the real data type to the int data type also uses the data conversion service (DTS) before transforming or transforming data between heterogeneous data and the target server, consider different programs, providers, and driver support.
Changes in data types and SQL statements.

When Microsoft®SQL Server™Consider the following when using as a data source:

Converting the real data type to the int data type may not return the exact value, because SQL Server 2000 only supports 6-digit precision
Real data type. For example, a real number of 2147480000 may generate an int value of 2147480065.


When a text file converts the string (DBTYPE_WSTR) column to the date (DBTYPE_DATE) or time (DBTYPE_TIME) column, ole db data is transferred
The service change component only accepts one date or time format (yyyy-mm-dd hh: mm: ss. fffffffff ). Use the date and time string to convert, or use the CDate
Function Code: Microsoft ActiveX®The script converts the date correctly.


SQL Server 2000 does not support OLE DB type DBTYPE_DATE or DBTYPE_TIME. SQL Server 2000 only supports DBTYPE_DATETIME.


To access data through multiple steps, avoid using the temp table during the conversion process. Use a global temp table or create a permanent table in tempdb.


The stored procedure for returning rows from the temp table cannot be used as the conversion source. You can use stored procedures to return rows from global temp tables or tables.


When you use temporary tables for data conversion tasks, data-driven query tasks, or SQL tasks executed in the DTS designer, remember that you cannot use-
SQL statement or call the stored procedure of the temp table as the source.
This restriction is not applied outside the DTS designer. You can use programming methods to access the SQL Server temp table source statements or stored procedures.

DTS import/export wizard and DTS designer
When using the DTS import and export wizard and DTS designer to create a package, consider the following:

The DTS user interface allows existing connections in shared tasks, but the same connection cannot be used in both a conversion source and a conversion target.


Using the DTS designer or DTS import/export wizard, you may specify the read-only or in-use status (for example,
Microsoft Access and odbc dsn ). Click the "Advanced" tab in the "connection properties" dialog box, and then in the "Advanced connection properties" dialog box, set the mode
Set the property value to 1.


When you use the DTS import/export wizard or DTS designer to create a table, the owner of the table created at the destination is the current user (usually dbo), who is the source table
. This will result in dbo's attempt to create a table in the destination, and the table name already exists, leading to an attempt failure.


When you use the DTS designer to define a data-driven query, the data destination must support the ole db ICommand interface. Due to this limitation, it is similar to text
The object is not supported.


If the length of text, ntext, and image fields exceeds 8388602 bytes, the data transmission's copy SQL Server object task will be truncated.
Points. The DTS designer or DTS import/export wizard does not display any error information, but displays that the task has been successfully completed.
The unique failure indicates that a log message is written into the log file. the file name is . . Log, located in the "copy SQL Server pair
In the script file directory specified by the copy tab of the task properties dialog box. This log message describes tables and columns in detail, but does not indicate the truncated rows. Renren
What error records are written into DTS error files or SQL Server logs.

Microsoft SNA Server
When using Microsoft SNA Server as the data source, consider the following:

The Microsoft ole db provider of AS/400 and VSAM does not support SQL statements used by the DTS import/export wizard to create or truncate tables.
Microsoft Access
When using Access, consider the following:

When exporting data from SQL Server 2000 to Microsoft Access 97 or earlier, the Microsoft OLE DB provider of Access
Set the buffer for all inserts in the memory, and submit them only when the DTS import/export wizard completes the operation. The result is that when a large number of tables are exported
The memory size is small. However, you can solve this problem by constructing a SELECT statement that sends fewer rows in multiple passes.
Microsoft Visual FoxPro
Microsoft Visual FoxPro®Only the () precision of the numeric data type is supported. If the data exported to Visual FoxPro exceeds the precision
Degree is truncated and rounded in.

Visual FoxPro does not support select into statements.


The DTS Query Designer supports the Visual FoxPro insert value statement, but does not support the INSERT statement using the SELECT statement.


ODBC's Microsoft ole db driver cannot use the FoxPro ODBC driver to write BLOB to Visual FoxPro because
FoxPro does not support dynamic cursors.
ODBC
When connecting to the ODBC data source, consider the following:

The ODBC Microsoft ole db provider requires a unique key for all target tables with BLOB data columns when performing the export operation.


When using an ODBC Microsoft ole db provider with an SQL Server ODBC driver
All BLOB columns are arranged after the column. You can use the SELECT statement to re-arrange the BLOB column to the end of the source row set. The DTS import/export wizard automatically runs
Operation.


Important when you use an ODBC Microsoft ole db provider with an SQL Server ODBC driver, please try to preview
Stored procedure failed. If you use the Microsoft ole db provider of SQL Server, this problem does not occur.

If a Microsoft ODBC driver connection for SQL Server is shared by multiple threads, the connection may fail and an error message is returned.
Information "Connection is busy with results for another hstmt" (this Connection is busy with other hstmt results ). In some cases
Affects packages generated by the DTS import/export wizard. Use one of the following methods to solve this problem:
Set the MaxConcurrentSteps attribute to 1 to eliminate competing threads.


Create other ODBC connections to eliminate connection sharing.


Use the Microsoft ole db provider (SQLOLEDB) for SQL Server to connect to the database. To connect to SQL Server 6.5 data
Run Instcatl. SQL to enable access through the Microsoft ole db Provider for SQL Server.
Oracle
When using Oracle as the data source, consider the following:

The Microsoft ODBC and OLEDB drivers for Oracle support the Oracle 7.3 BLOB data type, but do not support the Oracle 8.0 data type.
For example, BLOB, CLOB, NCLOB, and BFILE are not supported.


The Microsoft OBDC driver for Oracle does not support sending Unicode strings to the Oracle server. Oracle requires Unicode
Prefix N before the string.


The Microsoft OBDC driver for Oracle does not support negative scaling of the Oracle number data type.


The Microsoft OBDC driver for Oracle reports that the Oracle number data type with no specified precision is 20 digits in length. When
If there are more than 20 digits in Oracle (not for the purpose) import, and if the destination table does not exist, you may have to manually increase the accuracy.


In the table, Oracle only supports one LONG (BLOB) data column.


You cannot import or export Oracle columns with mixed or lowercase names. You cannot use the Oracle column name (including the null value of the import/export wizard using DTS ).
Format) to convert or copy data. Oracle requires exact specifying and referencing case-sensitive column names.


To execute distributed transactions between SQL Server 2000 and Oracle, you must use Oracle 8.0.4.1 or an updated version. For more information,
See distributed transactions.


Because the Microsoft ole db Provider for Oracle does not support IcommandWithParameters, it cannot be used as a data-driven query task.
. When using this provider in the DTS designer, convert the data task, the data-driven query task, and the "parameter" button on the SQL task execution
Will be disabled.
DB2 on ibm as/400
When connecting to the DB2 data source, consider the following:

Unicode or BLOB is not supported on AS/400 systems.


On the AS/400 server, no tables with NULL column values can be converted, because AS/400 does not support the NULL syntax in its create table statement. However
If you edit the create table syntax to clear NULL references, you can send NULL values. AS/400 does NOT support not null; if NOT specified
It is assumed to be NULL.
Use Sybase ODBC driver
When connecting to a Sybase ODBC data source, consider the following:

When you use the DTS import/export wizard to convert data from SQL Server to Sybase 11:
By default, the SQL Server numeric () data type is mapped to Sybase smallmoney. To avoid data loss, change this setting.


By default, the SQL Server numeric (18, x or 19, x) data type is mapped to the Sybase money data type. To avoid data loss, change
This setting.


When you move data to a new Sybase table, if you click "OK" in the "column ING and conversion" dialog box, the wizard returns the error message "The table has been saved.
In ". Ignore this message.


You cannot use the DTS import/export wizard to remove and recreate the Sybase table. You must not use the wizard to perform this operation.
The data transmission Query Designer does not support Sybase SQLAnywhere create table statements.


Due to restrictions in the SQLAnywhere driver, the DTS import/export wizard can only move one table to the SQLAnywhere database at a time. Available
The data transmission designer can overcome this limitation. However, because the SQLAnywhere driver is not a secure thread, you must
The ExecuteInMainThread attribute is set to True.


If the table contains BLOB columns, you cannot copy the table to Sybase.


If you copy a table that contains the image data type from Sybase programmatically, changing the default BLOB setting will cause failure.
DBase and Paradox
When connecting to the dBase and Paradox data sources, consider the following:

The table names in dBase and Paradox are limited to eight characters. The column name in dBase is limited to 10 characters.
File import or export
When importing or exporting data from a text file, consider the following:

When importing or exporting data in the char or varchar column, if the client OEM code page is different from the code page on the server, some extended characters may not
Copied. When importing or exporting data in the nchar or nvarchar column, all characters can be correctly copied.


If you export BLOB (including SQL Server's text and ntext data types) columns to a fixed-length text field, set the default length
The maximum length of the BLBO field (about 2 GB ). You can choose a small but sufficient field length to prevent disk overflow or use a separator when possible.
.


The ole db provider used in DTS for text files cannot process BLOB data columns that exceed two megabytes (MB.
Code page, sorting rules, and non-Unicode data
When DTS is used to copy data between SQL Server databases with different code pages and sorting rules, data may be lost or the conversion is incorrect.

To avoid conversion problems, international data should be stored in Unicode format. Once converted to Unicode, you can easily sort any sort rules or code
The data on the page is transmitted to any Microsoft SQL Server 2000 or Microsoft SQL Server 7.0 database without data loss or conversion.
Incorrect.

In Microsoft SQL Server 2000, sorting rules are associated with specific code pages and assigned to columns. (Microsoft SQL Server 7.0
Use a single default code page and does not support column-level sorting rules ). If the code page used for the source and target columns matches, non-Unicode columns do not cause data loss.
Loss. When copying data between non-Unicode columns, if the source column does not match the target column, data will be lost. In some cases, DTS performs the best matching
ING. If the characters on the source code page do not appear in the target code page, data will be lost. In addition, DTS does not need to perform any conversions during replication.
This will cause data loss in the two code pages that are not represented by the same binary value. The following questions and guidelines apply to the use of replication SQL Server
The status of object tasks, and the use of copy column conversion to copy data between different sorting rules or code pages.

Copy an SQL Server object task
The following describes how to process non-Unicode data in a copy SQL Server object task:

When copying data between SQL Server 2000 instances, you only need to set the UseCollation attribute of the task for copying SQL Server objects, no data loss occurs.
Loss.


When you copy data from an SQL Server 2000 instance to SQL Server 7.0
Column to use the best matching ING. The data stored in columns with different code pages is interpreted as being encoded using the default code page. the conversion process
Will be accompanied by data loss.


When you copy data from SQL Server 7.0 to an SQL Server 2000 instance, SQL Server 7.0 cannot determine the sorting mapped by its default code page.
The UseCollation attribute is unavailable. No sorting rules are supported during the execution of the copy SQL Server object task. therefore
The default sorting rules for the target database. If the code page associated with the sorting rule does not match the code page of the source database, DTS will execute the best
Configuration ING.


When you copy data from SQL Server 7.0 to SQL Server 7.0, DTS executes
Optimal matching ING.
To ensure that data is not lost when copying non-Unicode data, you can use the SQL Server large-capacity copy function to export data in Unicode format, and then use
Large-capacity copy or DTS import data.

To disable the default sorting rule script, add the code or add the SQLDMOScript2_70Only value to the copy SQL
In the ScriptOptionEx attribute of the Server object task.

Copy column conversion
The following describes how to process non-Unicode data between different code pages during copy column conversion:

If the source column is Unicode and the target column is non-Unicode, perform the best match ING and try to convert data between the source column and the target column.


If the source column is not Unicode and the target column is Unicode, DTS regards the source column as a code page 1252 regardless of the actual code page.


If both the source and target columns are non-Unicode, the original data is copied without conversion, and part of the data is lost.