Sybase Bulk Copy via DLL: Value does not fall within the expected range

Question

I am trying to migrate data from Sql Server to a Sybase 16.0 database using the .net DLLs supplied with the Sybase installation (Sybase.AdoNet4.AseClient.dll version 16.0.02).

To keep things simple I am trying to copy values from a table with a single INT column

--source table (MSSQL)
CREATE TABLE [dbo].[TO_INTS](
    [TO_INT] [int] NULL,
    [TO_INT2] [int] NULL,
    [NAME] [varchar](50) NULL,
    [DT] [datetime] NULL
) ON [PRIMARY]

to

--target table (Sybase)
CREATE TABLE dbo.TO_INTS
    (
    FROM_INT INT NOT NULL
    )
    ON 'default'

I am using the code:

public void BulkCopyFromSqlServer(string sourceConnectionString, string targetConnectionString)
{
    SqlConnection sourceConnection = null;
    AseConnection targetConnection = new AseConnection(targetConnectionString);
    IDataReader dataSource=null;
    try
    {
        targetConnection.Open();
        MssqlCommand.GetDataReader(sourceConnectionString, out sourceConnection, out dataSource);  //see below
        AseBulkCopy blk = new AseBulkCopy(targetConnection);
        blk.BulkCopyTimeout = 1200;
        blk.DestinationTableName = "TO_INTS";
        blk.ColumnMappings.Clear();
        blk.ColumnMappings.Add(new AseBulkCopyColumnMapping(0,0));
        blk.WriteToServer(dataSource);  // System.ArgumentException thrown here.
        blk.Close();

    }
    catch (AseException ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        sourceConnection.Dispose(); 
        targetConnection.Dispose();             
    }

}

//MssqlCommand.GetDataReader(sourceConnectionString, out sourceConnection, out dataSource):
public static void GetDataReader(string sqlServerConnectionString, out SqlConnection conn, out IDataReader reader)
{
    conn = new SqlConnection(sqlServerConnectionString);
    conn.Open();

    SqlCommand cmd = new SqlCommand("select * from TO_INTS", conn);
    cmd.CommandTimeout = 60;

    reader = cmd.ExecuteReader();
}

A System.ArgumentException is thrown at when WriteToServer() is called with the message "Value does not fall within the expected range". The stack trace is interesting in that it looks like the Sybase DLL cannot resolve the DB columnn name using the index provided in the mapping, which seems odd:

   at Sybase.Data.AseClient.AseBulkCopy.GetDBColName(String clientColName, Int32 clientColInx)
   at Sybase.Data.AseClient.AseBulkCopy.GenerateInsertCmdByReaderMetaInfo(DataTable rowFmt)
   at Sybase.Data.AseClient.AseBulkCopy.WriteToServer(IDataReader reader)

I have followed the same process for Sybase > Sql Server (pretty much line by line, but with the relevant DLLs switched) and this works.

Am I missing something obvious?

Answer 1

It looks like I have fixed this now.

There were two errors in my code.

The initial error arose because I did not know about putting the EnableBulkLoad parameter in the connection string. My working connection string looks like this:

string SybaseConnectionString = "Data Source=server1;Initial Catalog=mydb;persist security info=False;User Id=sa;Password=password1;Port=5000;EnableBulkLoad=2" 

Once that was added a second error was raised:

Bad row data received from the client while bulk copying into object 2080007410 partition 2080007410 in database 6. Received a row of length 11 whilst maximum or expected row length is 6.

This arose because the table name was set using:

blk.DestinationTableName = "TO_INTS";

when it should have been:

blk.DestinationTableName = "dbo.TO_INTS";

Once I added the owner then the BulkCopy worked.

For ref, now that I have got it working I am able to complete a WriteToServer call between tables with different names. Further, the column names are unique in each table, ie, source Sql Server table:

CREATE TABLE [dbo].[SOURCE_INTS](
    [TO_INT] [int] NULL,
    [TO_INT2] [int] NULL,
    [NAME] [varchar](50) NULL,
    [DT] [datetime] NULL
) ON [PRIMARY]

Target Sybase table:

CREATE TABLE dbo.TO_INTS
    (
    THE_STRING VARCHAR(50) NOT NULL,
    THE_INT INT NOT NULL,
    THE_DT DATETIME NOT NULL
    )
    LOCK ALLPAGES
    ON 'default'
GO

You will also notice that the order differs but WriteToServer handles this fine with mappings:

blk.ColumnMappings.Add(new AseBulkCopyColumnMapping(2, 0)); //string col
blk.ColumnMappings.Add(new AseBulkCopyColumnMapping(1, 1)); //int col
blk.ColumnMappings.Add(new AseBulkCopyColumnMapping(3, 2)); //datetime col

If you need more info on the Sybase Data Provider for C#, try Sybase Books Online

Hope this helps someone.