SqlCommand issues

[Mike]

Greetings,

I hope I am posting to the correct newsgroup. We are having some issues
with SqlCommand as it relates to calling SP.

Actually, breaking the problem down, we are having a couple of
different issues, first with SQL Server 2005, which is failing to
adequately drop a SP in order so that we can re-create it. Or alter it.
In either case, so that we can drop an output parameter.

Thus far, attempts to drop the SP and recreate, or alter the SP, in
anyway have been futile. For example, I am seeing parameters linger
which should by rights have been dropped along with the drop or alter
attempts. Moreover, ad hoc attempts to delete the parameters we don't
want have also failed.

Assuming we can get the SP interface issues addressed, the SqlCommand
object seems to be having issues interfacing with the SP, not providing
adequate information to sp_executesql. We get errors saying SP
parameter names are not being provided, when it is plain to see that
they are. We are using the Visual Studio 2005 Professional, .NET 2.0
framework (v2.0 AFAIK).

We are developing the database using the SQL Server 2005 Developer
Edition, which may or may not be the best solution. For example, does
it provide adequate support to get the DB up and running for the first
time? Thus far I am seeing issues, such as not seeing SP in the editor
even after it has apparently been added. This could prove disastrous if
we can't see the SP we are maintaining, as in earlier versions.

Thinking out loud, I would be okay with relying upon simple INSERT,
UPDATE, and DELETE queries if it meant just getting the darned thing to
work. In other words, not using SP altogether, as cool as they are, if
they are going to be an issue that prevents us from succeeding with the
project.

Probably we need to concentrate on just getting the DB working. Then
when this is working we should be able to add in the .NET layers, but
not before.

So the question becomes, what's the next step? Our confidence in SQL
Server 2005 is marginal at best, at least using the Developer Edition
editor.

Any insight into our situation would be welcome.

Regards,
Michael

 

[Mike]

Ok, after further inspection, it looks as though SQL Server 2005 is
doing the right thing, at least from the SP maintenance POV.

It appears as though somehow the .NET framework still thinks an '@id'
parameter is expected when I know for a fact it has been updated.

Upon still further review, it looks like we were not issuing a USE
db_name statement, which explains a whole lot! This might be the thing
we were overlooking.

 

[Jim Wooley]

Check the ownership of your object in SQL. By default they are typically
set to dbo (for DataBase Owner). If your logged in user is in a different
chain, they won't see the appropriate version. Try specifying the owner in
connection with the SP name and see if that helps resolve the problem. In
other words, instead of "Alter Procedure MyProcedure" try "Alter Procedure
dbo.MyProcedure". Similarly in your command object, set the commandText to
"dbo.MyProcedure" rather than just "MyProcedure".

You may find additional help in one of the SQL Server newsgroups.

Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx