Dear Experts
I think this is an SSMS issue. My version is v18.10, aka 15.0.18390.0. Honestly don't know where best to post this - please advise if it is out of place.
Until recently, when I right clicked on an existing stored procedure and selected Script Stored Procedure As > ALTER to > New Query Editor window, it would display ALTER PROCEDURE QualifiedProcedureName AS... .
However now, when I do this, it does the above, but then it wraps the whole procedure body up as a massive quoted Unicode string and sticks an EXEC dbo.sp_executesql @statement = in front of it, after some code checking that the thing exists.
Two of my colleagues have tried doing the same thing to the same object and their SSMS setups behave as mine used to.
What do I tweak to restore the previous behaviour? I seem to remember changing a setting in the SSMS options not long ago, but can't spot it amongst the multitude of other options.
MarkD
Ha, sorted it.
The critical setting was Tools > Options > SQL Server Object Explorer > Scripting > Check for object existence. The option was set to True.
When I reverted it to False, the old behaviour returned. Very logical if you think like a computer.
Oh well -- maybe this post will help someone.
MarkD
February 16, 2022 at 2:06 pm
Ha, sorted it.
The critical setting was Tools > Options > SQL Server Object Explorer > Scripting > Check for object existence. The option was set to True.
When I reverted it to False, the old behaviour returned. Very logical if you think like a computer.
Oh well -- maybe this post will help someone.
MarkD
That's one of the most aggravating things with the SSMS scripting. Instead of just doing an "IF Exists", it wraps the entire thing in dynamic SQL.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 11, 2022 at 8:56 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply