copying proc from sp_helptext and running it breaks lines

when copying a proc from one box to another, some lines are broken into 2 because of long lines, and hence causes errors.

is there any way to overcome this (during the copy stage).

Who is Participating?

Improve company productivity with a Business Account.Sign Up

rajeshprasathConnect With a Mentor Commented:
you can perform these operations by using the  Transfer SQL Server Objects Task in SSIS without having to write any  code.

The Transfer SQL Sever Objects task is used to transfer one or more SQL  Server objects to a different database, either on the same or another  SQL Server instance. This allows you to select different types of  objects you want to transfer. You can select tables, views, stored  procedures, user defined functions etc. Not only this, you can select a  combination of these types of objects to transfer and even select  particular objects of a particular object type.

for more details you can refer the following url,
monicaiConnect With a Mentor Commented:
instead of running sp_helptext, you can right-click the stored procedure in Object Explorer and choose "Modify" or "Script as create to > new query window".  then run the query to the new box.
BodestoneConnect With a Mentor Commented:
A couple of things would need clarification to answer this efficiently. For future reference it is always good to give a bit more background.
or instance are you running sp_helptext in SSMS and cut and pasting or is is part of a script generating dynamic SQL?

If interface based and you are just running sp_helptext in SSMS in text output mode then:
In Tools\Options go to Query Results\SQL Server\Results to Text
Change the Maximum number of characters displayed in each column.

Also you can just expand the database to the SP level in the left panel, right click the SP and Script as Create/Alter

It is also worth considering using version control software to keep textual copies of the SPs that automatically update the version in a comment within the SP so you can tell which version is on which server.

I am old fashioned with mine and use CVS.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

monicaiConnect With a Mentor Commented:
if you really insist of using sp_helptext, you may need to rewrite a new one that reuses sys,sql_modules function.
sp_helptext has a limitation in the resultset, declared as nvarchar(255).  sys.sql_modules on the other hand has a field called definition which contains the sql_text and it is declared at nvarchar(max).  

Here is a sample query for the sys.sql_modules:

SELECT a.definition
   FROM sys.sql_modules a, sys.procedures b
   WHERE a.object_id = b.object_id
   AND = '<stored procedure>';  

anushahannaAuthor Commented:
rajeshprasath/monicai, thanks for the alternatives.
anushahannaAuthor Commented:
Bodestone,I am running sp_helptext in SSMS.

your point about "Results to Text" option, is that the lines will not be truncated, even if very long, right?

>>I am old fashioned with mine and use CVS.

do you keep a version of every proc in text file, archived?
anushahannaAuthor Commented:
monicai, sql_modules is not nicely formatted, like helptext, though, not?
BodestoneConnect With a Mentor Commented:
CVS keeps a version of each committed change on the server.

It's bassic version control so you check out the SP, make your changes on the test site and commit them when done.

It also will auto update a version number in a comment when you commit so

-- $Revision: 1.1 $
-- $Revision: 1.2 $

etc. so you can tell which server has which version. That's a very simplistic view of course.

Your copy will generally be either the latest commited version or the version you are currently working on. At any point you can check out any previous version of the SP.

There are many other version control systems and I believe that you can version control SPs inside SQL Server (maybe only 2k5 and up) but I have not researched it.

I like the idea of having copies of the DDL for my procedures outside of the DB though, Especially when stored in a backed up version control system.

I actually use a 3rd party tool (Apex SQL Edit) for writing all my procs and stuff and it supports projects so it is second nature to have a cvs module for a project that contains all the SPs and functions.
If changes are needed I just edit them there and apply the changes in test. Onc ethey are commited the version numbers are all updated and I just change the DB to UAT and re-apply and test and so on.
Oh, I've not read it yet since it I have only just seen it in my mail but often the articles are good and this one is sure to engender some lively debate on exactly your topic here:
anushahannaAuthor Commented:
yes, very good article with nice concept like yours. thanks for sharing that.

is CVS a part of Apex SQL tool?
CVS is a totally separate version control tool. You need to have a CVS server which stores the repositories and some kind of client tool for checking in and out etc. Obviously these can be the same box if neccessary.

I'm not neccessarily saying that CVS is the one you use though. It's just the one I do because I am used to it and so used it as an example

It's probably worth research the best way to approach version control separately, there are many issues and pros and cons of the various implementstions. Another free one which has become very popular over the last few years is Subversion.
anushahannaAuthor Commented:
Thanks for your insights and ideas into this matter.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.