CreateSPs Sample Application Causes Exception.

When I use the CreateSPs sample app on a sample database (Person), with a sample table (Contact), the following exception occurred.

=============================================

Add object to collection failed for StoredProcedureParameterCollection of StoredProcedure 'SmoDemo.uspContactSelect'. (Microsoft.SqlServer.Smo)

-
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.1116.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add object to collection+StoredProcedureParameterCollection&LinkId=20476

-
Program Location:

at Microsoft.SqlServer.Management.Smo.ArrayListCollectionBase.AddImpl(SqlSmoObject obj)
at Microsoft.SqlServer.Management.Smo.StoredProcedureParameterCollection.Add(StoredProcedureParameter storedProcedureParameter)
at Microsoft.Samples.SqlServer.CreateStoredProcs.CreateSelectProcedure(Schema spSchema, Table tbl) in C:\Program Files\Microsoft SQL Server 2005 Samples\Engine\Programmability\SMO\CreateSPs\CS\CreateSPs\CreateStoredProcs.cs:line 276

=============================================

Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)

-
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.1116.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.CollectionCannotBeModified&LinkId=20476

-
Program Location:

at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.CheckCollectionLock()
at Microsoft.SqlServer.Management.Smo.ArrayListCollectionBase.AddImpl(SqlSmoObject obj, Int32 insertAtPosition)
at Microsoft.SqlServer.Management.Smo.ArrayListCollectionBase.AddImpl(SqlSmoObject obj)

I have filed a bug for this sample to get fixed.

Thanks for reporting!

|||Can you please post the DDL that you used to create the database and table?

Thanks,
Tony Green [MSFT]|||

Hey Tony

Here is the code, if you'd like me to e-mail you a .cs file then please let me have your e-mail address.

The database was created with SMO, as was the table and associated index.

I'm just learning SMO now and cutting and pasting from other samples so please let me know if you see anything obviously wrong on my end.

Also worthy of note is this code is really just a command line experiment and has not undergone the checks and balances I'd usually do before sharing it.

Thanks

- Doug

