SQL 2K SP issue

[woyler]

Hi All,

I have an issue when I alter a stored procedure. For instance, I have 3 different SQL 2K servers. All 3 are identical. Recently I made a change to a SP on all 3. On 2 of the servers , all was fine. The SP ran as usual. On the third, when I run the altered SP, it will just hang. Very few , if any results are returned and I always have to cancel the execution. If I drop the SP and re-create is I get the same reults. However , if I create a new SP with the same code, just a different name, the newly named SP will work fine. This have happened to me a few times, on different servers. I imagine it is some sort of cache issue, but I cant find it. Anyone heard of this before?



regards,

Bill

 

[SQLBill]

Have you checked to make sure all three servers and their instances have the same service pack level? You can run this on them to check it:

SELECT ServerProperty('Edition'),
ServerProperty('ProductLevel'),
ServerProperty('ProductVersion')

-SQLBill

Posting advice: FAQ481-4875

 

[woyler]

I did find one server(the one I am having trouble with) is on SP3. The rest are SP4. Any idea what would cause this? I did recompile and free the cache and still no improvement. I will update the SP and try again.

 

[SQLBill]

I did find one server(the one I am having trouble with) is on SP3. The rest are SP4. Any idea what would cause this?

My guess would be that you didn't install SP4 on that server. Remember, if there are more than one instance, you must apply the service pack to each instance.

-SQLBill


Posting advice: FAQ481-4875

 

[woyler]

SQLBill,
Sorry , my "What would cause this" was not directed at the service pack. I was refering to what would cause the stored procedure behavior regardless of what service pack is installed.

 

[SQLBill]

SQL Server has bugs and other issues which the Service Packs fix. My guess would be that your stored procedure does something that didn't work properly before and was fixed in service pack 4.

-SQLBill

Posting advice: FAQ481-4875