Solved

copying proc from sp_helptext and running it breaks lines

Posted on 2010-08-20
12
3,055 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

732 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