none
CreateSPs Sample Application Causes Exception. RRS feed

  • Question

  • 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)

     

    Tuesday, August 9, 2005 6:05 PM

Answers

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

    Thanks for reporting!

    Wednesday, August 10, 2005 5:26 PM
  • 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)

    Thursday, August 11, 2005 2:45 PM

All replies

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

    Thanks for reporting!

    Wednesday, August 10, 2005 5:26 PM
  • Can you please post the DDL that you used to create the database and table?

    Thanks,
    Tony Green [MSFT]
    Wednesday, August 10, 2005 6:44 PM
  • 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);
       //}
      }
     }
    }

    Wednesday, August 10, 2005 10:12 PM
  • 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)

    Thursday, August 11, 2005 2:45 PM
  • 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)


    Thanks a lot! I've learned a lot.
    Friday, February 25, 2011 2:11 AM