Sybase BulkCopy WriteToServer error: Incorrect syntax near ','

Question

Is it possible to populate a temp table using AseBulkCopy.WriteToServer?

I am calling the below method twice in my test app: firstly with a non-temp table and secondly with a temp table. The code runs fine with the non-temp table, but when trying to populate a temp table the error:

Incorrect syntax near ','.

is raised.

In both cases the target and source tables have just a single column, defined as an INT with the same name.

I have tried using a DataTable and an IDataReader as the source of the data and both result in the same error being raised.

I have tried using both "EnableBulkLoad=1" and "EnableBulkLoad=2" in the connection string.

I have tried using both the raw temp table name and the name prefixed with "dbo."

The data to be loaded is a single int value (ie, 1 row, 1 column) although it also happens if have longer rows or multiple rows.

It's worth noting that I can insert data into the temp table (using AseCommand.ExecuteNonQuery) and can execute a 'SELECT COUNT (1)' from the temp table (using AseCommand.ExecuteScalar) successfully.

Here is the code:

  private static void BulkCopyInsertIntoTable(string tableName)
  {
    IDataReader dataSource = null;
    SqlConnection sourceConnection = null;
    MssqlCommand.GetDataReader(SqlServerConnectionString, out sourceConnection, out dataSource);
    AseConnection targetConnection = new AseConnection(SybaseConnectionString);
    try
    {
      targetConnection.Open();
      AseCommand cmd = new AseCommand();
      AseBulkCopy blk = new AseBulkCopy(targetConnection);
      blk.DestinationTableName = tableName;
      //blk.ColumnMappings.Clear();
      //blk.ColumnMappings.Add(new AseBulkCopyColumnMapping(0, 0));//Doesn't make any difference with a datasource, causes an error to be raised with a datatable.

      Console.WriteLine("bulkcopy insert into the table " + tableName + " ..starting: datasource");
      //blk.WriteToServer(dataSource);

      Console.WriteLine("bulkcopy insert into the table " + tableName + " ..starting: datatable");
      blk.ColumnMappings.Clear();
      DataTable dt = SybaseCommand.GetFakeDataTable(); ;
      blk.WriteToServer(dt);
    }
    catch (AseException ex)
    {
      Console.WriteLine(ex.Message);
    }
    finally
    {
      targetConnection.Dispose();
      Console.WriteLine("bulkcopy insert into the table " + tableName + " ..ended");
    }
  }

Firstly, is it possible to populate a temp table using WriteToServer? Assuming it is, what might I being doing wrong?

UPDATE: When I change the line

blk.DestinationTableName = tableName;

to

blk.DestinationTableName = "XXXX";

I get the same error, so are there rules about how the temp table is named when using WriteToServer? The value of tableName is what I was using for the direct INSERT and SELECT COUNT(1) queries so I expected it to be correct.

Thanks

Answer 1

In my experience, the answer is no, you can't use AseBulkCopy.WriteToServer to populate a temporary table.