Skip to Content
avatar image
Former Member

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

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 column 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?

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Jan 13, 2015 at 03:49 AM

    Maybe a stupid question - but in your program, you have:

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

    ...this will return 4 columns of data due to the select *

    ....do you get the same error if you use:

    SqlCommand cmd = new SqlCommand("select TO_INT from TO_INTS", conn);

    ???

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Dave,

      I never run into problem if I don't qualify with the schema (dbo), so I am not certain why this is happening. Which error above occurred when you didn't qualify and does it happen when using the 16.0 PL05 Provider?


      Cheers,

      -Paul


  • avatar image
    Former Member
    Jan 12, 2015 at 02:23 PM

    Hi,

    Is this the same problem as this ?

    http://scn.sap.com/thread/3633083 "C# BulkCopy column mappings don't work with IDataReader"


    We've found numerous problems until we used SDK15.7 SP122.


    There's a table here which helps with the versions of the SDK.

    http://scn.sap.com/thread/3607797


    The ADO driver its in earliest forms didn't actually do BCP, the calls were translated into insert calls.

    Make sure you have the later code. I'd have thought the 16.0 SDK would be properly implemented as proper BCP commands.

    Hope this helps.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 12, 2015 at 02:29 PM

    Here's another link

    Software Developer Kit for ASE driver versions for Windows - Wiki - SCN Wiki

    Sybase.AdoNet4.AseClient.dll version 16.0.02 is 20/4/2014 so should be pretty good.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Sorry not sure - it can't get much simpler.

      You could change the call

        MssqlCommand.GetDataReader(sourceConnectionString, out sourceConnection, out dataSource);

      to set up a static set of data table with 1 row and 1 value in it rather than read from another server.

      How many rows are in the source table ? Do any of them get loaded ?

  • Jan 13, 2015 at 02:41 PM

    Hi Dave,

    I have a better sample for the new ASE bulk copy. You need to make sure you do the mapping for the types. Let me search for it today and I will post it.

    Here is the connection string information:

    SyBooks Online


    Here is a good link on the ASE bulkcopy types etc.

    SyBooks Online

    Thanks,
    Dawn

    Add comment
    10|10000 characters needed characters exceeded