我在将NUMERIC(8,0)从Sql Server迁移到ASE (v16.0)中的列时遇到问题，该列具有用户定义的NUMERIC(8,0)类型。
IF EXISTS (SELECT * FROM systypes WHERE name='typ_small_id')
BEGIN
EXEC sp_droptype 'typ_small_id'
IF EXISTS (SELECT * FROM systypes WHERE name='typ_small_id')
PRINT '<<< FAILED DROPPING DATATYPE typ_small_id >>>'
ELSE
PRINT '<<< DROPPED DATATYPE typ_small_id >>>'
END
go
EXEC sp_addtype 'typ_small_id','numeric(8,0)','NOT NULL'
go
IF EXISTS (SELECT * FROM systypes WHERE name='typ_small_id')
PRINT '<<< CREATED DATATYPE typ_small_id >>>'
ELSE
PRINT '<<< FAILED CREATING DATATYPE typ_small_id >>>'
go
用于小表格:
create TABLE dbo.target
(
e_type_id typ_small_id /*NOT*/ NULL
)
我正在尝试使用AseBulkCopy.WriteToServer从SqlServer BulkCopy单个值。源值在SqlServer中定义为NUMERIC(8,0)。我使用。net客户端为ASE和我的应用程序代码是c#:
public void BulkCopyFromSqlServer_t_sec_exchange(string sourceConnectionString, string targetConnectionString)
{
SqlConnection sourceConnection = null;
AseConnection targetConnection = new AseConnection(targetConnectionString);
try
{
IDataReader dataSource;
//The next method call returns a single row with a single column, defined as a NUMERIC (8,0) in SqlSvr.
MssqlReader.GetDataReaderSelect_t_sec_exchange(out sourceConnection, out dataSource);
targetConnection.Open();
AseBulkCopy blk = new AseBulkCopy(targetConnection, new AseBulkCopyOptions(), null);
blk.BulkCopyTimeout = 1200;
blk.DestinationTableName = "dbo.target";
blk.ColumnMappings.Clear();
blk.WriteToServer(dataSource);
blk.Close();
}
catch (AseException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
sourceConnection.Dispose();
targetConnection.Dispose();
}
}
来自SqlServer的值为1。这一切都很好，直到我定义target.e_type_id为typ_small_id NOT NULL，而不是可空的。当我这样做，我得到一个AseError:The data type or the function is not supported.
堆栈跟踪: at Sybase.Data.AseClient.AseBulkCopyBusinessBulk.TdsToAseDBType(TdsTypesDefines type, Int32 len, Int32 usertype, Boolean IsNullable, Int32& DateTimeLen)
at Sybase.Data.AseClient.AseBulkCopyBusinessBulk.InitMetadata()
at Sybase.Data.AseClient.AseBulkCopyBusinessBulk.InitFmtData()
at Sybase.Data.AseClient.AseBulkCopyBusinessBulk.BulkOperation()
at Sybase.Data.AseClient.AseBulkCopyBusinessBulk.RunInsertsRowsReader()
at Sybase.Data.AseClient.AseBulkCopy.WriteToServer(IDataReader reader)
at SybaseBulkCopy.SybaseCommand.BulkCopyFromSqlServer(String sourceConnectionString, String targetConnectionString) in c:..SybaseCommand.cs:line 32
当列定义为typ_small_id NOT NULL时，以下查询都成功地从查询编辑器向列插入数据，因此在WriteToServer代码中看起来像一个错误:
insert into dbo.target ( e_type_id )
SELECT top 1 cast(1 as numeric(8,0)) as e_type_id from source_tbl
和
insert into dbo.target ( e_type_id )
SELECT top 1 cast(1 as int) as e_type_id from source_tbl
不用说，客户的表将列定义为非空(并且不能更改模式)，因此我需要找出如何将数据迁移到他们的ASE表中。
有人看到这个或找到一个解决方案吗?
SAP (Sybase技术支持)已经确认这是一个bug。