Recompile sp's after loading database

Recompile sp's after loading database

Post by Hien Da » Fri, 16 Oct 1998 04:00:00



I have to recompile some stored procedures every time I need to load a
database to a different server.  Is this a bug in SQL Server?

Thank you.

 
 
 

Recompile sp's after loading database

Post by Neil Pik » Sat, 17 Oct 1998 04:00:00


Hien,

 Not if the sp's contain database names and the databases have different
dbid numbers in the separate servers.

Quote:> I have to recompile some stored procedures every time I need to load a
> database to a different server.  Is this a bug in SQL Server?

 Neil Pike MVP/MCSE
 Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated
otherwise)

 
 
 

1. Can't recompile SP - currently in use

I have made some changes in the source of my stored procedure a I want
to "store" it - I can't, because "the stored procedure is currently in
use". I can't drop it.
How can I find out, WHO USE this procedure (process id for example)?
DBCC MEMUSAGE shows top 20 procedure in memory only. I can't kill all
users (200 connects) to free the procedure.
Any help will be appreciated.
                                       Zdenek Bittner

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

2. FTP

3. Recompiling SP's

4. OOPS! Bad libpq patch last time..

5. Long recompile times for SP's

6. Australia here you come

7. Should sp's be recompiled periodically?

8. Is there any way I can reset the log reader retries based on time

9. Optimizer recompiling SP's during Execution

10. sp's keep recompiling

11. SQL2K Profiler doesn't show duration for SP:recompile event

12. DTS - Load and execute SP's