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.

1
0
12/8/2017 5:11:01 PM

Popular Answer

CREATE TABLE generated by sp_oledb_columns procedure, called during bulk copy operation preparation steps.

So as RobV said, you need to allow DDL to tempdb (execute from master db):

sp_dboption tempdb, 'ddl in tran', 'true'
0
11/24/2019 12:20:10 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow