June 17, 2015 at 9:21 pm
Comments posted to this topic are about the item A Syntax Mystery in a Previously Working Procedure
June 17, 2015 at 9:35 pm
Interesting post!
But, I'm curious why would you use sp_helptext to get the content of stored procedure? Wouldn't it be easier if just right click on SP and ask SSMS to create script for Alter or Create? I tried that and it gives me correct content, the way it should be.
June 18, 2015 at 12:48 am
Well said! Those are other useful ways to get the contents. But it is obvious that many programmers may think that all ways give same results. For a programmer who is used to use sp_helptext may waste a lot of time diagnosing until he/she think of trying other ways.
June 18, 2015 at 2:09 am
Personally I would rather perform my updates from a "master" script maintained in a source control system.
You can use a VS Database project, third party equivalent, or a custom solution.
Your database will be a reflection of what the schema is, but your source code repository is a reflection of what it "should be".
It's extremely difficult to track what the schema "should be" across multiple databases; even if you only have only 1 prod database, you should at a minimum have separate dev, test and fail-over databases.
June 18, 2015 at 2:59 am
I've had similar issues before, also being an sp_helptext fan, but typically got round it by adjusting results to text and upping the output in query options.
I suspect that some of the issue is related more to wide lines without line breaks? Quite why anyone would want to scroll horizontally when they have a perfectly good mouse wheel escapes me.
Mind you, I have seen various bits of code sneakily appended to the end of a long line of white space so it doesn't usually show on the screen...
June 18, 2015 at 3:23 am
If the script comes from a third part application you will not have a "master script" in your source code control.
In that case you will have to use one of the ways highlighted to get the contents.
June 18, 2015 at 5:57 am
Thanks a lot for sharing your experience. I personally did not know/remember about sp_helptext, but I'm sure it will help me a lot in the future ...
🙂
June 18, 2015 at 6:54 am
Your example uses AdventureWorks2012, which suggests you're using SQL2012.
Then why aren't you using [sys].[all_sql_modules] or [INFORMATION_SCHEMA].[ROUTINES] ???
Creating a new custom sp_helptext seems a fruitless endeavour. I'm struggling to find something positive to say about this article. The described problem only seems to be a problem if you're using very old tooling and very old uncontrolled approaches to software development.
June 18, 2015 at 7:46 am
gary.strange-sqlconsumer (6/18/2015)
Your example uses AdventureWorks2012, which suggests you're using SQL2012.Then why aren't you using [sys].[all_sql_modules] or [INFORMATION_SCHEMA].[ROUTINES] ???
Creating a new custom sp_helptext seems a fruitless endeavour. I'm struggling to find something positive to say about this article. The described problem only seems to be a problem if you're using very old tooling and very old uncontrolled approaches to software development.
The positive point of this article is that it creates an awareness of a limitation in sp_helptext. I personally never code with lines anywhere near the maximum length supported by sp_helptext but I still appreciate this being brought to my attention. I find sp_helptext much faster than a drill down in object explorer. Some of us prefer to type instead of point and click.
June 18, 2015 at 7:50 am
I'm with @hr_sn on this. There is no reason to use sp_helptext and work at getting the output correct, when you can just right-click the procedure in the Object Explorer and get entire create script in a new tab or on the clipboard. Even better, you can right-click the procedure and select Modify from the menu and you will get a MODIFY PROC script that needs no extra work to get it right.
Really, the whole idea of these tools is to reduce the amount of work you expend to get something done. Many of the SPs I work on are somewhere between 600 to 1200 lines long and I just don't have time to spend on sp_helptext's crazy output when the better solution is only a click away.
So, if any of you are using sp_helptext to reconstruct procedures so you can modify and rebuild them, I strongly suggest dropping that practice and get on the fast track with the right-click options.
June 18, 2015 at 7:57 am
I don't know why this article only got 3 stars. I'll admit that I didn't do a deep dive on the article (I'm familiar with the problem) but the article appears to correctly identify the problem, what the symptoms and effect is, what the cause is, and what the fix is. I haven't actually tried the code but the article also provides code that anyone can use to demonstrate the problem to themselves.
While I agree that a lot of people don't even know that sp_HelpText exists, it's a good lesson to be learned for any stored procedure output especially if you look at the history of previous posts complaining of this very problem right here on SSC.
Good job, Marwa.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2015 at 8:03 am
First of all marwa, congratulations on sticking your head over the parapet and being brave enough to post an article on SQL Server Central; sharing knowledge is how people learn.
Having said that I think you've really solved a problem that was of your own making (MSDN lists the 255 character limitation). SSMS is there for a reason, so use it. Even the crazy world of Oracle has given in and created SQLDeveloper.
One last point is that if you did not have the source of the SP in a VCS because it was in a third party application, then their licensing terms probably prohibit you from modifying it.
June 18, 2015 at 8:10 am
hr_sn (6/17/2015)
Wouldn't it be easier if just right click on SP and ask SSMS to create script for Alter or Create?
Not really, because you've missed a few steps...
- Expand Databases
- Expand the specific database you're interested in
- Expand Programmability
- Expand Stored Procedures
- Locate the stored procedure you're interested in.
Only now can you "just" right click the SP.
The above rigmarole is rarely easier than typing: sp_helptext procname and pressing Alt+X. (Hands don't even have to leave the keyboard.)
Sometimes I don't even need to type the proc name, Ctrl+V will suffice. 😉
June 18, 2015 at 8:14 am
craig 81366 (6/18/2015)
hr_sn (6/17/2015)
Wouldn't it be easier if just right click on SP and ask SSMS to create script for Alter or Create?Not really, because you've missed a few steps...
- Expand Databases
- Expand the specific database you're interested in
- Expand Programmability
- Expand Stored Procedures
- Locate the stored procedure you're interested in.
Only now can you "just" right click the SP.
The above rigmarole is rarely easier than typing: sp_helptext procname and pressing Alt+X. (Hands don't even have to leave the keyboard.)
Sometimes I don't even need to type the proc name, Ctrl+V will suffice. 😉
+1
June 18, 2015 at 9:21 am
What's surprising to me here is that no one has brought up the idea of using a source control system. sp_helptext is great if you want to see if the version of a sproc in a database is up to date, but you should be using a version control repository.
Don Simpson
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply