Solved

cannot modify sql server stored procedure after update of SSMS

Posted on 2016-09-21
19
289 Views
Last Modified: 2016-09-22
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
0
Comment
Question by:PeterBaileyUk
  • 9
  • 4
  • 3
  • +2
19 Comments
 

Author Comment

by:PeterBaileyUk
ID: 41810112
PS I can run the vb.net app ok and the sp works, its just access to modify them.
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41810123
How about selecting the Stored Procedure --> Right Click -->Script Stored procedure as --> ALTER To
0
 

Author Comment

by:PeterBaileyUk
ID: 41810124
no it says syntax error in TextHeader of Stored Procedure 'name of proc'. microsoft.sqlserver.smo
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 10

Expert Comment

by:HuaMinChen
ID: 41810141
Hi,
How did you restore your current schema? Did you restore it from previous backup?
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41810146
Run this command
sp_helptext N'dbo.usp_GetBulkDescStringsVehCat'

Open in new window

It seems that nested comments is the usual suspect
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41810148
What the following returns?
USE Databasename
GO
EXEC sp_helptext 'dbo.usp_GetBulkDescStringsVehCat'

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41810158
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
 

Author Comment

by:PeterBaileyUk
ID: 41810161
@ HuaMinChen no i am a rookie and this is my first foot in the water with sql server having moved from access
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41810162
then you can drop it and recreate it and check if it working...
0
 

Author Comment

by:PeterBaileyUk
ID: 41810164
ok I will try that, i need to pop out first. ran out of time
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41810165
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
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 41810168
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
 
LVL 12

Accepted Solution

by:
Nakul Vachhrajani earned 250 total points
ID: 41810171
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
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41810179
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
 

Author Comment

by:PeterBaileyUk
ID: 41810202
do i choose drop and create to?
0
 

Author Comment

by:PeterBaileyUk
ID: 41810207
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
 

Author Comment

by:PeterBaileyUk
ID: 41810209
so if i save the solution is that enough then uninstall
, then reinstall?
0
 
LVL 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41810215
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 41810250
thank you all running fine now
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question