Stored procedure script in SQL 2000

My goal here is to create a scripted text file that can be used on another server to create the same stored procedures.  I have been using sp_helptext to script out the create statement, but every method I have used to create the script file results in something different, and unusable.

I know I can create using save results to file in QA and then pull the .rpt into QA from another server, that works fine.  However, I need to automate this and I do not get the same results from DTS... the text file is full of garbage, or fails due to characters.  

Hoping someone can get me pointed back in the right direction on this!  Thanks!
OzarkSunshineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jogosCommented:
As I recall sp_helptext truncates large lines or procedures. From SQL2005 it's eaysier with getting the source (definition) from sys.sql_modules, you can even check if source is equal so you won't have to copy it.  Be carefull about the anssi_nulls settings though if the option can be different.

Another approach  script all your procedures (select database , right click, script database ....) and put your procedures in a version control system. So when you change it in your version control system for applying it on your first server you have it ready for your second server. And you have all the benifits of version control.
 
0
jogosCommented:
And for realy automating it in SQL2000 you could look at SQL-DMO http://msdn.microsoft.com/en-us/library/aa274758(SQL.80).aspx.
But personally I think it's better to upgrade first.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OzarkSunshineAuthor Commented:
Although I'm adding to the list of why my client needs to upgrade, SQL-DMO can get me exactly what I need.  Actually found this to be most helpful as well --

http://www.nigelrivett.net/DMO/DMOScripting.html

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.