Solved

copying proc from sp_helptext and running it breaks lines

Posted on 2010-08-20
12
2,955 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 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