Can't execute View script using SMO

Hi !

I have another problem. Here is now what i'm doing:
Dim unTransfert As New Transfer(BDConfig)
unTransfert.CopyAllObjects = True

unTransfert.DropDestinationObjectsFirst = True
unTransfert.CopySchema = True
unTransfert.CopyData = False
unTransfert.Options.ContinueScriptingOnError = True
unTransfert.DestinationServer = ServeurLocal.Name.ToString
unTransfert.DestinationDatabase = NouvelleBD.Name.ToString
unTransfert.Options.IncludeIfNotExists = True
NouvelleBD.ExecuteNonQuery(unTransfert.ScriptTransfer())

This failed because of my views. If i tried to copy tables only it's working fine. I've tried to copy one by one the view and then i notice that most of then failed and i think i know the reason but i don't know what to do. I've notice that the view that failed are view where the SELECT contain other view. Since those view are not created yet i've got those error. This is a big problem. We have over 300 views and i don't know which one need which one.
Is it possible to copy the views without having this kind of problem ?

Thanks !This is the first time i'm using SMO so i'm probably making some mistakes. Instead of doing this line:
NouvelleBD.ExecuteNonQuery(unTransfert.ScriptTransfer())

Now i'm doing this:
unTransfert.TransferData()

I still have the same problem. But i've notice something. If i add:
unTransfert.CopyAllObjects = false
unTransfert.CopyAllTables = True
unTransfert.CopyAllViews = True

After running for some seconds i've got an error saying it can create this view because this table doesn't exists. In fact the table exist in my BD but while doing the tranfer the views seems to be create before all the table are added. Did i do something wrong ? All i want is to copy all object from one BD to a new one and i need to do this online.

I've been working on this for 2 days and it still not working :-(....

Is someone have an idea of what i'm missing ?

Thanks !!

Can't create table definition with primary key and SMO.

I'm trying this code, but it doesn't work. Can you help me?

I show you the error and code...I don't understand what is the adverted "Primary element" in error messaje and I'm really astonished because one time the code works whitout index definition, but any way, it doesn't work now.

Thanks,

Asereware

Context Info:

Visual C# Express Edition.

SQL Server 2005 Express & SQL Server 2000. The behavior is the same in both.

Error message:

<ERROR>-
Fuente:Vivenda.Testings
Descripción simple:
Error de Crear para Tabla 'dbo.AsereTablaSMO1'.

Detalle:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Error de Crear para
Tabla 'dbo.AsereTablaSMO1'. > Microsoft.SqlServer.Management.Smo.FailedOper
ationException: No se puede crear Table '[dbo].[AsereTablaSMO1]' si aún no se ha
creado el elemento primario.
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetParentObject()
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplInit(StringColle
ction& createQuery, ScriptingOptions& so)
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
Fin del seguimiento de la pila de la excepción interna
en Vivenda.Testings.Program.TestSQLServerSMO() en Program.cs:línea 640
en Vivenda.Testings.Program.Main(String[] args) en Program.cs:línea 50

Source Code:

private static void TestSQLServerSMO()

{

SqlConnection cnn = null;

Microsoft.SqlServer.Management.Common.ServerConnection cnn1 = null;

try

{

cnn = new SqlConnection("data source=(local);initial catalog=bap;Integrated Security=true;Connect Timeout=30");

cnn1 = new Microsoft.SqlServer.Management.Common.ServerConnection(cnn);

Server local = new Server(cnn1);

Database vivenda = new Database(local, "pubs");

if (vivenda.Tables.Contains("AsereTablaSMO1"))

vivenda.Tables["AsereTablaSMO1"].Drop();

//--

//Table definition

//--

Table impTable = new Table(vivenda, "AsereTablaSMO1");

Column col1 = new Column(impTable, "AsID", new DataType(SqlDataType.Int));

col1.Nullable = false;

col1.Identity = true;

impTable.Columns.Add(col1);

Column col2 = new Column(impTable, "Description", new DataType(SqlDataType.NVarChar, 150));

col2.Nullable = true;

impTable.Columns.Add(col2);

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//Create object.

impTable.Create();

}

catch (Exception ex)

{

throw (ex);

}

finally

{

if (cnn1 != null)

{

cnn1.Disconnect();

cnn1 = null;

}

}

}

//End

You have to create the table before you can create an index on the table. Consider this VB example (derived from the Books Online examples):

Dim srv As Server
srv = New Server("MyServer")
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Table object variable by supplying the parent database and table name in the constructor.
Dim tb As Table
tb = New Table(db, "Test_Table")
'Add various columns to the table.
Dim col1 As Column
col1 = New Column(tb, "Name", DataType.NChar(50))
col1.Collation = "Latin1_General_CI_AS"
col1.Nullable = True
tb.Columns.Add(col1)
Dim col2 As Column
col2 = New Column(tb, "ID", DataType.Int)
col2.Identity = True
col2.IdentitySeed = 1
col2.IdentityIncrement = 1
tb.Columns.Add(col2)
Dim col3 As Column
col3 = New Column(tb, "Value", DataType.Real)
tb.Columns.Add(col3)
Dim col4 As Column
col4 = New Column(tb, "Date", DataType.DateTime)
col4.Nullable = False
tb.Columns.Add(col4)
'Create the table on the instance of SQL Server.
tb.Create()
Dim idx As Index
idx = New Index(tb, "TestIndex")
'Add indexed columns to the index.
Dim icol1 As IndexedColumn
icol1 = New IndexedColumn(idx, "ID", True)
idx.IndexedColumns.Add(icol1)
idx.IndexKeyType = IndexKeyType.DriUniqueKey
idx.IsClustered = False
idx.FillFactor = 50
'Create the index on the instance of SQL Server.
idx.Create()

