cannot modify sql server stored procedure after update of SSMS

I updated the smss yesterday and this morning ive tried to modify a stored procedure and it wont allow me to.

its says:
TITLE: Microsoft SQL Server Management Studio
------------------------------

Script failed for StoredProcedure 'dbo.usp_GetBulkDescStringsVehCat'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.15800.18+((SSMS_Rel).160914-0312)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Syntax error in TextHeader of StoredProcedure 'usp_GetBulkDescStringsVehCat'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.15800.18+((SSMS_Rel).160914-0312)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Open in new window


ex
PeterBaileyUkAsked:
Who is Participating?
 
Nakul VachhrajaniConnect With a Mentor Technical Architect, Capgemini IndiaCommented:
I did some more digging and the version v13.0.15800.18 corresponds to "SSMS 16.4 (September 2016) release", which has been rolled back due to known issues as documented on the Download SSMS page: https://msdn.microsoft.com/en-us/library/mt238290.aspx

Suggest you rollback to the previous stable release as recommended by Microsoft.

Here is the note, verbatim:

"
  • There is a known issue with the SSMS 16.4 release and we have rolled back the download to SSMS 16.3. We will update the download link when the issue has been resolved.
  • If you have installed SSMS 16.4 and would like to revert to SSMS 16.3, you must uninstall SSMS 16.4 prior to installing SSMS 16.3
"

Reference
- SSMS Change Log: https://msdn.microsoft.com/en-us/library/mt588477.aspx
- SSMS download page: https://msdn.microsoft.com/en-us/library/mt238290.aspx
2
 
PeterBaileyUkAuthor Commented:
PS I can run the vb.net app ok and the sp works, its just access to modify them.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
How about selecting the Stored Procedure --> Right Click -->Script Stored procedure as --> ALTER To
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
PeterBaileyUkAuthor Commented:
no it says syntax error in TextHeader of Stored Procedure 'name of proc'. microsoft.sqlserver.smo
0
 
HuaMinChenBusiness AnalystCommented:
Hi,
How did you restore your current schema? Did you restore it from previous backup?
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Run this command
sp_helptext N'dbo.usp_GetBulkDescStringsVehCat'

Open in new window

It seems that nested comments is the usual suspect
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What the following returns?
USE Databasename
GO
EXEC sp_helptext 'dbo.usp_GetBulkDescStringsVehCat'

Open in new window

0
 
PeterBaileyUkAuthor Commented:
it returned so i am guessing thats good so i havnt lost anything.


Text
CREATE PROCEDURE [dbo].[usp_GetBulkDescStringsVehCat]  
  
(  
@VCategory varchar(50),  
@paramWithoutJoin int  
)  
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
 SET NOCOUNT ON;  
  
    -- Insert statements for procedure here  
 IF @paramWithoutJoin = 0   
 BEGIN   
  select strshort,StrShortTag, [dbo].[TblWords].ClientName, TblVehCat.VehCategory  
  FROM [dbo].[TblWords] LEFT JOIN [TblWordTags] ON [TblWords].ClientCodeWordPosition = [TblWordTags].ClientCodeWordPosition LEFT JOIN [TblVehCat] ON [TblWords].ClientCode = [TblVehCat].ClientCode  
  WHERE TblVehCat.VehCategory= @VCategory AND [TblWordTags].ClientCodeWordPosition Is Not Null  
  group by strshort, StrShortTag, [dbo].[TblWords].ClientName, TblVehCat.VehCategory  
  order by ClientName;  
 END   
 ELSE  
    BEGIN  
  select strshort,StrShortTag, [dbo].[TblWords].ClientName, TblVehCat.VehCategory  
  FROM [dbo].[TblWords] LEFT JOIN [TblWordTags] ON [TblWords].ClientCodeWordPosition = [TblWordTags].ClientCodeWordPosition LEFT JOIN [TblVehCat] ON [TblWords].ClientCode = [TblVehCat].ClientCode  
  WHERE TblVehCat.VehCategory= @VCategory  
  group by strshort, StrShortTag, [dbo].[TblWords].ClientName, TblVehCat.VehCategory  
  order by ClientName;  
    END   
END

Open in new window

0
 
PeterBaileyUkAuthor Commented:
@ HuaMinChen no i am a rookie and this is my first foot in the water with sql server having moved from access
0
 
John TsioumprisSoftware & Systems EngineerCommented:
then you can drop it and recreate it and check if it working...
0
 
PeterBaileyUkAuthor Commented:
ok I will try that, i need to pop out first. ran out of time
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
DO NOT DROP!
You'll lose any user permissions on the SP if you drop it. Instead use ALTER PROCEDURE. Just replace the CREATE word with ALTER and run the command.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Scripting can fail if the compatibility level of the database (or the version of SQL Server) is something that is not supported by the version of SSMS being used.

What is the version of the your underlying SQL Server, and what is the compatibility level of the database?

The v13.0 in the SSMS error message tells me that it is the SSMS for SQL Server 2016.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Nice find, Nakul.
That's the main reason why I never run to install the last versions. I like to let time pass and wait for known bugs :)
Cheers
2
 
PeterBaileyUkAuthor Commented:
do i choose drop and create to?
0
 
PeterBaileyUkAuthor Commented:
ok i just saw the intervening posts ok i havnt dropped, how do i roll back to the previous version do i just follow their link?
0
 
PeterBaileyUkAuthor Commented:
so if i save the solution is that enough then uninstall
, then reinstall?
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
You just need to uninstall SSMS 16.4, download and install SSMS 16.3.
SSMS doesn't have any impact on your databases so you won't lose any data of SQL Server configuration.
0
 
PeterBaileyUkAuthor Commented:
thank you all running fine now
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.