Solved

copying proc from sp_helptext and running it breaks lines

Posted on 2010-08-20
12
2,776 Views
Last Modified: 2012-05-10
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).

thanks
0
Comment
Question by:anushahanna
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 2

Accepted Solution

by:
rajeshprasath earned 100 total points
Comment Utility
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,
http://www.mssqltips.com/tip.asp?tip=2064
0
 
LVL 4

Assisted Solution

by:monicai
monicai earned 200 total points
Comment Utility
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.
0
 
LVL 10

Assisted Solution

by:Bodestone
Bodestone earned 200 total points
Comment Utility
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.
0
 
LVL 4

Assisted Solution

by:monicai
monicai earned 200 total points
Comment Utility
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 b.name = '<stored procedure>';  
GO

HTH!
1
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
rajeshprasath/monicai, thanks for the alternatives.
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
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?
0
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.

 
LVL 6

Author Comment

by:anushahanna
Comment Utility
monicai, sql_modules is not nicely formatted, like helptext, though, not?
0
 
LVL 10

Assisted Solution

by:Bodestone
Bodestone earned 200 total points
Comment Utility
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 $
becomes
-- $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.
0
 
LVL 10

Expert Comment

by:Bodestone
Comment Utility
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:

http://www.sqlservercentral.com/articles/scripting/70783/
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
yes, very good article with nice concept like yours. thanks for sharing that.

is CVS a part of Apex SQL tool?
0
 
LVL 10

Expert Comment

by:Bodestone
Comment Utility
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.
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
Thanks for your insights and ideas into this matter.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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

10 Experts available now in Live!

Get 1:1 Help Now