[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

cannot modify sql server stored procedure after update of SSMS

Posted on 2016-09-21
19
Medium Priority
?
490 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 19

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Expert Comment

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

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 53

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 19

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 53

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 14

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 14

Accepted Solution

by:
Nakul Vachhrajani earned 1000 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 53

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 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

810 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