June 11, 2009 at 7:39 am
I edited one of my stored procedures last night. I added an extra parameter and changed quite a bit of code inside the procedure.
This morning, I right click on the stored procedure name and chose Modify. The source code that appeared it the window is not the new code, but the old code from before I made the changes.
However, I have a web app that calls the procedure with the extra parameters. That web app is currently working just fine. So, I know that the compiled procedure is the "right" one. However, I am stumped as to how to pull up the source code for further editing. I also tried sp_helptext with the same results.
I have looked for two hours on google to try and discover how to "refresh" whatever system tables are holding this inaccurate data.
Worse, I now have no confidence that any proc I pull up to modify will be the most up to date source code for that proc.
Any guidance would be much appreciated.
June 11, 2009 at 8:09 am
1. are you sure you updated the SP code on the same same server..
2. you can use a profiler to capture the sp name etc...
3. did you look at the object explorer details to see the modified date for the SP.
4. goto the database and run this:select * from information_Schema.routines
order by last_altered, check to see if the Sp appears herein... also check to see that it is not saved with antoher schema name... apart from dbo.spname to something like your_username.sp name
Maninder
www.dbanation.com
June 11, 2009 at 8:26 am
I appreciate the help. But I'm asking a pretty specific question. I need to know how to "refresh" the internal system tables.
1. are you sure you updated the SP code on the same same server..
2. you can use a profiler to capture the sp name etc...
3. did you look at the object explorer details to see the modified date for the SP.
4. goto the database and run this:select * from information_Schema.routines
order by last_altered, check to see if the Sp appears herein... also check to see that it is not saved with antoher schema name... apart from dbo.spname to something like your_username.sp name
1. Yes, it's the ONLY server.
2. Don't know how to do that.
3. Don't know how to do that either.
4. I did this. Last modified time is 5:42pm yesterday. That's right. However, the Routine_Definition column still has the old code.
As I said. I know the new version of the code is what is actually compiled in the database because I have a call to it that works and it would be passing too many parameters if the old version were still the compiled version.
June 11, 2009 at 8:31 am
gmartin (6/11/2009)
I appreciate the help. But I'm asking a pretty specific question. I need to know how to "refresh" the internal system tables.
You can't 'refresh' the system tables. When you run an alter procedure, the code that's stored in the system tables is updated to the new definition. It's only stored on one place. Futhermore, as soon as a proc is modified, the exec plan for that proc is dropped from cache and will be recompiled (based on the new definition) the next time it's run.
Try refreshing Object explorer as it may have a cached and old version (Right click on Stored Procedures and select refresh)
To see the code that the server has, run sp_helptext and pass the name of the proc. That will pull the definition out of the system tables for you to see.
Are you sure the web app's looking at the same database, the same schema and the same procedure name?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2009 at 8:32 am
I am not sure about the internal system tables, but yes you can re-work the CACHE by:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
read in BOL about more info.
Maninder
www.dbanation.com
June 11, 2009 at 8:33 am
Are you sure you didnt alter the procedure within a transaction that then got rolled back ?
June 11, 2009 at 8:38 am
I tried that. I've refreshed, I've quit Management Studio completely and restarted. What I haven't done is Restart SQL Server or Reboot the machine. I can't do either of those until tonight.
I'm sure it wasn't rolled back or any of that. As I said, I am POSITIVE that the compiled (and runnable) code isn't the same as I'm getting from these tools. POSITIVE. Otherwise, the app would be broken.
There must be a way to refresh or reload that meta data.
June 11, 2009 at 8:45 am
gmartin (6/11/2009)
There must be a way to refresh or reload that meta data.
There is nothing to refresh. Procedures are only stored in one place in SQL, in a hidden system table. The views sys.sql_modules and syscomments both read that table, as does sp_helptext, as does management studio when it generates procedure definitions, as does the query engine when it parses, compiles and executes a stored procedure. There is no second place that could be out of date and no where to refresh from.
Run profiler and use the SP:Starting and SP:StatementStarting event (with the proc's name as a filter) to see exactly what you app is running.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2009 at 8:49 am
So, if I understand you correctly, you are saying that I am mistaken. That the version of the sp that I'm getting when I use sp_helptext is the compiled version.
Again, I have verifiable proof that it is not. But, it's your position that it is. Correct?
Also, I have zero experience with the Profiler.
June 11, 2009 at 8:56 am
As frustrating as it sounds, that is correct.
Double check every small little point.
June 11, 2009 at 9:00 am
I'm not sure how much smaller of a point I can check.
Example: Current "Compiled" version is
create procedure myProc @Var1 int, @Var2 int
as
begin
select @Var2 - @Var1 as RetVal
end
Old Version (What is showing using these tools)
create procedure myProc @Var1 int
as
begin
select @Var1 as RetVal
end
My web page issues ADODB call similar to...
rs.open cn, "exec myProc 15, 50"
if not rs.eof then
response.write rs("RetVal")
end if
When I run this web page, it displays 35 on the screen.
How can this be?
June 11, 2009 at 9:10 am
gmartin (6/11/2009)
So, if I understand you correctly, you are saying that I am mistaken. That the version of the sp that I'm getting when I use sp_helptext is the compiled version.
Well, compiled is a bit of a misnomer, since T-SQL is not a compiled language.
What you get when run sp_helptext is straight from the system tables. The cached plan (which is the only thing in SQL that can be refered to as 'compiled') is based off the same system tables.
Again, I have verifiable proof that it is not.
Do you? If you can show that the exact statements that executes when the app calls that procedure is different from the ones that are returned from sp_helptext, and it's the same procedure in the same schema, in the same database on the same server, then you have proof.
The only way that an app can be executing an old version of a proc is if the execution started before the proc was altered and has not yet completed. Any execution that starts after the alteration will have to get a new execution plan (because the old one is made invalid by the ALTER PROCEDURE) and will then run the new version of the proc.
Check that the proc you're looking at and the one the app's executing are in the same schema. Check that they're in the same database. Check that they have the same name. It's easy to look at something in the wrong schema and wonder why it's not matching what's running. I've done it several times.
Also, I have zero experience with the Profiler.
It's really not hard to use and there's lots of articles available for it. Here's a series of videos - http://www.sqlservercentral.com/articles/Video/66676/, and here's an MSDN page - http://msdn.microsoft.com/en-us/library/ms173757.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2009 at 9:12 am
Try this and check the results to look for duplicate names or otherwise... any altered procedures...etc
sp_msforeachdb @command1 = ' USE [?]; select db_name() as dbname, specific_catalog,routine_name,created,last_altered from information_Schema.routines
Where last_altered >= getdate()-2
order by last_altered'
Maninder
www.dbanation.com
June 11, 2009 at 9:14 am
Yikes!!!
I fear that I owe you and everyone else on this thread a sincere apology! I am very sorry that I doubted you and that I took the tone I took. My bad, all the way around.
I have one server, but it has multiple databases. The main db is where this code originally lived. Yesterday, in addition to editing it, I had to move it to a different db on the same server. I also had to change my web code to look for it on the other db rather than on the main db.
It was a late night. Again, I apologize.
Now that I have your ear, though, can I get a little info about "Set Isolation Level Read Uncommitted"?
I just discovered this feature and I have mucho use for it. But, I'm unclear as to how long it "lives" when set. If I issue that statement in a stored procedure right before a Begin Trans, will it only be in effect until I issue commit or rollback? If so, that's exactly what I want.
Thank you.
June 11, 2009 at 9:19 am
I think we've all been there , cant quite believe what you are seeing , then realising the massive cock-up.
Careful with uncommitted reads http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply