SMO db.StoredProcedures not finding stored procedures in different schema by name.

[pdbowling]

Hello, Everyone.

I am using Sql Management Objects (SMO) in Visual Studio 2012 C#.

I am attempting to instantiate a new StoredProcedure object from the db.StoredProcedures Collection.

When I try to find a stored procedure in the [dbo] schema. It works fine.

StoredProcedure sproc = db.StoredProcedures["MyDboSprocName"];

When I try to do this for a stored procedure in another schema (my other schema is called EA), it will not find the sproc.

StoredProcedure sproc = db.StoredProcedures["MyEASprocName"];  //sproc = null.

I know that the stored procedures from other schemas are in the list because I can access them by index.

StoredProcedure sproc = db.StoredProcedures[195]; // this is the sproc I am looking for "MyEASprocName"

I tried qualifying the name better to no avail using EA.MyEASprocName, [EA].[MyEASprocName], and {[EA].[MyEASprocName]}
I have no way of knowing the index beforehand. I just added a stop point and watch point and scrolled through the collection. That's how I was able to find the index number for the example.

Any insights on what the SMO is doing here would be greatly appreciated.

Thanks.
Patrick B

 

[johnny9864]

I am not sure about this but I read several comments on that problem.

Usually the default schema for a user is [dbo] and when SQL looks for an sp by name it looks at the default schema for the user and then [dbo] if not found. Maybe you should try to set the default schema for the user you are using to connect to [EA].

 

[pdbowling]

Hello, Everyone.

While I do not completely understand why I can't seem to find the stored procedure I am looking for by name straight from the collection, I did come up with a solution.

If you use Linq, you can find your item.

StoredProcedure storedProcedure =
db.StoredProcedures.Cast<StoredProcedure>().SingleOrDefault(sp => sp.Schema == "YourSchemaName" && sp.Name == "YourSprocName");

I hope this helps anyone who runs in to this in the future.
Patrick B