SqlCommandBuilder.DeriveParameters() for different user schemas

[vbkris]

Hi,

In my current SQL Server database we have multiple DB users (who could have SPs in the same name).
e.g.:
User1.SP1 Param1,Param2
User2.SP1 Param1,Param2

We use a version of EntLib that uses the SqlCommandBuilder.DeriveParameters() method that will retrieve the parameter count for an SP.

Whats happening now is when the DeriveParameters() method is invoked for "SP1", it returns the count for both the users! Therefore even though SP1 has only 2 parameters it returns 4 (2 for User1 and 2 for User2). This causes an error in our application (Parameter count mismatch error).

Is there anyway by which i can make DeriveParameters() to recognise user Schema (user name will be available in the connection string)?

We are currently using .NET 1.1...

Known is handfull, Unknown is worldfull

 

[ca8msm]

How about doing this directly on the SQL Server e.g.

sp_sproc_columns @procedure_name='SP1'

You can also pass in a @procedure_owner parameter if needed.


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions

 

[vbkris]

i dont control this. the DeriveParameter() method seems to use another hidden SP (dont have the name immediately, will give it to you once in office).

and the SQLCommandBuilder object does not seem to take schema as a property / arguement at all...

Known is handfull, Unknown is worldfull

 

[vbkris]

hey,

the SP used is sp_procedure_params_rowset.

and even the sp_sproc_columns SP is returning both the schemas.

However both do have an option of submitting the schema name. But this would mean i would have to rewrite the code in the EntLib ...

Known is handfull, Unknown is worldfull

 

[ca8msm]

Rather than call the SqlCommandBuilder.DeriveParameters() method, couldn't you just call your own function which queries the sp I mentioned above?


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions

 

[vbkris]

>>Rather than call the SqlCommandBuilder.DeriveParameters() method

yup. i have to do just that. the problem is that we do not control this call. its done by a DAL library that is used in our organisation.
on debugging it i found that the problem was with the SqlHelper class which is a member of the EntLib (Data access block).
So now i will have to rewrite the method in the SqlHelper class and compile it. the good thing is luckily our application has the entire code for the SqlHelper...

Known is handfull, Unknown is worldfull

 

[ca8msm]

Yes, I see now - I thought you were specifically calling the SqlCommandBuilder.DeriveParameters() method from your own code, rather than the library calling it. At least the code is available to change although thid does present the problem again if you ever update to a lter version of the library/SQLHelper code.

Would another option to be to block duplicate sp names in the database? I'm not sure whether you are creating these via an application or directly on the database, but there may be some method to enforce this.


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions

 

[vbkris]

>>Would another option to be to block duplicate sp names in the database?

its a little complicated. This actually happens only in our local environments.

previously our developers were using one SQL Server user to create / alter etc the objects in the DB. this user had system admin role (User1). we wanted to streamline this process by creating another user who will not have a sysadmin role.

what we did is create another user with database owner rights (User2).

but since SQL Server uses the schema as "dbo" for any object created by sysadmin role users (User1), the objects created by the new user (User2) appear with in a new schema called "User2".

therefore lets say we had an SP called SP1 in the DB, previously this would have been in the dbo schema. but now changes made to it will now be in the User2 schema.

so we now have two SPs with the same name:
dbo.SP1
User2.SP1

the only way to overcome this would be to delete the SP1 under dbo's schema everytime we make changes using this new user.
this is tedious.

the other option would be to change the database's owner to User2. this is different from the db_owner role. this will cause all SPs changed henceforth to fall under the dbo schema even though the user is not a sysadmin (atleast thats what BOL states).

This is also not possible currently as SQL Server will not allow a user who has already created / modified objects under a different role to become the database owner.

To cut a long story short, i am stuck with "fixing" this issue from the front end code...

Known is handfull, Unknown is worldfull

 

[ca8msm]

Yeah, I'd say the safest option would be to modify the library, although how about using a completely not recommended method and altering the "sp_procedure_params_rowset" procedure to always use the user 2 account? But it wasn't me who recommended that if it all goes horribly wrong which it very possibly could


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions

 

[vbkris]

>> procedure to always use the user 2 account?

no go. thought that too. unfortunately there are other DBs in this SQL Server instance

Known is handfull, Unknown is worldfull