C# transaction error in AseBulkCopy

c# sqlbulkcopy sybase

Question

I have a program that is performing a bulk copy into a few Sybase db tables. However, the rollback transaction isn't working. When stepping through the logic, the issue seems to be with the transaction object. I'm at a loss right now. Any advice would be great.

    public static void SqlBulkCopy()
    {
        try
        {
            conn.Open();
            using (transaction = conn.BeginTransaction())
            {
                using (bulkCopy = new AseBulkCopy(conn, AseBulkCopyOptions.Default, transaction))
                {
                    BulkCopying("DB_TABLE_1", dataTable1);
                    BulkCopying("DB_TABLE_2", dataTable2);
                    BulkCopying("DB_TABLE_3", dataTable3);
                    transaction.Commit();
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
        }
    }

    private static void BulkCopying(string dbTableName, DataTable dtSourceName)
    {
        try
        {
            bulkCopy.BatchSize = 1000;
            bulkCopy.BulkCopyTimeout = 720;
            bulkCopy.NotifyAfter = 1000;
            bulkCopy.DestinationTableName = dbTableName;
            bulkCopy.WriteToServer(dtSourceName);
        }
        catch (Exception ex)
        {
            transaction.Rollback(); 
            throw ex;
        }
    }

This is the error I got:

Sybase.Data.AseClient.AseException: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.

Popular Answer

Your problem is not with the rollback. The problem is as the error msg says: you cannot do a DDL statement inside a transaction. I don't know where that DDL statement comes from, but the message is pretty clear. One possible solution is to enable the option 'ddl in tran' for the database in questions (using sp_dboption).




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why