Re: A peculiar problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Sheetal (Sheetal_at_discussions.microsoft.com)
Date: 06/11/04 

Date: Fri, 11 Jun 2004 07:26:01 -0700

I am obliged to adopt this way of doing things because of my company's decision..
Anyway, I worked around this problem, and its working :-)

"Louis Davidson" wrote:

> The thing that bothers me about this question is:
>
> > The catch is, I can't use any of the database API's like ADO, VBscript
> etc.
>
> Why not? Because SQL is not the way to do this. Even find and replace
> would be easier.
>
> > I need to alter data types in all my stored procedures and triggers using
> TSQL(~1000 in number)
> > and then to execute them. But it's not only the parameter data types which
> need to be changed,
> > it's all the variables, temp tables etc present in the entire text of a
> stored procedure.I am doing the following:
>
> Text manipulation in SQL Server is very hard, if not impossible. But sounds
> of either bad management or insane punishment homework to me :)
>
> --
> ----------------------------------------------------------------------------
> Louis Davidson (drsql@hotmail.com)
> Compass Technology Management
>
> Pro SQL Server 2000 Database Design
> http://www.apress.com/book/bookDisplay.html?bID=266
>
> Note: Please reply to the newsgroups only unless you are
> interested in consulting services. All other replies will be ignored :)
>
> "Sheetal" <Sheetal@discussions.microsoft.com> wrote in message
> news:9DE82548-8B5D-406E-A965-B9CA2A45A0FD@microsoft.com...
> > Hello newsgroup,
> >
> > I have a problem quite specific to my project.The catch is, I can't use
> any of the database API's like ADO, VBscript etc.I need to alter data types
> in all my stored procedures and triggers using TSQL(~1000 in number) and
> then to execute them. But it's not only the parameter data types which need
> to be changed, it's all the variables, temp tables etc present in the entire
> text of a stored procedure.I am doing the following:
> > 1.Create a batch file with isql utility using an input file and putting
> the result in an output file.My input file does the following:
> > a. List all the SP's in a temp table.
> > b. Use cursors to print the text of each stored procedure one by one
> (sp_helptext)
> > c. Use a SP to serach and replace the regular expressions
> > d. Concatenate the modified syntax into one single line and assign it to a
> text variable (declared a 3rd temp table) usinf 'textptr' and 'updatetext'
> >
> > 2. Export the generated file(with the modified stored procedures) using
> isql to be executed.
> >
> > Only the problem is that they can't be executed since a text variable has
> been used.And EXEC does not allow that.
> > I'd appreciate if you could help me out on this.
> > Sheetal Verghese.
>
>
>

Quantcast