StoredProcedures[schema, name] returns null

Category: sql server smodmo

Question

shaun_chiburi on Tue, 02 Dec 2014 09:50:10


Hi.

I'm trying to pull a specific stored procedure from the StoredProcedures collection without looping through.

According to the docs

http://msdn.microsoft.com/en-us/library/Microsoft.SqlServer.Management.Smo.StoredProcedureCollection.aspx

I can do

StoredProcedure spHdt = db.StoredProcedures["template", "HashDimTemplate"];

-- i.e.  StoredProcedures[schema, name]

however this returns null all the time. Note I'm connecting to the right server, it connects fine and the stored procedure is there in the template schema and it is called HashDimTemplate. I've triple checked this! No prizes for guessing what my trying to achieve.They're there and I can get them if I loop through the collection and pick them out by name e.g. below. I really don't like this though when I should just be able to pluck them out of collection using specific attributes.

            foreach (StoredProcedure sp in db.StoredProcedures)
            {
               if (sp.Schema == "template")
               {
                    switch (sp.Name)
                    {
                        case CONST_SP_HASHDIMTEMPLATE:
                            spHdtScript = GetScript(sp);
                            break;
                    }
               }
            }


shaun

Replies

shaun_chiburi on Tue, 02 Dec 2014 09:56:14


Massive fail!

Works if I read the docs properly and do the following.

StoredProcedure spHdt = db.StoredProcedures["HashDimTemplate","template"];

i.e. name, schema

Seems a bit counter intuitive. If it's in a specific schema it seems like you have to pass the schema as well to get the proc; as I would expect since there could be more than one with the same name otherwise.