SMO db.StoredProcedures not finding stored procedures in different schema by name.
SMO db.StoredProcedures not finding stored procedures in different schema by name.
(OP)
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.
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.
I know that the stored procedures from other schemas are in the list because I can access them by index.
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
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.
CODE
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.
CODE
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.
CODE
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
Talk To Other Members
RE: SMO db.StoredProcedures not finding stored procedures in different schema by name.
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].
RE: SMO db.StoredProcedures not finding stored procedures in different schema by name.
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.
CODE
I hope this helps anyone who runs in to this in the future.
Patrick B