Solved

copying proc from sp_helptext and running it breaks lines

Posted on 2010-08-20
12
2,833 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
ID: 33490184
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
ID: 33490193
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
ID: 33490204
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
ID: 33490216
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
ID: 33501090
rajeshprasath/monicai, thanks for the alternatives.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33501113
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 6

Author Comment

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

Assisted Solution

by:Bodestone
Bodestone earned 200 total points
ID: 33501323
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
ID: 33501360
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
ID: 33513371
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
ID: 33519043
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
ID: 33535724
Thanks for your insights and ideas into this matter.
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

895 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

13 Experts available now in Live!

Get 1:1 Help Now