SQL Server's Query Analyzer not refreshing SPs

This problem appeared two days ago. I'm using SQL Server 2000, and nobody's made any modifications/updates/service pack installs recently. Here's the version information:

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
\tDec 17 2002 14:22:05
\tCopyright (c) 1988-2003 Microsoft Corporation
\tStandard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Anyway, I generally create stored procedures in Notepad, then copy and paste them into a blank query window in Query Analyzer; then I click on the buttons to check the syntax and save the procedure. From that point on QA will display the newest version of my procedures when I right-click on them and select "Edit".

However, as of two days ago, when I edit and save a stored procedure, everything works as expected and I don't receive any error messages. BUT, when I reopen the stored procedure in QA, my changes do not appear! It's as if QA doesn't update the stored procedure in the database. HOWEVER, when I display the stored procedure in Enterprise Manager, my changes are there. I've even printed out all of my stored procedures to a .SQL file for examination, just to verify that all stored procedures have been saved with my modifications.

I've closed and reopened QA numerous times; I've rebooted my PC at least a half dozen times, and yet the problem still persists. Asof this morning, QA still shows older versions of stored procedures, as if they're stuck in some kind of database cache that won't clear.

If anyone has seen this problem before and has a solution and/or work-around, please contact me ASAP. Thank You.

...I can't replicate the problem. Doesn't mean that there's not a problem. I generally don't use QA's or Enterprise Manager's of stored procs, as I keep the scripts external. I think it makes for easier source control. As a result, I've never used QA's object browser for the purpose.

The best way to tell if there's a cache issue is to dig into the tables and pull out the source from the underlying sys tables (that's how the QA and EM reconstruct the stored proc source code - at least that's how they're supposed to). Note, that the source for the stored proc is just a note in one of the tables, which is different than the compiled stored procedure itself.

Short of the possibility that you may be looking at a different database or server in QA than the one you are compiling, I couldn't explain the behavior. It's possible that QA has it's own cache for speed purposes, and that cache may be out of sync. But I would've that the MS would do a real time poll. But then that is assigning rational behavior based on the possible tradeoff of things slowing down.