using System;
using System.Text;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace DatabaseFactory
{
internal static class DatabaseFactory
{
public static Database Create(string name)
{
Server _server = new Server(new ServerConnection("localhost"));

if (_server.Databases.Contains(name))
{
Console.WriteLine("Database already exists");

return null;
}
else
{
Database _database = new Database(_server, name);

FileGroup fg = new FileGroup(_database, @."PRIMARY");

// Create a new data file and add it to the file group's Files collection
// Give the data file a physical filename using the master database path of the server
DataFile df = new DataFile(fg, name + @."_Data0",
_server.Settings.MasterDBPath + @."\"
+ name + @."_Data0" + @.".mdf");

// Set the growth type to KB
df.GrowthType = FileGrowthType.KB;

// Set the growth size in KB
df.Growth = 1024;

// Set initial size in KB (optional)
df.Size = 10240;

// Set the maximum size in KB
df.MaxSize = 20480;

// Add file to file group
fg.Files.Add(df);

// Create a new data file and add it to the file group's Files collection
// Give the data file a physical filename using the master database path of the server
df = new DataFile(fg, name + @."_Data1",
_server.Settings.MasterDBPath + @."\"
+ name + @."_Data1" + @.".ndf");

// Set the growth type to KB
df.GrowthType = FileGrowthType.KB;

// Set the growth size in KB
df.Growth = 1024;

// Set initial size in KB (optional)
df.Size = 2048;

// Set the maximum size in KB
df.MaxSize = 8192;

// Add file to file group
fg.Files.Add(df);

// Add the new file group to the database's FileGroups collection
_database.FileGroups.Add(fg);

// Create a new file group named SECONDARY
fg = new FileGroup(_database, @."SECONDARY");

// Create a new data file and add it to the file group's Files collection
// Give the data file a physical filename using the master database path of the server
df = new DataFile(fg, name + @."_Data2",
_server.Settings.MasterDBPath + @."\"
+ name + @."_Data2" + @.".ndf");

// Set the growth type to KB
df.GrowthType = FileGrowthType.KB;

// Set the growth size in KB
df.Growth = 512;

// Set initial size in KB (optional)
df.Size = 1024;

// Set the maximum size in KB
df.MaxSize = 4096;

// Add file to file group
fg.Files.Add(df);

// Create a new data file and add it to the file group's Files collection
// Give the data file a physical filename using the master database path
df = new DataFile(fg, name + @."_Data3",
_server.Settings.MasterDBPath + @."\"
+ name + @."_Data3" + @.".ndf");

// Set the growth type to KB
df.GrowthType = FileGrowthType.KB;

// Set the growth size in KB
df.Growth = 512; // In KB

// Set initial size in KB (optional)
df.Size = 1024; // Set initial size in KB (optional)

// Set the maximum size in KB
df.MaxSize = 4096;

// Add file to file group
fg.Files.Add(df);

// Add the new file group to the database's FileGroups collection
_database.FileGroups.Add(fg);

// Define the database transaction log.
LogFile lf = new LogFile(_database, name + @."_Log",
_server.Settings.MasterDBPath + @."\" + name +
@."_Log" + @.".ldf");

// Set the growth type to KB
lf.GrowthType = FileGrowthType.KB;

// Set the growth size in KB
lf.Growth = 1024; // In KB

// Set initial size in KB (optional)
lf.Size = 2048; // Set initial size in KB (optional)

// Set the maximum size in KB
lf.MaxSize = 8192; // In KB

// Add file to file group
_database.LogFiles.Add(lf);

// Create the database as defined.
_database.Create();

return _database;
}
}
}

internal static class TableFactory
{
internal static Table Create(Database db, string name)
{
if (db.Tables.Contains(name) == false)
{
Default _default = new Default(db, "dfltEmptyString");

if (_default == null)
{
_default.TextBody = @."''";
_default.Create();
}

Table _table = new Table(db, name, db.DefaultSchema);

StringBuilder columnBuilder = new StringBuilder(name);
columnBuilder.Append("ID");

Column _column = new Column(_table, columnBuilder.ToString(), DataType.Int);
_table.Columns.Add(_column);
_column.Nullable = false;
_column.Identity = true;
_column.IdentitySeed = 1;
_column.IdentityIncrement = 1;

StringBuilder indexBuilder = new StringBuilder(@."PK_");
indexBuilder.Append(_table.Name);

Index _index = new Index(_table, indexBuilder.ToString());
_table.Indexes.Add(_index);
_index.IndexedColumns.Add(new IndexedColumn(_index, _column.Name));
_index.IsClustered = true;
_index.IsUnique = true;
_index.IndexKeyType = IndexKeyType.DriPrimaryKey;

_table.Create();

return _table;
}
else
{
return null;
}
}
}

//internal static class StoredProcedureFactory
//{
// internal static StoredProcedureCollection Create(Database database, Table table)
// {
// StoredProcedure sp = new StoredProcedure(database, "usp_ContactSelect");

// sp.TextBody = @."select * from Criteria";

// sp.TextHeader = @."-- comment";

// sp.Create();

// return database.StoredProcedures;
// }
//}

class Program
{
static void Main(string[] args)
{
Database _database = DatabaseFactory.Create("Person");

Table _table = null;

if (_database != null)
{
_table = TableFactory.Create(_database, "Contact");
}

//if (_table != null)
//{
// StoredProcedureCollection procedures = StoredProcedureFactory.Create(_database, _table);
//}
}
}
}

|||This was fixed after the last CTP and will be in the next. If you want to fix it now add the line below in red.

// Create the new stored procedure object

sp = new StoredProcedure(tbl.Parent, procName, spSchema.Name);

sp.TextMode = false; //ADD THIS LINE

foreach (Column col in tbl.Columns)