Considerations for transferring data from sqlserver to Oracle

If you use the DTs data import and export tool provided by sqlserver, you should note that the table names in Oracle are in upper case, while SQL Server is in lower case by default, and should be changed to upper case during conversion, otherwise, all transferred table names are enclosed in quotation marks.

================ The following content is a repost ========================== ======================================

Before using DTS to convert or convert data between heterogeneous data and the target server, consider differentProgramThe provider and driver support changes in data types and SQL statements.

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

Converting a real data type to an int data type may not return a precise value, because SQL Server 2000 only supports a 6-digit 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: the Microsoft ActiveX 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.
Bitscn. Net * China Network Management blog

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.
[Bitscn. com]

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, which is located in the "Copy SQL Server
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 data is exported from SQL Server 2000 to Microsoft Access 97 or earlier, the Microsoft OLE DB Provider DL @ bitscn_com of access is downloaded.
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 supports the numeric data type () precision. 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, [bitscn. com]
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: China _ Network Management Alliance bitscn.com

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,
[Bitscn_com]

see distributed transactions.
because the Microsoft ole db provider for Oracle does not support icommandwithparameters, it cannot be used as the destination of a data-driven Query Task
. When this provider is used in the DTs designer, the "parameter" button on the conversion data task, data-driven query task, and 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 the AS/400 system.
no tables with null column values can be converted on the AS/400 server, Because AS/400 does not support the null syntax in its create table statement.
However, If you edit the create table syntax to clear the reference to null, you can send a null value. AS/400 does not support not null. If not specified,
is null.
use the 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, SQL Server numeric (3,0) the data type is mapped to Sybase smallmoney. To avoid data loss, change this setting.
by default, the SQL Server numeric (18, X, 19, x) data type is mapped to the Sybase money data type. To avoid data loss, change
bitscn ~ Com

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. You can use the
DTS designer to overcome this restriction. However, because the sqlanywhere driver