cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member182259
Contributor
0 Kudos

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);

???

Former Member
0 Kudos

Jeff,

Thanks for your reply.

Thanks for your reply; however that does not make any difference.

I found a post citing the need to put the "EnableBulkLoad=" parm in the connection string.

I get the following results:

EnableBulkLoad Value Result.
0Value does not fall within the expected range.
1Bad 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.
2Bad 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.
3Value does not fall within the expected range

Any ideas?

Thanks

Dave

former_member182259
Contributor
0 Kudos

One possibility is that the bulk operation INTO ASE is expecting native data format ....and you are sending data in character mode.   This would be analogous to running bcp -n vs. bcp -c with ASE when loading from a flat file.   With an integer, you could get 10 characters - but with negatives, you would have a char length of 11 - which kind of corresponds with the error....   You may want to check ASEBulkCopy class to see if any properties to control native/character mode - should be in same class as the one that signals column/row terminators, etc.

Former Member
0 Kudos

Jeff,

That sounds plausible.

Where will I get more info on setting the character mode.

I've read the PDF from SyBooks Online (which is for 15.7 as I could not find a 16.0 version) but the only references in there to character set is something set in the ConnectionString to ClientDefault or ServerDefault, neither of which got me any closer.

I could not find anything in the AseBulkCopy class to specify delimiters of any sort.

Have you any references that will help me?

Dave

Former Member
0 Kudos

C# ADO doesn't require delimiters or char format.

The only documented methods are

void WriteToServer( DataRow[ ] rows )

void WriteToServer( DataTable table )

void WriteToServer( IDataReader reader )

void WriteToServer( DataTable table, DataRowState rowState )

Parameters

    reader . an IDataReader. Data from the interface is written to the data source.

    rows . rows of data in a DataTable. The data is written to the data source.

    rowState . specifies the state a data row must be in for it to be copied.

    table . a DataTable. Data from this table is written to the data source.

Former Member
0 Kudos

Hi Dave,

Please verify the 16.0 PL02 version.  The errors

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.

should be resolved in that release.  If possible try PL05.  It's an odd error in that you aren't mapping the varchar column at all but perhaps this is happening internally.

I don't know why the mapping using the column ordinals is not working. Can you try using the actual column names in the mapping to see if you can get that to work?  If I use (0, 0) I get exceptions:

Exception: System.IndexOutOfRangeException: Index was outside the bounds of the array.

If you are able - update to PL05, test and if problems persist I need to report this to ASE Driver engineering after some initial investigation. For one thing, I am surprised to see the "bad row data" error.  We'll need to replicate that (I can't using 15.7 SP130 release of 2.0 Provider and 4.0 Provider) error.  I can only replicate the IndexOutOfRange.

I am using DataAdapter for the source so maybe Reader has something to do with it but not sure why that would make a difference.  I suggest the incident since it makes for tracking much easier.


Cheers,

-Paul

Former Member
0 Kudos

It looks like I have fixed this now.

The initial error arose because I did not know about putting the EnableBulkLoad parameter in the connection string.

Once that was added the second error arose because the table name was set using:

blk.DestinationTableName = "TO_INTS";

when it should have been:

blk.DestinationTableName = "dbo.TO_INTS";

Can I ask that you ask your engineering team to provide a more useful error message, such as "Table not found" or "Owner not specified"? That would have made debugging (once I'd added the EnableBulkLoad parameter to the connection string) easier.

Thanks all for your interest & assistance.

dawn_kim
Contributor
0 Kudos

Hi Dave,

Did you get any error? I thought we had a CR for this problem and I thought it was fixed already but maybe not, I will double check.

Thanks,
Dawn Kim

Former Member
0 Kudos

Dawn,

The errors are detailed in my posts above, timestamped

13-Jan-2015 12:34

and

12-Jan-2015 12:18

.

Does that answer your question?

Former Member
0 Kudos

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


Answers (3)

Answers (3)

dawn_kim
Contributor
0 Kudos

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

Former Member
0 Kudos

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.

Former Member
0 Kudos

Mike,

Thanks for your reply.

I have tried not using IDataReader; instead moving the data to a DataTable then calling WriteToServer with the DataTable.

I get the following results:

EnableBulkLoad Value Result.
0Unable to map columns. Check to ensure correct number of column mappings and that all columns exist.
1Bad 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.
2Bad 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.
3Unable to map columns. Check to ensure correct number of column mappings and that all columns exist.

Is this similar to anything you saw?

Thanks

Dave

Former Member
0 Kudos

> I have tried not using IDataReader; instead moving the data to a DataTable then calling WriteToServer with the DataTable.

I suspect the issue is with the mapping code - we've found some issues with this.

Try removing the mappings and change your SQL select from

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

to (or similar)

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

That way your DataTable should match the table you're bcp-ing into.

This "Received a row of length 11 whilst maximum or expected row length is 6."

certainly implies that too much data is being send.

I think there's a way to log the TDS packets being sent. Its not easy to decipher but not impossible. Freetds has some of the specification. I'll have a look into this.

Former Member
0 Kudos

> I think there's a way to log the TDS packets being sent.

Sorry - I can't find anything - I'm probably mis-remembering from using freetds.

Former Member
0 Kudos

Mike,

Thanks.

I should have said that I'd removed the code which set the mappings when I changed the code to use the DataTable.  And I had already changed the select to "select TO_INT from TO_INTS".

Cheers

Dave

Former Member
0 Kudos

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 ?

Former Member
0 Kudos

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.