Hi,

I happened to see your post find it quite informative. I would like to share a link where a software engineer has shared a tip on "SqlBulkCopy In .NET".

Here is the link:

www.mindfiresolutions.com/SqlBulkCopy-In-NET-215.php

Hope you find it useful and of assistance.

Thanks,

Bijayani

I have an alternative work-around.

First, I capture the name of the schema and save it to a String.  Then I remove the dot character from the name and create a new schema.  Then I use the "ChangeSchema" method of the SMO Table Class to move the table to the new schema, and then I bulk copy the data.

Here is the code:

// System.Data cannot handle schema names that contain a "." character

// This code creates a new schema without the "." character

if (objSchema.Name.Contains("."))

{

   // Create String variables to hold the schema names

   String schemaName = objSchema.Name;

   String schemaNameNew = "";

   // Execute a Replace operation on schema name

   schemaNameNew = schemaName.Replace(".", "");

   // Create a new schema with the new name

   Schema objNewSchema = new Schema (destDB, schemaNameNew);

   // You may be loading multiple tables from this schema

   // Check to see if the schema already exists

   // Schema exists, move the Table object to the new scheam

   if (destDB.Schemas.Contains(objNewSchema.Name))

   {

       objTable.ChangeSchema(objNewSchema.Name);

   }

   // Schema does NOT exist.  Create the new schema and then

   // move the Table object to the new schema

   else

   {

       objNewSchema.Create();

       objTable.ChangeSchema(objNewSchema.Name);

   }

}

After the transfer, you can run

"ALTER AUTHORIZATION ON NewSchemaName.TableName TO OriginalSchemaName" for each table.  This is a very fast process and saves you from having to create a view.  You should be able to run ALTER AUTHORIZATION using a SqlCommand object, but I haven't tried that yet.

In our environment, we don't have to worry about that.  The target database will not be available for queries while the bulk copy operation is running.

However, the current version of my code transfers the table back to the original schema immediately after the SqlBulkCopy operation is completed:

// Now create a SqlDataReader and transfer the data

using (SqlDataReader dr = cmdCopyData.ExecuteReader())

{

 DateTime start = DateTime.Now;                        

 SqlBulkCopy sbc = new SqlBulkCopy(dest.ConnectionString.ToString());

 sbc.BulkCopyTimeout = 6000;

 sbc.BatchSize = 10000;

 sbc.DestinationTableName = "[" + objTable.Schema + "].[" + objTable.Name + "]";

 Console.WriteLine("Beginning Copy ...");

 sbc.WriteToServer(dr);

 Console.WriteLine("Copy completed in {0} seconds.", DateTime.Now.Subtract(start).Seconds);

 sbc.Close();

}

// Now transfer the object back to its original schema, if necessary

if (blnSchemaChanged == true)

{

 Schema objOrigSchema = new Schema(destDB, schemaName);

 objTable.ChangeSchema(objOrigSchema.Name);

}

Here's an issue I found with my code.  The first time I tested my code, my destination database was "empty".  The database is created at run time, so it has no schemas or tables.  When I ran my code using the empty database as the destination, it worked.  When I used an existing database as the destination, I got an error when it ran the following section of code:

// Schema exists, just move the Table object to the new schema

if (destDB.Schemas.Contains(objNewSchema.Name))

{

 objTable.ChangeSchema(objNewSchema.Name);

}

// Schema does NOT exist.  Create the new schema and then

// move the Table object to the new schema

else

{

 objNewSchema.Create();

 objTable.ChangeSchema(objNewSchema.Name);

 // Add the temporary schema to the collection so we can clean it up later

 tempSchemas.Add(schemaNameNew);

}

The code throws an SmoException when it tries to execute the ChangeSchema method.  The exception says "Schema can only be changed for an existing object."  Any ideas why this is happening?

 You commit an error. Write to me in PM, we will talk.

<a href="www.hpixel.com/.../a>