Solved

cannot modify sql server stored procedure after update of SSMS

Posted on 2016-09-21
19
333 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 16

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 16

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 49

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 16

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 49

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 13

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 13

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 49

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 49

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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