0

Anytime I'm editing and debugging a SQL Server stored procedure, I'll make the changes, then refresh all along the line. I'll refresh folders: Stored Procedures, Programmability, the DB itself, all the way up to the server.

Regardless of how far up I refresh, when I click on the changed procedure, and no, I don't need to post examples of 'which' stored procedure or the code, it does this every time on any changes regardless of type.

But, when I right click on the stored procedure and say, 'Execute Stored Procedure', it always runs on the 'unchanged' code.

It takes 3 or 4 clicks on the 'Debug' button and a check of the code before the 'changed' code suddenly appears.

There seems no rhyme or reason for when the app refreshes with the edited changes, usually 3 or 4 reruns of 'Debug'.

This isn't a huge issue, just a very time consuming one.

Does anyone know how to make the dang thing refresh the 1st time? Without having to re-debug over and over and check the content for changes each time before I know they 'took'?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jdosser
  • 143
  • 1
  • 7
  • 1
    Don't run stored procedures from the GUI? Have you tried dropping the connection and reopening it? Have you tried Ctrl-Shift-R to refresh intellisense? – Richard Hansell Dec 16 '19 at 17:11
  • 1
    Regardless of what you're seeing in your SSMS window, an `EXECUTE` command is going to execute the code that's on the server. So it sounds as though the `ALTER PROCEDURE` command is getting held up somewhere along the line. And while you don't think the code you're editing is relevant, it's hard to rule that out without seeing it. – Eric Brandt Dec 16 '19 at 17:19
  • Richard Hansell's suggestion to drop the connection, (Close Management Studio) works. Eric's comment on Execute doesn't seem to apply as the call to run the stored procedure uses an 'Exec' command. So far, the only thing that works is a restart of Mgt. Studio, but that is a hassle as I either have to save and reload all the query windows I'm using, or reproduce them at restart. The optimal solution would be for 'Refresh' to work. Is this maybe a bug? – jdosser Dec 18 '19 at 16:37

1 Answers1

0

I try to use the option with recompile in the SP, and some time works,

ALTER PROCEDURE [dbo].[xx] (
    @pC_IDLEGAJO char(9),
   ....
    @P_MensajeError VarChar(500) output
)
WITH RECOMPILE

But, finally a drop it

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NombreStored]') 
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[NombreStored];

Create procedure NombreStored ....
Thom A
  • 88,727
  • 11
  • 45
  • 75
Adrian Recio
  • 1
  • 1