title: Sybase ASE BulkCopy "The data type or the function is not supported" writing to a non-null user-defined type
body: <p>I am having a problem migrating a <code>NUMERIC(8,0)</code> from Sql Server to a column in ASE (v16.0) which has a user-defined type of <code>NUMERIC(8,0)</code> .</p>
  
  <p>I have simple user-defined type:</p>
  
  <pre><code>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 '&lt;&lt;&lt; FAILED DROPPING DATATYPE typ_small_id &gt;&gt;&gt;'
  ELSE
      PRINT '&lt;&lt;&lt; DROPPED DATATYPE typ_small_id &gt;&gt;&gt;'
  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 '&lt;&lt;&lt; CREATED DATATYPE typ_small_id &gt;&gt;&gt;'
  ELSE
      PRINT '&lt;&lt;&lt; FAILED CREATING DATATYPE typ_small_id &gt;&gt;&gt;'
  go
  </code></pre>
  
  <p>This is used in a small table:</p>
  
  <pre><code>create TABLE dbo.target
  (
      e_type_id     typ_small_id  /*NOT*/ NULL
  )
  </code></pre>
  
  <p>I am trying to BulkCopy a single value from SqlServer using <code>AseBulkCopy.WriteToServer</code>. The source value is defined as <code>NUMERIC(8,0)</code> in SqlServer.
  I am using the .net client for ASE and my app code is C# :</p>
  
  <p><code> 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();
       }
  }
  </code>  </p>
  
  <p>The value coming from SqlServer is 1.
  This all works fine until I define <code>target.e_type_id</code> as <code>typ_small_id  NOT NULL</code> instead of being nullable.
  When I do this I get an AseError:
  <code>The data type or the function is not supported.</code></p>
  
  <p>Stack Trace:
  <code>   at Sybase.Data.AseClient.AseBulkCopyBusinessBulk.TdsToAseDBType(TdsTypesDefines type, Int32 len, Int32 usertype, Boolean IsNullable, Int32&amp; 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</code> </p>
  
  <p>When the column is defined as <code>typ_small_id  NOT NULL</code> the below queries both successfully insert data into the column from a query editor so it looks like an error in the WriteToServer code:</p>
  
  <p><code> insert into dbo.target (    e_type_id     )
  SELECT top 1 cast(1 as numeric(8,0)) as e_type_id from source_tbl</code> </p>
  
  <p>and</p>
  
  <p><code> insert into dbo.target (    e_type_id     )
  SELECT top 1 cast(1 as int) as e_type_id from source_tbl</code> </p>
  
  <p>Needless to say the customer's table has the column defined as not null (and cannot alter the schema) so I need to find out how to migrate the data into their ASE table.</p>
  
  <p>Has any one seen this or found a workaround?</p>