If you try to create the index before issuing the tb.Create() method you get an exception, because the table doesn't yet exist. By creating the index after the table has been created the idx.Create() works without exception.

|||

Thanks Allen. Here is the final code section changed and working:

Alvaro

//Create object. It is fundamental before set index.

impTable.Create();

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//-

//Create Index.

//-

idx.Create();

Can't create table definition with primary key and SMO.

I'm trying this code, but it doesn't work. Can you help me?

I show you the error and code...I don't understand what is the adverted "Primary element" in error messaje and I'm really astonished because one time the code works whitout index definition, but any way, it doesn't work now.

Thanks,

Asereware

Context Info:

Visual C# Express Edition.

SQL Server 2005 Express & SQL Server 2000. The behavior is the same in both.

Error message:

<ERROR>-
Fuente:Vivenda.Testings
Descripción simple:
Error de Crear para Tabla 'dbo.AsereTablaSMO1'.

Detalle:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Error de Crear para
Tabla 'dbo.AsereTablaSMO1'. > Microsoft.SqlServer.Management.Smo.FailedOper
ationException: No se puede crear Table '[dbo].[AsereTablaSMO1]' si aún no se ha
creado el elemento primario.
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetParentObject()
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplInit(StringColle
ction& createQuery, ScriptingOptions& so)
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
Fin del seguimiento de la pila de la excepción interna
en Vivenda.Testings.Program.TestSQLServerSMO() en Program.cs:línea 640
en Vivenda.Testings.Program.Main(String[] args) en Program.cs:línea 50

Source Code:

private static void TestSQLServerSMO()

{

SqlConnection cnn = null;

Microsoft.SqlServer.Management.Common.ServerConnection cnn1 = null;

try

{

cnn = new SqlConnection("data source=(local);initial catalog=bap;Integrated Security=true;Connect Timeout=30");

cnn1 = new Microsoft.SqlServer.Management.Common.ServerConnection(cnn);

Server local = new Server(cnn1);

Database vivenda = new Database(local, "pubs");

if (vivenda.Tables.Contains("AsereTablaSMO1"))

vivenda.Tables["AsereTablaSMO1"].Drop();

//--

//Table definition

//--

Table impTable = new Table(vivenda, "AsereTablaSMO1");

Column col1 = new Column(impTable, "AsID", new DataType(SqlDataType.Int));

col1.Nullable = false;

col1.Identity = true;

impTable.Columns.Add(col1);

Column col2 = new Column(impTable, "Description", new DataType(SqlDataType.NVarChar, 150));

col2.Nullable = true;

impTable.Columns.Add(col2);

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//Create object.

impTable.Create();

}

catch (Exception ex)

{

throw (ex);

}

finally

{

if (cnn1 != null)

{

cnn1.Disconnect();

cnn1 = null;

}

}

}

//End

You have to create the table before you can create an index on the table. Consider this VB example (derived from the Books Online examples):

Dim srv As Server
srv = New Server("MyServer")
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Table object variable by supplying the parent database and table name in the constructor.
Dim tb As Table
tb = New Table(db, "Test_Table")
'Add various columns to the table.
Dim col1 As Column
col1 = New Column(tb, "Name", DataType.NChar(50))
col1.Collation = "Latin1_General_CI_AS"
col1.Nullable = True
tb.Columns.Add(col1)
Dim col2 As Column
col2 = New Column(tb, "ID", DataType.Int)
col2.Identity = True
col2.IdentitySeed = 1
col2.IdentityIncrement = 1
tb.Columns.Add(col2)
Dim col3 As Column
col3 = New Column(tb, "Value", DataType.Real)
tb.Columns.Add(col3)
Dim col4 As Column
col4 = New Column(tb, "Date", DataType.DateTime)
col4.Nullable = False
tb.Columns.Add(col4)
'Create the table on the instance of SQL Server.
tb.Create()
Dim idx As Index
idx = New Index(tb, "TestIndex")
'Add indexed columns to the index.
Dim icol1 As IndexedColumn
icol1 = New IndexedColumn(idx, "ID", True)
idx.IndexedColumns.Add(icol1)
idx.IndexKeyType = IndexKeyType.DriUniqueKey
idx.IsClustered = False
idx.FillFactor = 50
'Create the index on the instance of SQL Server.
idx.Create()

If you try to create the index before issuing the tb.Create() method you get an exception, because the table doesn't yet exist. By creating the index after the table has been created the idx.Create() works without exception.

|||

Thanks Allen. Here is the final code section changed and working:

Alvaro

//Create object. It is fundamental before set index.

impTable.Create();

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//-

//Create Index.

//-

idx.Create();