Post #244,928 by lincoln 2/16/06 10:14:37 AM 2/16/06 10:24:44 AM Reply	SQL Server's Query Analyzer not refreshing SPs This problem appeared two days ago. I'm using SQL Server 2000, and nobody's made any modifications/updates/service pack installs recently. Here's the version information: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) \tDec 17 2002 14:22:05 \tCopyright (c) 1988-2003 Microsoft Corporation \tStandard Edition on Windows NT 5.2 (Build 3790: Service Pack 1) Anyway, I generally create stored procedures in Notepad, then copy and paste them into a blank query window in Query Analyzer; then I click on the buttons to check the syntax and save the procedure. From that point on QA will display the newest version of my procedures when I right-click on them and select "Edit". However, as of two days ago, when I edit and save a stored procedure, everything works as expected and I don't receive any error messages. BUT, when I reopen the stored procedure in QA, my changes do not appear! It's as if QA doesn't update the stored procedure in the database. HOWEVER, when I display the stored procedure in Enterprise Manager, my changes are there. I've even printed out all of my stored procedures to a .SQL file for examination, just to verify that all stored procedures have been saved with my modifications. I've closed and reopened QA numerous times; I've rebooted my PC at least a half dozen times, and yet the problem still persists. Asof this morning, QA still shows older versions of stored procedures, as if they're stuck in some kind of database cache that won't clear. If anyone has seen this problem before and has a solution and/or work-around, please contact me ASAP. Thank You. lincoln "Chicago to my mind was the only place to be. ... I above all liked the city because it was filled with people all a-bustle, and the clatter of hooves and carriages, and with delivery wagons and drays and peddlers and the boom and clank of freight trains. And when those black clouds came sailing in from the west, pouring thunderstorms upon us so that you couldn't hear the cries or curses of humankind, I liked that best of all. Chicago could stand up to the worst God had to offer. I understood why it was built--a place for trade, of course, with railroads and ships and so on, but mostly to give all of us a magnitude of defiance that is not provided by one house on the plains. And the plains is where those storms come from." -- E.L. Doctorow Never apply a Star Trek solution to a Babylon 5 problem. I am not merely a "consumer" or a "taxpayer". I am a *Citizen* of the United States. [link\|mailto:bconnors@ev1.net\|contact me] Edited by lincoln Feb. 16, 2006, 10:24:44 AM EST Expand All History
Post #244,942 by ChrisR 2/16/06 11:12:46 AM Reply	How are you retrieving the stored proc in Query Analyzer? The database holds the stored procedures in the system tables. I'm too lazy at the moment to drill down to the table that holds the source code, but you can start at the object level: SELECT *\nFROM sysobjects tab\nWHERE\n (tab.type = 'P') AND\n (tab.name = 'yourStoredProc') Substituting the name of whatever you called the stored proc.
Post #245,015 by lincoln 2/16/06 4:29:29 PM Reply	from the Object Browser panel on the left side of the screen: I expand the database tree, expand the stored procedures tree, highlight a specific stored procedure, then right-click it and select Edit. That opens it up in a new query window. lincoln "Chicago to my mind was the only place to be. ... I above all liked the city because it was filled with people all a-bustle, and the clatter of hooves and carriages, and with delivery wagons and drays and peddlers and the boom and clank of freight trains. And when those black clouds came sailing in from the west, pouring thunderstorms upon us so that you couldn't hear the cries or curses of humankind, I liked that best of all. Chicago could stand up to the worst God had to offer. I understood why it was built--a place for trade, of course, with railroads and ships and so on, but mostly to give all of us a magnitude of defiance that is not provided by one house on the plains. And the plains is where those storms come from." -- E.L. Doctorow Never apply a Star Trek solution to a Babylon 5 problem. I am not merely a "consumer" or a "taxpayer". I am a *Citizen* of the United States. [link\|mailto:bconnors@ev1.net\|contact me]
Post #245,034 by ChrisR 2/16/06 5:35:57 PM Reply	just glossing over it... ...I can't replicate the problem. Doesn't mean that there's not a problem. I generally don't use QA's or Enterprise Manager's of stored procs, as I keep the scripts external. I think it makes for easier source control. As a result, I've never used QA's object browser for the purpose. The best way to tell if there's a cache issue is to dig into the tables and pull out the source from the underlying sys tables (that's how the QA and EM reconstruct the stored proc source code - at least that's how they're supposed to). Note, that the source for the stored proc is just a note in one of the tables, which is different than the compiled stored procedure itself. Short of the possibility that you may be looking at a different database or server in QA than the one you are compiling, I couldn't explain the behavior. It's possible that QA has it's own cache for speed purposes, and that cache may be out of sync. But I would've that the MS would do a real time poll. But then that is assigning rational behavior based on the possible tradeoff of things slowing down.
Post #245,100 by ChrisR 2/17/06 9:54:26 AM Reply	Getting at the source If you want to directly see the source in the underlying database, the following query can be used: SELECT com.text\nFROM\n dbo.sysobjects obj\n INNER JOIN dbo.syscomments com ON\n (com.id = obj.id)\nWHERE\n (obj.xtype = 'P') AND\n (obj.name = 'YourStoredProcName')
Post #245,585 by lincoln 2/21/06 10:04:19 AM Reply	the source shows the latest changes As mentioned previously, when using Enterprise Manager to display/edit the stored procedures, my latest modifications are shown there, so they've made it into the database. I just don't understand why Query Analyzer isn't showing them correctly. I've been working with SQL Server since v 7.0 and have never encountered this problem before. This morning I even talked our hardware guy into rebooting the server, just for kicks, but that didn't change the situation. lincoln "Chicago to my mind was the only place to be. ... I above all liked the city because it was filled with people all a-bustle, and the clatter of hooves and carriages, and with delivery wagons and drays and peddlers and the boom and clank of freight trains. And when those black clouds came sailing in from the west, pouring thunderstorms upon us so that you couldn't hear the cries or curses of humankind, I liked that best of all. Chicago could stand up to the worst God had to offer. I understood why it was built--a place for trade, of course, with railroads and ships and so on, but mostly to give all of us a magnitude of defiance that is not provided by one house on the plains. And the plains is where those storms come from." -- E.L. Doctorow Never apply a Star Trek solution to a Babylon 5 problem. I am not merely a "consumer" or a "taxpayer". I am a *Citizen* of the United States. [link\|mailto:bconnors@ev1.net\|contact me]
Post #245,592 by ChrisR 2/21/06 11:27:39 AM Reply	Occam's Razor and whatnot The simplest explanation is that program #1 (Enterprise Manager) is looking at different underlying data than program #2 (Query Analyzer). In order to narrow the problem down further, one eliminates the variable of program #1 and concentrates on the internal consistency of program #2. If the query run from program #2 returns the correct result, but its object browser does not, then you can report the inconsistent behavior completely in terms of program #2 without bringing program #1 into the mix - it's just a corroborating piece of evidence.
Post #245,586 by mmoffitt 2/21/06 10:08:37 AM Reply	Izzat better than sp_helptext? bcnu, Mikem It would seem, therefore, that the three human impulses embodied in religion are fear, conceit, and hatred. The purpose of religion, one might say, is to give an air of respectibility to these passions. -- Bertrand Russell
Post #245,587 by ChrisR 2/21/06 10:50:35 AM Reply	Same difference TIMTOWTDI - or some such acronym. Looking at the source for sp_helptext, it's pretty well just grabbing the syscomments: SELECT text\nFROM syscomments \nWHERE id = OBJECT_ID(@objname) and encrypted = 0\nORDER BY number, colid Since it's a question of tools, though, I'd want to know what's actually in the underlying tables, stripped of any intermediate facilities (including stored procs). And I'd want to see that raw value in the same tool that is causing the grief.
Post #246,693 by lincoln 3/3/06 2:39:44 PM Reply	Problem no longer appearing had to make a bunch of SPs today, so for shits and grins I went out and edited a few after creation, saved them, then re-opened them in Query Analyzer. All changes were there. Nobody has futzed with the box, installed any patches, no automatic installtion of Windows Updates, etc. in the past couple of weeks. Bah. lincoln "Chicago to my mind was the only place to be. ... I above all liked the city because it was filled with people all a-bustle, and the clatter of hooves and carriages, and with delivery wagons and drays and peddlers and the boom and clank of freight trains. And when those black clouds came sailing in from the west, pouring thunderstorms upon us so that you couldn't hear the cries or curses of humankind, I liked that best of all. Chicago could stand up to the worst God had to offer. I understood why it was built--a place for trade, of course, with railroads and ships and so on, but mostly to give all of us a magnitude of defiance that is not provided by one house on the plains. And the plains is where those storms come from." -- E.L. Doctorow Never apply a Star Trek solution to a Babylon 5 problem. I am not merely a "consumer" or a "taxpayer". I am a *Citizen* of the United States. [link\|mailto:bconnors@ev1.net\|contact me]
Post #246,791 by JayMehaffey 3/4/06 12:24:42 PM Reply	Its the magic of Microsoft
Post #246,817 by folkert 3/4/06 6:22:46 PM Reply	s/magic/black-spaghetti-code-magic/ -- [link\|mailto:greg@gregfolkert.net\|greg], [link\|http://www.iwethey.org/ed_curry\|REMEMBER ED CURRY!] @ *i_wethey* Freedom is not FREE. Yeah, but 10s of Trillions of US Dollars? SELECT * FROM scog WHERE ethics > 0; 0 rows returned.
Post #246,833 by a6l6e6x 3/4/06 11:09:15 PM Reply	Also known as "non-deterministic" behavior. Alex When fascism comes to America, it'll be wrapped in a flag and carrying a cross. -- Sinclair Lewis
Post #246,840 by folkert 3/5/06 1:11:56 AM Reply	I was joking. You sound serious! No, I wasn't really joking. -- [link\|mailto:greg@gregfolkert.net\|greg], [link\|http://www.iwethey.org/ed_curry\|REMEMBER ED CURRY!] @ *i_wethey* Freedom is not FREE. Yeah, but 10s of Trillions of US Dollars? SELECT * FROM scog WHERE ethics > 0; 0 rows returned.
Post #246,862 by a6l6e6x 3/5/06 12:09:09 PM Reply	It's a "hot button" for me. :) Alex When fascism comes to America, it'll be wrapped in a flag and carrying a cross. -- Sinclair Lewis

Welcome to IWETHEY!