Solved

cannot modify sql server stored procedure after update of SSMS

Posted on 2016-09-21
19
229 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
 
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 45

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

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

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 11

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 11

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 45

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 45

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now