Hi,

I just made a change in the stored procedure to send back 1 more column.
However why I try my application to get the data from that column, it fails
to do so (.NET application)

But when I goto Management Studio and run the same query, I get the response
with that column.
I couldn't figure out what is wrong.

In addition to that, yesterday on the production envoirment, I changed 1
procedure which added 3 new parameters. This was a temp change and during
that time, when the application wanted to use this procedure it got the
famous paramater count not the same problem which I expect it to do.
However, when my testing was over, I changed the procedure back but I kept
on getting the same error. Although everything is correct I got this
message. Then I got mad and remove the procedure completely from the
database to see what is going to happen. And surprise, I still get pamater
count error. It doesn't even complain about procedure not existing.

I tried sp_recompile etc. but nothing worked so we decieded to wait. This
morning that procedure was back to normal.

Can someone tell me what is wrong in SQL 2005? We were doing similar things
in SQL 2000 and it was not generating that problem. Is this a bug?

I even run the following commands but it still does not change the returned
records.

DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM
master.dbo.sysdatabases WHERE name = 'DataBaseName')

PRINT @intDBID

DBCC FLUSHPROCINDB (@intDBID)

DBCC DROPCLEANBUFFERS



Please help!

How did you make this change?  Did you use right-click/modify?  Or some
other way?  Have you considered issuing DROP/CREATE instead of ALTER?

Hi Aaron,

I did the change as you've said "right-click/modify".
As I said in earlier message, I tried DROP/CREATE.

It is still the same. And this is holding a lot of work in our end. I know
that the problem will be solved overnight but as of now, I am having this
problem.

Please help...

Show quoteHide quote

I don't have, nor have heard of, that problem in SQL Server 2005 (and I am
making changes to stored procedures daily).  So, I am not sure what help I
can offer without having more information about the environment, the
application, whether some procedures "refresh" correctly or if they all fail
the same way.  It might also be something as silly as connection string
confusion, connecting to the wrong database/environment, etc.  Embarrassing
but not uncommon.





Show quoteHide quote

Hi Sev,

Welcome to use MSDN Managed Newsgroup Support. And thanks Aaron's great
help.

From your description, my understanding of this issue is: After you modify
the store Procedure in the SQL Server 2005, you can not get the changed
effect in your application. If i misunderstood your concern, please feel
free to point it out.

As Aaron stated, you need to provide some more information.

1. Is there any procedure could refresh correctly?

2. Does this issue occured on a specific database ?

3. Since you can execute the modified store procedure correctly in
Management Studio, it's most likely that this issue is related with your
own application. What's the connection string in your application?

4. Use DBCC FREEPROCCACHE to free the procedure cache and test it again.

Hope this will be helpful!

Sincerely yours,

Wei Lu
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

use trigger
Is subquery bad?
Generating the perfect Primary Key: Using CRC32? Using MD5?
block comment???
Increased Datetime Precision